Upgrading the PEM Backend Postgres Database

If you are updating both PEM components and the PEM backend database, you should perform PEM component updates (the server and Agent) before updating the backend database. For more information about updating PEM component software, see Upgrading a PEM Installation.

Note

From PEM 8.0 onwards, the PostgreSQL or EPAS version 11 or higher are only supported as backend database server. Hence if your backend database server is lower than version 11 then first you need to upgrade your backend database server and then upgrade the PEM components.

The update process described in this section uses the pg_upgrade utility to migrate from one version of the backend server to a more recent version. pg_upgrade facilitates migration between any version of Postgres (version 9.5 or later), and any subsequent release of Postgres that is 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 9.6 backend database to a PostgreSQL 11 backend database, but not to an Advanced Server 11 backend database. If you wish to migrate to a different type of backend database (i.e from a PostgreSQL server to Advanced Server), see Moving the Postgres Enterprise Manager™ Server.

You can find more information about using pg_upgrade at:

http://www.postgresql.org/docs/current/static/pgupgrade.html

1. Download and invoke the updated installer; installers for PostgreSQL and Advanced Server are available through the EDB website:

   https://www.enterprisedb.com/software-downloads-postgres

   After downloading the installer for the server version to which you will be upgrading, 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. Note that if you are 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; you can download the SSL Utils package that is used in EDB installers at:

   https://www.enterprisedb.com/downloads/modified-gpl-source-code

   You are not required to manually add the sslutils extension when using the Advanced Server as the new backend database. The process of configuring sslutils is platform-specific.

On Linux

• On an Advanced Server backend database, the sslutils extension is installed by default.

• If you are using a PostgreSQL as PEM backend database, ensure you have access to the PostgreSQL community repository, and use the command:

  yum install sslutils_<X>
  

Where <X> is the server version.

• If you are using a EDB one-click installer of PostgreSQL as PEM backend database

  yum install gcc openssl-devel
  
  Set the value of PATH so it can locate the pg_config program
  export PATH=$PATH:/opt/postgres_inst_dir/<X>/bin/
  
  Move into the sslutils folder, and enter
  make USE_PGXS=1
  make USE_PGXS=1 install
  
  Use psql to create the sslutils extension
  CREATE EXTENSION sslutils
  

Please note that Debian 10 and Ubuntu 20 has increased the requirements to accept the certificates due to security reason. If a user wants to install the PEM Agent on any of the machines, they must upgrade ssltuils to 1.3 where 4096 bit RSA key and sha256 signature algorithm support has added.If the user does not upgrade sslutils to 1.3, then PEM Agent may fail to connect to the PEM backend database server, and it might log the error ca md too weak.

On Windows

sslutils must be compiled on the new backend database with the same compiler that was used to compile sslutils on the original backend database. If you are moving to a Postgres database that was installed using a PostgreSQL one-click installer (from EDB) or an Advanced Server installer, use Visual Studio to build sslutils. If you are upgrading to PostgreSQL 9.5 or later, use Visual Studio 2010.

For detailed information about building a specific version of Postgres on Windows, please consult the core documentation for that version. Core documentation is available at the PostgreSQL project website at:

http://www.postgresql.org/docs/

or at the EDB website at:

https://www.enterprisedb.com/edb-docs

While specific details of the process will vary by platform and compiler, the basic steps on each platform are the same. The example that follows demonstrates compiling OpenSSL support for PostgreSQL on a 32-bit Windows system.

Before compiling the OpenSSL extension, you must locate and install OpenSSL for your version of Windows. Before invoking the OpenSSL installer you may be required to download and install a pre-requisite redistributable (such as vcredist_x86.exe).

After installing OpenSSL, download and unpack the sslutils utility package available at:

https://www.enterprisedb.com/downloads/modified-gpl-source-code

Copy the unpacked sslutils folder to the Postgres installation directory (i.e. C:\ProgramFiles\PostgreSQL\<x.x>)

Open the Visual Studio command line, and navigate into the sslutils directory. Use the following commands to build sslutils:

SET USE_PGXS=1

SET GETTEXTPATH=\ <path_to_gettext>

SET OPENSSLPATH=\ <path_to_openssl>

SET PGPATH=\ <path_to_pg_installation_dir>

SET ARCH=x86

msbuild sslutils.proj /p:Configuration=Release

Where:

• path_to_gettext specifies the location of the GETTEXT library and header files.

• path_to_openssl specifies the location of the openssl library and header files.

• path_to_pg_installation_dir specifies the location of the Postgres installation.

For example, the following set of commands builds OpenSSL support into the PostgreSQL 11 server:

SET USE_PGXS=1

SET OPENSSLPATH=C:\OpenSSL-Win32

SET GETTEXTPATH="C:\Program Files\PostgreSQL\11"

SET PGPATH="C:\Program Files\PostgreSQL\11"

SET ARCH=x86

msbuild sslutils.proj /p:Configuration=Release

When the build completes, the sslutils directory will contain the following files:

sslutils--1.3.sql

sslutils--unpackaged--1.3.sql

sslutils--pemagent.sql.in

sslutils.dll

Copy the compiled sslutils files to the appropriate directory for your installation; for example:

COPY sslutils*.sql "%PGPATH%\share\extension\"

COPY sslutils.dll "%PGPATH%\lib\"

COPY sslutils.control "%PGPATH%\share\extension\"

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

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

   /etc/init.d/<service_name> stop
   

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

   systemctl <service_name> stop
   

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

   On Windows, you can use the Services dialog to control the service. To open the Services dialog, navigate through the Control Panel to the System and Security menu. Select Administrative Tools, and then double-click the Services icon. When the Services dialog opens, highlight the service name in the list, and use the option provided on the dialog to stop the service.

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 may need to add an entry to the .pgpass file that specifies the connection properties (and password) for the database superuser, or 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, please see the PostgreSQL core documentation, available at:

   http://www.postgresql.org/docs/current/static/libpq-pgpass.html

   During the upgrade process, pg_upgrade will write 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 will remove the log files when the upgrade completes. To instruct pg_upgrade to not 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 into a directory in which the cluster owner has write privileges, and execute the command:

   <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:

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

   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).

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

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

   http://www.postgresql.org/docs/current/static/pgupgrade.html

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, the files should have the (platform-specific) permissions described below:

   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:

   chown postgres <file_name>
   

   Where file_name specifies the name of the certificate file.

   The server.crt file may only be modified by the owner of the file, but may be read by any user. You can use the following command to set the file permissions for the server.crt file:

   chmod 644 server.crt
   

   The other certificate files may only be modified or read by the owner of the file. You can use the following command to set the file permissions:

   chmod 600 <file_name>
   

   Where file_name specifies the name of the file.

   Permissions and Ownership on Windows

   On Windows, the certificate files moved from the source host must be owned by the service account that performed the PEM server and backend database installation on the target host. If you invoked the PEM server and Postgres installer using the Run as Administrator option (selected from the context menu of the installer), the owner of the certificate files will be Administrators.

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

   

   The Security tab

   Navigate to the Security tab and highlight a Group or user name to view the assigned permissions. Select Edit or Advanced to access dialogs that allow you to modify the permissions associated with the selected user.

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

   By default, the postgresql.conf file is located:

   • For Postgres version lower than 10 on Linux, in /opt/PostgreSQL/<X>/data

   • For Postgres version 10 or higher when installed with graphical installers on Linux, in /opt/PostgreSQL/<X>/data

   • For Postgres version 10 or higher when installed with an RPM on Linux, in /usr/pgsql/<X>/data

   • For any Postgres version on Windows, in C:\Program Files\PostgreSQL\<X>\data

   Where, <X> is the version of Postgres on your system.

   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, 5432).

   • The ssl parameter should 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 may have other parameter settings that require modification to ensure that the new backend database behaves in a manner comparable to 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 will enforce 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

   By default, the pg_hba.conf file is located at the following location:

   • For Postgres version lower than 10 on Linux, in /opt/PostgreSQL/<X>/data

   • For Postgres version 10 or higher when installed with graphical installers on Linux, in /Opt/PostgreSQL/<X>/data

   • For Postgres version 10 or higher when installed with RPMs on Linux, in /var/lib/pgsql/<X>/data

   • For Advanced Server version 10 or higher when installed with RPMs on Linux, in /var/lib/edb/as<X>/data

   • For any Postgres version on Windows, in C:\Program Files\PostgreSQL\<X>\data

   Where, <X> is the version of Postgres on your system.

   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 or CentOS 7.x or 8.x, open a command line and assume the identity of a superuser. Enter the command:

   systemctl stop <service_name>
   

   Where service_name is the name of the backend database server.

   If you are using Windows, you can use the Services dialog to control the service. To open the Services dialog, navigate through the Control Panel to the System and Security menu. Select Administrative Tools, and then double-click the Services icon. When the Services dialog opens, highlight the service name in the list, and use the option provided on the dialog to start the service.