PG_UPGRADE(1) PostgreSQL Client Applications PG_UPGRADE(1)

NNAAMMEE

pg_upgrade - upgrade tool

SSYYNNOOPPSSIISS

Allows upgrading from a previous release without reloading

data

DDEESSCCRRIIPPTTIIOONN

pg_upgrade is a utility for upgrading from a previous

PostgreSQL release without reloading all the data. Not all

PostgreSQL releases can be handled this way. Check the

release notes for details about your version.

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 current install. In both phases, the same

pg_upgrade script matching the newly installed version

must be used.

UUppggrraaddiinngg PPoossttggrreeSSQQLL wwiitthh ppgg__uuppggrraaddee

1) Back up your existing data directory, preferably by

making a complete dump with pg_dumpall.

2) Copy the program _p_g_s_q_l_/_c_o_n_t_r_i_b_/_p_g___u_p_g_r_a_d_e_/_p_g___u_p_g_r_a_d_e

from the current PostgreSQL distribution somewhere into

your path.

3) Change your working directory to the pgsql main direc-

tory, and type:

$$ ppgg__uuppggrraaddee --11

to collect information about the old database needed for

the upgrade.

4) Do:

$$ mmaakkee iinnssttaallll

to install the new binaries.

5) Do:

$$ ccdd ppggssqqll//ccoonnttrriibb//ppgg__rreesseettxxlloogg

$$ mmaakkee iinnssttaallll

to install the _p_g___r_e_s_e_t_x_l_o_g utility which is needed by

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.

14 Jan 2002 PG_UPGRADE(1) 1

PG_UPGRADE(1) PostgreSQL Client Applications PG_UPGRADE(1)

7) Start the new _p_o_s_t_m_a_s_t_e_r_. (Note: it is critical that

no users connect to the server until the upgrade is com-

plete. You may wish to start the postmaster without -i or

alter pg_hba.conf temporarily.)

8) Change your working directory to the pgsql main direc-

tory, and type:

$$ ppgg__uuppggrraaddee --22

The program will do some checking to make sure everything

is properly configured, and will then recreate all the

databases and tables you had, 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 _p_g___h_b_a_._c_o_n_f 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_dump backup. You can delete

the _p_g___u_p_g_r_a_d_e___i_n_f_o_/ directory when you are satisfied.

The upgraded databases will be in an un-vacuumed state.

You will probably want to run a _V_A_C_U_U_M _A_N_A_L_Y_Z_E bbeeffoorree

bbeeggiinnnniinngg pprroodduuccttiioonn wwoorrkk..

SSEEEE AALLSSOO

initdb(1), postmaster(1), pg_dump(1), pg_dumpall(1), vacu-

umdb(1)

14 Jan 2002 PG_UPGRADE(1) 2