Upgrading to EDB Postgres Advanced Server v17

You can use pg_upgrade to upgrade from an existing installation of EDB Postgres Advanced Server into the cluster built by the EDB Postgres Advanced Server installer or into an alternative cluster created using the initdb command.

The basic steps to perform an upgrade into an empty cluster created with the initdb command are the same as the steps to upgrade into the cluster created by the EDB Postgres Advanced Server installer. However, you can omit Step 2 - Empty the edb database and substitute the location of the alternative cluster when specifying a target cluster for the upgrade.

If a problem occurs during the upgrade process, you can revert to the previous version. See Reverting to the old cluster for detailed information about this process.

You must be an operating system superuser or Windows Administrator to perform an EDB Postgres Advanced Server upgrade.

Step 1 - Install the new server

Install the new version of EDB Postgres Advanced Server, specifying the same non-server components that were installed during the previous EDB Postgres Advanced Server installation. The new cluster and the old cluster must reside in different directories.

Step 2 - Empty the target database

The target cluster must not contain any data. You can create an empty cluster using the initdb command, or you can empty a database that was created during the installation of EDB Postgres Advanced Server. If you installed EDB Postgres Advanced Server in PostgreSQL mode, the installer creates a single database named postgres. Installing EDB Postgres Advanced Server in Oracle mode creates a database named postgres and a database named edb.

The easiest way to empty the target database is to drop the database and then create a new database. Before invoking the DROP DATABASE command, you must disconnect any users and halt any services that are currently using the database.

On Windows, from the Control Panel, go to the Services manager. Select each service in the Services list, and select Stop.

After stopping any services that are currently connected to EDB Postgres Advanced Server, you can use the EDB-PSQL command line client to drop and create a database. When the client opens, connect to the template1 database as the database superuser. If prompted, provide authentication information. Then, use the following command to drop your database:

DROP DATABASE <database_name>;

Where database_name is the name of the database.

Then, create an empty database based on the contents of the template1 database.

CREATE DATABASE <database_name>;

Step 3 - Set both servers in trust mode

During the upgrade process, pg_upgrade connects to the old and new servers several times. To make the connection process easier, you can edit the pg_hba.conf file, setting the authentication mode to trust. To modify the pg_hba.conf file, from the Start menu, select EDB Postgres > EDB Postgres Advanced Server > Expert Configuration. Select Edit pg_hba.conf to open the pg_hba.conf file.

You must allow trust authentication for the previous EDB Postgres Advanced Server installation and EDB Postgres Advanced Server servers. Edit the pg_hba.conf file for both installations of EDB Postgres Advanced Server as shown in the figure.

After editing each file, save the file and exit the editor.

If the system is required to maintain md5 authentication mode during the upgrade process, you can specify user passwords for the database superuser in a password file (pgpass.conf on Windows, .pgpass on Linux). For more information about configuring a password file, see the PostgreSQL core documentation.

Step 4 - Stop all component services and servers

Before you invoke pg_upgrade, you must stop any services that belong to the original EDB Postgres Advanced Server installation, EDB Postgres Advanced Server, or the supporting components. Stopping these services ensures that a service doesn't attempt to access either cluster during the upgrade process.

The services in the table are most likely to be running in your installation.

To stop a service on Windows

Open the Services applet. Select each EDB Postgres Advanced Server or supporting component service displayed in the list, and select Stop.

To stop a service on Linux

Open a terminal window and manually stop each service at the command line.

Step 5 for Linux only - Assume the identity of the cluster owner

If you're using Linux, assume the identity of the EDB Postgres Advanced Server cluster owner. This example assumes EDB Postgres Advanced Server was installed in the default, compatibility-with-Oracle database mode, assigning enterprisedb as the cluster owner. (If installed in compatibility-with-PostgreSQL database mode, postgres is the cluster owner.)

su - enterprisedb

If prompted, enter the EDB Postgres Advanced Server cluster owner password. Then, set the path to include the location of the pg_upgrade executable:

export PATH=$PATH:/usr/edb/as17/bin

During the upgrade process, pg_upgrade writes a file to the current working directory of the enterprisedb user. You must invoke pg_upgrade from a directory where the enterprisedb user has write privileges. After performing the previous commands, navigate to a directory in which the enterprisedb user has sufficient privileges to write a file.

cd /tmp

Step 5 for Windows only - Assume the identity of the cluster owner

If you're using Windows, open a terminal window, assume the identity of the EDB Postgres Advanced Server cluster owner, and set the path to the pg_upgrade executable.

If the --serviceaccount service_account_user parameter was specified during the initial installation of EDB Postgres Advanced Server, then service_account_user is the EDB Postgres Advanced Server cluster owner. In that case, give this user with the RUNAS command:

RUNAS /USER:service_account_user "CMD.EXE"
SET PATH=%PATH%;C:\Program Files\edb\as17\bin

During the upgrade process, pg_upgrade writes a file to the current working directory of the service account user. You must invoke pg_upgrade from a directory where the service account user has write privileges. After performing the previous commands, navigate to a directory in which the service account user has privileges to write a file:

cd %TEMP%

If you omitted the --serviceaccount parameter during the initial installation of EDB Postgres Advanced Server, then the default owner of the EDB Postgres Advanced Server service and the database cluster is NT AUTHORITY\NetworkService.

When NT AUTHORITY\NetworkService is the service account user, the RUNAS command might not be usable. It prompts for a password, and the NT AUTHORITY\NetworkService account isn't assigned a password. Thus, there's typically a failure with an error message such as “Unable to acquire user password.”

Under this circumstance, you must use the Windows utility program PsExec to run CMD.EXE as the service account NT AUTHORITY\NetworkService.

Obtain the PsExec program by downloading PsTools, which is available at the Microsoft site.

You can then use the following command to run CMD.EXE as NT AUTHORITY\NetworkService. Then set the path to the pg_upgrade executable:

psexec.exe -u "NT AUTHORITY\NetworkService" CMD.EXE
SET PATH=%PATH%;C:\Program Files\edb\as17\bin

During the upgrade process, pg_upgrade writes a file to the current working directory of the service account user. You must invoke pg_upgrade from a directory where the service account user has write privileges. After performing the previous commands, navigate to a directory in which the service account user has privileges to write a file:

cd %TEMP%

Step 6 - Perform a consistency check

Before attempting an upgrade, perform a consistency check to ensure that the old and new clusters are compatible and properly configured. Include the --check option to instruct pg_upgrade to perform the consistency check.

This example shows invoking pg_upgrade to perform a consistency check on Linux:

pg_upgrade -d /var/lib/edb/as15/data
-D /var/lib/edb/as17/data -U enterprisedb
-b /usr/edb/as15/bin -B /usr/edb/as17/bin -p 5444 -P 5445 --check

If the command is successful, it returns *Clusters are compatible*.

If you're using Windows, quote any directory names that contain a space:

pg_upgrade.exe
-d "C:\Program Files\ PostgresPlus\15AS\data"
-D "C:\Program Files\edb\as17\data" -U enterprisedb
-b "C:\Program Files\PostgresPlus\15AS\bin"
-B "C:\Program Files\edb\as17\bin" -p 5444 -P 5445 --check

During the consistency checking process, pg_upgrade logs any discrepancies that it finds to a file located in the directory from which you invoked pg_upgrade. When the consistency check completes, review the file to identify any missing components or upgrade conflicts. Resolve any conflicts before invoking pg_upgrade to perform a version upgrade.

If pg_upgrade alerts you to a missing component, you can use StackBuilder Plus to add the component that contains the component. Before using StackBuilder Plus, restart the EDB Postgres Advanced Server service. Then, open StackBuilder Plus by selecting from the Start menu EDB Postgres Advanced Server version > StackBuilder Plus. Follow the onscreen advice of the StackBuilder Plus wizard to download and install the missing components.

After pg_upgrade confirms that the clusters are compatible, you can perform a version upgrade.

Step 7 - Run pg_upgrade

After confirming that the clusters are compatible, you can invoke pg_upgrade to upgrade the old cluster to the new version of EDB Postgres Advanced Server.

On Linux:

pg_upgrade -d /var/lib/edb/as15/data
-D /var/lib/edb/as17/data -U enterprisedb
-b /usr/edb/as15/bin -B /usr/edb/as17/bin -p 5444 -P 5445

On Windows:

pg_upgrade.exe -d "C:\Program Files\PostgresPlus\15AS\data"
-D "C:\Program Files\edb\as17\data" -U enterprisedb
-b "C:\Program Files\PostgresPlus\15AS\bin"
-B "C:\Program Files\edb\as17\bin" -p 5444 -P 5445

pg_upgrade displays the progress of the upgrade onscreen:

$ pg_upgrade -d /var/lib/edb/as15/data -D /var/lib/edb/as17/data -U
enterprisedb -b /usr/edb/as15/bin -B /usr/edb/as17/bin -p 5444 -P 5445
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories            ok
Checking cluster versions                              ok
Checking database user is a superuser                  ok
Checking for prepared transactions                     ok
Checking for reg* system OID user data types           ok
Checking for contrib/isn with bigint-passing mismatch  ok
Creating catalog dump                                  ok
Checking for presence of required libraries            ok
Checking database user is a superuser                  ok
Checking for prepared transactions                     ok

If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing. Otherwise, it continues as follows:

Performing Upgrade
------------------
Analyzing all rows in the new cluster                  ok
Freezing all rows on the new cluster                   ok
Deleting files from new pg_clog                        ok
Copying old pg_clog to new server                      ok
Setting next transaction ID for new cluster            ok
Resetting WAL archives                                 ok
Setting frozenxid counters in new cluster              ok
Creating databases in the new cluster                  ok
Adding support functions to new cluster                ok
Restoring database schema to new cluster               ok
Removing support functions from new cluster            ok
Copying user relation files                            ok

Setting next OID for new cluster                       ok
Creating script to analyze new cluster                 ok
Creating script to delete old cluster                  ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    analyze_new_cluster.sh

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

While pg_upgrade runs, it might generate SQL scripts that handle special circumstances that it encountered during your upgrade. For example, if the old cluster contains large objects, you might need to invoke a script that defines the default permissions for the objects in the new cluster. When performing the pre-upgrade consistency check, pg_upgrade alerts you to any script that you might need to run manually.

You must invoke the scripts after pg_upgrade completes. To invoke the scripts, connect to the new cluster as a database superuser with the EDB-PSQL command-line client, and invoke each script using the \i option:

\i complete_path_to_script/script.sql

It's generally unsafe to access tables referenced in rebuild scripts until the rebuild scripts finish. Accessing the tables might yield incorrect results or poor performance. You can access tables not referenced in rebuild scripts immediately.

If pg_upgrade fails to complete the upgrade process, the old cluster is unchanged except that $PGDATA/global/pg_control is renamed to pg_control.old and each tablespace is renamed to tablespace.old. To revert to the pre-invocation state:

1. Delete any tablespace directories created by the new cluster.
2. Rename $PGDATA/global/pg_control, removing the .old suffix.
3. Rename the old cluster tablespace directory names, removing the .old suffix.
4. Remove any database objects from the new cluster that were moved before the upgrade failed.

Then, resolve any upgrade conflicts encountered and try the upgrade again.

When the upgrade is complete, pg_upgrade might also recommend vacuuming the new cluster. It provides a script that allows you to delete the old cluster.

Step 8 - Restore the authentication settings in the pg_hba.conf file

If you modified the pg_hba.conf file to permit trust authentication, update the contents of the pg_hba.conf file to reflect your preferred authentication settings.

Step 9 - Move and identify user-defined tablespaces (optional)

If you have data stored in a user-defined tablespace, you must manually relocate tablespace files after upgrading. Move the files to the new location, and update the symbolic links to point to the files. The symbolic links are located in the pg_tblspc directory under your cluster's data directory.