1 <!-- $PostgreSQL: pgsql/doc/src/sgml/pgupgrade.sgml,v 1.4 2010/05/13 22:51:00 momjian Exp $ -->
4 <title>pg_upgrade</title>
6 <indexterm zone="pgupgrade">
7 <primary>pg_upgrade</primary>
11 <application>pg_upgrade</> (formerly called pg_migrator) allows data
12 stored in Postgres data files to be migrated to a later Postgres
13 major version without the data dump/reload typically required for
14 major version upgrades, e.g. from 8.4.7 to the current major release
15 of Postgres. It is not required for minor version upgrades, e.g.
20 <title>Supported Versions</title>
23 pg_upgrade supports upgrades from 8.3.X and later to the current
24 major release of Postgres, including snapshot and alpha releases.
31 <title>Upgrade Steps</title>
36 Optionally move the old cluster
40 If you are using a version-specific PostgreSQL install directory, e.g.
41 /opt/PostgreSQL/8.4, you do not need to move the old cluster. The
42 one-click installers all use version-specific install directories.
46 If your PostgreSQL install directory is not version-specific, e.g.
47 /usr/local/pgsql, it is necessary to move the current Postgres install
48 directory so it does not interfere with the new Postgres installation.
49 Once the current Postgres server is shut down, it is safe to rename the
50 Postgres install directory; assuming the old directory is
51 /usr/local/pgsql, you can do:
54 mv /usr/local/pgsql /usr/local/pgsql.old
56 to rename the directory.
60 If you are using tablespaces and migrating to 8.4 or earlier, there must
61 be sufficient directory permissions to allow pg_upgrade to rename each
62 tablespace directory to add a ".old" suffix.
68 For PostgreSQL source installs, build the new PostgreSQL version
72 Build the new Postgres source with configure flags that are compatible
73 with the old cluster. pg_upgrade will check pg_controldata to make
74 sure all settings are compatible before starting the upgrade.
80 Install the new Postgres binaries
84 Install the new server's binaries and support files. You can use the
85 same port numbers for both clusters, typically 5432, because the old and
86 new clusters will not be running at the same time.
90 For source installs, if you wish to install the new server in a custom
91 location, use 'prefix':
94 gmake prefix=/usr/local/pgsql.new install
101 Install <application>pg_upgrade</> and
102 <application>pg_upgrade_support</> in the new PostgreSQL cluster
108 Initialize the new PostgreSQL cluster
112 Initialize the new cluster using initdb. Again, use compatible initdb
113 flags that match the old cluster (pg_upgrade will check that too.) Many
114 prebuilt installers do this step automatically. There is no need to
115 start the new cluster.
122 Install custom shared object files (or DLLs)
126 Install any custom shared object files (or DLLs) used by the old cluster
127 into the new cluster, e.g. pgcrypto.so, whether they are from /contrib
128 or some other source. Do not install the schema definitions, e.g.
129 pgcrypto.sql --- these will be migrated from the old cluster.
135 Adjust authentication
139 pg_upgrade will connect to the old and new servers several times,
140 so you might want to set authentication to <literal>trust</> in
141 <filename>pg_hba.conf</>, or if using <literal>md5</> authentication,
142 use a <filename>pgpass</> file to avoid being prompted repeatedly
153 Make sure both database servers are stopped using on Unix, e.g.:
156 pg_ctl --pgdata /opt/PostgreSQL/8.4 stop
157 pg_ctl --pgdata /opt/PostgreSQL/8.5 stop
163 NET STOP postgresql-8.4
164 NET STOP postgresql-9.0
170 NET STOP pgsql-8.3 (different service name)
179 Always run the pg_upgrade binary in the new server, not the old one.
180 pg_upgrade requires the specification of the old and new cluster's
181 PGDATA and executable (/bin) directories. You can also specify separate
182 user and port values, and whether you want the data linked instead of
183 copied (the default). If you use linking, the migration will be much
184 faster (no data copying), but you will no longer be able to access your
185 old cluster once you start the new cluster after the upgrade. See
186 pg_upgrade --help for a full list of options.
190 For Windows users, you must be logged into an administrative account, and
191 then start a shell as the 'postgres' user and set the proper path:
194 RUNAS /USER:postgres "CMD.EXE"
195 SET PATH=%PATH%;C:\Program Files\PostgreSQL\8.5\bin;
198 and then run pg_upgrade with quoted directories, e.g.:
202 --old-datadir "C:/Program Files/PostgreSQL/8.4/data"
203 --new-datadir "C:/Program Files/PostgreSQL/8.5/data"
204 --old-bindir "C:/Program Files/PostgreSQL/8.4/bin"
205 --new-bindir "C:/Program Files/PostgreSQL/8.5/bin"
208 Once started, pg_upgrade will verify the two clusters are compatible
209 and then do the migration. You can use pg_upgrade <option>--check</>
210 to perform only the checks, even if the old server is still
211 running. pg_upgrade <option>--check</> will also outline any
212 manual adjustments you will need to make after the migration.
216 Obviously, no one should be accessing the clusters during the migration.
220 If an error occurs while restoring the database schema, pg_upgrade will
221 exit and you will have to revert to the old cluster as outlined in step
222 #15 below. To try pg_upgrade again, you will need to modify the old
223 cluster so the pg_upgrade schema restore succeeds. If the problem is a
224 /contrib module, you might need to uninstall the /contrib module from
225 the old cluster and install it in the new cluster after the migration,
226 assuming the module is not being used to store user data.
232 Restore <filename>pg_hba.conf</>
236 If you modified <filename>pg_hba.conf</> to use <literal>trust</>,
237 restore its original authentication settings.
243 Post-Migration processing
247 If any post-migration processing is required, pg_upgrade will issue
248 warnings as it completes. It will also generate script files that must
249 be run by the administrator. The script files will connect to each
250 database that needs post-migration processing. Each script should be
254 psql --username postgres --file script.sql postgres
257 The scripts can be run in any order and can be deleted once they have
262 In general it is unsafe to access tables referenced in rebuild scripts
263 until the rebuild scripts have run to completion; doing so could yield
264 incorrect results or poor performance. Tables not referenced in rebuild
265 scripts can be accessed immediately.
275 Because optimizer statistics are not transferred by pg_upgrade, you will
276 be instructed to run a command to regenerate that information at the end
287 Once you are satisfied with the upgrade, you can delete the old
288 cluster's data directories by running the script mentioned when
289 pg_upgrade completes. You will need to manually delete the old install
290 directories, e.g. /bin, /share.
296 Reverting to old cluster
300 If, after running pg_upgrade, you wish to revert to the old cluster,
301 there are several options.
305 If you ran pg_upgrade with <option>--check</>, no modifications
306 were made to the old cluster and you can re-use it anytime.
310 If you ran pg_upgrade with <option>--link</>, the data files
311 are shared between the old and new cluster. If you started
312 the new cluster, the new server has written to those shared
313 files and it is unsafe to use the old cluster.
317 If you ran pg_upgrade <emphasis>without</>_ <option>--link</>
318 or did not start the new server, the old cluster was not
319 modified except that an <literal>.old</> suffix was appended
320 to <filename>$PGDATA/global/pg_control</> and perhaps tablespace
321 directories. To reuse the old cluster, remove the ".old"
322 suffix from <filename>$PGDATA/global/pg_control</>. and, if
323 migrating to 8.4 or earlier, remove the tablespace directories
324 created by the migration and remove the ".old" suffix from
325 the tablespace directory names; then you can restart the old
335 <title>Limitations In Migrating <emphasis>from</> PostgreSQL 8.3</title>
339 pg_upgrade will not work for a migration from 8.3 if a user column
344 a <type>tsquery</> data type
349 data type <type>name</> and is not the first column
356 You must drop any such columns and migrate them manually.
360 pg_upgrade will require a table rebuild if:
364 a user column is of data type tsvector
371 pg_upgrade will require a reindex if:
375 an index is of type hash or gin
380 an index uses <function>bpchar_pattern_ops</>
387 Also, the default datetime storage format changed to integer after
388 Postgres 8.3. pg_upgrade will check that the datetime storage format
389 used by the old and new clusters match. Make sure your new cluster is
390 built with the configure flag <option>--disable-integer-datetimes</>.
394 For Windows users, note that due to different integer datetimes settings
395 used by the one-click installer and the MSI installer, it is only
396 possible to upgrade from version 8.3 of the one-click distribution to
397 version 8.4 of the one-click distribution. It is not possible to upgrade
398 from the MSI installer to the one-click installer.
402 All failure, rebuild, and reindex cases will be reported by pg_upgrade
403 if they affect your installation; post-migration scripts to rebuild
404 tables and indexes will be automatically generated.
408 For deployment testing, create a schema-only copy of the old cluster,
409 insert dummy data, and migrate that.
413 If you want to use link mode and you don't want your old cluster
414 to be modified when the new cluster is started, make a copy of the
415 old cluster and migrate that with link mode. To make a valid copy
416 of the old cluster, use <application>rsync</> to create a dirty
417 copy of the old cluster while the server is running, then shut down
418 the old server and run rsync again to update the copy with any
419 changes to make it consistent.