Upgrading the PEM backend Postgres database#
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 on high-availability mode using HTTPD .
Note
If your backend database server is earlier than version 13, first upgrade your backend database server and then upgrade the PEM components.
- After upgrading the backend database server, if you encounter this error while creating the server in the PEM web interface:
Error - User does not have enough permission to add new server.
Please contact the administrator to grant pem_database_server_registration role
to the enterprisedb user.
Resolve the error by updating the roles and granting appropriate permissions:
UPDATE pem.roles SET rolid = pr.oid FROM pg_roles pr WHERE pr.rolname = pem_ || component;
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 PEM server .
You can find more information about using pg_upgrade at pg_upgrade .
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.
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.
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:
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: 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.
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
.pgpassfile that specifies the connection properties (and password) for the database superuser. Or you might need to modify thepg_hba.conffile to allow trust connections before invokingpg_upgrade. For more information about creating an entry in the.pgpassfile, 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:
<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):
C:\>"C:\Program Files\PostgreSQL1\bin\pg_upgrade.exe"
-d "C:\Program Files\PostgreSQL0\data"
-D "C:\Program Files\PostgreSQL1\data"
-b "C:\Program Files\PostgreSQL0\bin"
-B "C:\Program Files\PostgreSQL1\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 .
Copy the following certificate files from the
datadirectory of the old backend database to thedatadirectory 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:
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:
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:
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.
The
postgresql.conffile contains parameter settings that specify server behavior. Modify thepostgresql.conffile on the new server to match the configuration specified in thepostgresql.conffile of the old server.
Use your choice of editor to update the postgresql.conf file of the
new server. Modify the following parameters:
The
portparameter to listen on the port monitored by your original backend database (typically set to5432).The
sslparameter to be set toon
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.
The
pg_hba.conffile contains parameter settings that specify how the server enforces host-based authentication. When you install the PEM server, the installer modifies thepg_hba.conffile, 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.
Restart the service of the new backend database.
On RHEL 8.x, at the command line as superuser enter:
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. 1. Double-click the Services icon. 1. In the Services dialog box, select the service name and start the service.