pg_upgrade 7.41 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.20 2002/01/09 21:50:52 momjian Exp $
7
#
8 9
# NOTE: we must be sure to update the version-checking code a few dozen lines
# below for each new PostgreSQL release.
10

11 12 13
TMPFILE="/tmp/pgupgrade.$$"

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

15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
SCHEMA=""
DATA=""
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
	elif [ "X$1" = "X-d" ]
	then	DATA="$2"
		if [ ! -s "$DATA" ]
		then	echo "$DATA does not exist" 1>&2
			exit 1
		fi
		shift 2
	else	echo "Usage:  $0 -s schema_dump [ -d data_dump ] old_data_dir" 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
34 35
		exit 1
	fi
36
done
Bruce Momjian's avatar
Bruce Momjian committed
37

38 39
if [ "$#" -ne 1 -o ! "$SCHEMA" ]
then	echo "Usage:  $0 -s schema_dump [ -d data_dump ] old_data_dir" 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
40 41 42
	exit 1
fi

43
OLDDIR="$1"
Bruce Momjian's avatar
Bruce Momjian committed
44 45 46

# check things

47
if [ ! -d "./data" ]
48
then	echo "`basename $0` must be run from the directory containing the database directory \`data\' (`dirname $PGDATA`.)" 1>&2
49
	echo "You must have run initdb to create the template1 database." 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
50 51 52
	exit 1
fi

53
if [ ! -d "./$OLDDIR" ]
54 55 56 57
then	echo "You must rename your old data directory to $OLDDIR and run initdb." 1>&2
	exit 1
fi

58
if [ ! -d "./data/base/1" ]
59 60
then	echo "Cannot find database template1 in ./data/base." 1>&2
	echo "Are you running $0 as the postgres superuser?" 1>&2
61 62 63
	exit 1
fi

64
if [ ! -d "./$OLDDIR/base/1" ]
65
then	echo "There is no database template1 in ./$OLDDIR/base." 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
66 67 68
	exit 1
fi

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

74 75
if [ ! -r "./$OLDDIR/PG_VERSION" ]
then	echo "Cannot read ./$OLDDIR/PG_VERSION --- something is wrong." 1>&2
Bruce Momjian's avatar
Bruce Momjian committed
76 77 78
	exit 1
fi

79 80 81
# Get the actual versions seen in the data dirs.
DESTVERSION=`cat ./data/PG_VERSION`
SRCVERSION=`cat ./$OLDDIR/PG_VERSION`
Bruce Momjian's avatar
Bruce Momjian committed
82

83 84 85 86
# Check for version compatibility.
# This code will need to be updated/reviewed for each new PostgreSQL release.

# MYVERSION is the expected output database version
87
MYVERSION="7.1"
88

89 90 91 92 93 94
if [ "$SRCVERSION" = "7.1" -a ! "$DATA" ]
then	echo "$0 requires a full data dump file to upgrade from version $SRCVERSION." 1>&2
	echo "Use the '-d' parameter to specify the dump file" 1>&2
	exit 1
fi

95
if [ "$DESTVERSION" != "$MYVERSION" -a "$DESTVERSION" != "$SRCVERSION" ]
96 97 98 99 100 101 102 103 104 105 106 107
then	echo "$0 is for PostgreSQL version $MYVERSION, but ./data/PG_VERSION contains $DESTVERSION." 1>&2
	echo "Did you run initdb for version $MYVERSION?" 1>&2
	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
# something like "$SRCVERSION -ge $MINVERSION" because test(1) isn't bright
# enough to compare dotted version strings properly.  Using a case statement
# looks uglier but is more flexible.

case "$SRCVERSION" in
108
#    7.2) ;;
109 110 111 112 113 114 115 116 117
    *)	echo "Sorry, `basename $0` cannot upgrade database version $SRCVERSION to $DESTVERSION." 1>&2
	echo "The on-disk structure of tables has changed." 1>&2
	echo "You will need to dump and restore using pg_dump." 1>&2
	exit 1;;
esac


# OK, ready to proceed.

118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133
# Execute the schema script to create everything, except modify any
# sequences with int4 maximums if we are upgrading from 7.1.
cat $SCHEMA | awk -F' ' '{
				if ("'"$SRCVERSION"'" == "7.1" &&
				    $1 == "CREATE" &&
				    $2 == "SEQUENCE" &&
				    ($9 >= 2147483646 && # handle OS round
				    ($9 <= 2147483648))
				{
					for(i=1; i < NF; i++)
						if (i != 9)
							printf "%s ", $i;
						else
							printf "%s ", "9223372036854775807";
					print;
				}
Bruce Momjian's avatar
Bruce Momjian committed
134
				else	print $0;
135 136
			}' |
psql "template1"
137 138

if [ $? -ne 0 ]
139
then	echo "There were errors in the input script $SCHEMA.
140 141 142
$0 aborted." 1>&2
	exit 1
fi
143

144 145 146 147 148 149 150 151 152 153 154

if [ "$SRCVERSION" != "7.1" ]
then	echo "Input script $SCHEMA complete, fixing row commit statuses..."
else	echo "Input script $SCHEMA complete, setting int8 sequences..."

# Set all the sequence counters because they are not brought over
# in the schema dump, and the old 7.1 sequences where int4 in size
# so bringing over the file wouldn't help us anyway.
cat $DATA | awk '$0 == "\\connect " || "SELECT setval (" \
			{print $0;}' |
psql "template1"
Bruce Momjian's avatar
Bruce Momjian committed
155

156
if [ $? -ne 0 ]
157
then	echo "There were errors in the input script $SCHEMA.
158
$0 aborted." 1>&2
159 160 161
	exit 1
fi

162 163 164 165 166 167 168 169 170 171 172 173 174 175 176
echo "Int8 sequences set, fixing row commit statuses..."
fi

# Now vacuum each result database in case our transaction increase
# causes all the XID's to be marked with the frozen XID.
psql -l | while read DB
do
	echo "VACUUM;" | psql "$DB"
	if [ $? -ne 0 ]
	then	echo "There were errors during VACUUM.
$0 aborted." 1>&2
		exit 1
	fi
done

177 178 179 180 181 182
# should be pretty small file
pg_dumpall -s > $TMPFILE 2>/dev/null

# flush buffers to disk
pg_ctl stop

183
echo "Commit fixes complete, moving data files..."
184

185
cat "$SCHEMA" | while read LINE
Bruce Momjian's avatar
Bruce Momjian committed
186
do
187 188 189 190 191 192 193 194 195 196 197 198 199
	if /bin/echo "$LINE" | grep -q "^\\\\connect "
	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
200
	if echo "$LINE" | egrep -q "^-- Name: [^ ]* Type: (TABLE|INDEX) "
201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217
	then	TABLE="`echo \"$LINE\" | cut -d' ' -f3`"
		# skip system tables
		if [ "`echo \"$TABLE\" | cut -c 1-3`" = "pg_" ]
		then	TABLE=""
		fi
	fi
	if [ "$DB" -a "$OID" -a "$TABLE" ]
	then
		NEWOID=`awk -F' ' '
				BEGIN 	{ newdb=""; newoid=""; 
					  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"}
218 219
				($0 ~ /^-- Name: [^ ]* Type: TABLE / && \
				 $0 ~ /^-- Name: [^ ]* Type: INDEX /) && \
220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250
				newdb == "'"$DB"'" && \
				$3 == "'"$TABLE"'" \
					{ ret=newoid; exit}
				END { print ret;}' $TMPFILE`
		if [ "$NEWOID" -eq 0 ]
		then	echo "Move of database $DB, OID $OID, table $TABLE failed.\nNew oid not found;  exiting" 1>&2
			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 ]
		then	echo "Move of database $DB, OID $OID, table $TABLE failed.\nFile not found;  exiting" 1>&2
			exit 1
		fi
  		if [ `ls "$OLDDIR"/base/*/"$OID" | wc -l` -gt 1 ]
		then	echo "Move of database $DB, OID $OID, table $TABLE failed.\nToo many found;  exiting" 1>&2
			exit 1
		fi
		if [ `ls data/base/*/"$NEWOID" | wc -l` -eq 0 ]
		then	echo "Move of database $DB, OID $OID, table $TABLE to $NEWOID failed.\nFile not found;  exiting" 1>&2
			exit 1
		fi
		if [ `ls data/base/*/"$NEWOID" | wc -l` -gt 1 ]
		then	echo "Move of database $DB, OID $OID, table $TABLE to $NEWOID failed.\nToo many found;  exiting" 1>&2
			exit 1
		fi
		mv -f "$OLDDIR"/base/*/"$OID" data/base/*/"$NEWOID"
		if [ "$?" -ne 0 ]
		then	echo "Move of database $DB, OID $OID, table $TABLE \n to $NEWOID failed.;  exiting" 1>&2
			exit 1
		fi
		TABLE=""
Bruce Momjian's avatar
Bruce Momjian committed
251 252 253
	fi
done

254 255 256 257 258 259 260 261 262 263 264 265
# set max transaction id, check < 2gig

# 7.1 has non-compressed log file format
if [ "$SRCVERSION" = "7.1" ]
# pg_log is oid 1269 in 7.1
LOGSIZE=`ls -l "$OLDDIR"/global/1269 "$OLDDIR"/global/1269.* 2>/dev/null |
awk -F'  *' '
	BEGIN 	{sum=0;}
		{sum += $5;}
	END	{print sum;}'`
fi

Bruce Momjian's avatar
Bruce Momjian committed
266
echo "You must stop/start the postmaster before doing anything else."
267
echo "You may remove the $OLDDIR directory with 'rm -r $OLDDIR'."
268

269
exit 0