Using Postgresql logical replication, how do you know that the subscriber is caught up?

Postgresql has some interesting monitoring tools for monitoring the new logical replication system's progress, but I don't really understand them. The two tools I'm aware of are:

pg_stat_replication

and it's sibling:

pg_stat_subscription

I've read the documentation for these, but they don't say how to know if a replica is actually synced, and interpreting these tables didn't seem obvious to me. Can anybody explain?

I used replication on Postgres 10 last year and I had the same problem understanding how to monitor the process, as documentation is not clear enough. Anyway, I'll try to give you an example to check if monitoring is going on.

On the publisher side there are few things you can check:

• pg_catalog.pg_publication;

• pg_catalog.pg_publication_tables;

• pg_current_wal_lsn();

I'll create a publication "test_publication" with two tables: t_1 and t_2. I won't cover the prerequisites (user, roles and so on).

test_logical_replication=# create publication test_publication for table t_1, t_2;  
CREATE PUBLICATION  
test_logical_replication=# select * from pg_catalog.pg_publication;  
pubname      | pubowner | puballtables | pubinsert | pubupdate | pubdelete  
-----------------+----------+--------------+-----------+-----------+-----------  
test_publication |       10 | f            | t         | t         | t  
(1 row) 

test_logical_replication=# select * from pg_publication_tables;    
    pubname      | schemaname | tablename  
    ------------------+------------+-----------  
     test_publication | public     | t_1  
     test_publication | public     | t_2  
    (2 rows)

On the subscriber side:

test_logical_replication_subscriber=# create subscription test_subscription CONNECTION 'dbname=test_logical_replication host=XXX user=repuser' PUBLICATION test_publication;  
NOTICE:  created replication slot "test_subscription" on publisher   
CREATE SUBSCRIPTION

Interesting information is in the table pg_catalog.pg_stat_subscription.

Here the important columns are:

• received_lsn: Last write-ahead log location received .

• last_msg_send_time: send time of last message received from the publisher.

• last_msg_receipt_time: Receipt time of last message received from the publisher.

• latest_end_lsn: Last write-ahead log location reported to the publisher.

• latest_end_time: Time of last write-ahead log location reported to the publisher.

You have to check these columns to catch what is happening.

First, check if the two databases are in sync;

Publisher side:

test_logical_replication=> select pg_current_wal_lsn();  
 pg_current_wal_lsn  
--------------------  
 0/8EB83768

This shows the location in the WAL file where we are now, before starting a new insert.

We can check on the subscriber that at this moment the two database are in sync, because the value returned by pg_current_wal_lsn() on the publisher matches the value in the
columns received_lsn and latest_end_lsn on the subscriber:

test_logical_replication_subscriber=# select received_lsn, latest_end_lsn from pg_catalog.pg_stat_subscription;  

received_lsn    | latest_end_lsn  
----------------+------------------     
 0/8EB83768     | 0/8EB83768

I'll add 4000 rows to table t_1, and see what happens on the publisher:

test_logical_replication=> insert into t_1 select id+1, txt||'--BB' from t_1;  
INSERT 0 4000  

test_logical_replication=> select pg_current_wal_lsn();  
 pg_current_wal_lsn
--------------------
 0/8EC4B9D0              select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/8EC4DE78              select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/8EC4DEB0              select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/8EC4DEB0            <<< same value, WAL sending has finished
(1 row)

Let's look how pg_catalog.pg_stat_subscription values change during replication on the subscriber:

```
test_logical_replication_subscriber=# select received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time from pg_catalog.pg_stat_subscription;
received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time
--------------+-------------------------------+------------------------------+----------------+-------------------------------
0/8EC4B9D0 | 2018-12-17 11:39:56.014564+01 | 2018-12-17 11:39:56.07322+01 | 0/8EC4B9D0 | 2018-12-17 11:39:56.014564+01
(1 row)

test_logical_replication_subscriber=# select received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time from pg_catalog.pg_stat_subscription;
received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time
--------------+-------------------------------+-------------------------------+----------------+-------------------------------
0/8EC4BA08 | 2018-12-17 11:39:56.737101+01 | 2018-12-17 11:39:56.736303+01 | 0/8EC4BA08 | 2018-12-17 11:39:56.737101+01
(1 row)

test_logical_replication_subscriber=# select received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time from pg_catalog.pg_stat_subscription;
received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time
--------------+-------------------------

StackExchange Database Administrators Q#224490, answer score: 29