Upgrading the PEM backend Postgres database v10.2

If you're updating PEM components and the PEM backend database, perform PEM component updates on the server and agent before updating the backend database. For more information about updating PEM component software, see Upgrading a PEM installation.

The update process uses the pg_upgrade utility to migrate from one version of the backend server to a more recent version. pg_upgrade enables migration between any supported version of Postgres and any subsequent release of Postgres that's supported on the same platform.

pg_upgrade supports a transfer of data between servers of the same type. For example, you can use pg_upgrade to move data from a PostgreSQL 13 backend database to a PostgreSQL 14 backend database but not to an EDB Postgres Advanced Server 14 backend database. If you want to migrate to a different type of backend database (such as from a PostgreSQL server to EDB Postgres Advanced Server), see Moving the Postgres Enterprise Manager server.

You can find more information about using pg_upgrade at pg_upgrade.

1. Download and invoke the updated installer. Installers for PostgreSQL and EDB Postgres Advanced Server are available through the EDB website.

   After downloading the installer for the server version you are upgrading to, invoke the installer on the host of the PEM server. Follow the onscreen instructions of the installation wizard to configure and install the Postgres server.

   You can optionally use a custom-built PostgreSQL server as a host of the PEM backend database. If you're upgrading from a PostgreSQL backend database listening on port 5432, the new server must be configured to listen on a different port.

2. Configure SSL utilities on the new server. The new backend database must be running the same version of sslutils that the current backend database is running.

   The process of configuring sslutils is platform specific. Please refer to the installation documentation for your platform for details of how to install sslutils.

3. Stop the services of both the old backend database and the new backend database.

   On RHEL 8.x, open a command line and assume the identity of a superuser. Enter the command:

   → WrapCopy

   systemctl <service_name> stop

   Where <service_name> specifies the name of the Postgres service.

   On Windows, you can use the Services dialog box to control the service. To stop the service:

   → WrapCopy

   1. On the Control Panel select **System and Security > Administrative Tools**.
   1. Double-click **Services**. 
   1. In the Services dialog box, select the service name and selet **Stop**.

4. Use the pg_upgrade utility to perform an in-place transfer of existing data between the old backend database and the new backend database. If your server is configured to enforce md5 authentication, you might need to add an entry to the .pgpass file that specifies the connection properties (and password) for the database superuser. Or you might need to modify the pg_hba.conf file to allow trust connections before invoking pg_upgrade. For more information about creating an entry in the .pgpass file, see the PostgreSQL core documentation.

   During the upgrade process, pg_upgrade writes a series of log files. The cluster owner must invoke pg_upgrade from a directory in which they have write privileges. If the upgrade completes successfully, pg_upgrade removes the log files when the upgrade completes. If you don't want pg_upgrade to delete the upgrade log files, include the --retain keyword when invoking pg_upgrade.

   To invoke pg_upgrade, assume the identity of the cluster owner, navigate to a directory in which the cluster owner has write privileges, and execute the command:

   → WrapCopy

   <path_to_pg_upgrade> pg_upgrade
   
   -d <old_data_dir_path>
   
   -D <new_data_dir_path>
   
   -b <old_bin_dir_path> -B <new_bin_dir_path>
   
   -p <old_port> -P <new_port>
   
   -u <user_name>

   Where:

   • path_to_pg_upgrade specifies the location of the pg_upgrade utility. By default, pg_upgrade is installed in the bin directory under your Postgres directory.
   • old_data_dir_path specifies the complete path to the data directory of the old backend database.
   • new_data_dir_path specifies the complete path to the data directory of the new backend database.
   • old_bin_dir_path specifies the complete path to the bin directory of the old backend database.
   • new_bin_dir_path specifies the complete path to the bin directory of the old backend database.
   • old_port specifies the port on which the old server is listening.
   • new_port specifies the port on which the new server is listening.
   • user_name specifies the name of the cluster owner.

   For example, the following command instructs pg_upgrade to migrate the PEM database from PostgreSQL 9.6 to PostgreSQL 11 on a Windows system (if the backend databases are installed in their default locations):

   → WrapCopy

   C:\>"C:\Program Files\PostgreSQL\11\bin\pg_upgrade.exe"
   
   -d "C:\Program Files\PostgreSQL\10\data"
   
   -D "C:\Program Files\PostgreSQL\11\data"
   
   -b "C:\Program Files\PostgreSQL\10\bin"
   
   -B "C:\Program Files\PostgreSQL\11\bin"
   
   -p 5432 -P 5433
   
   -U postgres

   Once invoked, pg_upgrade performs consistency checks before moving the data to the new backend database. When the upgrade is finished, pg_upgrade notifies you that the upgrade is complete.

   For detailed information about using pg_upgrade options or troubleshooting the upgrade process, see pg_upgrade.

5. Copy the following certificate files from the data directory of the old backend database to the data directory of the new backend database:

   ca_certificate.crt

   ca_key.key

   root.crt

   root.crl

   server.key

   server.crt

   Once in place on the target server, make sure the files have these platform-specific permissions:

   Permissions and ownership on Linux

   File name	Owner	Permissions
   ca_certificate.crt	postgres	-rw-------
   ca_key.key	postgres	-rw-------
   root.crt	postgres	-rw-------
   root.crl	postgres	-rw-------
   server.key	postgres	-rw-------
   server.crt	postgres	-rw-r--r--

   On Linux, the certificate files must be owned by postgres. You can use the following command at the command line to modify the ownership of the files:

   → WrapCopy

   chown postgres <file_name>

   Where file_name specifies the name of the certificate file.

   Only the owner of the server.crt file can modify it, but any user can read it. You can use the following command to set the file permissions for the server.crt file:

   → WrapCopy

   chmod 644 server.crt

   Only the owner of the other certificate files can modify or read the file. You can use the following command to set the file permissions:

   → WrapCopy

   chmod 600 <file_name>

   Where file_name specifies the name of the file.

   Permissions and ownership on Windows

   On Windows, the service account that performed the PEM server and backend database installation on the target host must own the certificate files moved from the source host. If you invoked the PEM server and Postgres installer using Run as Administrator from the context menu of the installer, the owner of the certificate files is Administrators.

   To review and modify file permissions on Windows, right-click the file name and select Properties.

   On the Security tab select a group or user name to view the assigned permissions. Select Edit or Advanced to open dialog boxes that allow you to modify the permissions associated with the selected user.

6. The postgresql.conf file contains parameter settings that specify server behavior. Modify the postgresql.conf file on the new server to match the configuration specified in the postgresql.conf file of the old server.

   Use your choice of editor to update the postgresql.conf file of the new server. Modify the following parameters:

   • The port parameter to listen on the port monitored by your original backend database (typically set to 5432).
   • The ssl parameter to be set to on

   You must also ensure that the following parameters are enabled. If the parameters are commented out, remove the pound sign from in front of each postgresql.conf file entry:

   • ssl_cert_file = 'server.crt' # (change requires restart)
   • ssl_key_file = 'server.key' # (change requires restart)
   • ssl_ca_file = 'root.crt' # (change requires restart)
   • ssl_crl_file = 'root.crl'

   Your installation might have other parameter settings that require modification to ensure that the new backend database behaves like the old backend database. Review the postgresql.conf files carefully to ensure that the configuration of the new server matches the configuration of the old server.

7. The pg_hba.conf file contains parameter settings that specify how the server enforces host-based authentication. When you install the PEM server, the installer modifies the pg_hba.conf file, adding entries to the top of the file:

   # Adding entries for PEM Agens and admins to connect to PEM server

   hostssl pem +pem_user 192.168.2.0/24 md5

   hostssl pem +pem_agent 192.168.2.0/24 cert

   # Adding entries (localhost) for PEM Agens and admins to connect to PEM server

   hostssl pem +pem_user 127.0.0.1/32 md5

   hostssl postgres +pem_user 127.0.0.1/32 md5

   hostssl pem +pem_user 127.0.0.1/32 md5

   hostssl pem +pem_agent 127.0.0.1/32 cert

   Using your editor of choice, copy the entries from the pg_hba.conf file of the old server to the pg_hba.conf file for the new server.

8. Restart the service of the new backend database.

   On RHEL 8.x, at the command line as superuser enter:

   → WrapCopy

   systemctl restart <service_name>

   Where service_name is the name of the backend database server.

   If you're using Windows, you can use the Services dialog box to control the service:

   1. In the Control Panel, select System and Security > Administrative Tools.
   2. Double-click the Services icon.
   3. In the Services dialog box, select the service name and start the service.