3
# pg_upgrade: update a database without needing a full dump/reload cycle.
4
# CAUTION: Read the manual page before trying to use this!
6
# $PostgreSQL: pgsql/contrib/pg_upgrade/pg_upgrade,v 1.16 2003-11-29 19:51:35 pgsql Exp $
8
# To migrate this to newer versions of PostgreSQL:
9
# 1) Update the version numbers at the top of the file
10
# 2) Search for specific version mentions in the script and update
12
# 3) Add changes for next version.
16
# UPGRADE_VERSION is the expected old database version
20
# Set this to "Y" to enable this program
23
if [ "$ENABLE" != "Y" ]
25
echo "Sorry, $0 cannot upgrade database version $SRC_VERSION to $DST_VERSION." 1>&2
26
echo "The on-disk structure of tables has changed." 1>&2
27
echo "You will need to dump and restore using pg_dumpall." 1>&2
32
trap "rm -f /tmp/$$.*" 0 1 2 3 15
34
BASENAME=`basename "$0"`
42
elif [ "X$1" = "X-2" ]
45
elif [ "X$1" = "X-D" ]
52
then echo "You must run $BASENAME in either mode 1 or mode 2." 1>&2
53
echo "Usage: $BASENAME [-D datadir] -1 | -2" 1>&2
58
then echo "You must set the PGDATA environment variable or specify it with -D." 1>&2
59
echo "Usage: $BASENAME [-D datadir] -1 | -2" 1>&2
64
then echo "$PGDATA does not exist. Exiting." 1>&2
66
then echo "Perhaps you didn't run initdb." 1>&2
71
if [ "$USER" = "root" -o ! -r "$PGDATA"/PG_VERSION ]
72
then echo "You must run this as the PostgreSQL superuser. Exiting." 1>&2
76
# Strip off the trailing directory name and store our data there
77
# in the hope we are in the same filesystem so 'mv 'works.
79
INFODIR=`dirname "$PGDATA"`/pg_upgrade_info
80
SAVEDATA="$INFODIR"/data
84
psql -d template1 -At -c "SELECT datname FROM pg_database" |
85
grep -v '^template0$' |
88
QUERY="SELECT relname, oid
90
WHERE relkind = 'r' OR
95
psql -d "$DB" -At -F' ' -c "$QUERY" |
96
while read RELNAME_OID
98
echo "$DB $RELNAME_OID"
106
psql -d template1 -At -F' ' -c \
107
'SELECT datname, oid FROM pg_database;' |
108
grep -v '^template0$'
114
# Test to make sure there is a matching file in each place
116
if [ ! -f "$SAVEDATA"/base/"$SRC_DBOID"/"$SRC_OID" -a \
117
! -h "$SAVEDATA"/base/"$SRC_DBOID"/"$SRC_OID" ]
118
then echo "Moving of database $DB, OID $SRC_OID, object $OBJ failed." 1>&2
119
echo "File not found. Exiting." 1>&2
123
if [ ! -f "$PGDATA"/base/"$DST_DBOID"/"$DST_OID" -a \
124
! -h "$PGDATA"/base/"$DST_DBOID"/"$DST_OID" ]
125
then echo "Moving of database $DB, OID $DST_OID, object $OBJ failed." 1>&2
126
echo "File not found. Exiting." 1>&2
132
mv -f "$SAVEDATA"/base/"$SRC_DBOID"/"$SRC_OID" "$PGDATA"/base/"$DST_DBOID"/"$DST_OID"
134
then echo "Moving of database $DB, OID $SRC_OID, object $OBJ" 1>&2
135
echo "to $DST_OID failed. Exiting" 1>&2
139
# handle table extents
141
ls "$SAVEDATA"/base/"$SRC_DBOID"/"$SRC_OID".* 2> /dev/null | while read FILE
143
EXT=`basename "$FILE" | sed 's/^.*\.\(.*\)$/\1/'`
144
mv -f "$FILE" "$PGDATA"/base/"$DST_DBOID"/"$DST_OID"."$EXT"
146
then echo "Moving of database $DB, OID $SRC_OID, object $OBJ" 1>&2
147
echo "to $DST_OID failed. Exiting." 1>&2
153
if [ "$PHASE" -eq 1 ]
156
##########################
157
# Phase 1 starts here #
158
##########################
161
if [ ! -d "$PGDATA"/base/1 ]
162
then echo "There is no database template1 in $PGDATA/base." 1>&2
167
SRC_VERSION=`cat "$PGDATA"/PG_VERSION`
168
if [ "$SRC_VERSION" = "" ]
169
then echo "$BASENAME can not find the PostgreSQL version file" 1>&2
170
echo "'$PGDATA/PG_VERSION'. Exiting." 1>&2
174
if [ "$SRC_VERSION" != "$CUR_VERSION" -a \
175
"$SRC_VERSION" != "$UPGRADE_VERSION" ]
176
then echo "$BASENAME supports versions $UPGRADE_VERSION and $CUR_VERSION only." 1>&2
177
echo "However, your database is version $SRC_VERSION." 1>&2
178
echo "You will need to dump and restore using pg_dumpall. Exiting." 1>&2
182
# If server is down, start it so we can do some work.
183
if ! pg_ctl status | sed -n '1p' | grep "is running" > /dev/null 2>&1
186
then echo "Can not start server. Exiting." 1>&2
191
# create directory for our data
192
if ! rm -rf "$INFODIR"
193
then echo "Deletion of old pg_upgrade_info directory $INFODIR failed." 1>&2
197
if ! mkdir "$INFODIR"
198
then echo "Creation of new pg_upgrade_info directory $INFODIR failed." 1>&2
203
if ! chmod og-rwx "$INFODIR"
204
then echo "Permission change on new pg_upgrade_info directory $INFODIR failed." 1>&2
211
pg_dumpall -s > "$INFODIR"/schema
213
then echo "Can not dump schema. Exiting." 1>&2
217
# Generate mappings for database
218
make_dboidmap > "$INFODIR"/dboidmap || exit "$?"
219
make_dbobjoidmap > "$INFODIR"/dbobjoidmap || exit "$?"
221
# Vacuum all databases to remove exipired rows.
222
# We will lose our transaction log file during the upgrade so we
227
then echo "Can not vacuum server. Exiting." 1>&2
231
# Stop server so we can move the directory.
234
then echo "Can not stop server. Exiting." 1>&2
238
# No matter what the directory name, call it data
239
mv "$PGDATA" "$INFODIR"/data
241
then echo "Can not move old $PGDATA out of the way. Exiting." 1>&2
246
echo "$BASENAME phase 1 completed."
247
echo "Continue with the steps outlined in the $BASENAME manual page."
252
##########################
253
# Phase 2 starts here #
254
##########################
258
if [ ! -d "$INFODIR" ]
259
then echo "There is no '$INFODIR' directory from the phase 1 run of $BASENAME." 1>&2
263
if [ ! -d "$SAVEDATA" ]
264
then echo "There is no '$SAVEDATA' directory from the phase 1 run of $BASENAME." 1>&2
268
if [ ! -f "$SAVEDATA/PG_VERSION" ]
269
then echo "Cannot read '$SAVEDATA/PG_VERSION' --- something is wrong." 1>&2
273
if [ ! -f "$PGDATA/PG_VERSION" ]
274
then echo "Cannot read '$PGDATA/PG_VERSION' --- something is wrong." 1>&2
278
if [ ! -d "$PGDATA/base/1" ]
279
then echo "Cannot find database template1 in '$PGDATA/base'." 1>&2
280
echo "Are you running $BASENAME as the postgres superuser?" 1>&2
284
# Get the actual versions seen in the data dirs.
286
SRC_VERSION=`cat "$SAVEDATA"/PG_VERSION`
287
DST_VERSION=`cat "$PGDATA"/PG_VERSION`
289
# Check for version compatibility.
290
# This code will need to be updated/reviewed for each new PostgreSQL release.
292
if [ "$DST_VERSION" != "$CUR_VERSION" ]
293
then echo "$BASENAME is for PostgreSQL version $CUR_VERSION" 1>&2
294
echo "but $PGDATA/PG_VERSION contains $DST_VERSION." 1>&2
295
echo "Did you run initdb for version $UPGRADE_VERSION by mistake?" 1>&2
299
# Stop server for pg_resetxlog use
301
if pg_ctl status | sed -n '1p' | grep "is running" > /dev/null 2>&1
304
then echo "Can not start server. Exiting." 1>&2
309
# check for proper pg_resetxlog version
311
pg_resetxlog 2> /dev/null
312
# file not found status is normally 127, not 1
314
then echo "Unable to find pg_resetxlog in your path." 1>&2
315
echo "Install it from pgsql/contrib/pg_resetxlog and continue. Exiting." 1>&2
319
if ! pg_resetxlog -x 2>&1 | grep 'xid' > /dev/null 2>&1
320
then echo "Old version of pg_resetxlog found in path." 1>&2
321
echo "Install a newer version of pg_resetxlog from pgsql/contrib/pg_resetxlog." 1>&2
326
SRC_XID=`pg_resetxlog -n "$SAVEDATA" | grep "NextXID" | awk -F' *' '{print $4}'`
327
DST_XID=`pg_resetxlog -n "$PGDATA" | grep "NextXID" | awk -F' *' '{print $4}'`
329
# compare locales to make sure they match
331
pg_resetxlog -n "$SAVEDATA" | grep "^LC_" > /tmp/$$.0
332
pg_resetxlog -n "$PGDATA" | grep "^LC_" > /tmp/$$.1
333
if ! diff /tmp/$$.0 /tmp/$$.1 > /dev/null
334
then echo "Locales do not match between the two versions. Exiting." 1>&2
342
then echo "Can not start server. Exiting." 1>&2
347
###################################
348
# Checking done. Ready to proceed.
349
###################################
352
# Execute the schema script to create everything
354
psql template1 < "$INFODIR"/schema
356
then echo "There were errors in the input script. Exiting." 1>&2
360
echo "Input script completed, fixing row commit statuses..."
362
# Generate mappings for new database
363
make_dboidmap > /tmp/$$.dboidmap || exit "$?"
364
make_dbobjoidmap > /tmp/$$.dbobjoidmap || exit "$?"
366
# we are done with SQL database access
367
# shutdown forces buffers to disk
371
then echo "Unable to stop database server. Exiting." 1>&2
375
echo "Commit fixes complete, moving data files..."
377
# Move table/index/sequence files
379
cat "$INFODIR"/dbobjoidmap | while read LINE
381
DB=`echo "$LINE" | awk '{print $1}'`
382
OBJ=`echo "$LINE" | awk '{print $2}'`
384
# Skip system tables, except for pg_largeobject
385
# pg_toast tables are handled later as part of the
387
if [ `expr X"$OBJ" : X'pg_'` -eq 4 -a \
388
`expr X"$OBJ" : X'pg_largeobject'` -ne 15 ]
392
SRC_OID=`echo "$LINE" | awk '{print $3}'`
393
SRC_DBOID=`grep "^$DB " "$INFODIR"/dboidmap | awk '{print $2}'`
394
DST_DBOID=`grep "^$DB " /tmp/$$.dboidmap | awk '{print $2}'`
395
DST_OID=`grep "^$DB $OBJ " /tmp/$$.dbobjoidmap | awk '{print $3}'`
399
# Handle TOAST files if they exist
400
if grep "^$DB pg_toast_$SRC_OID " "$INFODIR"/dbobjoidmap \
403
SAVE_SRC_OID="$SRC_OID"
404
SAVE_DST_OID="$DST_OID"
405
SRC_OID=`grep "^$DB pg_toast_$SAVE_SRC_OID " \
406
"$INFODIR"/dbobjoidmap | awk '{print $3}'`
407
DST_OID=`grep "^$DB pg_toast_$SAVE_DST_OID " \
408
/tmp/$$.dbobjoidmap | awk '{print $3}'`
411
SRC_OID=`grep "^$DB pg_toast_${SAVE_SRC_OID}_idx " \
412
"$INFODIR"/dbobjoidmap | awk '{print $3}'`
413
DST_OID=`grep "^$DB pg_toast_${SAVE_DST_OID}_idx " \
414
/tmp/$$.dbobjoidmap | awk '{print $3}'`
420
# Set this so future backends don't think these tuples are their own
421
# because it matches their own XID.
422
# Commit status already updated by vacuum above
423
# Set to maximum XID just in case SRC wrapped around recently and
424
# is lower than DST's database
426
if [ "$SRC_XID" -gt "$DST_XID" ]
427
then MAX_XID="$SRC_XID"
428
else MAX_XID="$DST_XID"
431
pg_resetxlog -x "$MAX_XID" "$PGDATA"
433
then echo "Unable to set new XID. Exiting." 1>&2
439
rm -r "$PGDATA"/pg_xlog
440
mv -f "$SAVEDATA"/pg_xlog "$PGDATA"
444
rm -r "$PGDATA"/pg_clog
445
mv -f "$SAVEDATA"/pg_clog "$PGDATA"
447
# Set last log file id and segment from old database
449
LOG_ID=`pg_resetxlog -n "$SAVEDATA" | grep "Current log file id:" |
450
awk -F' *' '{print $5}'`
451
if [ "$LOG_ID" = "" ]
452
then echo "Unable to get old log file id. Exiting." 1>&2
455
SEG_ID=`pg_resetxlog -n "$SAVEDATA" | grep "Next log file segment:" |
456
awk -F' *' '{print $5}'`
457
if [ "$SEG_ID" = "" ]
458
then echo "Unable to get old log segment id. Exiting." 1>&2
462
# Set checkpoint location of new database
464
pg_resetxlog -l "$LOG_ID","$SEG_ID" "$PGDATA"
466
then echo "Unable to set new log file/segment id. Exiting." 1>&2
470
# Restart server with moved data
474
then echo "Unable to restart database server. Exiting." 1>&2
478
# Now that we have moved the WAL/transaction log files, vacuum again to
479
# mark install rows with fixed transaction ids to prevent problems on xid
484
then echo "There were errors during VACUUM. Exiting." 1>&2
490
echo "$BASENAME phase 2 completed."
491
echo "You may remove the old database files with 'rm -r $INFODIR'."