Streaming Replication Wizard

The PEM Streaming Replication Wizard walks you through the process of creating or modifying a streaming replication scenario. You can use the wizard to:

  • Install new servers to act as master and standby nodes in a replication scenario.
  • Configure existing servers in the roles of master and standby nodes in a replication scenario.
  • Add new or existing standby servers to an existing replication scenario.

If you are configuring replication using an existing server as the master node or as a standby node within the replication scenario, the servers must have been installed with the graphical installer. The Streaming Replication wizard does not support pre-existing servers installed via RPM packages at this time.

The Streaming Replication wizard is supported by PEM agent version 6.0 (or later). Each node of a replication scenario must have a resident PEM agent; remote monitoring of master or standby nodes is not supported at this time. After installing the PEM agent, you must:

  • on a Linux host, modify the PEM agent configuration file (agent.cfg) located in /opt/PEM/agent/etc/agent.cfg setting the following parameters to true:

    • allow_package_management
    • allow_server_restart
    • allow_streaming_replication
  • on a Windows host, modify the Windows registry (HKEY_LOCAL_MACHINESoftwareWow6432NodeEnterpriseDBPEMagent), setting the following entries to true:

    • AllowPackageManagement
    • AllowServerRestart
    • AllowStreamingReplication

After updating the configuration file or registry, restart the PEM agent service:

  • on a Linux host, open a command line, assume superuser privileges and enter the command /etc/init.d/pemagent restart (on RHEL or CentOS 6.x) or systemctl pemagent restart (on RHEL or CentOS 7.x).
  • on a Windows host, use the Services applet to restart the Postgres Enterprise Manager - pemAgent service.

Then, to open the Streaming Replication wizard, select Streaming Replication… from the Management menu. The Streaming Replication wizard welcomes you:

Streaming replication welcome page

Click Next to continue. The Refresh Packages window offers you the option to refresh the package data that is stored on the PEM server about the currently installed packages.

Streaming replication refresh packages page

The PEM installed_packages probe retrieves information about the currently installed packages that reside on hosts that are monitored by PEM agents. Select Yes to invoke the probe and update the information that is stored on the PEM server. If you have not added servers to the monitored hosts since the last probe execution (by default, the installed_packages probe executes once every 24 hours), select No, and click Next to continue.

Streaming replication master selection page

Fields on the master node selection dialog prompt you to provide information about the master node of the streaming replication scenario:

  • Use the drop-down in the Host field to select the name of the PEM agent that monitors the master node from the list of active agents. To be displayed, the agent must be configured with allow_streaming_replication, allow_package_management, and allow_server_restart enabled (set to true) in the PEM Agent configuration file. Please note that each node of a replication scenario must have a resident agent; remote monitoring of replication nodes is not supported.
  • Use the drop-down in the Database server field to specify the server or server version of the master node. You can select:
    • A previously installed server to act as the master node of the replication scenario. Existing servers include the word (Installed) in their description.
    • The server version of the new master node that PEM will install when configuring the streaming replication scenario. To create a new server, select a server version that does not include the word (Installed) in the description.
  • Use the drop-down in the Replication host address field to select the address of the host on which the master node will reside.
  • Optionally, provide a name for a replication slot in the Replication slot name field; the PEM server will create the replication slot on the master node, and add entries to the recovery.conf files on standby nodes. A replication slot name can contain lower-case letters, numbers, and the underscore character. This option is valid only for database server versions 9.4 and above. For more information, see the PostgreSQL Core documentation.

Click Next to continue.

Streaming replication master authentication page
  • Use the Replication user name field to specify the name of an existing role that is either a database superuser or has REPLICATION privileges, or the name of a role that will be created by PEM for use during replication-related transactions. Please note that PEM will return an error if you specify the name of an existing user with insufficient privileges.
  • Use the Replication password field to specify the password that will be associated with the replication user.
  • Use the Database user name field to specify the name of a database superuser on the master node.
  • Use the Database password field to specify the password associated with the database superuser.

If you using a pre-existing database server as the master node of the replication scenario, the Validate button will be enabled. Click the Validate button to instruct the server to attempt to connect using the credentials provided; if the validation succeeds, you can click Next to continue.

Streaming replication standby selection page

Use the table on the Standby Server Selection window to provide properties of one or more standby nodes. Click the add icon (+) to add an entry to the table:

Streaming replication add standby host page
  • Use the Agent drop-down to select the name of the agent that will monitor a standby node in the replication scenario. Please note that you will not be able to edit the properties of a standby node that is already part of a replication scenario.
  • Use the IP address drop-down to select the IP address of the standby node.
  • Set Hot Standby to Yes if the standby node should be used for read-only queries while acting as a standby node in the replication scenario.
  • Set Synchronous to Yes to enable synchronous replication; streaming replication is asynchronous by default. If a standby node is specified as Synchronous, a transaction will not be committed until it is written to the transaction log of both the master node and standby node. Data loss is less-likely in the event of a server failure of a node of a synchronous replication scenario, but will increase the processing time of each transaction.
  • Use the Priority drop-down to specify the order in which the standby nodes will be listed in the postgresql.conf file of the master node. For example, select 1 to indicate that in the standby should be listed first, 2 to indicate that the node should be listed second, etc.
  • If you are adding the standby to an existing replication scenario, PEM will display the identity of the replication master in the Replication state field, and the name of the application (from the pg_stat_replication table) in the Application name field. These values are not user-modifiable.

Click the Add icon to add the standby definition to the table, and open a fresh definition dialog. When you’ve defined all of the standby servers in the replication scenario, click Next to continue.

Streaming replication extension summary page

The Extension Summary panel lists the extensions or modules that are installed on the nodes of the replication scenario. Any extension installed on the master node must also be installed on each standby node of the replication scenario.

Click Next to continue.

Streaming replication install options page

If PEM is installing new servers, the Streaming Replication wizard opens to a tree control that provides an overview of the master and standby nodes, and allows you to specify installation properties for each server. To review or modify the installation properties, highlight the name of a node in the tree control, and provide values in the Option value fields. Prompts will notify you of each required field.

When updating the installation properties, you should confirm that the user name and password specified match the name and password provided on the Master Server Selection dialog. You should also confirm that the specified port is not already in use on the host of the master or standby. The data directory for the cluster may be created in a non-default location. If you move the data directory from the path specified during the installation, you must update the path specified in the /etc/postgres-reg.ini file.

After providing any required options, click Next to continue.

Streaming replication install summary page

The Download Packages dialog displays a list of the packages that will be required to install the configured replication scenario. Click Back to return to a previous screen and modify the selections, or click Start Download to begin downloading the packages that will be used for the installation.

Streaming replication download page

When the download completes, click Next to continue; the streaming replication wizard will open a dialog that allows you to schedule streaming replication setup.

Streaming replication download schedule page

Use fields on the Schedule Setup dialog to specify the most convenient time for the server to configure the replication scenario:

  • Set Setup now? to Yes to instruct PEM that it should install and configure streaming replication immediately.
  • Set Setup now? to No to enable the date and time selectors; use the selectors to specify when you would like PEM to (optionally) perform installations and configure streaming replication.

Click Finish to save your choice and exit the wizard; PEM will either begin the installation and configuration process or schedule the installation and configuration for the specified time. You can review the job schedule and job progress on the Scheduled Tasks tab; to open the Scheduled Tasks tab, select Scheduled Tasks… from the Management menu.

When the installation and setup completes, you can define a server connection to the master or standby nodes in the PEM client, and monitor the new replication scenario on the Streaming Replication dashboard. Please note that the Streaming Replication wizard only modifies the pg_hba.conf file on replication nodes to allow connections by the replication user; before defining a server connection in the PEM client, you may need to modify the pg_hba.conf file on each node to allow the connection.

After defining each server connection, enable the appropriate probes on each node:

To view the Streaming Replication Analysis dashboard for the master node of a replication scenario, you must ensure that the following probes are enabled:

  • Streaming Replication
  • WAL Archive Status

To view the Streaming Replication Analysis dashboard for the slave node of a replication scenario, you must ensure that the following probe is enabled:

  • Streaming Replication Lag Time

Then, to open the Streaming Replication dashboard, select the name of the master or standby node of the replication scenario from the Servers menu, and Streaming Replication Analysis from the Dashboards menu.