Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
PG_UPGRADE(1)		PostgreSQL 9.6.19 Documentation		 PG_UPGRADE(1)

       pg_upgrade - upgrade a PostgreSQL server	instance

       pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir

       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/reload typically required for major
       version upgrades, e.g. from 8.4.7 to the	current	major release of
       PostgreSQL. It is not required for minor	version	upgrades, e.g. from
       9.0.1 to	9.0.4.

       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

       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 8.4.X and later to the	current	major
       release of PostgreSQL, including	snapshot and alpha releases.

       pg_upgrade accepts the following	command-line arguments:

       -b bindir
	   the old PostgreSQL executable directory; environment	variable

       -B bindir
	   the new PostgreSQL executable directory; environment	variable

	   check clusters only,	don't change any data

       -d datadir
	   the old cluster data	directory; environment variable	PGDATAOLD

       -D datadir
	   the new cluster data	directory; environment variable	PGDATANEW

       -j njobs
	   number of simultaneous processes or threads to use

	   use hard links instead of copying files to the new cluster

       -o options
       --old-options options
	   options to be passed	directly to the	old postgres command; multiple
	   option invocations are appended

       -O options
       --new-options options
	   options to be passed	directly to the	new postgres command; multiple
	   option invocations are appended

       -p port
	   the old cluster port	number;	environment variable PGPORTOLD

       -P port
	   the new cluster port	number;	environment variable PGPORTNEW

	   retain SQL and log files even after successful completion

       -U username
	   cluster's install user name;	environment variable PGUSER

	   enable verbose internal logging

	   display version information,	then exit

	   show	help, then exit

       These are the steps to perform an upgrade with pg_upgrade:

	1. Optionally move the old cluster: If you are using a
	   version-specific installation directory, e.g.  /opt/PostgreSQL/9.1,
	   you do not need to move the old cluster. The	graphical installers
	   all use version-specific installation directories.

	   If your installation	directory is not version-specific, e.g.
	   /usr/local/pgsql, it	is necessary to	move the current PostgreSQL
	   install directory so	it does	not interfere with the new PostgreSQL
	   installation. Once the current PostgreSQL server is shut down, it
	   is safe to rename the PostgreSQL installation directory; assuming
	   the old directory is	/usr/local/pgsql, you can do:

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

	   to rename the directory.

	2. For source installs,	build the new version: Build the new
	   PostgreSQL source with configure flags that are compatible with the
	   old cluster.	 pg_upgrade will check pg_controldata to make sure all
	   settings are	compatible before starting the upgrade.

	3. Install the new PostgreSQL binaries:	Install	the new	server's
	   binaries and	support	files.	pg_upgrade is included in a default

	   For source installs,	if you wish to install the new server in a
	   custom location, use	the prefix variable:

	       make prefix=/usr/local/	install

	4. Initialize the new PostgreSQL cluster: Initialize the new cluster
	   using initdb. Again,	use compatible initdb flags that match the old
	   cluster. Many prebuilt installers do	this step automatically. There
	   is no need to start the new cluster.

	5. Install custom shared object	files: Install any custom shared
	   object files	(or DLLs) used by the old cluster into the new
	   cluster, e.g., whether they are	from contrib or	some
	   other source. Do not	install	the schema definitions,	e.g.  CREATE
	   EXTENSION pgcrypto, because these will be upgraded from the old
	   cluster. Also, any custom full text search files (dictionary,
	   synonym, thesaurus, stop words) must	also be	copied to the new

	6. Adjust authentication: pg_upgrade will connect to the old and new
	   servers several times, so you might want to set authentication to
	   peer	in pg_hba.conf or use a	~/.pgpass file (see Section 32.15,
	   "The	Password File",	in the documentation).

	7. Stop	both servers: Make sure	both database servers are stopped
	   using, on Unix, e.g.:

	       pg_ctl -D /opt/PostgreSQL/8.4 stop
	       pg_ctl -D /opt/PostgreSQL/9.0 stop

	   or on Windows, using	the proper service names:

	       NET STOP	postgresql-8.4
	       NET STOP	postgresql-9.0

	   Streaming replication and log-shipping standby servers can remain
	   running until a later step.

	8. Prepare for standby server upgrades:	If you are upgrading standby
	   servers using methods outlined in section Step 10, verify that the
	   old standby servers are caught up by	running	pg_controldata against
	   the old primary and standby clusters. Verify	that the "Latest
	   checkpoint location"	values match in	all clusters. (There will be a
	   mismatch if old standby servers were	shut down before the old
	   primary or if the old standby servers are still running.) Also,
	   make	sure wal_level is not set to minimal in	the postgresql.conf
	   file	on the new primary cluster.

	9. Run pg_upgrade: Always run the pg_upgrade binary of the new server,
	   not the old one.  pg_upgrade	requires the specification of the old
	   and new cluster's data and executable (bin) directories. You	can
	   also	specify	user and port values, and whether you want the data
	   linked instead of copied (the default).

	   If you use link mode, the upgrade will be much faster (no file
	   copying) and	use less disk space, but you will not be able to
	   access your old cluster once	you start the new cluster after	the
	   upgrade. Link mode also requires that the old and new cluster data
	   directories be in the same file system. (Tablespaces	and pg_xlog
	   can be on different file systems.) See pg_upgrade --help for	a full
	   list	of options.

	   The --jobs option allows multiple CPU cores to be used for
	   copying/linking of files and	to dump	and reload database schemas in
	   parallel; a good place to start is the maximum of the number	of CPU
	   cores and tablespaces. This option can dramatically reduce the time
	   to upgrade a	multi-database server running on a multiprocessor

	   For Windows users, you must be logged into an administrative
	   account, and	then start a shell as the postgres user	and set	the
	   proper path:

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

	   and then run	pg_upgrade with	quoted directories, e.g.:

		       --old-datadir "C:/Program Files/PostgreSQL/8.4/data"
		       --new-datadir "C:/Program Files/PostgreSQL/9.0/data"
		       --old-bindir "C:/Program	Files/PostgreSQL/8.4/bin"
		       --new-bindir "C:/Program	Files/PostgreSQL/9.0/bin"

	   Once	started, pg_upgrade will verify	the two	clusters are
	   compatible and then do the upgrade. You can use pg_upgrade --check
	   to perform only the checks, even if the old server is still
	   running.  pg_upgrade	--check	will also outline any manual
	   adjustments you will	need to	make after the upgrade.	If you are
	   going to be using link mode,	you should use the --link option with
	   --check to enable link-mode-specific	checks.	 pg_upgrade requires
	   write permission in the current directory.

	   Obviously, no one should be accessing the clusters during the
	   upgrade.  pg_upgrade	defaults to running servers on port 50432 to
	   avoid unintended client connections.	You can	use the	same port
	   number for both clusters when doing an upgrade because the old and
	   new clusters	will not be running at the same	time. However, when
	   checking an old running server, the old and new port	numbers	must
	   be different.

	   If an error occurs while restoring the database schema, pg_upgrade
	   will	exit and you will have to revert to the	old cluster as
	   outlined in Step 16 below. To try pg_upgrade	again, you will	need
	   to modify the old cluster so	the pg_upgrade schema restore
	   succeeds. If	the problem is a contrib module, you might need	to
	   uninstall the contrib module	from the old cluster and install it in
	   the new cluster after the upgrade, assuming the module is not being
	   used	to store user data.

	10. Upgrade Streaming Replication and Log-Shipping standby servers: If
	   you used link mode and have Streaming Replication (see Section
	   26.2.5, "Streaming Replication", in the documentation) or
	   Log-Shipping	(see Section 26.2, "Log-Shipping Standby Servers", in
	   the documentation) standby servers, you can follow these steps to
	   quickly upgrade them. You will not be running pg_upgrade on the
	   standby servers, but	rather rsync on	the primary. Do	not start any
	   servers yet.

	   If you did not use link mode, do not	have or	do not want to use
	   rsync, or want an easier solution, skip the instructions in this
	   section and simply recreate the standby servers once	pg_upgrade
	   completes and the new primary is running.  Install the new
	   PostgreSQL binaries on standby servers: Make	sure the new binaries
	   and support files are installed on all standby servers.  Make sure
	   the new standby data	directories do not exist: Make sure the	new
	   standby data	directories do not exist or are	empty. If initdb was
	   run,	delete the standby servers' new	data directories.  Install
	   custom shared object	files: Install the same	custom shared object
	   files on the	new standbys that you installed	in the new primary
	   cluster.  Stop standby servers: If the standby servers are still
	   running, stop them now using	the above instructions.	 Save
	   configuration files:	Save any configuration files from the old
	   standbys' data directories you need to keep,	e.g.  postgresql.conf,
	   recovery.conf, because these	will be	overwritten or removed in the
	   next	step.  Run rsync: When using link mode,	standby	servers	can be
	   quickly upgraded using rsync. To accomplish this, from a directory
	   on the primary server that is above the old and new database
	   cluster directories,	run this on the	primary	for each standby

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

	   where old_cluster and new_cluster are relative to the current
	   directory on	the primary, and remote_dir is above the old and new
	   cluster directories on the standby. The directory structure under
	   the specified directories on	the primary and	standbys must match.
	   Consult the rsync manual page for details on	specifying the remote
	   directory, e.g.

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

	   You can verify what the command will	do using rsync's --dry-run
	   option. While rsync must be run on the primary for at least one
	   standby, it is possible to run rsync	on an upgraded standby to
	   upgrade other standbys, as long as the upgraded standby has not
	   been	started.

	   What	this does is to	record the links created by pg_upgrade's link
	   mode	that connect files in the old and new clusters on the primary
	   server. It then finds matching files	in the standby's old cluster
	   and creates links for them in the standby's new cluster. Files that
	   were	not linked on the primary are copied from the primary to the
	   standby. (They are usually small.) This provides rapid standby
	   upgrades. Unfortunately, rsync needlessly copies files associated
	   with	temporary and unlogged tables because these files don't
	   normally exist on standby servers.

	   If you have tablespaces, you	will need to run a similar rsync
	   command for each tablespace directory, e.g.:

	       rsync --archive --delete	--hard-links --size-only --no-inc-recursive /vol1/pg_tblsp/PG_9.5_201510051 \

	   If you have relocated pg_xlog outside the data directories, rsync
	   must	be run on those	directories too.  Configure streaming
	   replication and log-shipping	standby	servers: Configure the servers
	   for log shipping. (You do not need to run pg_start_backup() and
	   pg_stop_backup() or take a file system backup as the	standbys are
	   still synchronized with the primary.)

	11. Restore pg_hba.conf: If you	modified pg_hba.conf, restore its
	   original settings. It might also be necessary to adjust other
	   configuration files in the new cluster to match the old cluster,
	   e.g.	 postgresql.conf.

	12. Start the new server: The new server can now be safely started,
	   and then any	rsync'ed standby servers.

	13. Post-Upgrade processing: If	any post-upgrade processing is
	   required, pg_upgrade	will issue warnings as it completes. It	will
	   also	generate script	files that must	be run by the administrator.
	   The script files will connect to each database that needs
	   post-upgrade	processing. Each script	should be run using:

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

	   The scripts can be run in any order and can be deleted once they
	   have	been run.

	       In general it is	unsafe to access tables	referenced in rebuild
	       scripts until the rebuild scripts have run to completion; doing
	       so could	yield incorrect	results	or poor	performance. Tables
	       not referenced in rebuild scripts can be	accessed immediately.

	14. Statistics:	Because	optimizer statistics are not transferred by
	   pg_upgrade, you will	be instructed to run a command to regenerate
	   that	information at the end of the upgrade. You might need to set
	   connection parameters to match your new cluster.

	15. Delete old cluster:	Once you are satisfied with the	upgrade, you
	   can delete the old cluster's	data directories by running the	script
	   mentioned when pg_upgrade completes.	(Automatic deletion is not
	   possible if you have	user-defined tablespaces inside	the old	data
	   directory.) You can also delete the old installation	directories
	   (e.g.  bin, share).

	16. Reverting to old cluster: If, after	running	pg_upgrade, you	wish
	   to revert to	the old	cluster, there are several options:

	   o   If the --check option was used, the old cluster was unmodified;
	       it can be restarted.

	   o   If the --link option was	not used, the old cluster was
	       unmodified; it can be restarted.

	   o   If the --link option was	used, the data files might be shared
	       between the old and new cluster:

	       o   If pg_upgrade aborted before	linking	started, the old
		   cluster was unmodified; it can be restarted.

	       o   If you did not start	the new	cluster, the old cluster was
		   unmodified except that, when	linking	started, a .old	suffix
		   was appended	to $PGDATA/global/pg_control. To reuse the old
		   cluster, remove the .old suffix from
		   $PGDATA/global/pg_control; you can then restart the old

	       o   If you did start the	new cluster, it	has written to shared
		   files and it	is unsafe to use the old cluster. The old
		   cluster will	need to	be restored from backup	in this	case.

       pg_upgrade does not support upgrading of	databases containing these
       reg* OID-referencing system data	types: regproc,	regprocedure, regoper,
       regoperator, regconfig, and regdictionary. (regtype can be upgraded.)

       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.

       If you are upgrading a pre-PostgreSQL 9.2 cluster that uses a
       configuration-file-only directory, you must pass	the real data
       directory location to pg_upgrade, and pass the configuration directory
       location	to the server, e.g.  -d	/real-data-directory -o	'-D

       If using	a pre-9.1 old server that is using a non-default Unix-domain
       socket directory	or a default that differs from the default of the new
       cluster,	set PGHOST to point to the old server's	socket location. (This
       is not relevant on Windows.)

       If you want to use link mode and	you do not want	your old cluster to be
       modified	when the new cluster is	started, 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., as documented in Section 25.3.3,
       "Making a Base Backup Using the Low Level API", in the documentation.
       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.

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

PostgreSQL 9.6.19		     2020			 PG_UPGRADE(1)


Want to link to this manual page? Use this URL:

home | help