pg_dumpall

text/plain

Download

#! /bin/sh

#
# pg_dumpall [pg_dump parameters]
# dumps all databases to standard output
# It also dumps the pg_shadow table
#
# $Header$
#

CMDNAME=`basename $0`
PATHNAME="`echo $0 | sed "s,$CMDNAME\$,,"`"

# 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=`${PATHNAME}/psql -XAqt -d template1 -c \
    "select datdba from pg_database where datname = '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
#
echo "copy pg_shadow from stdin;"
${PATHNAME}/psql -Xq -d template1 <<END
select * into table tmp_pg_shadow from pg_shadow where usesysid <> $POSTGRES_SUPER_USER_ID;
copy tmp_pg_shadow to stdout;
drop table tmp_pg_shadow;
END
echo "${BS}."

${PATHNAME}/psql -AXqt -F ' ' -d template1 -c "select * from pg_database where datname <> 'template1'" | \
while read DATABASE DBUSERID ENCODING DATAPATH
do
	POSTGRES_USER="`${PATHNAME}/psql -AXqt -d template1 -c \
            "select usename from pg_shadow where usesysid = $DBUSERID"`"
	echo "${BS}connect template1 $POSTGRES_USER"

	if [ $ENCODING -ne 0 ];
	then
		echo "create database $DATABASE with encoding=$ENCODING;"
	else
		echo "create database $DATABASE;"
	fi

	echo "${BS}connect $DATABASE $POSTGRES_USER"
	${PATHNAME}/pg_dump ${1+"$@"} $DATABASE
	if [ "$?" -ne 0 ]
	then
        	echo "pg_dump failed on $DATABASE, exiting" 1>&2
		exit 1
	fi
done