3 Upgrading the Backing Postgres Database

Table of Contents Previous Next


3 Upgrading the Backing Postgres Database

The new backing database must be running the same version of sslutils that the current backing database is running; you can download the SSL Utils package that is used in EnterpriseDB installers at:
You are not required to manually add the sslutils extension when using the Advanced Server as the new backing database. The process of configuring sslutils is platform-specific.
When the download completes, extract the sslutils folder, and move it into the Postgres installation directory for the Postgres version to which you are upgrading.
Open a command line, assume superuser privileges, and set the value of the PATH environment variable to allow make to locate the pg_config program:
export PATH=$PATH:/opt/Postgres/x.x/bin/
Postgres specifies either:
PostgreSQL if you are upgrading to a PostgreSQL server.
PostgresPlus if you are upgrading to an Advanced Server server.
x.x specifies the version of Postgres to which you are migrating.
Then, use yum to install sslutil dependencies:
Navigate into the sslutils folder, and build the sslutils package by entering:
sslutils must be compiled on the new backing database with the same compiler that was used to compile sslutils on the original backing database. If you are moving to a Postgres database that was installed using a PostgreSQL one-click installer (from EnterpriseDB) or an Advanced Server installer, use Visual Studio to build sslutils. If you are upgrading to:
After installing OpenSSL, download and unpack the SSL Util utility package available at:
Copy the unpacked sslutils utilities folder to the Postgres installation directory (i.e. C:\Program Files\PostgreSQL\9.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
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.
When the build completes, the sslutils directory will contain the following files:
Copy the compiled sslutils files to the appropriate directory for your installation; for example:
/etc/init.d/service_name stop
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.
You can use the pg_upgrade utility to perform an in-place transfer of existing data between the old backing database and the new backing 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:
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
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 backing database.
new_data_dir_path specifies the complete path to the data directory of the new backing database.
old_bin_dir_path specifies the complete path to the bin directory of the old backing database.
new_bin_dir_path specifies the complete path to the bin directory of the old backing 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.
Copy the following certificate files from the data directory of the old backing database to the data directory of the new backing database:
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:
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 600 file_name
Where file_name specifies the name of the file.
C:\Users\susan\AppData\Local\Temp\vmware-susan\VMwareDnD\c34d18de\server_key.png
Navigate to the Security tab (see Figure 3.3) 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.
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:
On Linux, in /opt/PostgreSQL/9.x/data
On Windows, in C:\Program Files\PostgreSQL\9.x\data
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 backing database (typically, 5432).
The ssl parameter should be set to on.
Your installation may have other parameter settings that require modification to ensure that the new backing database behaves in a manner comparable to the old backing 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.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:
By default, the pg_hba.conf file is located:
On Linux, in /opt/PostgreSQL/9.x/data
On Windows, in C:\Program Files\PostgreSQL\9.x\data
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.
/etc/init.d/service_name start
systemctl/service_name stop
Where service_name is the name of the backing 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.

3 Upgrading the Backing Postgres Database

Table of Contents Previous Next