2 #-------------------------------------------------------------------------
4 # initdb creates (initializes) a PostgreSQL database cluster (site,
5 # instance, installation, whatever). A database cluster is a
6 # collection of PostgreSQL databases all managed by the same postmaster.
8 # To create the database cluster, we create the directory that contains
9 # all its data, create the files that hold the global tables, create
10 # a few other control files for it, and create two databases: the
11 # template0 and template1 databases.
13 # The template databases are ordinary PostgreSQL databases. template0
14 # is never supposed to change after initdb, whereas template1 can be
15 # changed to add site-local standard data. Either one can be copied
16 # to produce a new database.
18 # To create template1, we run the postgres (backend) program in bootstrap
19 # mode and feed it data from the postgres.bki library file. After this
20 # initial bootstrap phase, some additional stuff is created by normal
21 # SQL commands fed to a standalone backend. Those commands are just
22 # embedded into this script (yeah, it's ugly).
24 # template0 is made just by copying the completed template1.
27 # Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
28 # Portions Copyright (c) 1994, Regents of the University of California
30 # $Header: /cvsroot/pgsql/src/bin/initdb/Attic/initdb.sh,v 1.142 2001/11/25 22:19:30 petere Exp $
32 #-------------------------------------------------------------------------
35 ##########################################################################
40 stty echo > /dev/null 2>&1
42 echo "$CMDNAME failed." 1>&2
43 if [ "$noclean" != yes ]; then
44 if [ "$made_new_pgdata" = yes ]; then
45 echo "Removing $PGDATA." 1>&2
46 rm -rf "$PGDATA" || echo "Failed." 1>&2
49 echo "Data directory $PGDATA will not be removed at user's request." 1>&2
57 # Placed here during build
60 # Note that "datadir" is not the directory we're initializing, it's
61 # merely how Autoconf names PREFIX/share.
63 # as set by configure --enable-multibyte[=XXX].
64 MULTIBYTE='@MULTIBYTE@'
67 # Check for echo -n vs echo \c
68 if echo '\c' | grep -s c >/dev/null 2>&1
79 # Find out where we're located
81 if echo "$0" | grep '/' > /dev/null 2>&1
83 # explicit dir name given
84 self_path=`echo $0 | sed 's,/[^/]*$,,'` # (dirname command is not portable)
86 # look for it in PATH ('which' command is not portable)
87 for dir in `echo "$PATH" | sed 's/:/ /g'`
89 # empty entry in path means current dir
90 [ -z "$dir" ] && dir='.'
91 if [ -f "$dir/$CMDNAME" ]
100 # Check for right version of backend. First we check for an
101 # executable in the same directory is this initdb script (presuming
102 # the above code worked). Then we fall back to the hard-wired bindir.
103 # We do it in this order because during upgrades users might move
104 # their trees to backup places, so $bindir might be inaccurate.
106 if [ x"$self_path" != x"" ] \
107 && [ -x "$self_path/postgres" ] \
108 && [ x"`$self_path/postgres -V 2>/dev/null`" = x"postgres (PostgreSQL) $VERSION" ]
111 elif [ -x "$bindir/postgres" ]; then
112 if [ x"`$bindir/postgres -V 2>/dev/null`" = x"postgres (PostgreSQL) $VERSION" ]
116 # Maybe there was an error message?
117 errormsg=`$bindir/postgres -V 2>&1 >/dev/null`
120 echo " '$bindir/postgres'"
121 echo "needed by $CMDNAME does not belong to PostgreSQL version $VERSION, or"
122 echo "there may be a configuration problem."
123 if test x"$errormsg" != x""; then
125 echo "This was the error message issued by that program:"
132 echo "The program 'postgres' is needed by $CMDNAME but was not found in" 1>&2
133 echo "the directory '$bindir'. Check your installation." 1>&2
138 # Now we can assume that 'pg_id' belongs to the same version as the
139 # verified 'postgres' in the same directory.
140 if [ ! -x "$PGPATH/pg_id" ]; then
141 echo "The program 'pg_id' is needed by $CMDNAME but was not found in" 1>&2
142 echo "the directory '$PGPATH'. Check your installation." 1>&2
147 EffectiveUser=`$PGPATH/pg_id -n -u`
148 if [ -z "$EffectiveUser" ]; then
149 echo "$CMDNAME: could not determine current user name" 1>&2
153 if [ `$PGPATH/pg_id -u` -eq 0 ]
155 echo "You cannot run $CMDNAME as root. Please log in (using, e.g., 'su')" 1>&2
156 echo "as the (unprivileged) user that will own the server process." 1>&2
161 short_version=`echo $VERSION | sed -e 's!^\([0-9][0-9]*\.[0-9][0-9]*\).*!\1!'`
162 if [ x"$short_version" = x"" ] ; then
163 echo "$CMDNAME: bug: version number has wrong format" 1>&2
168 ##########################################################################
170 # COMMAND LINE OPTIONS
172 # 0 is the default (non-)encoding
180 # Note: There is a single compelling reason that the name of the database
181 # superuser be the same as the Unix user owning the server process:
182 # The single user postgres backend will only connect as the database
183 # user with the same name as the Unix user running it. That's
184 # a security measure.
185 POSTGRES_SUPERUSERNAME="$EffectiveUser"
195 echo "initdb (PostgreSQL) $VERSION"
200 echo "Running with debug mode on."
207 echo "Running with noclean mode on. Mistakes will not be cleaned up."
209 # The name of the database superuser. Can be freely changed.
211 POSTGRES_SUPERUSERNAME="$2"
214 POSTGRES_SUPERUSERNAME=`echo $1 | sed 's/^--username=//'`
217 POSTGRES_SUPERUSERNAME=`echo $1 | sed 's/^-U//'`
219 # The default password of the database superuser.
220 # Make initdb prompt for the default password of the database superuser.
224 # Directory where to install the data. No default, unless the environment
225 # variable PGDATA is set.
230 PGDATA=`echo $1 | sed 's/^--pgdata=//'`
233 PGDATA=`echo $1 | sed 's/^-D//'`
235 # The directory where the .bki input files are stored. Normally
236 # they are in PREFIX/share and this option should be unnecessary.
241 datadir=`echo $1 | sed 's/^-L//'`
243 # The encoding of the template1 database. Defaults to what you chose
244 # at configure time. (see above)
249 MULTIBYTE=`echo $1 | sed 's/^--encoding=//'`
252 MULTIBYTE=`echo $1 | sed 's/^-E//'`
255 echo "$CMDNAME: invalid option: $1"
256 echo "Try '$CMDNAME --help' for more information."
266 if [ "$usage" ]; then
267 echo "$CMDNAME initializes a PostgreSQL database cluster."
270 echo " $CMDNAME [options] datadir"
273 echo " [-D, --pgdata] DATADIR Location for this database cluster"
274 echo " -W, --pwprompt Prompt for a password for the new superuser"
275 if [ -n "$MULTIBYTE" ] ; then
276 echo " -E, --encoding ENCODING Set default encoding for new databases"
278 echo " -U, --username NAME Database superuser name"
279 echo "Less commonly used options: "
280 echo " -L DIRECTORY Where to find the input files"
281 echo " -d, --debug Generate lots of debugging output"
282 echo " -n, --noclean Do not clean up after errors"
284 echo "Report bugs to <pgsql-bugs@postgresql.org>."
288 #-------------------------------------------------------------------------
289 # Resolve the multibyte encoding name
290 #-------------------------------------------------------------------------
294 MULTIBYTEID=`$PGPATH/pg_encoding -b $MULTIBYTE`
298 echo "$CMDNAME: pg_encoding failed"
300 echo "Perhaps you did not configure PostgreSQL for multibyte support or"
301 echo "the program was not successfully installed."
305 if [ -z "$MULTIBYTEID" ]
307 echo "$CMDNAME: $MULTIBYTE is not a valid backend encoding name" 1>&2
313 #-------------------------------------------------------------------------
314 # Make sure he told us where to build the database system
315 #-------------------------------------------------------------------------
320 echo "$CMDNAME: You must identify where the the data for this database"
321 echo "system will reside. Do this with either a -D invocation"
322 echo "option or a PGDATA environment variable."
328 #-------------------------------------------------------------------------
329 # Find the input files
330 #-------------------------------------------------------------------------
332 POSTGRES_BKI="$datadir"/postgres.bki
333 POSTGRES_DESCR="$datadir"/postgres.description
335 PG_HBA_SAMPLE="$datadir"/pg_hba.conf.sample
336 PG_IDENT_SAMPLE="$datadir"/pg_ident.conf.sample
337 POSTGRESQL_CONF_SAMPLE="$datadir"/postgresql.conf.sample
339 if [ "$show_setting" = yes ] || [ "$debug" = yes ]
343 echo "initdb variables:"
344 for var in PGDATA datadir PGPATH MULTIBYTE MULTIBYTEID \
345 POSTGRES_SUPERUSERNAME POSTGRES_BKI \
346 POSTGRES_DESCR POSTGRESQL_CONF_SAMPLE \
347 PG_HBA_SAMPLE PG_IDENT_SAMPLE ; do
348 eval "echo ' '$var=\$$var"
353 if [ "$show_setting" = yes ] ; then
357 for PREREQ_FILE in "$POSTGRES_BKI" "$POSTGRES_DESCR" \
358 "$PG_HBA_SAMPLE" "$PG_IDENT_SAMPLE" "$POSTGRESQL_CONF_SAMPLE"
360 if [ ! -f "$PREREQ_FILE" ] ; then
362 echo "$CMDNAME does not find the file '$PREREQ_FILE'."
363 echo "This means you have a corrupted installation or identified the"
364 echo "wrong directory with the -L invocation option."
370 for file in "$POSTGRES_BKI"
372 if [ x"`sed 1q $file`" != x"# PostgreSQL $short_version" ]; then
374 echo "The input file '$file' needed by $CMDNAME does not"
375 echo "belong to PostgreSQL $VERSION. Check your installation or specify the"
376 echo "correct path using the -L option."
383 trap 'echo "Caught signal." ; exit_nicely' 1 2 3 15
386 echo "The files belonging to this database system will be owned by user \"$EffectiveUser\"."
387 echo "This user must also own the server process."
390 ##########################################################################
392 # CREATE DATABASE DIRECTORY
394 # umask must disallow access to group, other for files and dirs
397 # find out if directory is empty
398 pgdata_contents=`ls -A "$PGDATA" 2>/dev/null`
399 if [ x"$pgdata_contents" != x ]
402 echo "$CMDNAME: The directory $PGDATA exists but is not empty."
403 echo "If you want to create a new database system, either remove or empty"
404 echo "the directory $PGDATA or run initdb with"
405 echo "an argument other than $PGDATA."
409 if [ ! -d "$PGDATA" ]; then
410 $ECHO_N "creating directory $PGDATA... "$ECHO_C
411 mkdir -p "$PGDATA" >/dev/null 2>&1 || mkdir "$PGDATA" || exit_nicely
414 $ECHO_N "Fixing permissions on existing directory $PGDATA... "$ECHO_C
415 chmod go-rwx "$PGDATA" || exit_nicely
419 if [ ! -d "$PGDATA"/base ]
421 $ECHO_N "creating directory $PGDATA/base... "$ECHO_C
422 mkdir "$PGDATA"/base || exit_nicely
425 if [ ! -d "$PGDATA"/global ]
427 $ECHO_N "creating directory $PGDATA/global... "$ECHO_C
428 mkdir "$PGDATA"/global || exit_nicely
431 if [ ! -d "$PGDATA"/pg_xlog ]
433 $ECHO_N "creating directory $PGDATA/pg_xlog... "$ECHO_C
434 mkdir "$PGDATA"/pg_xlog || exit_nicely
437 if [ ! -d "$PGDATA"/pg_clog ]
439 $ECHO_N "creating directory $PGDATA/pg_clog... "$ECHO_C
440 mkdir "$PGDATA"/pg_clog || exit_nicely
446 ##########################################################################
448 # RUN BKI SCRIPT IN BOOTSTRAP MODE TO CREATE TEMPLATE1
450 # common backend options
451 PGSQL_OPT="-F -D$PGDATA"
453 if [ "$debug" = yes ]
455 BACKEND_TALK_ARG="-d"
457 PGSQL_OPT="$PGSQL_OPT -o /dev/null"
461 $ECHO_N "creating template1 database in $PGDATA/base/1... "$ECHO_C
463 rm -rf "$PGDATA"/base/1 || exit_nicely
464 mkdir "$PGDATA"/base/1 || exit_nicely
466 # Top level PG_VERSION is checked by bootstrapper, so make it first
467 echo "$short_version" > "$PGDATA/PG_VERSION" || exit_nicely
469 cat "$POSTGRES_BKI" \
470 | sed -e "s/POSTGRES/$POSTGRES_SUPERUSERNAME/g" \
471 -e "s/ENCODING/$MULTIBYTEID/g" \
472 | "$PGPATH"/postgres -boot -x1 $PGSQL_OPT $BACKEND_TALK_ARG template1 \
475 # Make the per-database PGVERSION for template1 only after init'ing it
476 echo "$short_version" > "$PGDATA/base/1/PG_VERSION" || exit_nicely
480 ##########################################################################
482 # CREATE CONFIG FILES
484 $ECHO_N "creating configuration files... "$ECHO_C
486 cp "$PG_HBA_SAMPLE" "$PGDATA"/pg_hba.conf || exit_nicely
487 cp "$PG_IDENT_SAMPLE" "$PGDATA"/pg_ident.conf || exit_nicely
488 cp "$POSTGRESQL_CONF_SAMPLE" "$PGDATA"/postgresql.conf || exit_nicely
489 chmod 0600 "$PGDATA"/pg_hba.conf "$PGDATA"/pg_ident.conf \
490 "$PGDATA"/postgresql.conf
494 ##########################################################################
496 # CREATE VIEWS and other things
498 # NOTE: because here we are driving a standalone backend (not psql), we must
499 # follow the standalone backend's convention that commands end at a newline.
500 # To break an SQL command across lines in this script, backslash-escape all
501 # internal newlines in the command.
503 PGSQL_OPT="$PGSQL_OPT -O"
505 $ECHO_N "initializing pg_shadow... "$ECHO_C
507 "$PGPATH"/postgres $PGSQL_OPT template1 >/dev/null <<EOF
508 -- Create a trigger so that direct updates to pg_shadow will be written
509 -- to the flat password file pg_pwd
510 CREATE TRIGGER pg_sync_pg_pwd AFTER INSERT OR UPDATE OR DELETE ON pg_shadow \
511 FOR EACH ROW EXECUTE PROCEDURE update_pg_pwd();
512 -- needs to be done before alter user, because alter user checks that
513 -- pg_shadow is secure ...
514 REVOKE ALL on pg_shadow FROM public;
516 if [ "$?" -ne 0 ]; then
522 if [ "$PwPrompt" ]; then
523 $ECHO_N "Enter new superuser password: "$ECHO_C
524 stty -echo > /dev/null 2>&1
526 stty echo > /dev/null 2>&1
528 $ECHO_N "Enter it again: "$ECHO_C
529 stty -echo > /dev/null 2>&1
531 stty echo > /dev/null 2>&1
533 if [ "$FirstPw" != "$SecondPw" ]; then
534 echo "Passwords didn't match." 1>&2
537 $ECHO_N "setting password... "$ECHO_C
538 "$PGPATH"/postgres $PGSQL_OPT template1 >/dev/null <<EOF
539 ALTER USER "$POSTGRES_SUPERUSERNAME" WITH PASSWORD '$FirstPw';
541 if [ "$?" -ne 0 ]; then
544 if [ ! -f "$PGDATA"/global/pg_pwd ]; then
546 echo "The password file wasn't generated. Please report this problem." 1>&2
553 $ECHO_N "enabling unlimited row size for system tables... "$ECHO_C
555 "$PGPATH"/postgres $PGSQL_OPT template1 >/dev/null <<EOF
556 ALTER TABLE pg_attrdef CREATE TOAST TABLE;
557 ALTER TABLE pg_description CREATE TOAST TABLE;
558 ALTER TABLE pg_proc CREATE TOAST TABLE;
559 ALTER TABLE pg_relcheck CREATE TOAST TABLE;
560 ALTER TABLE pg_rewrite CREATE TOAST TABLE;
561 ALTER TABLE pg_statistic CREATE TOAST TABLE;
563 if [ "$?" -ne 0 ]; then
569 $ECHO_N "creating system views... "$ECHO_C
571 "$PGPATH"/postgres $PGSQL_OPT template1 >/dev/null <<EOF
573 CREATE VIEW pg_user AS \
581 '********'::text as passwd, \
585 CREATE VIEW pg_rules AS \
587 C.relname AS tablename, \
588 R.rulename AS rulename, \
589 pg_get_ruledef(R.rulename) AS definition \
590 FROM pg_rewrite R, pg_class C \
591 WHERE R.rulename !~ '^_RET' \
592 AND C.oid = R.ev_class;
594 CREATE VIEW pg_views AS \
596 C.relname AS viewname, \
597 pg_get_userbyid(C.relowner) AS viewowner, \
598 pg_get_viewdef(C.relname) AS definition \
600 WHERE C.relkind = 'v';
602 -- XXX why does pg_tables include sequences?
604 CREATE VIEW pg_tables AS \
606 C.relname AS tablename, \
607 pg_get_userbyid(C.relowner) AS tableowner, \
608 C.relhasindex AS hasindexes, \
609 C.relhasrules AS hasrules, \
610 (C.reltriggers > 0) AS hastriggers \
612 WHERE C.relkind IN ('r', 's');
614 CREATE VIEW pg_indexes AS \
616 C.relname AS tablename, \
617 I.relname AS indexname, \
618 pg_get_indexdef(X.indexrelid) AS indexdef \
619 FROM pg_index X, pg_class C, pg_class I \
620 WHERE C.relkind = 'r' AND I.relkind = 'i' \
621 AND C.oid = X.indrelid \
622 AND I.oid = X.indexrelid;
624 CREATE VIEW pg_stats AS \
626 relname AS tablename, \
627 attname AS attname, \
628 stanullfrac AS null_frac, \
629 stawidth AS avg_width, \
630 stadistinct AS n_distinct, \
632 WHEN stakind1 THEN stavalues1 \
633 WHEN stakind2 THEN stavalues2 \
634 WHEN stakind3 THEN stavalues3 \
635 WHEN stakind4 THEN stavalues4 \
636 END AS most_common_vals, \
638 WHEN stakind1 THEN stanumbers1 \
639 WHEN stakind2 THEN stanumbers2 \
640 WHEN stakind3 THEN stanumbers3 \
641 WHEN stakind4 THEN stanumbers4 \
642 END AS most_common_freqs, \
644 WHEN stakind1 THEN stavalues1 \
645 WHEN stakind2 THEN stavalues2 \
646 WHEN stakind3 THEN stavalues3 \
647 WHEN stakind4 THEN stavalues4 \
648 END AS histogram_bounds, \
650 WHEN stakind1 THEN stanumbers1[1] \
651 WHEN stakind2 THEN stanumbers2[1] \
652 WHEN stakind3 THEN stanumbers3[1] \
653 WHEN stakind4 THEN stanumbers4[1] \
655 FROM pg_class c, pg_attribute a, pg_statistic s \
656 WHERE c.oid = s.starelid AND c.oid = a.attrelid \
657 AND a.attnum = s.staattnum \
658 AND has_table_privilege(c.oid, 'select');
660 REVOKE ALL on pg_statistic FROM public;
662 CREATE VIEW pg_stat_all_tables AS \
665 C.relname AS relname, \
666 pg_stat_get_numscans(C.oid) AS seq_scan, \
667 pg_stat_get_tuples_returned(C.oid) AS seq_tup_read, \
668 sum(pg_stat_get_numscans(I.indexrelid)) AS idx_scan, \
669 sum(pg_stat_get_tuples_fetched(I.indexrelid)) AS idx_tup_fetch, \
670 pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins, \
671 pg_stat_get_tuples_updated(C.oid) AS n_tup_upd, \
672 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del \
673 FROM pg_class C LEFT OUTER JOIN \
674 pg_index I ON C.oid = I.indrelid \
675 WHERE C.relkind = 'r' \
676 GROUP BY C.oid, C.relname;
678 CREATE VIEW pg_stat_sys_tables AS \
679 SELECT * FROM pg_stat_all_tables \
680 WHERE relname ~ '^pg_';
682 CREATE VIEW pg_stat_user_tables AS \
683 SELECT * FROM pg_stat_all_tables \
684 WHERE relname !~ '^pg_';
686 CREATE VIEW pg_statio_all_tables AS \
689 C.relname AS relname, \
690 pg_stat_get_blocks_fetched(C.oid) - \
691 pg_stat_get_blocks_hit(C.oid) AS heap_blks_read, \
692 pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit, \
693 sum(pg_stat_get_blocks_fetched(I.indexrelid) - \
694 pg_stat_get_blocks_hit(I.indexrelid)) AS idx_blks_read, \
695 sum(pg_stat_get_blocks_hit(I.indexrelid)) AS idx_blks_hit, \
696 pg_stat_get_blocks_fetched(T.oid) - \
697 pg_stat_get_blocks_hit(T.oid) AS toast_blks_read, \
698 pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit, \
699 pg_stat_get_blocks_fetched(X.oid) - \
700 pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read, \
701 pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit \
702 FROM pg_class C LEFT OUTER JOIN \
703 pg_index I ON C.oid = I.indrelid LEFT OUTER JOIN \
704 pg_class T ON C.reltoastrelid = T.oid LEFT OUTER JOIN \
705 pg_class X ON T.reltoastidxid = X.oid \
706 WHERE C.relkind = 'r' \
707 GROUP BY C.oid, C.relname, T.oid, X.oid;
709 CREATE VIEW pg_statio_sys_tables AS \
710 SELECT * FROM pg_statio_all_tables \
711 WHERE relname ~ '^pg_';
713 CREATE VIEW pg_statio_user_tables AS \
714 SELECT * FROM pg_statio_all_tables \
715 WHERE relname !~ '^pg_';
717 CREATE VIEW pg_stat_all_indexes AS \
720 I.oid AS indexrelid, \
721 C.relname AS relname, \
722 I.relname AS indexrelname, \
723 pg_stat_get_numscans(I.oid) AS idx_scan, \
724 pg_stat_get_tuples_returned(I.oid) AS idx_tup_read, \
725 pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch \
729 WHERE C.relkind = 'r' AND \
730 X.indrelid = C.oid AND \
731 X.indexrelid = I.oid;
733 CREATE VIEW pg_stat_sys_indexes AS \
734 SELECT * FROM pg_stat_all_indexes \
735 WHERE relname ~ '^pg_';
737 CREATE VIEW pg_stat_user_indexes AS \
738 SELECT * FROM pg_stat_all_indexes \
739 WHERE relname !~ '^pg_';
741 CREATE VIEW pg_statio_all_indexes AS \
744 I.oid AS indexrelid, \
745 C.relname AS relname, \
746 I.relname AS indexrelname, \
747 pg_stat_get_blocks_fetched(I.oid) - \
748 pg_stat_get_blocks_hit(I.oid) AS idx_blks_read, \
749 pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit \
753 WHERE C.relkind = 'r' AND \
754 X.indrelid = C.oid AND \
755 X.indexrelid = I.oid;
757 CREATE VIEW pg_statio_sys_indexes AS \
758 SELECT * FROM pg_statio_all_indexes \
759 WHERE relname ~ '^pg_';
761 CREATE VIEW pg_statio_user_indexes AS \
762 SELECT * FROM pg_statio_all_indexes \
763 WHERE relname !~ '^pg_';
765 CREATE VIEW pg_statio_all_sequences AS \
768 C.relname AS relname, \
769 pg_stat_get_blocks_fetched(C.oid) - \
770 pg_stat_get_blocks_hit(C.oid) AS blks_read, \
771 pg_stat_get_blocks_hit(C.oid) AS blks_hit \
773 WHERE C.relkind = 'S';
775 CREATE VIEW pg_statio_sys_sequences AS \
776 SELECT * FROM pg_statio_all_sequences \
777 WHERE relname ~ '^pg_';
779 CREATE VIEW pg_statio_user_sequences AS \
780 SELECT * FROM pg_statio_all_sequences \
781 WHERE relname !~ '^pg_';
783 CREATE VIEW pg_stat_activity AS \
786 D.datname AS datname, \
787 pg_stat_get_backend_pid(S.backendid) AS procpid, \
788 pg_stat_get_backend_userid(S.backendid) AS usesysid, \
789 U.usename AS usename, \
790 pg_stat_get_backend_activity(S.backendid) AS current_query \
791 FROM pg_database D, \
792 (SELECT pg_stat_get_backend_idset() AS backendid) AS S, \
794 WHERE pg_stat_get_backend_dbid(S.backendid) = D.oid AND \
795 pg_stat_get_backend_userid(S.backendid) = U.usesysid;
797 CREATE VIEW pg_stat_database AS \
800 D.datname AS datname, \
801 pg_stat_get_db_numbackends(D.oid) AS numbackends, \
802 pg_stat_get_db_xact_commit(D.oid) AS xact_commit, \
803 pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback, \
804 pg_stat_get_db_blocks_fetched(D.oid) - \
805 pg_stat_get_db_blocks_hit(D.oid) AS blks_read, \
806 pg_stat_get_db_blocks_hit(D.oid) AS blks_hit \
810 if [ "$?" -ne 0 ]; then
815 $ECHO_N "loading pg_description... "$ECHO_C
818 CREATE TEMP TABLE tmp_pg_description ( \
822 description text) WITHOUT OIDS;
823 COPY tmp_pg_description FROM STDIN;
825 cat "$POSTGRES_DESCR"
828 INSERT INTO pg_description SELECT \
829 t.objoid, c.oid, t.objsubid, t.description \
830 FROM tmp_pg_description t, pg_class c WHERE c.relname = t.classname;
833 | "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
836 $ECHO_N "vacuuming database template1... "$ECHO_C
838 "$PGPATH"/postgres $PGSQL_OPT template1 >/dev/null <<EOF
841 if [ "$?" -ne 0 ]; then
846 $ECHO_N "copying template1 to template0... "$ECHO_C
848 "$PGPATH"/postgres $PGSQL_OPT template1 >/dev/null <<EOF
849 CREATE DATABASE template0;
851 UPDATE pg_database SET \
852 datistemplate = 't', \
854 WHERE datname = 'template0';
856 -- We use the OID of template0 to determine lastsysoid
858 UPDATE pg_database SET datlastsysoid = \
859 (SELECT oid - 1 FROM pg_database WHERE datname = 'template0');
861 VACUUM FULL pg_database;
863 if [ "$?" -ne 0 ]; then
869 ##########################################################################
874 echo "Success. You can now start the database server using:"
876 echo " $PGPATH/postmaster -D $PGDATA"
878 # (Advertise -l option here, otherwise we have a background
879 # process writing to the terminal.)
880 echo " $PGPATH/pg_ctl -D $PGDATA -l logfile start"