You can use pg_upgrade to upgrade from an existing installation of Advanced Server into the cluster built by the Advanced Server 9.4 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 9.4 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 9.4, 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 9.4. 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 or services that are currently using the database.
• pgbouncer - The PgBouncer service
• ppas-agent-9.4 - The PgAgent serviceOn 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 navigate to the /etc/init.d directory. Manually stop each service; for example, 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 re-create the database.To open the EDB-PSQL command line, navigate through the Start menu and select Run SQL Command Line; select EDB-PSQL to open the command line client. 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 the 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;During the upgrade process, pg_upgrade will connect to the old and new servers several times; to make the connection process easier, you should 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 each Postgres Plus 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 9.4 servers. Edit the pg_hba.conf file for both installations of Advanced Server as shown in Figure 8.1.Figure 8.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 invoking pg_upgrade, you must stop any services that belong to the original Advanced Server installation, Advanced Server 9.4 or the supporting components. This ensures that a service will not attempt to access either cluster during the upgrade process.
Navigate through the Control Panel to the Services manager; highlight each running Advanced Server service displayed in the list, and select Stop.Open a terminal window, assume superuser privileges, and navigate to the /etc/init.d directory. Manually stop each Advanced Server service at the command line; for example, to stop Advanced Server 9.4 invoke the command:Assume the identity of the Advanced Server cluster owner, and add the directory in which the pg_upgrade executable resides to the search path.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: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 super 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.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.3AS/data -D /opt/PostgresPlus/9.4AS/data -U enterprisedb -b /opt/PostgresPlus/9.3AS/bin -B /opt/PostgresPlus/9.4AS/bin -p 5444 -P 5445 --checkpg_upgrade.exe -d "C:\Program Files\PostgresPlus\9.3AS\data"
-D "C:\Program Files\PostgresPlus\9.4AS\data" -U enterprisedb
-b "C:\Program Files\PostgresPlus\9.3AS\bin" -B "C:\Program Files\PostgresPlus\9.4AS\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 9.4 service. After restarting the service, open StackBuilder Plus by navigating through the Start menu to the Postgres Plus Advanced Server 9.4 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 , 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.3AS/data -D /opt/PostgresPlus/9.4AS/data -U enterprisedb -b /opt/PostgresPlus/9.3AS/bin -B /opt/PostgresPlus/9.4AS/bin -p 5444 -P 5445pg_upgrade.exe -d "C:\Program Files\PostgresPlus\9.3AS\data"
-D "C:\Program Files\PostgresPlus\9.4AS\data" -U enterprisedb
-b "C:\Program Files\PostgresPlus\9.3AS\bin" -B "C:\Program Files\PostgresPlus\9.4AS\bin" -p 5444 -P 5445$ pg_upgrade -d /opt/PostgresPlus/9.3AS/data -D /opt/PostgresPlus/9.4AS/data -U enterprisedb -b /opt/PostgresPlus/9.3AS/bin -B /opt/PostgresPlus/9.4AS/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:Rename $PGDATA/global/pg_control, removing the .old suffix.Remove any database objects (from the new cluster) that may have been moved before the upgrade failed.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.