Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit3b3b730

Browse files
committed
More updates for int8 sequence restoration. Old dump file not required
anymore.
1 parent6bd45e5 commit3b3b730

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/1104:39:19 momjian Exp $
6+
# $Header: /cvsroot/pgsql/src/bin/pg_dump/Attic/pg_upgrade,v 1.25 2002/01/1105: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
thenecho"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-
elseprint $0;
143-
}'|
144-
psql"template1"
125+
# Execute the schema script to create everything
126+
127+
psql"template1"<"$SCHEMA"
145128
if [$?-ne 0 ]
146129
thenecho"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"|whileread DB
158-
do
159-
echo"VACUUM;"| psql"$DB"
160-
if [$?-ne 0 ]
161-
thenecho"There were errors during VACUUM.
140+
vacuumdb -a
141+
if [$?-ne 0 ]
142+
thenecho"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>$TMPFILE2>/dev/null
170150
if ["$?"-ne 0 ]
@@ -198,16 +178,7 @@ do
198178
ifecho"$LINE"| grep -q"^-- TOC Entry ID [0-9]* (OID"
199179
thenOID="`echo\"$LINE\"| cut -d'' -f7| tr -d')'`"
200180
fi
201-
ifecho"$LINE"| egrep -q"^-- Name: [^ ]* Type: (TABLE|INDEX)"
202-
thenTABLE="`echo\"$LINE\"| cut -d'' -f3`"
203-
# skip system tables
204-
if ["`echo\"$TABLE\"| cut -c 1-3`"="pg_" ]
205-
thenTABLE=""
206-
fi
207-
fi
208-
# 7.1 sequences were handled earlier because they were int4.
209-
iftest"$SRC_VERSION"!="7.1"&&
210-
echo"$LINE"| egrep -q"^-- Name: [^ ]* Type: SEQUENCE"
181+
ifecho"$LINE"| egrep -q"^-- Name: [^ ]* Type: (TABLE|INDEX|SEQUENCE)"
211182
thenTABLE="`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
thenecho"Unable to get old checkpoint location.; exiting"1>&2
@@ -314,7 +285,7 @@ elseecho "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 TABLEtemp_seq_int4 (
288+
CREATE TABLEpg_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 int8version of sequence out of the way
300+
-- Move int8columns 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 sequenceschema
305+
-- Replace with int4 sequencecolumns
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 TABLEtemp_seq_int4;
331+
DROP TABLEpg_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)
[8]ページ先頭
©2009-2025 Movatter.jp