Postgres pg_upgrade utility

pg_upgrade utility in PostgreSQL

pg_upgrade (formerly pg_migrator)

pg_upgrade upgrades a PostgreSQL cluster to a different major version.

pg_upgrade --help

Usage:

  pg_upgrade [OPTION]...

Options:

  -b, --old-bindir=BINDIR        old cluster executable directory

  -B, --new-bindir=BINDIR        new cluster executable directory (default same directory as pg_upgrade)

  -c, --check                    check clusters only, don't change any data

  -d, --old-datadir=DATADIR     old cluster data directory

  -D, --new-datadir=DATADIR   new cluster data directory

  -j, --jobs=NUM                number of simultaneous processes or threads to use

  -k, --link                    link instead of copying files to new cluster

  -N, --no-sync                  do not wait for changes to be written safely to disk

  -o, --old-options=OPTIONS    old cluster options to pass to the server

  -O, --new-options=OPTIONS  new cluster options to pass to the server

  -p, --old-port=PORT            old cluster port number (default 50432)

  -P, --new-port=PORT            new cluster port number (default 50432)

  -r, --retain                  retain SQL and log files after success

  -s, --socketdir=DIR          socket directory to use (default current dir.)

  -U, --username=NAME           cluster superuser (default "thirumani")

  -v, --verbose                  enable verbose internal logging

  -V, --version                  display version information, then exit

  --clone                        clone instead of copying files to new cluster

  -?, --help                    show this help, then exit

Before running pg_upgrade you must:

  create a new database cluster (using the new version of initdb)

  shutdown the postmaster servicing the old cluster

  shutdown the postmaster servicing the new cluster

When you run pg_upgrade, you must provide the following information:

  the data directory for the old cluster  (-d DATADIR)

  the data directory for the new cluster  (-D DATADIR)

  the "bin" directory for the old version (-b BINDIR)

  the "bin" directory for the new version (-B BINDIR)

For example:

  pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin -B newCluster/bin

or

  $ export PGDATAOLD=oldCluster/data

  $ export PGDATANEW=newCluster/data

  $ export PGBINOLD=oldCluster/bin

  $ export PGBINNEW=newCluster/bin

  $ pg_upgrade

pg_upgrade --version

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

/usr/pgsql/12/bin/pg_upgrade --old-bindir=/usr/pgsql/10/bin --new-bindir=/usr/pgsql/12/bin --old-datadir=/var/lib/pgsql/10/data --new-datadir=/var/lib/pgsql/12/data --check

pg_upgrade --old-bindir=/usr/pgsql/10/bin --new-bindir=/usr/pgsql/12/bin --old-datadir=/var/lib/pgsql/10/data --new-datadir=/var/lib/pgsql/12/data

pg_upgrade -b=/usr/pgsql/11/bin -B=/usr/pgsql/13/bin -d=/var/lib/pgsql/11/data -D=/var/lib/pgsql/13/data --check

pg_upgrade -b=/usr/pgsql/11/bin -B=/usr/pgsql/13/bin -d=/var/lib/pgsql/11/data -D=/var/lib/pgsql/13/data --link

Related Postgres Articles:  Comparison of PostgreSQL Oracle features