From 51afb9305c3a45891bd49637827a99631be8b488 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Mon, 3 Jul 2000 16:35:39 +0000 Subject: [PATCH] Have pg_dumpall write CREATE USER and CREATE GROUP commands instead of raw COPYs of pg_shadow and pg_group. It also turns out that pg_dumpall was all but broken for multiple servers running at non-standard port numbers. You might get the users and groups from one server and the databases from another. Fixed that. A little user interface and code cleanup along with that. This also takes care of the portability bug discussed in "[BUGS] pg_dumpall" in March 2000. --- src/bin/pg_dump/Makefile | 14 ++- src/bin/pg_dump/pg_dumpall | 95 --------------- src/bin/pg_dump/pg_dumpall.sh | 211 ++++++++++++++++++++++++++++++++++ 3 files changed, 221 insertions(+), 99 deletions(-) delete mode 100644 src/bin/pg_dump/pg_dumpall create mode 100644 src/bin/pg_dump/pg_dumpall.sh diff --git a/src/bin/pg_dump/Makefile b/src/bin/pg_dump/Makefile index 76c97ad7d4..693913f2d3 100644 --- a/src/bin/pg_dump/Makefile +++ b/src/bin/pg_dump/Makefile @@ -4,7 +4,7 @@ # # Copyright (c) 1994, Regents of the University of California # -# $Header: /cvsroot/pgsql/src/bin/pg_dump/Makefile,v 1.16 2000/06/28 18:29:16 petere Exp $ +# $Header: /cvsroot/pgsql/src/bin/pg_dump/Makefile,v 1.17 2000/07/03 16:35:39 petere Exp $ # #------------------------------------------------------------------------- @@ -17,11 +17,17 @@ OBJS= pg_dump.o common.o $(STRDUP) CFLAGS+= -I$(LIBPQDIR) -all: submake pg_dump$(X) +all: submake pg_dump pg_dumpall -pg_dump$(X): $(OBJS) $(LIBPQDIR)/libpq.a +pg_dump: $(OBJS) $(LIBPQDIR)/libpq.a $(CC) $(CFLAGS) -o $@ $(OBJS) $(LIBPQ) $(LDFLAGS) +pg_dumpall: pg_dumpall.sh + sed -e 's:__VERSION__:$(VERSION):g' \ + -e 's:__MULTIBYTE__:$(MULTIBYTE):g' \ + -e 's:__bindir__:$(bindir):g' \ + < $< > $@ + ../../utils/strdup.o: $(MAKE) -C ../../utils strdup.o @@ -44,7 +50,7 @@ depend dep: $(CC) -MM $(CFLAGS) *.c >depend clean distclean maintainer-clean: - rm -f pg_dump$(X) $(OBJS) + rm -f pg_dump$(X) $(OBJS) pg_dumpall ifeq (depend,$(wildcard depend)) include depend diff --git a/src/bin/pg_dump/pg_dumpall b/src/bin/pg_dump/pg_dumpall deleted file mode 100644 index 99b4eaee21..0000000000 --- a/src/bin/pg_dump/pg_dumpall +++ /dev/null @@ -1,95 +0,0 @@ -#!/bin/sh -# -# pg_dumpall [pg_dump parameters] -# dumps all databases to standard output -# It also dumps the pg_shadow and pg_group tables, which belong to the -# whole installation rather than any one individual database. -# -# $Header: /cvsroot/pgsql/src/bin/pg_dump/Attic/pg_dumpall,v 1.27 2000/05/25 20:18:15 tgl Exp $ -# -# to adapt to System V vs. BSD 'echo' -if echo '\\' | grep '\\\\' >/dev/null 2>&1 -then - BS='\' # BSD -else - BS='\\' # System V -fi -# -# Dump everyone but the postgres user -# initdb creates him -# -# get the postgres user id -# -POSTGRES_SUPER_USER_ID="`echo \" \ - select datdba \ - from pg_database \ - where datname = 'template1'; \" | \ - psql -A -q -t template1`" -echo "${BS}connect template1" -# -# delete all users in case they run this twice -# -# we don't use POSTGRES_SUPER_USER_ID because the postgres super user id -# could be different on the two installations -# -echo "select datdba into table tmp_pg_shadow \ - from pg_database where datname = 'template1';" -echo "delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;" -echo "drop table tmp_pg_shadow;" -# -# load all the non-postgres users -# XXX this breaks badly if the layout of pg_shadow ever changes. -# It'd be better to convert the data into CREATE USER commands. -# -echo "copy pg_shadow from stdin;" -psql -q template1 < $POSTGRES_SUPER_USER_ID; -copy tmp_pg_shadow to stdout; -drop table tmp_pg_shadow; -END -echo "${BS}." -# -# copy the pg_group table too -# XXX this breaks badly if the layout of pg_group ever changes. -# It'd be better to convert the data into CREATE GROUP commands. -# -echo "delete from pg_group;" -echo "copy pg_group from stdin;" -psql -q template1 </dev/null 2>&1 - then - echo "create database \"$DATABASE\" with encoding='`pg_encoding $ENCODING`';" - else - echo "create database \"$DATABASE\";" - fi - - echo "${BS}connect $DATABASE $DBUSERNAME" - pg_dump ${1+"$@"} "$DATABASE" - if [ "$?" -ne 0 ] - then echo "pg_dump failed on $DATABASE, exiting" 1>&2 - exit 1 - fi -done - -exit 0 diff --git a/src/bin/pg_dump/pg_dumpall.sh b/src/bin/pg_dump/pg_dumpall.sh new file mode 100644 index 0000000000..e2ce5472ce --- /dev/null +++ b/src/bin/pg_dump/pg_dumpall.sh @@ -0,0 +1,211 @@ +#! /bin/sh + +# pg_dumpall +# +# Dumps all databases to standard output. It also dumps the "pg_shadow" +# and "pg_group" tables, which belong to the whole installation rather +# than any one individual database. +# +# $Header: /cvsroot/pgsql/src/bin/pg_dump/Attic/pg_dumpall.sh,v 1.1 2000/07/03 16:35:39 petere Exp $ + +CMDNAME=`basename $0` + +# substituted at build +VERSION='__VERSION__' +MULTIBYTE='__MULTIBYTE__' +bindir='__bindir__' + +# +# Find out where we're located +# +PGPATH= +if echo "$0" | grep '/' > /dev/null 2>&1 ; then + # explicit dir name given + PGPATH=`echo $0 | sed 's,/[^/]*$,,'` # (dirname command is not portable) +else + # look for it in PATH ('which' command is not portable) + for dir in `echo "$PATH" | sed 's/:/ /g'` ; do + # empty entry in path means current dir + [ x"$dir" = x ] && dir='.' + if [ -f "$dir/$CMDNAME" ] ; then + PGPATH="$dir" + break + fi + done +fi + +# As last resort use the installation directory. We don't want to use +# this as first resort because depending on how users do release upgrades +# they might temporarily move the installation tree elsewhere, so we'd +# accidentally invoke the newly installed versions of pg_dump and psql. +if [ x"$PGPATH" = x"" ]; then + PGPATH=$bindir +fi + +# +# Look for needed programs +# +for prog in pg_dump psql ; do + if [ ! -x "$PGPATH/$prog" ] ; then + echo "The program $prog needed by $CMDNAME could not be found. It was" + echo "expected at:" + echo " $PGPATH/$prog" + echo "If this is not the correct directory, please start $CMDNAME" + echo "with a full search path. Otherwise make sure that the program" + echo "was installed successfully." + exit 1 + fi +done + +# +# to adapt to System V vs. BSD 'echo' +# +if echo '\\' | grep '\\\\' >/dev/null 2>&1 +then + BS='\' dummy='\' # BSD +else + BS='\\' # System V +fi +# The dummy assignment is necessary to prevent Emacs' font-lock +# mode from going ballistic when editing this file. + + +usage= +cleanschema= + +# +# Scan options. We're interested in the -h (host), -p (port), and -c (clean) options. +# The rest we pass to pg_dump, which may or may not be useful. +# +while [ $# -gt 0 ] ; do + case $1 in + --help) + usage=t + break + ;; + --version) + echo "pg_dumpall (PostgreSQL) $VERSION" + exit 0 + ;; + --host|-h) + connectopts="$connectopts -h $2" + shift;; + -h*) + connectopts="$connectopts $1" + ;; + --host=*) + connectopts="$connectopts -h "`echo $1 | sed 's/^--host=//'` + ;; + --port|-p) + connectopts="$connectopts -p $2" + shift;; + -p*) + connectopts="$connectopts $1" + ;; + --port=*) + connectopts="$connectopts -p "`echo $1 | sed 's/^--port=//'` + ;; + -c|--clean) + cleanschema=yes + pgdumpextraopts="$pgdumpextraopts -c" + ;; + *) + pgdumpextraopts="$pgdumpextraopts $1" + ;; + esac + shift +done + + +if [ "$usage" ] ; then + echo "$CMDNAME dumps a PostgreSQL database cluster." + echo + echo "Usage:" + echo " $CMDNAME [ -c ] [ -h host ] [ -p port ]" + echo + echo "Options:" + echo " -c, --clean clean (drop) schema prior to create" + echo " -h, --host server host name" + echo " -p, --port server port number" + echo "Any extra options will be passed to pg_dump." + echo + echo "Report bugs to ." + exit 0 +fi + + +PSQL="${PGPATH}/psql $connectopts" +PGDUMP="${PGPATH}/pg_dump $connectopts $pgdumpextraopts" + + +echo "--" +echo "-- pg_dumpall ($VERSION) $connectopts $pgdumpextraopts" +echo "--" +echo "${BS}connect template1" + +# +# Dump users (but not the user created by initdb) +# +echo "DELETE FROM pg_shadow WHERE usesysid NOT IN (SELECT datdba FROM pg_database WHERE datname = 'template1');" +echo + +$PSQL -d template1 -At <<__END__ +SELECT + 'CREATE USER "' || usename || '" WITH SYSID ' || usesysid + || CASE WHEN passwd IS NOT NULL THEN ' PASSWORD ''' || passwd || '''' else '' end + || CASE WHEN usecreatedb THEN ' CREATEDB'::text ELSE ' NOCREATEDB' END + || CASE WHEN usesuper THEN ' CREATEUSER'::text ELSE ' NOCREATEUSER' END + || CASE WHEN valuntil IS NOT NULL THEN ' VALID UNTIL '''::text + || CAST(valuntil AS TIMESTAMP) || '''' ELSE '' END || ';' +FROM pg_shadow +WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template1'); +__END__ +echo + +# +# Dump groups +# +echo "DELETE FROM pg_group;" +echo + +$PSQL -d template1 -At -F ' ' -c 'SELECT * FROM pg_group;' | \ +while read GRONAME GROSYSID GROLIST ; do + echo "CREATE GROUP \"$GRONAME\" WITH SYSID ${GROSYSID};" + raw_grolist=`echo "$GROLIST" | sed 's/^{\(.*\)}$/\1/' | tr ',' ' '` + for userid in $raw_grolist ; do + username=`$PSQL -d template1 -At -c "SELECT usename FROM pg_shadow WHERE usesysid = ${userid};"` + echo " ALTER GROUP \"$GRONAME\" ADD USER \"$username\";" + done +done + + +# For each database, run pg_dump to dump the contents of that database. + +$PSQL -d template1 -At -F ' ' \ + -c "SELECT d.datname, u.usename, pg_encoding_to_char(d.encoding) FROM pg_database d, pg_shadow u WHERE d.datdba = u.usesysid AND datname <> 'template1';" | \ +while read DATABASE DBOWNER ENCODING ; do + echo + echo "--" + echo "-- Database $DATABASE" + echo "--" + echo "${BS}connect template1 $DBOWNER" + + if [ "$cleanschema" = yes ] ; then + echo "DROP DATABASE \"$DATABASE\";" + fi + + if [ x"$MULTIBYTE" != x"" ] ; then + echo "CREATE DATABASE \"$DATABASE\" WITH ENCODING = '$ENCODING';" + else + echo "CREATE DATABASE \"$DATABASE\";" + fi + + echo "${BS}connect $DATABASE $DBOWNER" + $PGDUMP -d "$DATABASE" + if [ "$?" -ne 0 ] ; then + echo "pg_dump failed on $DATABASE, exiting" 1>&2 + exit 1 + fi +done + +exit 0 -- 2.30.2