Skip to content

Commit 3b3b730

Browse files
bmomjianbmomjian
committed
More updates for int8 sequence restoration. Old dump file not required
anymore.
1 parent 6bd45e5 commit 3b3b730

File tree

1 file changed

+42
-60
lines changed

1 file changed

+42
-60
lines changed

‎src/bin/pg_dump/pg_upgrade

Lines changed: 42 additions & 60 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
#!/bin/sh
22
#
33
# pg_upgrade: update a database without needing a full dump/reload cycle.
4-
# CAUTION: read the manual page before trying to use this!
4+
# CAUTION: Read the manual page before trying to use this!
55

6-
# $Header: /cvsroot/pgsql/src/bin/pg_dump/Attic/pg_upgrade,v 1.24 2002/01/11 04:39:19 momjian Exp $
6+
# $Header: /cvsroot/pgsql/src/bin/pg_dump/Attic/pg_upgrade,v 1.25 2002/01/11 05:54:59 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.
@@ -107,7 +107,7 @@ fi
107107

108108
if ! pg_resetxlog -x | grep -q XID
109109
then echo "Old version of pg_resetxlog found in path.
110-
Install a newer version from pgsql/contrib/pg_resetxlog and continue.; exiting" 1>&2
110+
Install a newer version from pgsql/contrib/pg_resetxlog.; exiting" 1>&2
111111
exit 1
112112
fi
113113

@@ -122,26 +122,9 @@ fi
122122

123123
# Checking done. Ready to proceed.
124124

125-
# Execute the schema script to create everything, except modify any
126-
# sequences with int4 maximums if we are upgrading from 7.1.
127-
128-
cat $SCHEMA | awk -F' ' '{
129-
if ("'"$SRC_VERSION"'" == "7.1" &&
130-
$1 == "CREATE" &&
131-
$2 == "SEQUENCE" &&
132-
($9 >= 2147483646 && # handle OS rounding
133-
($9 <= 2147483648))
134-
{
135-
for(i=1; i < NF; i++)
136-
if (i != 9)
137-
printf "%s ", $i;
138-
else
139-
printf "%s ", "9223372036854775807";
140-
print;
141-
}
142-
else print $0;
143-
}' |
144-
psql "template1"
125+
# Execute the schema script to create everything
126+
127+
psql "template1" < "$SCHEMA"
145128
if [ $? -ne 0 ]
146129
then echo "There were errors in the input script $SCHEMA.
147130
$0 aborted." 1>&2
@@ -154,17 +137,14 @@ echo "Input script $SCHEMA complete, fixing row commit statuses..."
154137
# Now vacuum each result database because our movement of transaction log
155138
# causes some committed transactions to appear as non-committed
156139

157-
psql -d template1 -At -c "SELECT datname FROM pg_database" | while read DB
158-
do
159-
echo "VACUUM;" | psql "$DB"
160-
if [ $? -ne 0 ]
161-
then echo "There were errors during VACUUM.
140+
vacuumdb -a
141+
if [ $? -ne 0 ]
142+
then echo "There were errors during VACUUM.
162143
$0 aborted." 1>&2
163-
exit 1
164-
fi
165-
done
144+
exit 1
145+
fi
166146

167-
# Used for scans looking for a database/tablename match
147+
# Used for scans looking for a database/table name match
168148
# New oid is looked up
169149
pg_dumpall -s > $TMPFILE 2>/dev/null
170150
if [ "$?" -ne 0 ]
@@ -198,16 +178,7 @@ do
198178
if echo "$LINE" | grep -q "^-- TOC Entry ID [0-9]* (OID "
199179
then OID="`echo \"$LINE\" | cut -d' ' -f7 | tr -d ')'`"
200180
fi
201-
if echo "$LINE" | egrep -q "^-- Name: [^ ]* Type: (TABLE|INDEX) "
202-
then TABLE="`echo \"$LINE\" | cut -d' ' -f3`"
203-
# skip system tables
204-
if [ "`echo \"$TABLE\" | cut -c 1-3`" = "pg_" ]
205-
then TABLE=""
206-
fi
207-
fi
208-
# 7.1 sequences were handled earlier because they were int4.
209-
if test "$SRC_VERSION" != "7.1" &&
210-
echo "$LINE" | egrep -q "^-- Name: [^ ]* Type: SEQUENCE "
181+
if echo "$LINE" | egrep -q "^-- Name: [^ ]* Type: (TABLE|INDEX|SEQUENCE) "
211182
then TABLE="`echo \"$LINE\" | cut -d' ' -f3`"
212183
# skip system tables
213184
if [ "`echo \"$TABLE\" | cut -c 1-3`" = "pg_" ]
@@ -278,7 +249,7 @@ fi
278249

279250
# set last checkpoint location from old database
280251

281-
CHKPOINT=`pg_resetxlog -n "$OLDDIR" | grep "checkpoint location" |
252+
CHKPOINT=`pg_resetxlog -n "$OLDDIR" | grep "checkpoint location:" |
282253
awk -F' *' '{print $4}'`
283254
if [ "$CHKPOINT" = "" ]
284255
then echo "Unable to get old checkpoint location.; exiting" 1>&2
@@ -314,7 +285,7 @@ else echo "Set int8 sequence values from 7.1..."
314285
psql -d "$DB" -At <<SQL_END
315286
316287
-- This table matches the 7.1 sequence schema
317-
CREATE TABLE temp_seq_int4 (
288+
CREATE TABLE pg_upgrade_temp_seq_int4 (
318289
sequence_name name
319290
last_value integer
320291
increment_by integer
@@ -326,40 +297,51 @@ CREATE TABLE temp_seq_int4 (
326297
is_called "char"
327298
);
328299
329-
-- Move int8 version of sequence out of the way
300+
-- Move int8 columns of sequence out of the way
330301
UPDATE pg_attribute
331302
SET attrelid = 1 -- OID of template1, not used anywhere else XXX correct?
332303
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = '$SEQUENCE');
333304
334-
-- Replace with int4 sequence schema
305+
-- Replace with int4 sequence columns
335306
UPDATE pg_attribute
336-
SET attrelid = (SELECT oid FROM pg_class WHERE relname = '$SEQUENCE')
337-
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'temp_seq_int4');
307+
SET attrelid =
308+
(SELECT oid FROM pg_class WHERE relname = '$SEQUENCE')
309+
WHERE attrelid =
310+
(SELECT oid FROM pg_class WHERE relname = 'pg_upgrade_temp_seq_int4');
311+
312+
-- Mark sequence as ordinary table so we can do COPY
313+
UPDATE pg_class
314+
SET relkind = 't'
315+
WHERE relname = '$SEQUENCE';
316+
317+
-- COPY sequence out
318+
COPY "$SEQUENCE" TO '/tmp/$$';
338319
339-
-- Select sequence value into temp table
340-
CREATE TEMP TABLE hold_sequence AS
341-
SELECT last_value
342-
FROM "$SEQUENCE"
320+
-- Delete int4 row from sequence
321+
-- XXX truncate ok?
322+
TRUNCATE "$SEQUENCE";
343323
344324
-- Prepare int4 sequence table for removal and remove it
345325
UPDATE pg_attribute
346-
SET attrelid = (SELECT oid FROM pg_class WHERE relname = 'temp_seq_int4')
347-
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = '$SEQUENCE');
326+
SET attrelid =
327+
(SELECT oid FROM pg_class WHERE relname = 'pg_upgrade_temp_seq_int4')
328+
WHERE attrelid =
329+
(SELECT oid FROM pg_class WHERE relname = '$SEQUENCE');
348330
349-
DROP TABLE temp_seq_int4;
331+
DROP TABLE pg_upgrade_temp_seq_int4;
350332
351333
-- Restore int8 version of sequence
352334
UPDATE pg_attribute
353335
SET attrelid = (SELECT oid FROM pg_class WHERE relname = '$SEQUENCE')
354336
WHERE attrelid = 1;
355337
356-
-- Mark sequence as ordinary table and update it
357-
UPDATE pg_class
358-
SET relkind = 't'
359-
WHERE relname = '$SEQUENCE';
338+
-- Load new values
339+
COPY "$SEQUENCE" FROM '/tmp/$$';
360340
341+
-- If previous max was int4, make it int8
361342
UPDATE "$SEQUENCE"
362-
SET last_value = (SELECT last_value FROM hold_sequence);
343+
SET max_value = 9223372036854775807
344+
WHERE max_value BETWEEN 2147483646 AND 2147483648; -- OS rounding
363345
364346
-- Restore sequence flag
365347
UPDATE pg_class

0 commit comments

Comments
 (0)