1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
|
#!/bin/sh
#
# pg_upgrade: update a database without needing a full dump/reload cycle.
# CAUTION: read the manual page before trying to use this!
# $Header: /cvsroot/pgsql/src/bin/pg_dump/Attic/pg_upgrade,v 1.19 2002/01/09 16:08:54 momjian Exp $
#
# NOTE: we must be sure to update the version-checking code a few dozen lines
# below for each new PostgreSQL release.
TMPFILE="/tmp/pgupgrade.$$"
trap "rm -f $TMPFILE" 0 1 2 3 15
if [ "$#" -eq 0 ]
then echo "Usage: $0 -f inputfile old_data_dir" 1>&2
exit 1
fi
if [ "X$1" = "X-f" ]
then INPUT="$2"
shift 2
if [ ! -f "$INPUT" ]
then echo "$INPUT does not exist" 1>&2
exit 1
fi
else echo "Usage: $0 -f inputfile old_data_dir" 1>&2
exit 1
fi
if [ "$#" -ne 1 ]
then echo "Usage: $0 -f inputfile old_data_dir" 1>&2
exit 1
fi
OLDDIR="$1"
# check things
if [ ! -d "./data" ]
then echo "`basename $0` must be run from the directory containing
the database directory \`data\' (`dirname $PGDATA`.)" 1>&2
echo "You must have run initdb to create the template1 database." 1>&2
exit 1
fi
if [ ! -d "./$OLDDIR" ]
then echo "You must rename your old data directory to $OLDDIR and run initdb." 1>&2
exit 1
fi
if [ ! -d "./data/base/1" ]
then echo "Cannot find database template1 in ./data/base." 1>&2
echo "Are you running $0 as the postgres superuser?" 1>&2
exit 1
fi
if [ ! -d "./$OLDDIR/base/1" ]
then echo "There is no database template1 in ./$OLDDIR/base." 1>&2
exit 1
fi
if [ ! -r "./data/PG_VERSION" ]
then echo "Cannot read ./data/PG_VERSION --- something is wrong." 1>&2
exit 1
fi
if [ ! -r "./$OLDDIR/PG_VERSION" ]
then echo "Cannot read ./$OLDDIR/PG_VERSION --- something is wrong." 1>&2
exit 1
fi
# Get the actual versions seen in the data dirs.
DESTVERSION=`cat ./data/PG_VERSION`
SRCVERSION=`cat ./$OLDDIR/PG_VERSION`
# Check for version compatibility.
# This code will need to be updated/reviewed for each new PostgreSQL release.
# MYVERSION is the expected output database version
MYVERSION="7.1"
if [ "$DESTVERSION" != "$MYVERSION" -a "$DESTVERSION" != "$SRCVERSION" ]
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
# 7.2) ;;
*) 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.
# Execute the input script to create everything, except that we remove
# any COPY statements, except for the ones that load pg_shadow/pg_group.
# There shouldn't be any others in there anyway...
cat $INPUT | awk ' {
if (tolower($1) == "copy" &&
$2 != "pg_shadow" &&
$2 != "pg_group")
while (getline $0 > 0 && $0 != "\\.")
;
else print $0;
}' > $TMPFILE
psql "template1" < $TMPFILE
if [ $? -ne 0 ]
then echo "There were errors in the input script $INPUT.
$0 aborted." 1>&2
exit 1
fi
echo "Input script $INPUT complete, fixing row commit statuses..."
# Now vacuum each result database to mark all system-table rows as committed,
# because when pg_clog is replaced with the saved version, the transaction
# statuses will no longer match the data. VACUUM will force the on-row
# status flags to the right value so that pg_clog will not matter anymore.
# Note: we used to try to do this as part of the previous step, but that
# risks permissions problems if VACUUM is run as the wrong user.
# Note: the initial VACUUM does template1, then we do everything else.
cat $INPUT | awk 'BEGIN { print "VACUUM;" }
{
if (tolower($1) == "copy")
while (getline $0 > 0 && $0 != "\\.")
;
else if (tolower($1) == "\\connect" &&
$2 != "-" &&
$2 != "template1")
printf "\\connect %s\nVACUUM;\n", $2;
}' > $TMPFILE
psql "template1" < $TMPFILE
if [ $? -ne 0 ]
then echo "There were errors in the vacuuming step.
$0 aborted." 1>&2
exit 1
fi
# should be pretty small file
pg_dumpall -s > $TMPFILE 2>/dev/null
# flush buffers to disk
pg_ctl stop
echo "Commit fixes complete, moving data files..."
cat "$INPUT" | while read LINE
do
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
if echo "$LINE" | grep -q "^-- Name: [^ ]* Type: TABLE "
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"}
$0 ~ /^-- Name: [^ ]* Type: TABLE / && \
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=""
fi
done
echo "You must stop/start the postmaster before doing anything else."
echo "You may remove the $OLDDIR directory with 'rm -r $OLDDIR'."
exit 0
|
