PostgreSQL Replication Settings Query Guide

calendar Mar 19, 2026 / · 5 min read · postgresql database administration sql queries replication configuration high availability pg_settings postgres dba  ·
Share on: linkedin copy

PostgreSQL Replication Settings Query Guide

This PostgreSQL query retrieves all replication-related configuration settings from the pg_settings system view. These parameters control streaming replication, logical replication, standby server behaviour, and WAL sender configuration.

Purpose and Overview

Replication is critical infrastructure for high availability and disaster recovery. Misconfigured replication settings are a common cause of replication lag, replication slot bloat, or standbys falling too far behind to catch up.

This query gives administrators a single view of every replication parameter — whether they are managing a primary server, a hot standby, or logical replication subscribers.

SQL Script

 1SELECT
 2    name,
 3    setting,
 4    -- category,
 5    vartype,
 6    short_desc,
 7    -- enumvals,
 8    source,
 9    min_val,
10    max_val,
11    boot_val,
12    reset_val,
13    -- not available on PostgreSQL < 9.5
14    pending_restart
15FROM
16    pg_settings
17WHERE
18    -- Replication / Standby Servers
19    -- Replication / Sending Servers
20    -- Replication / Master Server
21    -- Replication / Subscribers
22    -- Replication
23    category ILIKE '%Replication%';

Code Breakdown

Filter Condition

1WHERE category ILIKE '%Replication%'

Matches all subcategories under the Replication group:

  • Replication / Sending Servers — primary server settings for WAL senders
  • Replication / Master Server — primary-specific settings like synchronous_standby_names
  • Replication / Standby Servers — settings that govern standby behaviour
  • Replication / Subscribers — logical replication subscriber settings

Selected Columns

name: The configuration parameter name such as max_wal_senders or hot_standby.

setting: The current active value.

vartype: The data type (bool, integer, real, string, enum).

short_desc: A brief description of what the parameter controls.

source: Where the value originates — default, configuration file, or command line.

min_val / max_val: Allowed range for numeric parameters.

boot_val: Compiled-in default value.

reset_val: Value used if the parameter is reset.

pending_restart: Whether a server restart is needed for a pending change to take effect.

Key Replication Parameters

Sending Server (Primary) Settings

max_wal_senders — Maximum number of concurrent WAL sender processes. Must be greater than the number of standbys. Requires a server restart to change.

wal_keep_size — Minimum amount of WAL files (in MB) to retain for standbys that are not using replication slots. Prevents standbys from falling too far behind and being unable to reconnect.

max_replication_slots — Maximum number of replication slots. Slots guarantee WAL retention for a subscriber but can cause disk fill if a subscriber disconnects without the slot being dropped.

wal_sender_timeout — How long a WAL sender waits before terminating an inactive standby connection.

track_commit_timestamp — Enables recording of commit timestamps, required for some logical replication use cases.

Primary (Master) Server Settings

synchronous_standby_names — Lists standby servers that must confirm receipt of WAL before a commit is acknowledged. Controls synchronous replication behaviour.

vacuum_defer_cleanup_age — Number of transactions by which VACUUM will defer cleanup of dead row versions, giving standbys time to catch up before dead rows are removed.

Standby Server Settings

hot_standby — Enables read queries on a standby server during recovery. Required for readable standbys.

max_standby_archive_delay — How long a standby will delay applying archived WAL when conflicting read queries are running.

max_standby_streaming_delay — How long a standby will delay applying streamed WAL when conflicting read queries are running.

hot_standby_feedback — When enabled, the standby reports its oldest active transaction to the primary, preventing the primary from vacuuming rows the standby still needs.

recovery_min_apply_delay — Introduces an intentional delay before applying WAL on a standby, creating a delayed standby for accidental data loss protection.

wal_receiver_timeout — How long the WAL receiver on a standby waits before terminating an inactive connection to the primary.

wal_receiver_status_interval — How often the standby reports its status back to the primary.

Logical Replication Subscriber Settings

max_logical_replication_workers — Maximum total number of logical replication workers.

max_sync_workers_per_subscription — Maximum number of table synchronization workers per subscription.

Practical Applications

Replication Health Checks

Run this query as part of routine replication health checks to verify that max_wal_senders, max_replication_slots, and WAL retention settings are appropriate for the number of connected standbys.

Replication Slot Monitoring

Confirm max_replication_slots is set high enough and review wal_keep_size to ensure WAL is retained for all active subscribers.

Standby Configuration Audit

On standby servers, verify hot_standby is enabled for readable standbys and review delay settings (max_standby_streaming_delay) to understand how long conflicting queries can run before being cancelled.

Synchronous Replication Review

Check synchronous_standby_names to understand which standbys are in synchronous mode and the potential latency impact on write operations.

Version Compatibility

Requires PostgreSQL 9.5 or later due to the pending_restart column. Tested on PostgreSQL 9.5, 9.6, 10.x, 11.x, 12.x, and 13.0. Note: wal_keep_size replaced wal_keep_segments in PostgreSQL 13.

Best Practices

Monitor replication slots: Inactive replication slots retain WAL indefinitely and can fill the disk. Drop unused slots promptly with pg_drop_replication_slot().

Set wal_keep_size as a safety net: Even with replication slots, configure wal_keep_size so that WAL is retained independently of slot state.

Use hot_standby_feedback carefully: It prevents vacuum conflicts on the standby but can cause table bloat on the primary if long-running standby queries delay cleanup.

Test synchronous replication failover: Confirm synchronous_standby_names configuration is intentional — synchronous mode adds write latency and can block writes if the named standby becomes unavailable.

References

Posts in this series