Database Reference
In-Depth Information
Both the WALSender and WALReceiver will work continuously on any outstanding data to be
replicated until the queue is empty. If there is a quiet period, then the WALReceiver will sleep
for 100ms at a time, and the WALSender will sleep for
wal_sender_delay
. Typically, the
value of
wal_sender_delay
need not be altered, because it only affects behavior during
momentary quiet periods. The default value is a good balance between efficiency and data
protection. If the Master and Standby are connected by a low bandwidth network, and the
write rate on the Master is high, you may wish to lower this value to perhaps 20ms or 50ms.
Reducing this value will reduce the amount of data loss if the Master becomes permanently
unavailable, though will also marginally increase the cost of streaming the transaction log
data to the Standbys.
The Standby connects to the Master using native PostgreSQL libpq connections. That means
that all forms of authentication and security work for replication, just as they do for normal
connections. Note that for replication sessions the Standby is the "client" and the Master
is the "server", if any parameters need to be configured. Using standard PostgreSQL libpq
connections also means that normal network port numbers are used, so no additional firewall
rules are required. You should also note that if the connections use SSL, then encryption costs
will slightly increase the replication delay and CPU resources required.
There's more...
If the connection drops between Master and Standby, it will take some time for that to be
noticed across an indirect network. To ensure that a dropped connection is noticed as soon
as possible, you may wish to adjust the
keepalive
settings.
If you want a Standby to notice that the connection to the Master has dropped, you need
to set the
keepalives
in the
primary_conninfo
in the
recovery.conf
on the Standby
as follows:
primary_conninfo = '….
keepalives_idle= 60 …'
If you want the Master to notice that a streaming Standby connection has dropped, you can
set the
keepalive
parameters in
postgresql.conf
on the Master, such as:
tcp_keepalives_idle = 60
# time before we send keepalives
That setting will then apply to all connections from users and replication. If you want to be very
specific, and just set that for replication, you must supply this as an option to be passed to the
Master, which is specified like the following:
primary_conninfo = '….options="-c tcp_
keepalives_idle= 60" …'
All of the preceding examples set the length of time the connection will be idle before we start
sending keepalives to be 60 seconds. The default is two hours, and is not recommended.
There are multiple keepalive parameters we can set; I have avoided showing those here
for clarity. A related option is
connection_timeout
. Remember, you can hide all of this
complexity in a connection service file, so that
primary_conninfo
only refers to a single
service name, as described in the
First
Steps
chapter.
