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

Commit6bd45e5

Browse files
committed
Handle int4-int8 sequence migration without full data dump.
1 parentf43b5de commit6bd45e5

File tree

2 files changed

+101
-60
lines changed

2 files changed

+101
-60
lines changed

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

Lines changed: 16 additions & 13 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.15 2002/01/10 04:58:19 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/Attic/pg_upgrade.sgml,v 1.16 2002/01/11 04:39:19 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -24,7 +24,7 @@ PostgreSQL documentation
2424
<date>1999-07-31</date>
2525
</refsynopsisdivinfo>
2626
<synopsis>
27-
pg_upgrade -s <replaceable class="parameter">filename</replaceable>[ -d <replaceable class="parameter">filename</replaceable> ]<replaceable class="parameter">old_data_dir</replaceable>
27+
pg_upgrade -s <replaceable class="parameter">filename</replaceable> <replaceable class="parameter">old_data_dir</replaceable>
2828
</synopsis>
2929
</refsynopsisdiv>
3030

@@ -50,10 +50,14 @@ 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. 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.
53+
complete dump with pg_dumpall.
54+
</para>
55+
</step>
56+
57+
<step performance="required">
58+
<para>
59+
<command>VACUUM</command> your entire database using
60+
<command>vacuumdb -a</command.>
5761
</para>
5862
</step>
5963

@@ -111,7 +115,7 @@ $ make install
111115
Change your working directory to the
112116
pgsql main directory, and type:
113117
<programlisting>
114-
$ pg_upgrade -s schema.out-d data.outdata.old
118+
$ pg_upgrade -s schema.out data.old
115119
</programlisting>
116120
The program will do some checking to make sure everything is properly
117121
configured, and will run your db.out script to recreate all the databases
@@ -130,12 +134,6 @@ $ pg_upgrade -s schema.out -d data.out data.old
130134
</para>
131135
</step>
132136

133-
<step performance="required">
134-
<para>
135-
Stop and restart the postmaster.
136-
</para>
137-
</step>
138-
139137
<step performance="required">
140138
<para>
141139
<emphasis>Carefully</emphasis> examine the contents of the upgraded
@@ -154,6 +152,11 @@ $ pg_upgrade -s schema.out -d data.out data.old
154152
</para>
155153
</step>
156154

155+
<note>
156+
<para>
157+
pg_upgrade does not migrate large objects.
158+
</para>
159+
</note>
157160
</procedure>
158161
</refsect1>
159162
</refentry>

‎src/bin/pg_dump/pg_upgrade

Lines changed: 85 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.23 2002/01/1100:27:42 momjian Exp $
6+
# $Header: /cvsroot/pgsql/src/bin/pg_dump/Attic/pg_upgrade,v 1.24 2002/01/1104:39: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.
@@ -13,7 +13,6 @@ TMPFILE="/tmp/pgupgrade.$$"
1313
trap"rm -f$TMPFILE" 0 1 2 3 15
1414

1515
SCHEMA=""
16-
DATA=""
1716
while ["$#"-gt 1 ]
1817
do
1918
if ["X$1"="X-s" ]
@@ -23,20 +22,13 @@ do
2322
exit 1
2423
fi
2524
shift 2
26-
elif ["X$1"="X-d" ]
27-
thenDATA="$2"
28-
if [!-s"$DATA" ]
29-
thenecho"$DATA does not exist"1>&2
30-
exit 1
31-
fi
32-
shift 2
33-
elseecho"Usage:$0 -s schema_dump [ -d data_dump ] old_data_dir"1>&2
25+
elseecho"Usage:$0 -s schema_dump old_data_dir"1>&2
3426
exit 1
3527
fi
3628
done
3729

3830
if ["$#"-ne 1-o!"$SCHEMA" ]
39-
thenecho"Usage:$0 -s schema_dump[ -d data_dump ]old_data_dir"1>&2
31+
thenecho"Usage:$0 -s schema_dump old_data_dir"1>&2
4032
exit 1
4133
fi
4234

@@ -86,21 +78,6 @@ SRC_VERSION=`cat ./$OLDDIR/PG_VERSION`
8678
# UPGRADE_VERSION is the expected output database version
8779
UPGRADE_VERSION="7.1"
8880

89-
if ["$SRC_VERSION"="7.1"-a!"$DATA" ]
90-
thenecho"$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-
echo1>&2
95-
echo"pg_dumpall | egrep '^(\\connect)|SELECT setval \()[^]*$' > data.out"1>&2
96-
exit 1
97-
fi
98-
99-
if ["$SRC_VERSION"!="7.1"-a"$DATA" ]
100-
thenecho"$0 does not require the -d option for this version."1>&2
101-
exit 1
102-
fi
103-
10481
if ["$DEST_VERSION"!="$UPGRADE_VERSION"-a"$DEST_VERSION"!="$SRC_VERSION" ]
10582
thenecho"`basename$0` is for PostgreSQL version$UPGRADE_VERSION, but ./data/PG_VERSION contains$DEST_VERSION."1>&2
10683
echo"Did you run initdb for version$UPGRADE_VERSION?"1>&2
@@ -134,8 +111,8 @@ Install a newer version from pgsql/contrib/pg_resetxlog and continue.; exiting"
134111
exit 1
135112
fi
136113

137-
#We need a highXIDnumber so there is 1 gig gap inXID numbers so the
138-
#moved-over rows can be frozen on next VACUUM.
114+
#If theXIDis > 2 billion, 7.1 database will have non-frozenXID's in
115+
#low numbers, and 7.2 will think they are in the future --- bad.
139116

140117
XID=`pg_resetxlog -n"$OLDDIR"| grep"NextXID"| awk -F' *''{print $4}'`
141118
if ["$SRC_VERSION"="7.1"-a"$XID"-gt 2000000000 ]
@@ -171,26 +148,11 @@ $0 aborted." 1>&2
171148
exit 1
172149
fi
173150

174-
# Set sequence values for 7.1-version sequences, which are int4.
175-
if ["$SRC_VERSION"!="7.1" ]
176-
thenecho"Input script$SCHEMA complete, fixing row commit statuses..."
177-
elseecho"Input script$SCHEMA complete, setting int8 sequences..."
151+
echo"Input script$SCHEMA complete, fixing row commit statuses..."
178152

179-
# Set all the sequence counters because they are not brought over
180-
# in the schema dump.
181-
cat$DATA| egrep'^(\\connect)|SELECT setval \()[^]*$'|
182-
psql"template1"
183-
if [$?-ne 0 ]
184-
thenecho"There were errors in setting the sequence values.
185-
$0 aborted."1>&2
186-
exit 1
187-
fi
188-
189-
echo"Int8 sequences set, fixing row commit statuses..."
190-
fi
191-
192-
# Now vacuum each result database in case our transaction increase
193-
# causes all the XID's to be marked with the frozen XID.
153+
# XXX do we still need this?
154+
# Now vacuum each result database because our movement of transaction log
155+
# causes some committed transactions to appear as non-committed
194156

195157
psql -d template1 -At -c"SELECT datname FROM pg_database"|whileread DB
196158
do
@@ -337,6 +299,82 @@ thenecho "Unable to restart database server.; exiting" 1>&2
337299
exit 1
338300
fi
339301
302+
# Set sequence values for 7.1-version sequences, which were int4.
303+
if ["$SRC_VERSION"="7.1" ]
304+
elseecho"Set int8 sequence values from 7.1..."
305+
306+
psql -d template1 -At -c"SELECT datname FROM pg_database"|
307+
whileread DB
308+
do
309+
echo"$DB"
310+
# XXX is concurrency a problem here?
311+
psql -d"$DB" -At -c"SELECT relname FROM pg_class where relkind = 'S';"|
312+
whileread SEQUENCE
313+
do
314+
psql -d"$DB" -At<<SQL_END
315+
316+
-- This table matches the 7.1 sequence schema
317+
CREATE TABLE temp_seq_int4 (
318+
sequence_name name
319+
last_value integer
320+
increment_by integer
321+
max_value integer
322+
min_value integer
323+
cache_value integer
324+
log_cnt integer
325+
is_cycled "char"
326+
is_called "char"
327+
);
328+
329+
-- Move int8 version of sequence out of the way
330+
UPDATE pg_attribute
331+
SET attrelid = 1 -- OID of template1, not used anywhere else XXX correct?
332+
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = '$SEQUENCE');
333+
334+
-- Replace with int4 sequence schema
335+
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');
338+
339+
-- Select sequence value into temp table
340+
CREATE TEMP TABLE hold_sequence AS
341+
SELECT last_value
342+
FROM "$SEQUENCE"
343+
344+
-- Prepare int4 sequence table for removal and remove it
345+
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');
348+
349+
DROP TABLE temp_seq_int4;
350+
351+
-- Restore int8 version of sequence
352+
UPDATE pg_attribute
353+
SET attrelid = (SELECT oid FROM pg_class WHERE relname = '$SEQUENCE')
354+
WHERE attrelid = 1;
355+
356+
-- Mark sequence as ordinary table and update it
357+
UPDATE pg_class
358+
SET relkind = 't'
359+
WHERE relname = '$SEQUENCE';
360+
361+
UPDATE "$SEQUENCE"
362+
SET last_value = (SELECT last_value FROM hold_sequence);
363+
364+
-- Restore sequence flag
365+
UPDATE pg_class
366+
SET relkind = 'S'
367+
WHERE relname = '$SEQUENCE';
368+
369+
SQL_END
370+
if [$?-ne 0 ]
371+
thenecho"There were errors during int4 sequence restore.
372+
$0 aborted."1>&2
373+
exit 1
374+
done
375+
done
376+
fi
377+
340378
echo"You may remove the$OLDDIR directory with 'rm -r$OLDDIR'."
341379
342380
exit 0

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp