Skip to content

Commit 7a0672b

Browse files
bmomjianbmomjian
committed
More pg_upgrade improvements. Almost done, except for max transaction
setting.
1 parent 4d151d0 commit 7a0672b

File tree

2 files changed

+78
-49
lines changed

2 files changed

+78
-49
lines changed

‎doc/src/sgml/ref/pg_upgrade.sgml

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/Attic/pg_upgrade.sgml,v 1.14 2002/01/09 21:50:51 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/Attic/pg_upgrade.sgml,v 1.15 2002/01/10 04:58:19 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -50,7 +50,10 @@ pg_upgrade -s <replaceable class="parameter">filename</replaceable> [ -d <replac
5050
<step performance="required">
5151
<para>
5252
Back up your existing data directory, preferably by making a
53-
complete dump with pg_dumpall.
53+
complete dump with pg_dumpall. Those upgrading from 7.1 are
54+
required to supply this dump filename to pg_upgrade with the
55+
<option>-d</option> option. Other releases should not use the
56+
<option>-d</option> option.
5457
</para>
5558
</step>
5659

‎src/bin/pg_dump/pg_upgrade

Lines changed: 73 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
# pg_upgrade: update a database without needing a full dump/reload cycle.
44
# CAUTION: read the manual page before trying to use this!
55

6-
# $Header: /cvsroot/pgsql/src/bin/pg_dump/Attic/pg_upgrade,v 1.21 2002/01/10 03:05:48 momjian Exp $
6+
# $Header: /cvsroot/pgsql/src/bin/pg_dump/Attic/pg_upgrade,v 1.22 2002/01/10 04:58:19 momjian Exp $
77
#
88
# NOTE: we must be sure to update the version-checking code a few dozen lines
99
# below for each new PostgreSQL release.
@@ -77,51 +77,60 @@ then echo "Cannot read ./$OLDDIR/PG_VERSION --- something is wrong." 1>&2
7777
fi
7878

7979
# Get the actual versions seen in the data dirs.
80-
DESTVERSION=`cat ./data/PG_VERSION`
81-
SRCVERSION=`cat ./$OLDDIR/PG_VERSION`
80+
DEST_VERSION=`cat ./data/PG_VERSION`
81+
SRC_VERSION=`cat ./$OLDDIR/PG_VERSION`
8282

8383
# Check for version compatibility.
8484
# This code will need to be updated/reviewed for each new PostgreSQL release.
8585

86-
# MYVERSION is the expected output database version
87-
MYVERSION="7.1"
86+
# UPGRADE_VERSION is the expected output database version
87+
UPGRADE_VERSION="7.1"
8888

89-
if [ "$SRCVERSION" = "7.1" -a ! "$DATA" ]
90-
then echo "$0 requires a full data dump file to upgrade from version $SRCVERSION." 1>&2
91-
echo "Use the '-d' parameter to specify the dump file" 1>&2
89+
if [ "$SRC_VERSION" = "7.1" -a ! "$DATA" ]
90+
then echo "$0 requires a full data dump file to upgrade from version $SRC_VERSION." 1>&2
91+
echo "Use the '-d' parameter to specify the data dump file" 1>&2
92+
echo "If you don't have enough disk space to keep a dump file, grep out the '\\connect' and" 1>&2
93+
echo "'SELECT setval' lines from the dump file and pass that file to $0, e.g:" 1>&2
94+
echo 1>&2
95+
echo " pg_dumpall | egrep '^(\\connect)|SELECT setval \()[^ ]*$' > data.out" 1>&2
9296
exit 1
9397
fi
9498

95-
if [ "$DESTVERSION" != "$MYVERSION" -a "$DESTVERSION" != "$SRCVERSION" ]
96-
then echo "$0 is for PostgreSQL version $MYVERSION, but ./data/PG_VERSION contains $DESTVERSION." 1>&2
97-
echo "Did you run initdb for version $MYVERSION?" 1>&2
99+
if [ "$SRC_VERSION" != "7.1" -a "$DATA" ]
100+
then echo "$0 does not require the -d option for this version." 1>&2
101+
exit 1
102+
fi
103+
104+
if [ "$DEST_VERSION" != "$UPGRADE_VERSION" -a "$DEST_VERSION" != "$SRC_VERSION" ]
105+
then echo "`basename $0` is for PostgreSQL version $UPGRADE_VERSION, but ./data/PG_VERSION contains $DEST_VERSION." 1>&2
106+
echo "Did you run initdb for version $UPGRADE_VERSION?" 1>&2
98107
exit 1
99108
fi
100109

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

107-
case "$SRCVERSION" in
116+
case "$SRC_VERSION" in
108117
# 7.2) ;;
109-
*) echo "Sorry, `basename $0` cannot upgrade database version $SRCVERSION to $DESTVERSION." 1>&2
118+
*) echo "Sorry, `basename $0` cannot upgrade database version $SRC_VERSION to $DEST_VERSION." 1>&2
110119
echo "The on-disk structure of tables has changed." 1>&2
111-
echo "You will need to dump and restore using pg_dump." 1>&2
120+
echo "You will need to dump and restore using pg_dumpall." 1>&2
112121
exit 1;;
113122
esac
114123

115124

116-
# OK, ready to proceed.
125+
# Checking done. Ready to proceed.
117126

118127
# Execute the schema script to create everything, except modify any
119128
# sequences with int4 maximums if we are upgrading from 7.1.
120129
cat $SCHEMA | awk -F' ' '{
121-
if ("'"$SRCVERSION"'" == "7.1" &&
130+
if ("'"$SRC_VERSION"'" == "7.1" &&
122131
$1 == "CREATE" &&
123132
$2 == "SEQUENCE" &&
124-
($9 >= 2147483646 && # handle OS round
133+
($9 >= 2147483646 && # handle OS rounding
125134
($9 <= 2147483648))
126135
{
127136
for(i=1; i < NF; i++)
@@ -134,32 +143,28 @@ cat $SCHEMA | awk -F' ' '{
134143
else print $0;
135144
}' |
136145
psql "template1"
137-
138146
if [ $? -ne 0 ]
139147
then echo "There were errors in the input script $SCHEMA.
140148
$0 aborted." 1>&2
141149
exit 1
142150
fi
143151

144-
145-
if [ "$SRCVERSION" != "7.1" ]
152+
# Set sequence values for 7.1-version sequences, which are int4.
153+
if [ "$SRC_VERSION" != "7.1" ]
146154
then echo "Input script $SCHEMA complete, fixing row commit statuses..."
147155
else echo "Input script $SCHEMA complete, setting int8 sequences..."
148156

149-
# Set all the sequence counters because they are not brought over
150-
# in the schema dump, and the old 7.1 sequences where int4 in size
151-
# so bringing over the file wouldn't help us anyway.
152-
cat $DATA | awk '$0 == "\\connect " || "SELECT setval (" \
153-
{print $0;}' |
154-
psql "template1"
155-
156-
if [ $? -ne 0 ]
157-
then echo "There were errors in the input script $SCHEMA.
157+
# Set all the sequence counters because they are not brought over
158+
# in the schema dump.
159+
cat $DATA | egrep '^(\\connect)|SELECT setval \()[^ ]*$' |
160+
psql "template1"
161+
if [ $? -ne 0 ]
162+
then echo "There were errors in setting the sequence values.
158163
$0 aborted." 1>&2
159-
exit 1
160-
fi
164+
exit 1
165+
fi
161166

162-
echo "Int8 sequences set, fixing row commit statuses..."
167+
echo "Int8 sequences set, fixing row commit statuses..."
163168
fi
164169

165170
# Now vacuum each result database in case our transaction increase
@@ -174,10 +179,16 @@ $0 aborted." 1>&2
174179
fi
175180
done
176181

177-
# should be pretty small file
182+
# Used for scans looking for a database/tablename match
183+
# New oid is looked up
178184
pg_dumpall -s > $TMPFILE 2>/dev/null
185+
if [ "$?" -ne 0 ]
186+
then echo "Unable to dump schema of new database.; exiting" 1>&2
187+
exit 1
188+
fi
179189

180-
# flush buffers to disk
190+
# we are done with SQL database access
191+
# shutdown forces buffers to disk
181192
pg_ctl stop
182193
if [ "$?" -ne 0 ]
183194
then echo "Unable to stop database server.; exiting" 1>&2
@@ -188,7 +199,7 @@ echo "Commit fixes complete, moving data files..."
188199

189200
cat "$SCHEMA" | while read LINE
190201
do
191-
if /bin/echo "$LINE" | grep -q "^\\\\connect [^ ]*$"
202+
if /bin/echo "$LINE" | grep -q '^\\connect [^ ]*$'
192203
then OLDDB="$DB"
193204
DB="`/bin/echo \"$LINE\" | cut -d' ' -f2`"
194205
if [ "$DB" = "-" ]
@@ -208,55 +219,71 @@ do
208219
then TABLE=""
209220
fi
210221
fi
222+
# 7.1 sequences were handled earlier because they were int4.
223+
if test "$SRC_VERSION" != "7.1" &&
224+
echo "$LINE" | egrep -q "^-- Name: [^ ]* Type: SEQUENCE "
225+
then TABLE="`echo \"$LINE\" | cut -d' ' -f3`"
226+
# skip system tables
227+
if [ "`echo \"$TABLE\" | cut -c 1-3`" = "pg_" ]
228+
then TABLE=""
229+
fi
230+
fi
211231
if [ "$DB" -a "$OID" -a "$TABLE" ]
212232
then
213233
NEWOID=`awk -F' ' '
214-
BEGIN { newdb=""; newoid="";
234+
BEGIN { newdb=""; newoid="";
215235
newtable=""; ret=0;}
216236
$1 == "\\\\connect" && $2 != "-" {newdb=$2;}
217237
$0 ~ /^-- TOC Entry ID [0-9]* .OID / \
218238
{ newoid = substr($7, 1, length($7)-1);}
219239
{print $0 >> "/tmp/x";
220240
print $3 >> "/tmp/x";
221241
print newdb," ", newoid >> "/tmp/x"}
222-
($0 ~ /^-- Name: [^ ]* Type: TABLE / && \
223-
$0 ~ /^-- Name: [^ ]* Type: INDEX /) && \
242+
($0 ~ /^-- Name: [^ ]* Type: TABLE / || \
243+
$0 ~ /^-- Name: [^ ]* Type: INDEX / || \
244+
$0 ~ /^-- Name: [^ ]* Type: SEQUENCE /) && \
224245
newdb == "'"$DB"'" && \
225246
$3 == "'"$TABLE"'" \
226247
{ ret=newoid; exit}
227248
END { print ret;}' $TMPFILE`
228249
if [ "$NEWOID" -eq 0 ]
229-
then echo "Move of database $DB, OID $OID, table $TABLE failed.\nNew oid not found; exiting" 1>&2
250+
then echo "Move of database $DB, OID $OID, table $TABLE failed.
251+
New oid not found; exiting" 1>&2
230252
exit 1
231253
fi
232254
# We use stars so we don't have to worry about database oids
233255
if [ `ls "$OLDDIR"/base/*/"$OID" | wc -l` -eq 0 ]
234-
then echo "Move of database $DB, OID $OID, table $TABLE failed.\nFile not found; exiting" 1>&2
256+
then echo "Move of database $DB, OID $OID, table $TABLE failed.
257+
File not found; exiting" 1>&2
235258
exit 1
236259
fi
237260
if [ `ls "$OLDDIR"/base/*/"$OID" | wc -l` -gt 1 ]
238-
then echo "Move of database $DB, OID $OID, table $TABLE failed.\nToo many found; exiting" 1>&2
261+
then echo "Move of database $DB, OID $OID, table $TABLE failed.
262+
Too many found; exiting" 1>&2
239263
exit 1
240264
fi
241265
if [ `ls data/base/*/"$NEWOID" | wc -l` -eq 0 ]
242-
then echo "Move of database $DB, OID $OID, table $TABLE to $NEWOID failed.\nFile not found; exiting" 1>&2
266+
then echo "Move of database $DB, OID $OID, table $TABLE to $NEWOID failed.
267+
File not found; exiting" 1>&2
243268
exit 1
244269
fi
245270
if [ `ls data/base/*/"$NEWOID" | wc -l` -gt 1 ]
246-
then echo "Move of database $DB, OID $OID, table $TABLE to $NEWOID failed.\nToo many found; exiting" 1>&2
271+
then echo "Move of database $DB, OID $OID, table $TABLE to $NEWOID failed.
272+
Too many found; exiting" 1>&2
247273
exit 1
248274
fi
249275
mv -f "$OLDDIR"/base/*/"$OID" data/base/*/"$NEWOID"
250276
if [ "$?" -ne 0 ]
251-
then echo "Move of database $DB, OID $OID, table $TABLE \n to $NEWOID failed.; exiting" 1>&2
277+
then echo "Move of database $DB, OID $OID, table $TABLE
278+
to $NEWOID failed.; exiting" 1>&2
252279
exit 1
253280
fi
254281
TABLE=""
255282
fi
256283
done
257284

258285
# 7.1 has non-compressed log file format
259-
if [ "$SRCVERSION" = "7.1" ]
286+
if [ "$SRC_VERSION" = "7.1" ]
260287
then
261288
# pg_log is oid 1269 in 7.1
262289
LOGSIZE=`ls -l "$OLDDIR"/global/1269 "$OLDDIR"/global/1269.* 2>/dev/null |
@@ -270,7 +297,6 @@ then
270297
# set max transaction id
271298

272299
else
273-
# how to handle 7.2?
274300
rm -r data/pg_clog &&
275301
mv "$OLDDIR"/data/pg_clog data/pg_clog &&
276302
mv "$OLDDIR"/data/global/pg_control data/global/pg_control

0 commit comments

Comments
 (0)