NAME¶

pg_upgrade - upgrade a PostgreSQL server instance

SYNOPSIS¶

pg_upgrade -b oldbindir [-B newbindir] -d oldconfigdir -D newconfigdir [option...]

DESCRIPTION¶

pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/restore typically required for major version upgrades, e.g., from 12.14 to 13.10 or from 14.9 to 15.5. It is not required for minor version upgrades, e.g., from 12.7 to 12.8 or from 14.1 to 14.5.

Major PostgreSQL releases regularly add new features that often change the layout of the system tables, but the internal data storage format rarely changes. pg_upgrade uses this fact to perform rapid upgrades by creating new system tables and simply reusing the old user data files. If a future major release ever changes the data storage format in a way that makes the old data format unreadable, pg_upgrade will not be usable for such upgrades. (The community will attempt to avoid such situations.)

pg_upgrade does its best to make sure the old and new clusters are binary-compatible, e.g., by checking for compatible compile-time settings, including 32/64-bit binaries. It is important that any external modules are also binary compatible, though this cannot be checked by pg_upgrade.

pg_upgrade supports upgrades from 9.2.X and later to the current major release of PostgreSQL, including snapshot and beta releases.

OPTIONS¶

pg_upgrade accepts the following command-line arguments:

-b bindir
--old-bindir=bindir

-B bindir
--new-bindir=bindir

-c
--check

-d configdir
--old-datadir=configdir

-D configdir
--new-datadir=configdir

-j njobs
--jobs=njobs

-k
--link

-N
--no-sync

-o options
--old-options options

-O options
--new-options options

-p port
--old-port=port

-P port
--new-port=port

-r
--retain

-s dir
--socketdir=dir

-U username
--username=username

-v
--verbose

-V
--version

--clone

--copy

-?
--help

USAGE¶

These are the steps to perform an upgrade with pg_upgrade:

mv /usr/local/pgsql /usr/local/pgsql.old

make prefix=/usr/local/pgsql.new install

pg_ctl -D /opt/PostgreSQL/12 stop
pg_ctl -D /opt/PostgreSQL/16 stop

NET STOP postgresql-12
NET STOP postgresql-16

RUNAS /USER:postgres "CMD.EXE"
SET PATH=%PATH%;C:\Program Files\PostgreSQL\16\bin;

pg_upgrade.exe


        --old-datadir "C:/Program Files/PostgreSQL/12/data"


        --new-datadir "C:/Program Files/PostgreSQL/16/data"


        --old-bindir "C:/Program Files/PostgreSQL/12/bin"


        --new-bindir "C:/Program Files/PostgreSQL/16/bin"

rsync --archive --delete --hard-links --size-only --no-inc-recursive old_cluster new_cluster remote_dir

rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/PostgreSQL/12 \


      /opt/PostgreSQL/16 standby.example.com:/opt/PostgreSQL

rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tblsp/PG_12_201909212 \


      /vol1/pg_tblsp/PG_16_202307071 standby.example.com:/vol1/pg_tblsp

psql --username=postgres --file=script.sql postgres

NOTES¶

pg_upgrade creates various working files, such as schema dumps, stored within pg_upgrade_output.d in the directory of the new cluster. Each run creates a new subdirectory named with a timestamp formatted as per ISO 8601 (%Y%m%dT%H%M%S), where all its generated files are stored. pg_upgrade_output.d and its contained files will be removed automatically if pg_upgrade completes successfully; but in the event of trouble, the files there may provide useful debugging information.

pg_upgrade launches short-lived postmasters in the old and new data directories. Temporary Unix socket files for communication with these postmasters are, by default, made in the current working directory. In some situations the path name for the current directory might be too long to be a valid socket name. In that case you can use the -s option to put the socket files in some directory with a shorter path name. For security, be sure that that directory is not readable or writable by any other users. (This is not supported on Windows.)

All failure, rebuild, and reindex cases will be reported by pg_upgrade if they affect your installation; post-upgrade scripts to rebuild tables and indexes will be generated automatically. If you are trying to automate the upgrade of many clusters, you should find that clusters with identical database schemas require the same post-upgrade steps for all cluster upgrades; this is because the post-upgrade steps are based on the database schemas, and not user data.

For deployment testing, create a schema-only copy of the old cluster, insert dummy data, and upgrade that.

pg_upgrade does not support upgrading of databases containing table columns using these reg* OID-referencing system data types:

If you want to use link mode and you do not want your old cluster to be modified when the new cluster is started, consider using the clone mode. If that is not available, make a copy of the old cluster and upgrade that in link mode. To make a valid copy of the old cluster, use rsync to create a dirty copy of the old cluster while the server is running, then shut down the old server and run rsync --checksum again to update the copy with any changes to make it consistent. (--checksum is necessary because rsync only has file modification-time granularity of one second.) You might want to exclude some files, e.g., postmaster.pid, as documented in Section 26.3.3. If your file system supports file system snapshots or copy-on-write file copies, you can use that to make a backup of the old cluster and tablespaces, though the snapshot and copies must be created simultaneously or while the database server is down.

SEE ALSO¶

initdb(1), pg_ctl(1), pg_dump(1), postgres(1)