You can use pg_upgrade to upgrade from an existing installation of Advanced Server into the cluster built by the Advanced Server 10 installer or into an alternate cluster created using the initdb command. In this section, we will provide the details of upgrading into the cluster provided by the installer.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 Advanced Server 10 installer, but you can omit Step 2 (Empty the edb database), and substitute the location of the alternate 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 Advanced Server upgrade.Install Advanced Server 10, specifying the same non-server components that were installed during the previous Advanced Server installation. Please note that the new cluster and the old cluster must reside in different directories.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 Advanced Server 10. If you have installed Advanced Server in PostgreSQL mode, the installer creates a single database named postgres; if you have installed Advanced Server in Oracle mode, it 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, navigate through the Control Panel to the Services manager; highlight each service in the Services list, and select Stop.On Linux, open a terminal window, assume superuser privileges, and manually stop each service; for example, if you are on Linux 6.x, invoke the command:After stopping any services that are currently connected to 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 (see Figure 8.1):CREATE DATABASE database_name;During the upgrade process, pg_upgrade will connect 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, navigate through the Start menu to the EDB Postgres menu; to the Advanced Server menu, and open the Expert Configuration menu; select the Edit pg_hba.conf menu option to open the pg_hba.conf file.You should allow trust authentication for the previous Advanced Server installation, and Advanced Server 10 servers. Edit the pg_hba.conf file for both installations of Advanced Server as shown in Figure 7.1.Figure 7.1 - Configuring Advanced Server to use trust authentication.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, available through:Before you invoke pg_upgrade, you must stop any services that belong to the original Advanced Server installation, Advanced Server 10 or the supporting components. This ensures that a service will not attempt to access either cluster during the upgrade process.
Open the Services applet; highlight each Advanced Server or supporting component service displayed in the list, and select Stop.If you are using Linux, assume the identity of the Advanced Server cluster owner. (The following example assumes Advanced Server was installed in the default, compatibility with Oracle database mode, thus assigning enterprisedb as the cluster owner. If installed in compatibility with PostgreSQL database mode, postgres is the cluster owner.)Enter the Advanced Server cluster owner password if prompted. Then, set the path to include the location of the pg_upgrade executable: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 above commands, navigate to a directory in which the enterprisedb user has sufficient privileges to write a file.If you are using Windows, open a terminal window, assume the identity of the 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 Advanced Server, then service_account_user is the Advanced Server cluster owner and is the user to be given with the RUNAS command.RUNAS /USER:service_account_user "CMD.EXE"
SET PATH=%PATH%;C:\Program Files\edb\as10\binDuring 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 above commands, navigate to a directory in which the service account user has sufficient privileges to write a file.If the --serviceaccount parameter was omitted during the initial installation of Advanced Server, then the default owner of the Advanced Server service and the database cluster is NT AUTHORITY\NetworkService.When NT AUTHORITY\NetworkService is the service account user, the RUNAS command may not be usable as it prompts for a password and the NT AUTHORITY\NetworkService account is not assigned a password. Thus, there is typically a failure with an error message such as, “Unable to acquire user password”.Under this circumstance a Windows utility program named PsExec must be used to run CMD.EXE as the service account NT AUTHORITY\NetworkService.The PsExec program must be obtained by downloading PsTools, which is available at the following site:You can then use the following command to run CMD.EXE as NT AUTHORITY\NetworkService, and then set the path to the pg_upgrade executable.psexec.exe -u "NT AUTHORITY\NetworkService" CMD.EXE
SET PATH=%PATH%;C:\Program Files\edb\as10\binDuring 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 above commands, navigate to a directory in which the service account user has sufficient privileges to write a file.Before attempting an upgrade, perform a consistency check to assure that the old and new clusters are compatible and properly configured. Include the --check option to instruct pg_upgrade to perform the consistency check.pg_upgrade -d /opt/PostgresPlus/9.6AS/data -D /opt/edb/as10/data -U enterprisedb -b /opt/PostgresPlus/9.6AS/bin -B /opt/edb/as10/bin -p 5444 -P 5445 --checkIf the command is successful, it will return *Clusters are compatible*.pg_upgrade.exe -d "C:\Program Files\ PostgresPlus\9.6AS \data" -D "C:\Program Files\edb\as10\data" -U enterprisedb
-b "C:\Program Files\PostgresPlus\9.6AS\bin" -B "C:\Program Files\edb\as10\bin" -p 5444 -P 5445 --checkDuring the consistency checking process, pg_upgrade will log any discrepancies that it finds to a file located in the directory from which pg_upgrade was invoked. When the consistency check completes, review the file to identify any missing components or upgrade conflicts. You must 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, you must restart the Advanced Server 10 service. After restarting the service, open StackBuilder Plus by navigating through the Start menu to the Advanced Server 10 menu, and selecting StackBuilder Plus. Follow the onscreen advice of the StackBuilder Plus wizard to download and install the missing components.For more information about using StackBuilder Plus, please see Section , Using StackBuilder Plus.After confirming that the clusters are compatible, you can invoke pg_upgrade to upgrade the old cluster to the new version of Advanced Server.pg_upgrade -d /opt/PostgresPlus/9.6AS/data -D /opt/edb/as10/data -U enterprisedb -b /opt/PostgresPlus/9.6AS/bin -B /opt/edb/as10/bin -p 5444 -P 5445pg_upgrade.exe -d "C:\Program Files\PostgresPlus\9.6AS\data"
-D "C:\Program Files\edb\as10\data" -U enterprisedb
-b "C:\Program Files\PostgresPlus\9.6AS\bin" -B "C:\Program Files\edb\as10\bin" -p 5444 -P 5445$ pg_upgrade -d /opt/edb/as10/data -D /opt/edb/as10/data -U enterprisedb -b /opt/edb/as10/bin -B /opt/edb/as10/bin -p 5444 -P 5445While pg_upgrade runs, it may generate SQL scripts that handle special circumstances that it has encountered during your upgrade. For example, if the old cluster contains large objects, you may 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 will alert you to any script that you may be required 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.sqlIt is generally unsafe to access tables referenced in rebuild scripts until the rebuild scripts have completed; accessing the tables could yield incorrect results or poor performance. Tables not referenced in rebuild scripts can be accessed immediately.Please Note: If pg_upgrade fails to complete the upgrade process, the old cluster will be 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:
2. Rename $PGDATA/global/pg_control, removing the .old suffix.
4. After performing these steps, resolve any upgrade conflicts encountered before attempting the upgrade again.When the upgrade is complete, pg_upgrade may also recommend vacuuming the new cluster, and will provide a script that allows you to delete the old cluster.Before removing the old cluster, ensure that the cluster has been upgraded as expected, and that you have preserved a backup of the cluster in case you need to revert to a previous version.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.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 (located in the pg_tblspc directory under your cluster's data directory) to point to the files.