PG_UPGRADE(1) PostgreSQL Client Applications PG_UPGRADE(1)NAMEpg_upgrade - upgrading from a previous release without
reloading
SYNOPSISpg_upgrade [-D data_dir] -1 | -2
DESCRIPTIONpg_upgrade is a utility for upgrading from a previous
PostgreSQL release without reloading all the data. It can
also be used as a data recovery tool.
pg_upgrade must be run in two stages. In phase one you
must run pg_upgrade with your old database installation in
place. In phase two, pg_upgrade must be run on a freshly
initdb'ed server. In both phases, the same newly
installed pg_upgrade script must be used.
Upgrading PostgreSQL with pg_upgrade
1) Back up your existing data directory, preferably using
pg_dumpall.
2) Copy the program pgsql/contrib/pg_upgrade/pg_upgrade
from the current PostgreSQL distribution somewhere into
your path.
3) Run phase one of pg_upgrade:
$ pg_upgrade-1
to collect information about the old database needed for
the upgrade. You may use -D to specify the data direc-
tory. By default it uses the environment variable PGDATA.
4) Do:
$ cd pgsql/src
$ make install
to install the PostgreSQL binaries for the new release.
5) Do:
$ cd pgsql/contrib/pg_resetxlog
$ make install
to install the pg_resetxlog utility, which is needed dur-
ing phase 2 of pg_upgrade.
6) Run initdb to create a new template1 database contain-
ing the system tables for the new release. Make sure you
use settings similar to those used in your previous ver-
sion.
7) Start the new postmaster. (Note: it is critical that no
users connect to the server until the upgrade is complete.
You may wish to start the postmaster without -i or alter
pg_hba.conf temporarily.)
8) Run phase two of pg_upgrade:
$ pg_upgrade-2
The program will do some checking to make sure everything
is properly configured, and will then recreate all your
databases and tables, but with no data. It will then
physically move the data files containing non-system
tables and indexes into the proper subdirectories.
9) Restore your old postmaster flags or pg_hba.conf if
needed to allow user logins.
10) Carefully examine the contents of the upgraded
databases. If you detect problems, you'll need to recover
by restoring from your full pg_dumpall backup. You can
delete the pg_upgrade_info/ directory when you are satis-
fied.
The upgraded databases will be in an un-vacuumed state.
You will probably want to run a VACUUM ANALYZE before
beginning production work.
NOTES
While pg_upgrade is primarly an upgrade tool, it can also
be used for data recovery. During phase 1, pg_upgrade cre-
ates database name / oid and database name / table name /
oid mapping files in pg_upgrade_info/. These files are
tab-delimited. If your system is too damaged, you may need
to manually pull this information out of pg_database and
pg_class and create the files manually. (Keep in mind
most tables have pg_toast_OID and pg_toast_OID_idx files
that store very long values. These must be recorded as
well.) It also creates a schema-only pg_dumpall. In a dam-
aged installation, you may be able to make one of these
from a recent full pg_dumpall.
Phase 2 rebuilds each database with the schema from the
old installation. It then moves the physical data files
from the old installation and makes some modifications so
the old data files work properly in the new installation.
SEE ALSOinitdb(1), postmaster(1), pg_dump(1), pg_dumpall(1), vacu-
umdb(1)14 Jan 2002PG_UPGRADE(1)PG_UPGRADE(1)
