data

DDEESSCCRRIIPPTTIIOONN

pg_upgrade is a utility for upgrading from a previous

ppgg__uuppggrraaddee is a utility for upgrading from a previous

PostgreSQL release without reloading all the data. Not all

PostgreSQL releases canbe handledthisway. Check the

PostgreSQL releasescan usethisutility. Check the

release notes for details about your version.

pg_upgrade must be run in two stages. In phase one you

must runpg_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

ppgg__uuppggrraaddee must be run in two stages. In phase one you

must runppgg__uuppggrraaddee with your old database installation in

place. In phase two,ppgg__uuppggrraaddee must be run on a freshly

iinniittddbb'ed current install. In both phases, the same

ppgg__uuppggrraaddee 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 withpg_dumpall.

making a complete dump withppgg__dduummppaallll..

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

$$ mmaakkee iinnssttaallll

to install the _p_g___r_e_s_e_t_x_l_o_g utility which is needed by

pg_upgrade.

ppgg__uuppggrraaddee.

6) Run initdb to create a new template1 database contain-

ing the system tables for the new release. Make sure you

alter pg_hba.conf temporarily.)

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

tory, and type:

tory again, and type:

$$ ppgg__uuppggrraaddee --22

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.

by restoring from your full ppgg__dduummppaallll backup. You can

delete the _p_g___u_p_g_r_a_d_e___i_n_f_o_/ directory when you are satis-

fied.

Theupgradeddatabaseswill 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..

The upgraded databases will be inanun-vacuumed state.

Youwillprobablywantto runa_V_A_C_U_U_M _A_N_A_L_Y_Z_Ebefore

beginning production work.

SSEEEE AALLSSOO

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

14 Jan 2002 PG_UPGRADE(1) 2

trap"rm -f /tmp/$$.*" 0 1 2 3 15

PHASE=""

if ["$#"-eq 1 ]

if ["X$1"="X-1" ]

elif ["X$1"="X-2" ]

thenPHASE="2"

elif ["X$1"="X-D" ]

thenDATADIR="$2"

if ["$PHASE"="" ]

exit 1

if ["$DATADIR"="" ]

exit 1

if [!-e data ]

if [!-d"$DATADIR" ]

if ["$PHASE"-eq 2 ]

exit 1

if ["$USER"="root"-o!-r"$DATADIR"/PG_VERSION ]

exit 1

INFODIR="pg_upgrade_info"

OLDDIR="$INFODIR/data"

SAVEDATA="$INFODIR"/"`basename\"$DATADIR\"`"

make_dbobjoidmap()

{

{

if [!-e"$OLDDIR"/base/"$SRC_DBOID"/"$SRC_OID" ]

if [!-f"$SAVEDATA"/base/"$SRC_DBOID"/"$SRC_OID"-a \

!-h"$SAVEDATA"/base/"$SRC_DBOID"/"$SRC_OID" ]

exit 1

if [!-e data/base/"$DST_DBOID"/"$DST_OID" ]

if [!-f"$DATADIR"/base/"$DST_DBOID"/"$DST_OID"-a \

!-h"$DATADIR"/base/"$DST_DBOID"/"$DST_OID" ]

exit 1

mv -f"$OLDDIR"/base/"$SRC_DBOID"/"$SRC_OID"data/base/"$DST_DBOID"/"$DST_OID"

mv -f"$SAVEDATA"/base/"$SRC_DBOID"/"$SRC_OID""$DATADIR"/base/"$DST_DBOID"/"$DST_OID"

if ["$?"-ne 0 ]

ls"$OLDDIR"/base/"$SRC_DBOID"/"$SRC_OID".*2>/dev/null|whileread FILE

ls"$SAVEDATA"/base/"$SRC_DBOID"/"$SRC_OID".*2>/dev/null|whileread FILE

EXT=`basename"$FILE"| sed's/^.*\.\(.*\)$/\1/'`

mv -f"$FILE"data/base/"$DST_DBOID"/"$DST_OID"."$EXT"

mv -f"$FILE""$DATADIR"/base/"$DST_DBOID"/"$DST_OID"."$EXT"

if ["$?"-ne 0 ]

if [!-ddata/base/1 ]

if [!-d"$DATADIR"/base/1 ]

exit 1

SRC_VERSION="`catdata/PG_VERSION`"

SRC_VERSION=`cat"$DATADIR"/PG_VERSION`

if ["$SRC_VERSION"="" ]

exit 1

exit 1

mvdata"$INFODIR"

mv"$DATADIR""$INFODIR"

if [$?-ne 0 ]

exit 1

exit 1

if [!-e"$OLDDIR" ]

if [!-d"$SAVEDATA" ]

exit 1

if [!-f"$OLDDIR/PG_VERSION" ]

if [!-f"$SAVEDATA/PG_VERSION" ]

exit 1

if [!-f"data/PG_VERSION" ]

if [!-f"$DATADIR/PG_VERSION" ]

exit 1

if [!-d"data/base/1" ]

if [!-d"$DATADIR/base/1" ]

exit 1

SRC_VERSION=`cat"$OLDDIR"/PG_VERSION`

DST_VERSION=`catdata/PG_VERSION`

SRC_VERSION=`cat"$SAVEDATA"/PG_VERSION`

DST_VERSION=`cat"$DATADIR"/PG_VERSION`

if ["$DST_VERSION"!="$CUR_VERSION" ]

exit 1

SRC_XID=`pg_resetxlog -n"$OLDDIR"| grep"NextXID"| awk -F' *''{print $4}'`

SRC_XID=`pg_resetxlog -n"$SAVEDATA"| grep"NextXID"| awk -F' *''{print $4}'`

if ["$SRC_VERSION"="7.1"-a"$SRC_XID"-gt 2000000000 ]

exit 1

DST_XID=`pg_resetxlog -ndata| grep"NextXID"| awk -F' *''{print $4}'`

DST_XID=`pg_resetxlog -n"$DATADIR"| grep"NextXID"| awk -F' *''{print $4}'`

pg_resetxlog -n"$OLDDIR"| grep"^LC_"> /tmp/$$.0

pg_resetxlog -ndata| grep"^LC_"> /tmp/$$.1

pg_resetxlog -n"$SAVEDATA"| grep"^LC_"> /tmp/$$.0

pg_resetxlog -n"$DATADIR"| grep"^LC_"> /tmp/$$.1

if! diff /tmp/$$.0 /tmp/$$.1>/dev/null

exit 1

elseMAX_XID="$DST_XID"

pg_resetxlog -x"$MAX_XID"data

pg_resetxlog -x"$MAX_XID""$DATADIR"

if ["$?"-ne 0 ]

exit 1

rm -rdata/pg_xlog

mv -f"$OLDDIR"/pg_xlogdata

rm -r"$DATADIR"/pg_xlog

mv -f"$SAVEDATA"/pg_xlog"$DATADIR"

LOG_ID=`pg_resetxlog -n"$OLDDIR"| grep"Current log file id:"|

LOG_ID=`pg_resetxlog -n"$SAVEDATA"| grep"Current log file id:"|

if ["$LOG_ID"="" ]

exit 1

SEG_ID=`pg_resetxlog -n"$OLDDIR"| grep"Next log file segment:"|

SEG_ID=`pg_resetxlog -n"$SAVEDATA"| grep"Next log file segment:"|

if ["$SEG_ID"="" ]

pg_resetxlog -l"$LOG_ID""$SEG_ID"data

pg_resetxlog -l"$LOG_ID""$SEG_ID""$DATADIR"

if ["$?"-ne 0 ]

exit 1

pg_upgrade\-upgrade tool

pg_upgrade\-upgrading from a previous release without reloading

pg_upgrade is a utility for upgrading from a previous PostgreSQL release

without reloading all the data. Not all PostgreSQL releases canbe

handledthisway. Check the release notes for details about your

\fBpg_upgrade\fP is a utility for upgrading from a previous PostgreSQL release

without reloading all the data. Not all PostgreSQL releases canuse

thisutility. Check the release notes for details about your

version.

.LP

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 freshlyinitdb'ed current install.

In both phases, the samepg_upgrade script matching the newly installed

\fBpg_upgrade\fP must be run in two stages. In phase one you must run

\fBpg_upgrade\fP with your old database installation in place. In phase two,

\fBpg_upgrade\fP must be run on a freshly\fBinitdb\fP'ed current install.

In both phases, the same\fBpg_upgrade\fP script matching the newly installed

version must be used.

.LP

1) Back up your existing data directory, preferably by making a complete

dump withpg_dumpall.

dump with\fBpg_dumpall.\fP

.LP

2) Copy the program\fIpgsql/contrib/pg_upgrade/pg_upgrade\fP from the current

PostgreSQL distribution somewhere into your path.

.B$pg_upgrade-1

.sp

to collect information about the old database needed for the upgrade.

You may use\fI-D\fP to specify the data directory, or use PGDATA.

.LP

4) Do:

.LP

.B$makeinstall

.sp

to install the\fIpg_resetxlog\fP utility which is needed by

pg_upgrade.

\fBpg_upgrade\fP.

.LP

6) Run initdb to create a new template1 database containing the system

tables for the new release. Make sure you use settings similar to those

to the server until the upgrade is complete. You may wish to start the

postmaster without -i or alter pg_hba.conf temporarily.)

.LP

8) Change your working directory to the pgsql main directory, and type:

8) Change your working directory to the pgsql main directory again, and type:

.LP

.B$pg_upgrade-2

.sp

.LP

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\fIpg_upgrade_info/\fP directory when you

\fBpg_dumpall\fP backup. You can delete the\fIpg_upgrade_info/\fP directory when you

are satisfied.

.LP

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

probably want to run a\fIVACUUMANALYZE\fBbeforebeginningproductionwork.

probably want to run a\fIVACUUMANALYZE\fP before beginning production work.

initdb(1), postmaster(1), pg_dump(1), pg_dumpall(1), vacuumdb(1)