Preferring synchronous secondary database servers v42.5.1.2
The EDB Postgres Advanced Server JDBC Connector supports the preferSyncSecondary
option for the targetServerType
connection property.
The preferSyncSecondary
option provides a preference for synchronous, standby servers for failover connection, thus ignoring asynchronous servers.
The specification of this capability in the connection URL is shown by the following syntax:
Parameters
primary:port
The IP address or a name assigned to the primary database server followed by its port number. If primary
is a name, you must specify it with its IP address in the /etc/hosts
file on the host running the Java program.
Note
You can specify the primary database server in any location in the list. It doesn't have to precede the secondary database servers.
secondary_n:port_n
The IP address or a name assigned to a standby, secondary database server followed by its port number. If secondary_n
is a name, you must specify it with its IP address in the /etc/hosts
file on the host running the Java program.
database
The name of the database to which to make the connection.
The following is an example of the connection URL:
The following characteristics apply to the preferSyncSecondary
option:
- You cam specify the primary database server in any location in the connection list.
- Connection for accessing the database for use by the Java program is first attempted on a synchronous secondary. The secondary servers are available for read-only operations.
- No connection attempt is made to any servers running in asynchronous mode.
- The order in which connection attempts are made is determined by the
loadBalanceHosts
connection property. If disabled, which is the default setting, connection attempts are made in the left-to-right order specified in the connection list. If enabled, connection attempts are made randomly. - If connection can't be made to a synchronous secondary, then connection to the primary database server is used. If the primary database server isn't active, then the connection attempt fails.
The synchronous secondaries to use for the preferSyncSecondary
option must be configured for hot standby usage.
Configuring primary and secondary database servers overview
The process for configuring a primary and secondary database servers is described in the PostgreSQL documentation.
For general information on hot standby usage, which is needed for the preferSyncSecondary
option, see the PostgreSQL core documentation.
For information about creating a base backup for the secondary database server from the primary, see Section 25.3.2, Making a Base Backup (describes usage of the pg_basebackup
utility program) or Section 25.3.3, Making a Base Backup Using the Low Level API in Section 25.3 Continuous Archiving and Point-in-Time Recovery (PITR) in The PostgreSQL Core Documentation.
For information on the configuration parameters to set for hot standby usage, see Section 19.6, Replication.
Example: Primary and secondary database servers
In the example that follows, the:
- Primary database server resides on host
192.168.2.24
, port5444
. - Secondary database server is named
secondary1
and resides on host192.168.2.22
, port5445
. - Secondary database server is named
secondary2
and resides on host192.162.2.24
, port5446
(same host as the primary).
In the primary database server’s pg_hba.conf
file, there must be a replication entry for each unique replication database USER/ADDRESS
combination for all secondary database servers. In the following example, the database superuser enterprisedb
is used as the replication database user for both the secondary1
database server on 192.168.2.22
and the secondary2
database server that is local relative to the primary.
After the primary database server is configured in the postgresql.conf
file along with its pg_hba.conf
file, database server secondary1
is created by invoking the following command on host 192.168.2.22
for secondary1
:
On the secondary database server, /opt/secondary1
, a recovery.conf
file is generated in the database cluster, which was edited in the following example by adding the application_name=secondary1
setting as part of the primary_conninfo
string and removing some of the other unneeded options automatically generated by pg_basebackup
. Also note the use of the standby_mode = 'on'
parameter.
The application name secondary1
must be included in the synchronous_standby_names
parameter of the primary database server’s postgresql.conf
file.
The secondary database server (secondary2
) is created in an alternative manner on the same host used by the primary:
On the secondary database server /opt/secondary2
, create the recovery.conf
file in the database cluster. The application_name=secondary2
setting is part of the primary_conninfo
string as shown in the following example. Also be sure to include the standby_mode = 'on'
parameter.
The application name secondary2
must be included in the synchronous_standby_names
parameter of the primary database server’s postgresql.conf
file.
You must ensure the configuration parameter settings in the postgresql.conf
file of the secondary database servers are properly set (particularly hot_standby=on
).
Note
As of EDB Postgres Advanced Server v12, the recovery.conf
file is no longer valid. It's replaced by the standby.signal
file. As a result, primary_conninfo
is moved from the recovery.conf
file to the postgresql.conf
file. The presence of the standby.signal
file signals the cluster to run in standby mode. Even if you try to create a recovery.conf
file manually and keep it under the data
directory, the server fails to start and reports an error.
The parameter standby_mode=on
is also removed from EDB Postgres Advanced Server v12, and the trigger_file
parameter name is changed to promote_trigger_file
.
The following table lists the basic postgresql.conf
configuration parameter settings of the primary database server as compared to the secondary database servers.
Parameter | Primary | Secondary | Description |
---|---|---|---|
archive_mode | on | off | Completed WAL segments sent to archive storage |
archive_command | cp %p /archive_dir/%f | n/a | Archive completed WAL segments |
| wal_level (10 or later) | replica | minimal | Information written to WAL segment | | max_wal_senders | n (positive integer) | 0 | Maximum concurrent connections from standby servers | | wal_keep_segments | n (positive integer) | 0 | Minimum number of past log segments to keep for standby servers | | synchronous_standby_names | n(secondary1, secondary2,...) | n/a | List of standby servers for synchronous replication. Must be present to enable synchronous replication. These are obtained from the application_name option of the primary_conninfo parameter in the recovery.conf file of each standby server. | | hot_standby | off | on | Client application can connect and run queries on the secondary server in standby mode |
The secondary database server (secondary1
) is started:
The secondary database server (secondary2
) is started:
To ensure that the secondary database servers are properly set up in synchronous mode, use the following query on the primary database server. The sync_state
column lists applications secondary1
and secondary2
as sync.
The connection URL is:
The /etc/hosts
file on the host running the Java program contains the following entries with the server names specified in the connection URL string:
For this example, the preferred synchronous secondary connection option results in the first usage attempt made on secondary1
, then on secondary2
if secondary1
is not active, and then on the primary if both secondary1
and secondary2
aren't active as shown by the following program. The program displays the IP address and port of the database server to which the connection is made.
Case 1: When all database servers are active, connection is made to secondary1
on 192.168.2.22
port 5445
.
Case 2: When secondary1
is shut down, connection is made to secondary2
on 192.168.2.24
port 5446
.
Case 3: When secondary2
is also shut down, connection is made to the primary
on 192.168.2.24
port 5444
.
- On this page
- Parameters