0

We are using PostgreSQL’s logical replication to replicate data from our main transaction database to a reporting database.

  • Main DB: Production transaction database (source)
  • Replication DB: Used for reports and inquiry screens (target)
  • Problem: After the EOD process, a very large volume of transactions is generated in the main DB. These need to be replicated to the reporting DB. However, the replication takes time, and until it completes, our reports show incomplete data.

What I want to know is:

👉 Is there a way in PostgreSQL to check whether the logical replication has fully caught up or if it is still lagging/processing, so that we can hold the report generation until replication is done?

I’ve tried checking the count of some important transaction tables using the current business date filter, but it's taking too much time on prod (almost an hour).

Environment:

  • PostgreSQL version: 13
  • Setup: Logical replication from one primary DB to one subscriber DB

Any pointers or SQL queries to check the replication status/lag would be very helpful.

Improve this question

1 Answer 1

Reset to default
3

You can query pg_stat_replication:

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)
FROM pg_stat_replication
WHERE application_name = 'my_subscription';

That will show you how many bytes of WAL the subscriber is behind.

Improve this answer