SoFunction
Updated on 2025-05-18

A detailed explanation of PostgreSQL replication parameters

1. Basic concepts of copying parameters

PostgreSQL's replication system is mainly based on the write-pre-log (WAL) mechanism, which realizes data redundancy by propagating data changes on the primary server to one or more backup servers. This process is controlled by multiple key parameters, which together determine the behavioral characteristics and performance of replication.

In the PostgreSQL replication schema, the primary server (primary) is responsible for generating WAL records, while the standby server (standby) receives and applies these records. This mechanism not only supports high availability solutions, but also realizes read and write separation, effectively improving the overall performance of the system. Understanding how these replication parameters work is critical to building a stable and reliable database replication environment.

2. In-depth analysis of core replication parameters

1. max_wal_senders: WAL sending process number control

max_wal_sendersThe parameters determine the maximum number of WAL sending processes that the system can run simultaneously. Each standby server connected to the primary server requires a separate WAL sending process. By default, this parameter is commented, meaning that the system does not reserve any WAL sending processes.

Configuration recommendations：

  • The setting value should be greater than the current number of backup servers, and space should be reserved for future expansion
  • Typical production environments are recommended to set to 5-10, depending on the replication topology complexity
  • Modifying this parameter requires restarting the PostgreSQL service to take effect

For example, in the case of 2 backup servers, it is recommended to set to:

max_wal_senders = 5

2. max_replication_slots: replication slot management

Replication slots are an important mechanism in PostgreSQL to ensure WAL file retention.max_replication_slotsThe maximum number of replication slots that can be created in the parameter control system. Each physical replication backup server usually requires a replication slot, while a logical replication subscriber may require an additional replication slot.

Key Features：

  • Prevent the primary server from deleting WAL files that have not been received by the standby server prematurely
  • Must be withmax_wal_sendersParameter coordination configuration
  • Restart the service as well

Typical configuration example:

max_replication_slots = 5

3. WAL retains policy parameter group

PostgreSQL provides multiple parameters to fine-grained control of WAL files retention policies:

wal_keep_size(Default 0MB):

  • Specifies the WAL file size (MB) that the master server should retain
  • A specified amount of WAL will be retained even if there is no copy slot.
  • Replaced the wal_keep_segments parameter in the old version

max_slot_wal_keep_size(Default -1MB):

  • Controls the maximum disk space for WAL files retained by the copy slot
  • -1 means no limit
  • Prevents the copy slot from causing unlimited growth of WAL files

Configuration recommendations：

wal_keep_size = 1024  # Keep 1GB WAL file as buffermax_slot_wal_keep_size = 2048  # Each copy slot can retain up to 2GB WAL

3. Replication performance and reliability parameters

1. wal_sender_timeout: Network reliability guarantee

wal_sender_timeoutThe parameter (default 60 seconds) determines the maximum time the WAL sending process waits for the backup server to respond. After this time limit is exceeded, the sending process will terminate the connection.

Tuning suggestions：

  • Keep default values ​​in a stable network environment
  • High latency or unstable networks should be appropriately increased
  • Set to 0 to disable timeout (not recommended for production environment)

Sample configuration:

wal_sender_timeout = 120s  # For cross-data center replication

2. track_commit_timestamp: Advanced replication support

track_commit_timestampParameters (default off) control whether to record the timestamp information of transaction commits. While this can bring a slight performance overhead, it is crucial for some advanced features.

Application scenarios：

  • Logical replication requires this function to determine transaction order
  • Point-in-time recovery (PITR) operation
  • Database audit and monitoring tools

Enable configuration:

track_commit_timestamp = on

4. Production environment configuration strategy

1. Parameter collaborative configuration principle

When configuring replication parameters, the mutual influence between each parameter must be considered:

  • max_wal_sendersShould be greater than or equal tomax_replication_slots
  • wal_keep_sizeand the replication slot mechanism can be used complementary
  • Network latency factors should be reflected inwal_sender_timeoutSettings

2. High Availability Configuration Example

A typical high availability environment configuration might be as follows:

# Copy the basic configurationmax_wal_senders = 10
max_replication_slots = 8

# WAL retention policywal_keep_size = 2048MB
max_slot_wal_keep_size = 4096MB

# Network and Performancewal_sender_timeout = 90s
track_commit_timestamp = on

3. Monitoring and Tuning Suggestions

  • Regular inspectionspg_stat_replicationView monitoring replication status
  • Monitor WAL directory size to prevent disk space from running out due to improper configuration
  • Adjust WAL reserved parameters according to the standby server latency
  • Re-evaluate replication configuration before major business changes (such as major promotions)

5. Special scene processing

1. Handle replication delay issues

When replication delay is encountered, consider:

  • Increasewal_keep_sizeProvides greater buffer space
  • Check network conditions and adjust appropriatelywal_sender_timeout
  • make suremax_wal_sendersand sufficient number of replication slots

2. Special considerations for logical replication

Logical replication requires special attention:

  • Must be enabledtrack_commit_timestamp
  • Additional copy slots may be required
  • WAL retention requirements are usually higher than physical replication

The above is a detailed explanation of PostgreSQL replication parameters in this article. For more information about PostgreSQL replication parameters, please follow my other related articles!