Contents

pg_upgrade

A server utility for performing a binary upgrade of a PostgreSQL database

pg_upgrade is a server utility for performing a binary upgrade of a PostgreSQL database cluster to a new major version.

pg_upgrade was added in PostgreSQL 9.0, initially as a contrib module based on pg_migrator.

Source code

pg_upgrade source code is located at src/bin/pg_upgrade/.

See src/bin/pg_upgrade/IMPLEMENTATION for a technical description of the implementation.

Change history

  • PostgreSQL 15
    • option -N/--no-sync added (commit 3d5ffccb)
    • filenode and tablespace OIDs preserved (commit 9a974cbc)
    • postgres and template0 database OIDs preserved (commit aa010514)
    • files generated by pg_upgrade will now be stored in the upgraded cluster's data directory under pg_upgrade_output.d (commits 38bfae36 and a0084963)
    • all databases which cannot be connected to will now be listed (commit 26ebb0e2)
    • progress status will not be emitted if output is not a tty (commit 27b02e07)
    • support for upgrading from versions older than PostgreSQL 9.2 removed (commits 38bfae36 and a0084963)
  • PostgreSQL 13
    • default new bindir is now the same location as the pg_upgrade binary being executed, removing the need to explicitly specify -B newbindir in most cases (commit 959f6d6a)
  • PostgreSQL 12
    • --clone option added (commit 3a769d82)
    • --socketdir option added (commit 2d34ad84)
  • PostgreSQL 10
    • hash indexes from older PostgreSQL versions will be invalidated and a script generated for recreating them (commit a95410e2)
  • PostgreSQL 9.5
  • PostgreSQL 9.4
    • -u/--user options renamed to -U/--username for consistency with other utilties (commit 457d6cf0)
    • memory consumption significantly reduced (commit 2a2e40aa)
  • PostgreSQL 9.3
    • --jobs option added to enable parallel schema dump/restore of databases, as well as parallel copy/link of data files (commit 6f1b9e4e and commit a89c46f9)
    • improvements to status display (commit 6dd95845)
    • performance improvement for databases with large numbers of tables (commit 29add0de)
    • properly detect the location of non-default socket directories (commit f763b771)
    • show executed commands in upgrade logs (commit 4741e9af)
    • improve status display during copy/link operation (commit 6dd95845)
  • PostgreSQL 9.2
    • environment variables specific to pg_upgrade renamed to start with PG; PGPORT now ignored and replaced by PGPORTOLD/PGPORTNEW (commit a88f4496)
    • uses port 50432 by default (commit a88f4496)
    • general logging improvements (commit 717f6d60)
    • old cluster will only be locked if link mode used (commit 2127aac6)
    • script to incrementally generate more accurate statistics added (commit faec2815)
    • -g/-G/-l logging options removed (commit 717f6d60)
    • -o/-O options added (commit 0dc3f57b)
    • -r/--retain option added (commit 717f6d60)
    • -v/--verbose behaviour improved (commit 717f6d60)
    • support upgrading clusters without a postgres database (commit a50d860a)
  • PostgreSQL 9.0

Note that an earlier version of pg_upgrade was originally added as a contrib module in PostgreSQL 7.2 (commit 92a8bc96) and removed in PostgreSQL 8.1 (commit d20763db)

 

Examples

Sample output of a pg_upgrade operation:

$ pg_upgrade \
  --old-bindir=/usr/local/pg12/bin \
  --old-datadir=/var/lib/pg12/data \
  --new-datadir=/var/lib/pg13/data \
  --old-port=5432 \
  --new-port=5433 \
  --jobs 4

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined postfix operators                 ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/local/pg13/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

Categories

Server utility, Upgrading