|
| 1 | +#! /bin/sh |
| 2 | + |
| 3 | +# pg_dumpall |
| 4 | +# |
| 5 | +# Dumps all databases to standard output. It also dumps the "pg_shadow" |
| 6 | +# and "pg_group" tables, which belong to the whole installation rather |
| 7 | +# than any one individual database. |
| 8 | +# |
| 9 | +# $Header: /cvsroot/pgsql/src/bin/pg_dump/Attic/pg_dumpall.sh,v 1.1 2000/07/03 16:35:39 petere Exp $ |
| 10 | + |
| 11 | +CMDNAME=`basename$0` |
| 12 | + |
| 13 | +# substituted at build |
| 14 | +VERSION='__VERSION__' |
| 15 | +MULTIBYTE='__MULTIBYTE__' |
| 16 | +bindir='__bindir__' |
| 17 | + |
| 18 | +# |
| 19 | +# Find out where we're located |
| 20 | +# |
| 21 | +PGPATH= |
| 22 | +ifecho"$0"| grep'/'> /dev/null2>&1;then |
| 23 | +# explicit dir name given |
| 24 | + PGPATH=`echo$0| sed's,/[^/]*$,,'`# (dirname command is not portable) |
| 25 | +else |
| 26 | +# look for it in PATH ('which' command is not portable) |
| 27 | +fordirin`echo"$PATH"| sed's/:/ /g'`;do |
| 28 | +# empty entry in path means current dir |
| 29 | + [ x"$dir"= x ]&& dir='.' |
| 30 | +if [-f"$dir/$CMDNAME" ];then |
| 31 | + PGPATH="$dir" |
| 32 | +break |
| 33 | +fi |
| 34 | +done |
| 35 | +fi |
| 36 | + |
| 37 | +# As last resort use the installation directory. We don't want to use |
| 38 | +# this as first resort because depending on how users do release upgrades |
| 39 | +# they might temporarily move the installation tree elsewhere, so we'd |
| 40 | +# accidentally invoke the newly installed versions of pg_dump and psql. |
| 41 | +if [ x"$PGPATH"= x"" ];then |
| 42 | + PGPATH=$bindir |
| 43 | +fi |
| 44 | + |
| 45 | +# |
| 46 | +# Look for needed programs |
| 47 | +# |
| 48 | +forprogin pg_dump psql;do |
| 49 | +if [!-x"$PGPATH/$prog" ];then |
| 50 | +echo"The program$prog needed by$CMDNAME could not be found. It was" |
| 51 | +echo"expected at:" |
| 52 | +echo"$PGPATH/$prog" |
| 53 | +echo"If this is not the correct directory, please start$CMDNAME" |
| 54 | +echo"with a full search path. Otherwise make sure that the program" |
| 55 | +echo"was installed successfully." |
| 56 | +exit 1 |
| 57 | +fi |
| 58 | +done |
| 59 | + |
| 60 | +# |
| 61 | +# to adapt to System V vs. BSD 'echo' |
| 62 | +# |
| 63 | +ifecho'\\'| grep'\\\\'>/dev/null2>&1 |
| 64 | +then |
| 65 | + BS='\' dummy='\'# BSD |
| 66 | +else |
| 67 | + BS='\\'# System V |
| 68 | +fi |
| 69 | +# The dummy assignment is necessary to prevent Emacs' font-lock |
| 70 | +# mode from going ballistic when editing this file. |
| 71 | + |
| 72 | + |
| 73 | +usage= |
| 74 | +cleanschema= |
| 75 | + |
| 76 | +# |
| 77 | +# Scan options. We're interested in the -h (host), -p (port), and -c (clean) options. |
| 78 | +# The rest we pass to pg_dump, which may or may not be useful. |
| 79 | +# |
| 80 | +while [$#-gt 0 ];do |
| 81 | +case$1in |
| 82 | + --help) |
| 83 | + usage=t |
| 84 | +break |
| 85 | + ;; |
| 86 | + --version) |
| 87 | +echo"pg_dumpall (PostgreSQL)$VERSION" |
| 88 | +exit 0 |
| 89 | + ;; |
| 90 | +--host|-h) |
| 91 | +connectopts="$connectopts -h$2" |
| 92 | +shift;; |
| 93 | + -h*) |
| 94 | + connectopts="$connectopts$1" |
| 95 | + ;; |
| 96 | + --host=*) |
| 97 | + connectopts="$connectopts -h"`echo$1| sed's/^--host=//'` |
| 98 | + ;; |
| 99 | +--port|-p) |
| 100 | +connectopts="$connectopts -p$2" |
| 101 | +shift;; |
| 102 | + -p*) |
| 103 | + connectopts="$connectopts$1" |
| 104 | + ;; |
| 105 | + --port=*) |
| 106 | + connectopts="$connectopts -p"`echo$1| sed's/^--port=//'` |
| 107 | + ;; |
| 108 | + -c|--clean) |
| 109 | + cleanschema=yes |
| 110 | + pgdumpextraopts="$pgdumpextraopts -c" |
| 111 | + ;; |
| 112 | +*) |
| 113 | + pgdumpextraopts="$pgdumpextraopts$1" |
| 114 | + ;; |
| 115 | +esac |
| 116 | +shift |
| 117 | +done |
| 118 | + |
| 119 | + |
| 120 | +if ["$usage" ];then |
| 121 | +echo"$CMDNAME dumps a PostgreSQL database cluster." |
| 122 | +echo |
| 123 | +echo"Usage:" |
| 124 | +echo"$CMDNAME [ -c ] [ -h host ] [ -p port ]" |
| 125 | +echo |
| 126 | +echo"Options:" |
| 127 | +echo" -c, --clean clean (drop) schema prior to create" |
| 128 | +echo" -h, --host <hostname> server host name" |
| 129 | +echo" -p, --port <port> server port number" |
| 130 | +echo"Any extra options will be passed to pg_dump." |
| 131 | +echo |
| 132 | +echo"Report bugs to <pgsql-bugs@postgresql.org>." |
| 133 | +exit 0 |
| 134 | +fi |
| 135 | + |
| 136 | + |
| 137 | +PSQL="${PGPATH}/psql$connectopts" |
| 138 | +PGDUMP="${PGPATH}/pg_dump$connectopts$pgdumpextraopts" |
| 139 | + |
| 140 | + |
| 141 | +echo"--" |
| 142 | +echo"-- pg_dumpall ($VERSION)$connectopts$pgdumpextraopts" |
| 143 | +echo"--" |
| 144 | +echo"${BS}connect template1" |
| 145 | + |
| 146 | +# |
| 147 | +# Dump users (but not the user created by initdb) |
| 148 | +# |
| 149 | +echo"DELETE FROM pg_shadow WHERE usesysid NOT IN (SELECT datdba FROM pg_database WHERE datname = 'template1');" |
| 150 | +echo |
| 151 | + |
| 152 | +$PSQL -d template1 -At<<__END__ |
| 153 | +SELECT |
| 154 | + 'CREATE USER "' || usename || '" WITH SYSID ' || usesysid |
| 155 | + || CASE WHEN passwd IS NOT NULL THEN ' PASSWORD ''' || passwd || '''' else '' end |
| 156 | + || CASE WHEN usecreatedb THEN ' CREATEDB'::text ELSE ' NOCREATEDB' END |
| 157 | + || CASE WHEN usesuper THEN ' CREATEUSER'::text ELSE ' NOCREATEUSER' END |
| 158 | + || CASE WHEN valuntil IS NOT NULL THEN ' VALID UNTIL '''::text |
| 159 | + || CAST(valuntil AS TIMESTAMP) || '''' ELSE '' END || ';' |
| 160 | +FROM pg_shadow |
| 161 | +WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template1'); |
| 162 | +__END__ |
| 163 | +echo |
| 164 | + |
| 165 | +# |
| 166 | +# Dump groups |
| 167 | +# |
| 168 | +echo"DELETE FROM pg_group;" |
| 169 | +echo |
| 170 | + |
| 171 | +$PSQL -d template1 -At -F'' -c'SELECT * FROM pg_group;'| \ |
| 172 | +whileread GRONAME GROSYSID GROLIST;do |
| 173 | +echo"CREATE GROUP\"$GRONAME\" WITH SYSID${GROSYSID};" |
| 174 | + raw_grolist=`echo"$GROLIST"| sed's/^{\(.*\)}$/\1/'| tr','''` |
| 175 | +foruseridin$raw_grolist;do |
| 176 | + username=`$PSQL -d template1 -At -c"SELECT usename FROM pg_shadow WHERE usesysid =${userid};"` |
| 177 | +echo" ALTER GROUP\"$GRONAME\" ADD USER\"$username\";" |
| 178 | +done |
| 179 | +done |
| 180 | + |
| 181 | + |
| 182 | +# For each database, run pg_dump to dump the contents of that database. |
| 183 | + |
| 184 | +$PSQL -d template1 -At -F'' \ |
| 185 | + -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';"| \ |
| 186 | +whileread DATABASE DBOWNER ENCODING;do |
| 187 | +echo |
| 188 | +echo"--" |
| 189 | +echo"-- Database$DATABASE" |
| 190 | +echo"--" |
| 191 | +echo"${BS}connect template1$DBOWNER" |
| 192 | + |
| 193 | +if ["$cleanschema"= yes ];then |
| 194 | +echo"DROP DATABASE\"$DATABASE\";" |
| 195 | +fi |
| 196 | + |
| 197 | +if [ x"$MULTIBYTE"!= x"" ];then |
| 198 | +echo"CREATE DATABASE\"$DATABASE\" WITH ENCODING = '$ENCODING';" |
| 199 | +else |
| 200 | +echo"CREATE DATABASE\"$DATABASE\";" |
| 201 | +fi |
| 202 | + |
| 203 | +echo"${BS}connect$DATABASE$DBOWNER" |
| 204 | +$PGDUMP -d"$DATABASE" |
| 205 | +if ["$?"-ne 0 ];then |
| 206 | +echo"pg_dump failed on$DATABASE, exiting"1>&2 |
| 207 | +exit 1 |
| 208 | +fi |
| 209 | +done |
| 210 | + |
| 211 | +exit 0 |