1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
|
#! /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.5 2000/10/25 10:21:38 pjw 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 <hostname> server host name"
echo " -p, --port <port> server port number"
echo "Any extra options will be passed to pg_dump."
echo
echo "Report bugs to <pgsql-bugs@postgresql.org>."
exit 0
fi
PSQL="${PGPATH}/psql $connectopts"
PGDUMP="${PGPATH}/pg_dump $connectopts $pgdumpextraopts -Fp"
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
# First we dump the template in case there are local extensions.
echo
echo "--"
echo "-- Database template1"
echo "--"
echo "${BS}connect template1"
$PGDUMP "template1"
if [ "$?" -ne 0 ] ; then
echo "pg_dump failed on template1, exiting" 1>&2
exit 1
fi
# 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 "$DATABASE"
if [ "$?" -ne 0 ] ; then
echo "pg_dump failed on $DATABASE, exiting" 1>&2
exit 1
fi
done
exit 0
|
