DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • Performing and Managing Incremental Backups Using pg_basebackup in PostgreSQL 17
  • Point-In-Time Recovery (PITR) in PostgreSQL
  • Setting Up Failover Slots in PostgreSQL-17
  • PostgreSQL Performance Metrics

Trending

  • A Complete Guide to Modern AI Developer Tools
  • Dropwizard vs. Micronaut: Unpacking the Best Framework for Microservices
  • How To Develop a Truly Performant Mobile Application in 2025: A Case for Android
  • Creating a Web Project: Caching for Performance Optimization
  1. DZone
  2. Data Engineering
  3. Databases
  4. Queries for Optimizing and Debugging PostgreSQL Replication

Queries for Optimizing and Debugging PostgreSQL Replication

Postgres replication is a powerful tool for replicating data between databases. Learn about the queries to monitor, optimize, and debug the PostgreSQL replication.

By 
Dr. Ibrar Ahmed user avatar
Dr. Ibrar Ahmed
·
Mar. 14, 25 · Tutorial
Likes (6)
Comment
Save
Tweet
Share
5.3K Views

Join the DZone community and get the full member experience.

Join For Free

PostgreSQL logical replication provides the power and organization behind a pgEdge replication cluster, allowing you to replicate tables selectively and, on a more granular level, the changes in those tables. Whether you're using pgEdge Distributed PostgreSQL replication for real-time analytics, low latency, or high availability, optimizing replication configuration and query use allows you to optimize for performance, consistency, and reliability.

Postgres replication is a powerful tool for replicating data between databases; unlike physical replication, logical replication gives you more control and flexibility over what data is replicated and how it's used.

This blog explores queries that make it easier to manage logical replication for your PostgreSQL database.

Monitoring Postgres Logical Replication Status

Monitoring the status of your logical replication setup is critical to ensure that your replication is running smoothly. Querying the pg_stat_subscription view can help you monitor the status of all of the subscriptions in your database:

SQL
 
SELECT 
    subname AS subscription_name,
    pid AS process_id,
    usename AS user_name,
    application_name,
    client_addr AS client_address,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    clock_timestamp() - write_lsn_timestamp AS replication_delay
FROM 
    pg_stat_subscription
ORDER BY 
    subscription_name;


subscription_name | process_id | user_name | application_name | client_address |    state    | sync_state |   sent_lsn   |  write_lsn   |  flush_lsn   |  replay_lsn  | replication_delay
-------------------+------------+-----------+------------------+----------------+-------------+------------+--------------+--------------+--------------+--------------+-------------------
 sub1              |      23456 | postgres  | logical_rep_sub   | 192.168.1.10   | streaming   | synced     | 0/3000128    | 0/3000128    | 0/3000128    | 0/3000128    | 00:00:00.12345
 sub2              |      23478 | postgres  | logical_rep_sub   | 192.168.1.11   | catchup     | async      | 0/4000238    | 0/4000200    | 0/40001F8    | 0/40001E0    | 00:00:02.67890


  • subname – The name of the subscription.
  • state – The state of the subscription process (e.g., streaming, catchup, initializing).
  • sync_state – The synchronization state of the subscription.
  • sent_lsn, write_lsn, flush_lsn, replay_lsn – These columns represent various Log Sequence Numbers (LSNs) that indicate replication progress.
  • replication_delay – The delay between the LSN being written and its application on the subscriber is crucial for identifying lag in replication.

This query provides a comprehensive overview of the logical replication status, allowing you to quickly identify issues such as replication lag or disconnected subscribers.

Analyzing Postgres Replication Lag

Understanding replication lag is essential in maintaining the consistency and freshness of data across your replicated databases. The pg_replication_slots system view can help you calculate the replication lag between the publisher and subscriber:

SQL
 
SELECT 
    s.slot_name,
    s.active,
    s.restart_lsn,
    pg_wal_lsn_diff(pg_current_wal_lsn(), s.restart_lsn) AS replication_lag_bytes,
    clock_timestamp() - pg_last_xact_replay_timestamp() AS replication_lag_time
FROM 
    pg_replication_slots s
WHERE 
    s.active = true
AND 
    s.plugin = 'pgoutput';


slot_name | active | restart_lsn | replication_lag_bytes | replication_lag_time 
-----------+--------+-------------+-----------------------+-----------------------
 slot1     | t      | 0/3000128   |                 65536 | 00:00:00.12345
 slot2     | t      | 0/4000238   |                131072 | 00:00:02.67890


  • slot_name – The name of the replication slot being used.
  • replication_lag_bytes – The difference in bytes between the current WAL position on the publisher and the last WAL position acknowledged by the subscriber.
  • replication_lag_time – The time difference between the last transaction replayed on the subscriber and the current time.

This query helps you assess the size and time-based lag in your logical replication, enabling you to take proactive measures if the lag exceeds acceptable thresholds.

Monitoring Replication Slot Usage

Replication slots are critical in logical replication, ensuring that WAL segments are retained until all subscribers process them. You can query the pg_replication_slots view to monitor the use of replication slots:

SQL
 
SELECT
    slot_name,
    plugin,
    slot_type,
    active,
    confirmed_flush_lsn,
    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS slot_lag_bytes
FROM 
    pg_replication_slots
WHERE 
    slot_type = 'logical';


slot_name | plugin  | slot_type | active | confirmed_flush_lsn | slot_lag_bytes 
-----------+---------+-----------+--------+---------------------+----------------
 slot1     | pgoutput| logical   | t      | 0/3000128           |           65536
 slot2     | pgoutput| logical   | t      | 0/4000238           |          131072


  • slot_name – The name of the replication slot.
  • slot_lag_bytes – The lag in bytes between the current WAL position and the last position is confirmed as flushed by the slot.

Monitoring replication slot usage is crucial for preventing issues related to WAL segment retention, which could potentially lead to disk space exhaustion on the publisher.

Dropping Unused Replication Slots

Over time, you may accumulate unused replication slots, especially after removing subscribers or changing replication configurations. These unused slots can cause unnecessary retention of WAL files, leading to wasted disk space. The following query identifies and drops unused replication slots:

SQL
 
DO $$
DECLARE
    slot_record RECORD;
BEGIN
    FOR slot_record IN
        SELECT slot_name FROM pg_replication_slots WHERE active = false
    LOOP
        EXECUTE format('SELECT pg_drop_replication_slot(%L)', slot_record.slot_name);
    END LOOP;
END $$;


This query iterates over your inactive replication slots and uses the pg_drop_replication_slot management function to drop them. Regularly cleaning up unused replication slots will ensure that your database remains efficient and prevent potential issues with WAL file retention.

Creating Replication Slots

If you need to create a new logical replication slot, the following query is useful:

SQL
 
SELECT * FROM pg_create_logical_replication_slot('my_slot', 'pgoutput');

slot_name | xlog_position 
-----------+---------------
 my_slot   | 0/3000128


This query uses the pg_create_logical_replication_slot function to create a new logical replication slot with the specified name and output plugin (pgoutput in our example). The query is useful when setting up new logical replication configurations; use it to confirm that the subscriber can start receiving changes from the correct point in the WAL records.

Optimizing Logical Replication With pglogical

If you’re using the pglogical extension for more advanced logical replication capabilities, the following query can help you check the status of all pglogical subscriptions:

SQL
 
SELECT 
    subscription_name,
    status,
    received_lsn,
    replay_lag,
    last_received_change,
    pending_changes
FROM 
    pglogical.show_subscription_status();


subscription_name | status   | received_lsn | replay_lag | last_received_change | pending_changes 
-------------------+----------+--------------+------------+---------------------+-----------------
 sub_pglogical1    | replicating | 0/3000128 | 00:00:01.234 | 2024-08-22 10:30:00 | 5
 sub_pglogical2    | idle      | 0/4000238 | 00:00:00.000 | 2024-08-22 10:29:30 | 0


  • subscription_name – The name of the pglogical subscription.
  • replay_lag – The lag between the last received change and the current time.
  • pending_changes – The number of changes pending to be applied to the subscriber.

This query provides a detailed overview of your pglogical subscriptions, helping you fine-tune replication settings and troubleshoot issues.

Conclusion

pgEdge Distributed PostgreSQL uses logical replication across your cluster, providing greater control and flexibility over precisely what data is replicated and how that data is stored. pgEdge continues to develop versatile tooling that offers fine-grained control over data replication processes. The queries outlined in this blog can help you effectively monitor, manage, and optimize your logical replication clusters. These queries help ensure data consistency, minimize replication lag, and prevent conflicts, all essential for maintaining a robust and reliable database environment.

As you continue to work with logical replication, consider incorporating these queries into your regular monitoring and maintenance routines to ensure your PostgreSQL databases and pgEdge clusters perform optimally.

Replication (computing) PostgreSQL write-ahead logging

Published at DZone with permission of Dr. Ibrar Ahmed. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Performing and Managing Incremental Backups Using pg_basebackup in PostgreSQL 17
  • Point-In-Time Recovery (PITR) in PostgreSQL
  • Setting Up Failover Slots in PostgreSQL-17
  • PostgreSQL Performance Metrics

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!