pg_upgrade 12.4 KB
Newer Older
1 2
#!/bin/sh
#
3
# pg_upgrade: update a database without needing a full dump/reload cycle.
4
# CAUTION: Read the manual page before trying to use this!
Bruce Momjian's avatar
Bruce Momjian committed
5

6
# $Header: /cvsroot/pgsql/src/bin/pg_dump/Attic/pg_upgrade,v 1.34 2002/01/13 17:52:00 momjian Exp $
7 8 9 10
#
# NOTE: we must be sure to update the version-checking code a few dozen lines
# below for each new PostgreSQL release.

Bruce Momjian's avatar
Bruce Momjian committed
11
#set -x
12

Bruce Momjian's avatar
Bruce Momjian committed
13
# Set this to "Y" to enable this program
Bruce Momjian's avatar
Bruce Momjian committed
14
ENABLE="N"
Bruce Momjian's avatar
Bruce Momjian committed
15

16 17 18 19 20 21 22 23 24 25
if [ "$ENABLE" != "Y" ]
then
	echo "Sorry, $0 cannot upgrade database
version $SRC_VERSION to $DST_VERSION." 1>&2
	echo "The on-disk structure of tables has changed." 1>&2
	echo "You will need to dump and restore using pg_dumpall." 1>&2
	exit 1
fi


Bruce Momjian's avatar
Bruce Momjian committed
26 27
# UPGRADE_VERSION is the expected old database version
UPGRADE_VERSION="7.1"
28
CUR_VERSION="7.2"
Bruce Momjian's avatar
Bruce Momjian committed
29

30
trap "rm -f /tmp/$$.*" 0 1 2 3 15
Bruce Momjian's avatar
Bruce Momjian committed
31

32 33 34 35 36 37 38 39 40 41 42
PHASE=""
if [ "$#" -eq 1 ]
then
	if [ "X$1" = "X-1" ]
	then	PHASE="1"
		shift
	elif [ "X$1" = "X-2" ]
	then	PHASE="2"
		shift
	fi
fi
43

44 45 46 47 48
if [ "$PHASE" = "" ]
then	echo "You must run $0 in either mode 1 or mode 2." 1>&2
	echo "Usage:  $0 [ -1 | -2 ]" 1>&2
	exit 1
fi
Bruce Momjian's avatar
Bruce Momjian committed
49

50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
if [ ! -e data ]
then	echo "$0 must be run from the directory above your /data directory.
$0 aborted." 1>&2
	if [ "$PHASE" -eq 2 ]
	then	echo "You must run initdb to create a template1 database." 1>&2
	fi
	exit 1
fi

INFODIR="pg_upgrade_info"
OLDDIR="$INFODIR/data"

make_dbobjoidmap()
{
	psql -d template1 -At -c "SELECT datname FROM pg_database" |
65
	grep -v '^template0$' |
66 67 68 69 70 71 72
	while read DB
	do	
		QUERY="`echo \"	SELECT 	relname, oid
				FROM 	pg_class
				WHERE 	relkind = 'r' OR
					relkind = 'i' OR
					relkind = 't'\"`"
73 74 75 76
		# Don't move over 7.1 int4 sequences;  use setval() file.
		# Sequence XIDs changed from 7.2beta4 to beta5;  don't copy them.
		if [ "$SRC_VERSION" != "7.1" -a \
		     "$SRC_VERSION" != "7.2" ]
77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92
		then	QUERY="$QUERY OR relkind = 'S';";
			QUERY="$QUERY;"
		fi

		psql -d "$DB" -At -F'	' -c "$QUERY" |
		while read RELNAME_OID
		do	
			echo "$DB	$RELNAME_OID"
		done
	done
}


make_dboidmap()
{
	psql -d template1 -At -F'	' -c \
93 94
		'SELECT datname, oid FROM pg_database;' |
	grep -v '^template0$'
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
}


move_objfiles()
{
	# Test to make sure there is a matching file in each place

	if [ ! -e "$OLDDIR"/base/"$SRC_DBOID"/"$SRC_OID" ]
	then	echo "Move of database $DB, OID $SRC_OID, object $OBJ failed.
File not found;  exiting" 1>&2
		exit 1
	fi

	if [ ! -e data/base/"$DST_DBOID"/"$DST_OID" ]
	then	echo "Move of database $DB, OID $DST_OID, object $OBJ failed.
File not found;  exiting" 1>&2
		exit 1
	fi

	# Move files

	mv -f "$OLDDIR"/base/"$SRC_DBOID"/"$SRC_OID" data/base/"$DST_DBOID"/"$DST_OID"
	if [ "$?" -ne 0 ]
	then	echo "Move of database $DB, OID $SRC_OID, object $OBJ
to $DST_OID failed.;  exiting" 1>&2
		exit 1
	fi

	# handle table extents

	ls "$OLDDIR"/base/"$SRC_DBOID"/"$SRC_OID".* 2>/dev/null | while read FILE
	do
		EXT=`basename "$FILE" | sed 's/^.*\.\(.*\)$/\1/'`
		mv -f "$FILE" data/base/"$DST_DBOID"/"$DST_OID"."$EXT"
		if [ "$?" -ne 0 ]
		then	echo "Move of database $DB, OID $SRC_OID, object $OBJ
to $DST_OID failed.;  exiting" 1>&2
132 133
			exit 1
		fi
134 135 136 137 138 139 140 141 142 143 144 145 146
	done
}

if [ "$PHASE" -eq 1 ]
then

	##########################
	#  Phase 1 starts here   #
	##########################


	if [ ! -d data/base/1 ]
	then	echo "There is no database template1 in data/base." 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
147 148 149
		exit 1
	fi

150 151 152 153 154 155 156
	# get version
	SRC_VERSION="`cat data/PG_VERSION`"
	if [ "$SRC_VERSION" = "" ]
	then	echo "$0 can not find PostgreSQL version file 'data/PG_VERSION'.
$0 aborted." 1>&2
		exit 1
	fi
Bruce Momjian's avatar
Bruce Momjian committed
157

158 159 160 161 162 163 164 165 166 167
	if [ 	"$SRC_VERSION" != "$CUR_VERSION" -a \
		"$SRC_VERSION" != "$UPGRADE_VERSION" ]
	then	echo 
	"$0 supports versions $UPGRADE_VERSION and $CUR_VERSION only." 1>&2
		echo 
	"However, your database is version $SRC_VERSION;
$0 aborted." 1>&2
		echo "You will need to dump and restore using pg_dumpall." 1>&2
		exit 1
	fi
Bruce Momjian's avatar
Bruce Momjian committed
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
	# Start server, if needed, so we can do some work.
	if ! pg_ctl status | head -1 | grep -q "is running"
	then	pg_ctl -w start
		if [ $? -ne 0 ]
		then	echo "Can not start server.
$0 aborted." 1>&2
			exit 1
		fi
	fi

	# create directory for our data
	rm -rf "$INFODIR"
	mkdir "$INFODIR"
	chmod og-rwx "$INFODIR"

	# Dump schema
	pg_dumpall -s |
	awk -F'  *' '
	{
		# Modify sequences with int8 maximums if we are upgrading from 7.1.
		if ("'"$SRC_VERSION"'" == "7.1" &&
		    $1 == "CREATE" &&
		    $2 == "SEQUENCE" &&
		    # handle OS rounding
		    $9 >= 2147483646 &&
		    $9 <= 2147483648)
		{
Bruce Momjian's avatar
Bruce Momjian committed
196
			for(i=1; i <= NF; i++)
197 198 199 200 201 202
			{
				if (i != 9)
					printf "%s ", $i;
				else
					printf "%s ", "9223372036854775807";
			}
Bruce Momjian's avatar
Bruce Momjian committed
203
			print "";
204 205 206 207 208 209 210 211 212 213 214 215 216 217
		}
		else    print $0;
	}' > "$INFODIR"/schema
	if [ $? -ne 0 ]
	then	echo "Can not dump schema.
$0 aborted." 1>&2
		exit 1
	fi
	
	# Generate mappings for database
	make_dboidmap > "$INFODIR"/dboidmap
	make_dbobjoidmap > "$INFODIR"/dbobjoidmap

	# Generate setval() script for 7.1 because it has int4 sequences
218 219 220
	# Sequence XIDs changed from 7.2beta4 to beta5;  we have to recreate them.
	if [ "$SRC_VERSION" = "7.1" -o \
	     "$SRC_VERSION" = "7.2" ]
221 222
	then
		psql -d template1 -At -c "SELECT datname FROM pg_database" |
223
		grep -v '^template0$' |
224
		while read DB
225 226 227
		do
			# We use awk as a portable way to output a backslash
			awk 'BEGIN {print "\\connect '"$DB"'"}'
228 229 230 231 232 233
			psql -d "$DB" -At -c "
				SELECT 	relname
				FROM 	pg_class
				WHERE 	relkind = 'S';" |
			while read SEQUENCE
			do
234
				VALUE=`psql -d "$DB" -At -c "SELECT last_value 
235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266
								 FROM \"$SEQUENCE\";"`
				echo "SELECT setval ('$SEQUENCE', $VALUE, true);"
			done
		done > "$INFODIR"/setval
	else	rm -f "$INFODIR"/setval
	fi

	# Vacuum all databases to remove exipired rows.
	# We will lose our transaction log file during the upgrade so we
	# have to do this.

	vacuumdb -a
	if [ $? -ne 0 ]
	then	echo "Can not vacuum server.
$0 aborted." 1>&2
		exit 1
	fi

	# Stop server so we can move the directory.
	pg_ctl -w stop
	if [ $? -ne 0 ]
	then	echo "Can not stop server.
$0 aborted." 1>&2
		exit 1
	fi

	mv data "$INFODIR"
	if [ $? -ne 0 ]
	then	echo "Can not move old /data out of the way.
$0 aborted." 1>&2
		exit 1
	fi
Bruce Momjian's avatar
Bruce Momjian committed
267 268
	echo
	echo
269 270
	echo "Plase 1 completed.
Continue with the steps outlined in the $0 manual page."
271
	exit 0
Bruce Momjian's avatar
Bruce Momjian committed
272 273
fi

274 275 276 277 278 279 280 281 282 283

	##########################
	#  Phase 2 starts here   #
	##########################

# check things

if [ ! -d "$INFODIR" ]
then	echo "There is no '$INFODIR' directory from a phase 1 run of $0." 1>&2
	exit 1	
284 285
fi

286 287 288
if [ ! -e "$OLDDIR" ]
then	echo "There is no '$OLDDIR' directory from the phase 1 run of $0." 1>&2
	exit 1	
289 290
fi

291 292
if [ ! -f "$OLDDIR/PG_VERSION" ]
then	echo "Cannot read '$OLDDIR/PG_VERSION' --- something is wrong." 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
293 294 295
	exit 1
fi

296 297
if [ ! -f "data/PG_VERSION" ]
then	echo "Cannot read 'data/PG_VERSION' --- something is wrong." 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
298 299 300
	exit 1
fi

301 302 303
if [ ! -d "data/base/1" ]
then	echo "Cannot find database template1 in 'data/base'." 1>&2
	echo "Are you running $0 as the postgres superuser?" 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
304 305 306
	exit 1
fi

307
# Get the actual versions seen in the data dirs.
Bruce Momjian's avatar
Bruce Momjian committed
308

309 310
SRC_VERSION=`cat "$OLDDIR"/PG_VERSION`
DST_VERSION=`cat data/PG_VERSION`
Bruce Momjian's avatar
Bruce Momjian committed
311

312 313 314
# Check for version compatibility.
# This code will need to be updated/reviewed for each new PostgreSQL release.

315 316 317 318
if [ "$DST_VERSION" != "$CUR_VERSION" ]
then	echo "$0 is for PostgreSQL version $CUR_VERSION
but data/PG_VERSION contains $DST_VERSION." 1>&2
	echo "Did you run initdb for version $UPGRADE_VERSION by mistake?" 1>&2
319 320 321
	exit 1
fi

322
# Stop server for pg_resetxlog use
323

324 325 326 327 328 329 330
if pg_ctl status | head -1 | grep -q "is running"
then	pg_ctl -w stop
	if [ $? -ne 0 ]
	then	echo "Can no start server.
$0 aborted." 1>&2
		exit 1
	fi
Bruce Momjian's avatar
Bruce Momjian committed
331 332 333
fi

# check for proper pg_resetxlog version
334

335
pg_resetxlog 2>/dev/null
Bruce Momjian's avatar
Bruce Momjian committed
336
# file not found status is normally 127, not 1
337 338 339 340 341 342
if [ "$?" -ne 1 ]
then	echo "Unable to find pg_resetxlog in path.
Install it from pgsql/contrib/pg_resetxlog and continue.;  exiting" 1>&2
	exit 1
fi

343
if ! pg_resetxlog -x 2>&1 | grep -q 'xid'
344
then	echo "Old version of pg_resetxlog found in path.
345
Install a newer version from pgsql/contrib/pg_resetxlog.;  exiting" 1>&2
346 347 348
	exit 1
fi

349 350
# If the XID is > 2 billion, 7.1 database will have non-frozen XID's in 
# low numbers, and 7.2 will think they are in the future --- bad.
351

Bruce Momjian's avatar
Bruce Momjian committed
352
SRC_XID=`pg_resetxlog -n "$OLDDIR" | grep "NextXID" | awk -F'  *' '{print $4}'`
353
if [ "$SRC_VERSION" = "7.1" -a "$SRC_XID" -gt 2000000000 ]
354 355 356
then	echo "XID too high for $0.;  exiting" 1>&2
	exit 1
fi
Bruce Momjian's avatar
Bruce Momjian committed
357
DST_XID=`pg_resetxlog -n data | grep "NextXID" | awk -F'  *' '{print $4}'`
358

Bruce Momjian's avatar
Bruce Momjian committed
359 360 361 362 363 364 365 366 367
# compare locales to make sure they match

pg_resetxlog -n "$OLDDIR" | grep "^LC_" > /tmp/$$.0
pg_resetxlog -n data | grep "^LC_" > /tmp/$$.1
if ! diff /tmp/$$.0 /tmp/$$.1 >/dev/null
then	echo "Locales do not match between the two versions.;  exiting" 1>&2
	exit 1
fi

368 369 370 371 372 373 374 375 376
# Restart postmaster

pg_ctl -w start
if [ $? -ne 0 ]
then	echo "Can not start server.
$0 aborted." 1>&2
	exit 1
fi

Bruce Momjian's avatar
Bruce Momjian committed
377 378

###################################
379
# Checking done.  Ready to proceed.
Bruce Momjian's avatar
Bruce Momjian committed
380 381
###################################

382

383 384
# Execute the schema script to create everything

385
psql template1 < "$INFODIR"/schema
386
if [ $? -ne 0 ]
387
then	echo "There were errors in the input script.
388 389 390
$0 aborted." 1>&2
	exit 1
fi
391

392
echo "Input script complete, fixing row commit statuses..."
393

394 395 396
# XXX do we still need this?
# Now vacuum each result database because our movement of transaction log
# causes some committed transactions to appear as non-committed
397

398 399 400
vacuumdb -a
if [ $? -ne 0 ]
then	echo "There were errors during VACUUM.
401
$0 aborted." 1>&2
402 403
	exit 1
fi
404

405 406 407
# Generate mappings for database
make_dboidmap > /tmp/$$.dboidmap
make_dbobjoidmap > /tmp/$$.dbobjoidmap
Bruce Momjian's avatar
Bruce Momjian committed
408

409 410
# we are done with SQL database access
# shutdown forces buffers to disk
411

Bruce Momjian's avatar
Bruce Momjian committed
412
pg_ctl -w stop
413 414 415 416
if [ "$?" -ne 0 ]
then	echo "Unable to stop database server.;  exiting" 1>&2
	exit 1
fi
417

418
echo "Commit fixes complete, moving data files..."
419

Bruce Momjian's avatar
Bruce Momjian committed
420 421
# Move table/index/sequence files

422
cat "$INFODIR"/dbobjoidmap | while read LINE
Bruce Momjian's avatar
Bruce Momjian committed
423
do
424 425 426 427 428 429 430 431 432
	DB=`echo "$LINE" | awk '{print $1}'`
	OBJ=`echo "$LINE" | awk '{print $2}'`

	# Skip system tables, except for pg_largeobject
	# pg_toast tables are handled later as part of the
	# base table move
	if [ `expr "$OBJ" : 'pg_'` -eq 3 -a \
	     `expr "$OBJ" : 'pg_largeobject'` -ne 14 ]
	then	continue
433
	fi
Bruce Momjian's avatar
Bruce Momjian committed
434

435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457
	SRC_OID=`echo "$LINE" | awk '{print $3}'`
	SRC_DBOID=`grep "^$DB	" "$INFODIR"/dboidmap | awk '{print $2}'`
	DST_DBOID=`grep "^$DB	" /tmp/$$.dboidmap | awk '{print $2}'`
	DST_OID=`grep "^$DB	$OBJ	" /tmp/$$.dbobjoidmap | awk '{print $3}'`

	move_objfiles

	# Handle TOAST files if they exist
	if grep -q "^$DB	pg_toast_$SRC_OID	" "$INFODIR"/dbobjoidmap
	then	# toast heap
		SAVE_SRC_OID="$SRC_OID"
		SAVE_DST_OID="$DST_OID"
		SRC_OID=`grep "^$DB	pg_toast_$SAVE_SRC_OID	" \
			"$INFODIR"/dbobjoidmap | awk '{print $3}'`
		DST_OID=`grep "^$DB	pg_toast_$SAVE_DST_OID	" \
			/tmp/$$.dbobjoidmap | awk '{print $3}'`
		move_objfiles
		# toast index
		SRC_OID=`grep "^$DB	pg_toast_${SAVE_SRC_OID}_idx	" \
			"$INFODIR"/dbobjoidmap | awk '{print $3}'`
		DST_OID=`grep "^$DB	pg_toast_${SAVE_DST_OID}_idx	" \
			/tmp/$$.dbobjoidmap | awk '{print $3}'`
		move_objfiles
Bruce Momjian's avatar
Bruce Momjian committed
458 459
	fi
done
460
								   
461

Bruce Momjian's avatar
Bruce Momjian committed
462 463
# Set this so future backends don't think these tuples are their own
# because it matches their own XID.
Bruce Momjian's avatar
Bruce Momjian committed
464
# Commit status already updated by vacuum above
Bruce Momjian's avatar
Bruce Momjian committed
465 466
# Set to maximum XID just in case SRC wrapped around recently and
# is lower than DST's database
467

Bruce Momjian's avatar
Bruce Momjian committed
468 469 470 471
if [ "$SRC_XID" -gt "$DST_XID" ]
then	MAX_XID="$SRC_XID"
else	MAX_XID="$DST_XID"
fi
Bruce Momjian's avatar
Bruce Momjian committed
472

Bruce Momjian's avatar
Bruce Momjian committed
473
pg_resetxlog -x "$MAX_XID" data
474 475 476
if [ "$?" -ne 0 ]
then	echo "Unable to set new XID.;  exiting" 1>&2
	exit 1
477 478
fi

Bruce Momjian's avatar
Bruce Momjian committed
479 480 481 482 483
# Move over old WAL

rm -r data/pg_xlog
mv -f "$OLDDIR"/pg_xlog data

484
# Set last log file id and segment from old database
485

486 487 488 489 490 491 492 493 494 495
LOG_ID=`pg_resetxlog -n "$OLDDIR" | grep "Current log file id:" |
	awk -F'  *' '{print $5}'`
if [ "$LOG_ID" = "" ]
then	echo "Unable to get old log file id.;  exiting" 1>&2
	exit 1
fi
SEG_ID=`pg_resetxlog -n "$OLDDIR" | grep "Next log file segment:" |
	awk -F'  *' '{print $5}'`
if [ "$SEG_ID" = "" ]
then	echo "Unable to get old log segment id.;  exiting" 1>&2
496 497 498
	exit 1
fi

Bruce Momjian's avatar
Bruce Momjian committed
499 500
# Set checkpoint location of new database

501
pg_resetxlog -l "$LOG_ID" "$SEG_ID" data
502
if [ "$?" -ne 0 ]
503
then	echo "Unable to set new log file/segment id.;  exiting" 1>&2
504 505 506 507 508
	exit 1
fi

# Restart server with moved data

Bruce Momjian's avatar
Bruce Momjian committed
509
pg_ctl -w start
510 511 512
if [ "$?" -ne 0 ]
then	echo "Unable to restart database server.;  exiting" 1>&2
	exit 1
513 514
fi

515
# Set sequence values for 7.1-version sequences, which were int4.
Bruce Momjian's avatar
Bruce Momjian committed
516

517 518 519
if [ "$SRC_VERSION" = "7.1" -o \
     "$SRC_VERSION" = "7.2" ]
then	echo "Set sequence values..."
520 521 522
	psql -d template1 -At < "$INFODIR"/setval
	if [ $? -ne 0 ]
	then	echo "There were errors during int4 sequence restore.
523
$0 aborted." 1>&2
524 525
		exit 1
	fi
526 527
fi

528 529
echo
echo
530
echo "You may remove the old database files with 'rm -r pg_upgrade'."
531

532
exit 0