pg_upgrade 9.84 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 7 8
#set -x

# $Header: /cvsroot/pgsql/src/bin/pg_dump/Attic/pg_upgrade,v 1.27 2002/01/11 06:33:01 momjian Exp $
9
#
10 11
# NOTE: we must be sure to update the version-checking code a few dozen lines
# below for each new PostgreSQL release.
12

13 14 15
TMPFILE="/tmp/pgupgrade.$$"

trap "rm -f $TMPFILE" 0 1 2 3 15
Bruce Momjian's avatar
Bruce Momjian committed
16

17 18 19 20 21 22 23 24 25 26
SCHEMA=""
while [ "$#" -gt 1 ]
do
	if [ "X$1" = "X-s" ]
	then	SCHEMA="$2"
		if [ ! -s "$SCHEMA" ]
		then	echo "$SCHEMA does not exist" 1>&2
			exit 1
		fi
		shift 2
27
	else	echo "Usage:  $0 -s schema_dump old_data_dir" 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
28 29
		exit 1
	fi
30
done
Bruce Momjian's avatar
Bruce Momjian committed
31

32
if [ "$#" -ne 1 -o ! "$SCHEMA" ]
33
then	echo "Usage:  $0 -s schema_dump old_data_dir" 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
34 35 36
	exit 1
fi

37
OLDDIR="$1"
Bruce Momjian's avatar
Bruce Momjian committed
38 39 40

# check things

41
if [ ! -d "./data" ]
42
then	echo "`basename $0` must be run from the directory containing the database directory \`data\' (`dirname $PGDATA`.)" 1>&2
43
	echo "You must have run initdb to create the template1 database." 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
44 45 46
	exit 1
fi

47
if [ ! -d "./$OLDDIR" ]
48 49 50 51
then	echo "You must rename your old data directory to $OLDDIR and run initdb." 1>&2
	exit 1
fi

52
if [ ! -d "./data/base/1" ]
53 54
then	echo "Cannot find database template1 in ./data/base." 1>&2
	echo "Are you running $0 as the postgres superuser?" 1>&2
55 56 57
	exit 1
fi

58
if [ ! -d "./$OLDDIR/base/1" ]
59
then	echo "There is no database template1 in ./$OLDDIR/base." 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
60 61 62
	exit 1
fi

63 64
if [ ! -r "./data/PG_VERSION" ]
then	echo "Cannot read ./data/PG_VERSION --- something is wrong." 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
65 66 67
	exit 1
fi

68 69
if [ ! -r "./$OLDDIR/PG_VERSION" ]
then	echo "Cannot read ./$OLDDIR/PG_VERSION --- something is wrong." 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
70 71 72
	exit 1
fi

73
# Get the actual versions seen in the data dirs.
74 75
DEST_VERSION=`cat ./data/PG_VERSION`
SRC_VERSION=`cat ./$OLDDIR/PG_VERSION`
Bruce Momjian's avatar
Bruce Momjian committed
76

77 78 79
# Check for version compatibility.
# This code will need to be updated/reviewed for each new PostgreSQL release.

80 81
# UPGRADE_VERSION is the expected output database version
UPGRADE_VERSION="7.1"
82

83 84 85
if [ "$DEST_VERSION" != "$UPGRADE_VERSION" -a "$DEST_VERSION" != "$SRC_VERSION" ]
then	echo "`basename $0` is for PostgreSQL version $UPGRADE_VERSION, but ./data/PG_VERSION contains $DEST_VERSION." 1>&2
	echo "Did you run initdb for version $UPGRADE_VERSION?" 1>&2
86 87 88 89 90
	exit 1
fi

# Check that input database is of a compatible version (anything with the same
# physical layout of user tables and indexes should be OK).  I did not write
91
# something like "$SRC_VERSION -ge $UPGRADE_VERSION" because test(1) isn't bright
92 93 94
# enough to compare dotted version strings properly.  Using a case statement
# looks uglier but is more flexible.

95
case "$SRC_VERSION" in
96
#    7.2) ;;
97
    *)	echo "Sorry, `basename $0` cannot upgrade database version $SRC_VERSION to $DEST_VERSION." 1>&2
98
	echo "The on-disk structure of tables has changed." 1>&2
99
	echo "You will need to dump and restore using pg_dumpall." 1>&2
100 101 102
	exit 1;;
esac

103
pg_resetxlog 2>/dev/null
Bruce Momjian's avatar
Bruce Momjian committed
104
# file not found status is normally 127, not 1
105 106 107 108 109 110
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

111
if ! pg_resetxlog -x 2>&1 | grep -q XID
112
then	echo "Old version of pg_resetxlog found in path.
113
Install a newer version from pgsql/contrib/pg_resetxlog.;  exiting" 1>&2
114 115 116
	exit 1
fi

117 118
# 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.
119 120 121 122 123 124

XID=`pg_resetxlog -n "$OLDDIR" | grep "NextXID" | awk -F'  *' '{print $4}'`
if [ "$SRC_VERSION" = "7.1" -a "$XID" -gt 2000000000 ]
then	echo "XID too high for $0.;  exiting" 1>&2
	exit 1
fi
125

126
# Checking done.  Ready to proceed.
127

128 129 130
# Execute the schema script to create everything

psql "template1" < "$SCHEMA"
131
if [ $? -ne 0 ]
132
then	echo "There were errors in the input script $SCHEMA.
133 134 135
$0 aborted." 1>&2
	exit 1
fi
136

137
echo "Input script $SCHEMA complete, fixing row commit statuses..."
138

139 140 141
# 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
142

143 144 145
vacuumdb -a
if [ $? -ne 0 ]
then	echo "There were errors during VACUUM.
146
$0 aborted." 1>&2
147 148
	exit 1
fi
149

150
# Used for scans looking for a database/table name match
151
# New oid is looked up
152
pg_dumpall -s > $TMPFILE 2>/dev/null
153 154 155 156
if [ "$?" -ne 0 ]
then	echo "Unable to dump schema of new database.;  exiting" 1>&2
	exit 1
fi
157

158 159
# we are done with SQL database access
# shutdown forces buffers to disk
160

161
pg_ctl stop
162 163 164 165
if [ "$?" -ne 0 ]
then	echo "Unable to stop database server.;  exiting" 1>&2
	exit 1
fi
166

167
echo "Commit fixes complete, moving data files..."
168

169
cat "$SCHEMA" | while read LINE
Bruce Momjian's avatar
Bruce Momjian committed
170
do
171
	if /bin/echo "$LINE" | grep -q '^\\connect [^	]*$'
172 173 174 175 176 177 178 179 180 181 182 183
	then	OLDDB="$DB"
		DB="`/bin/echo \"$LINE\" | cut -d' ' -f2`"
		if [ "$DB" = "-" ]
		then	DB="$OLDDB"
		fi
		if [ "$DB" = "template1" -o "$DB" = "template0" ]
		then	DB=""
		fi
	fi
	if echo "$LINE" | grep -q "^-- TOC Entry ID [0-9]* (OID "
	then	OID="`echo \"$LINE\" | cut -d' ' -f7 | tr -d ')'`"
	fi
184
	if echo "$LINE" | egrep -q "^-- Name: [^ ]* Type: (TABLE|INDEX|SEQUENCE) "
185 186 187 188 189 190
	then	TABLE="`echo \"$LINE\" | cut -d' ' -f3`"
		# skip system tables
		if [ "`echo \"$TABLE\" | cut -c 1-3`" = "pg_" ]
		then	TABLE=""
		fi
	fi
191 192 193
	if [ "$DB" -a "$OID" -a "$TABLE" ]
	then
		NEWOID=`awk -F' ' '
194
				BEGIN 	{ newdb=""; newoid="";
195 196 197 198 199 200 201
					  newtable=""; ret=0;}
				$1 == "\\\\connect" && $2 != "-" {newdb=$2;}
				$0 ~ /^-- TOC Entry ID [0-9]* .OID / \
					{ newoid = substr($7, 1, length($7)-1);}
				{print $0 >> "/tmp/x";
				print $3 >> "/tmp/x";
				print newdb," ", newoid >> "/tmp/x"}
202 203 204
				($0 ~ /^-- Name: [^ ]* Type: TABLE / || \
				 $0 ~ /^-- Name: [^ ]* Type: INDEX / || \
				 $0 ~ /^-- Name: [^ ]* Type: SEQUENCE /) && \
205 206 207 208 209
				newdb == "'"$DB"'" && \
				$3 == "'"$TABLE"'" \
					{ ret=newoid; exit}
				END { print ret;}' $TMPFILE`
		if [ "$NEWOID" -eq 0 ]
210 211
		then	echo "Move of database $DB, OID $OID, table $TABLE failed.
New oid not found;  exiting" 1>&2
212 213 214 215
			exit 1
		fi
		# We use stars so we don't have to worry about database oids
 		if [ `ls "$OLDDIR"/base/*/"$OID" | wc -l` -eq 0 ]
216 217
		then	echo "Move of database $DB, OID $OID, table $TABLE failed.
File not found;  exiting" 1>&2
218 219 220
			exit 1
		fi
  		if [ `ls "$OLDDIR"/base/*/"$OID" | wc -l` -gt 1 ]
221 222
		then	echo "Move of database $DB, OID $OID, table $TABLE failed.
Too many found;  exiting" 1>&2
223 224 225
			exit 1
		fi
		if [ `ls data/base/*/"$NEWOID" | wc -l` -eq 0 ]
226 227
		then	echo "Move of database $DB, OID $OID, table $TABLE to $NEWOID failed.
File not found;  exiting" 1>&2
228 229 230
			exit 1
		fi
		if [ `ls data/base/*/"$NEWOID" | wc -l` -gt 1 ]
231 232
		then	echo "Move of database $DB, OID $OID, table $TABLE to $NEWOID failed.
Too many found;  exiting" 1>&2
233 234 235 236
			exit 1
		fi
		mv -f "$OLDDIR"/base/*/"$OID" data/base/*/"$NEWOID"
		if [ "$?" -ne 0 ]
237 238
		then	echo "Move of database $DB, OID $OID, table $TABLE 
to $NEWOID failed.;  exiting" 1>&2
239 240 241
			exit 1
		fi
		TABLE=""
Bruce Momjian's avatar
Bruce Momjian committed
242 243 244
	fi
done

245 246

# Set this so the next VACUUM sets the old row XID's as "frozen"
Bruce Momjian's avatar
Bruce Momjian committed
247 248
# Commit status already updated by vacuum above

249 250 251 252
pg_resetxlog -x "$XID" data
if [ "$?" -ne 0 ]
then	echo "Unable to set new XID.;  exiting" 1>&2
	exit 1
253 254
fi

Bruce Momjian's avatar
Bruce Momjian committed
255
# Set last checkpoint location from old database
256

257
CHKPOINT=`pg_resetxlog -n "$OLDDIR" | grep "checkpoint location:" |
258 259 260 261 262 263
	awk -F'  *' '{print $4}'`
if [ "$CHKPOINT" = "" ]
then	echo "Unable to get old checkpoint location.;  exiting" 1>&2
	exit 1
fi

Bruce Momjian's avatar
Bruce Momjian committed
264 265
# Set checkpoint location of new database

266
pg_resetxlog -l `echo "$CHKPOINT" | tr '/' ' '` data
267 268 269 270 271 272 273
if [ "$?" -ne 0 ]
then	echo "Unable to set new checkpoint location.;  exiting" 1>&2
	exit 1
fi

# Restart server with moved data

274 275 276 277
pg_ctl start
if [ "$?" -ne 0 ]
then	echo "Unable to restart database server.;  exiting" 1>&2
	exit 1
278 279
fi

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

282
if [ "$SRC_VERSION" = "7.1" ]
283
then	echo "Set int8 sequence values from 7.1..."
284 285 286 287 288 289 290 291 292 293 294 295

	psql -d template1 -At -c "SELECT datname FROM pg_database" | 
	while read DB
	do	
		echo "$DB"
		# XXX is concurrency a problem here?
		psql -d "$DB" -At -c "SELECT relname FROM pg_class where relkind = 'S';" |
		while read SEQUENCE
		do
			psql -d "$DB" -At <<SQL_END

-- This table matches the 7.1 sequence schema
296
CREATE TABLE pg_upgrade_temp_seq_int4 (
297 298 299 300 301 302 303 304 305 306 307
	sequence_name name
	last_value    integer
	increment_by  integer
	max_value     integer
	min_value     integer
	cache_value   integer
	log_cnt       integer
	is_cycled     "char"
	is_called     "char"
);

308
-- Move int8 columns of sequence out of the way
309 310 311 312
UPDATE pg_attribute
SET attrelid = 1 -- OID of template1, not used anywhere else  XXX correct?
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = '$SEQUENCE');

313
-- Replace with int4 sequence columns
314
UPDATE pg_attribute
315 316 317 318 319 320 321 322 323 324 325 326
SET attrelid = 
	(SELECT oid FROM pg_class WHERE relname = '$SEQUENCE')
WHERE attrelid = 
	(SELECT oid FROM pg_class WHERE relname = 'pg_upgrade_temp_seq_int4');

-- Mark sequence as ordinary table so we can do COPY
UPDATE pg_class
SET relkind = 't'
WHERE relname = '$SEQUENCE';

-- COPY sequence out
COPY "$SEQUENCE" TO '/tmp/$$';
327

328 329 330
-- Delete int4 row from sequence
-- XXX truncate ok?
TRUNCATE "$SEQUENCE";
331 332 333

-- Prepare int4 sequence table for removal and remove it
UPDATE pg_attribute
334 335 336 337
SET attrelid = 
	(SELECT oid FROM pg_class WHERE relname = 'pg_upgrade_temp_seq_int4')
WHERE attrelid = 
	(SELECT oid FROM pg_class WHERE relname = '$SEQUENCE');
338

339
DROP TABLE pg_upgrade_temp_seq_int4;
340 341 342 343 344 345

-- Restore int8 version of sequence
UPDATE pg_attribute
SET attrelid = (SELECT oid FROM pg_class WHERE relname = '$SEQUENCE')
WHERE attrelid = 1;

346 347
-- Load new values
COPY "$SEQUENCE" FROM '/tmp/$$';
348

349
-- If previous max was int4, make it int8
350
UPDATE "$SEQUENCE"
351 352
SET max_value = 9223372036854775807
WHERE max_value BETWEEN 2147483646 AND 2147483648; -- OS rounding
353 354 355 356 357 358 359

-- Restore sequence flag
UPDATE pg_class
SET relkind = 'S'
WHERE relname = '$SEQUENCE';

SQL_END
360 361 362 363 364
			if [ $? -ne 0 ]
			then	echo "There were errors during int4 sequence restore.
$0 aborted." 1>&2
				exit 1
			fi
365 366 367 368
		done
	done
fi

369
echo "You may remove the $OLDDIR directory with 'rm -r $OLDDIR'."
370

371
exit 0