2

I am having 3 Node Postgres cluster and want to know tables/databases which are under logical replication. The issue I am facing is when the leader/master node goes down and one of the replicas takes over as leader/master node and then over time when the old leader/master rejoins the cluster. In this case, I want to know what has changed under logical replication from this new replica.

Note: New Replica would be synched and would contain the same data as other nodes. I am using postgres 11 and above

Improve this question

2 Answers 2

Reset to default
4

If you are using logical replication:

On the publisher side, the pg_publication_tables table will show you the tables you are logically decoding.

On the subscriber side, you can monitor pg_stat_subscription to get the received_lsn and last_* time columns

If you are using pglogical extension: On the publisher side, the pglogical.node_interface will show you the node id and its name. Also, the pglogical.replication_set table will show you what types of statements are getting replication in the set

On the subscriber side, pglogical.show_subscription_status() function will show you the subscription name and the status like replicating

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

select * from pg_publication_tables; returing empty. I have table reptable under testdb database which is under replication. Data is getting replicated, but no entry under 'pg_publication_tables'. postgres=# select * from pg_publication_tables; pubname | schemaname | tablename ---------+------------+----------- (0 rows) postgres=# \c testdb You are now connected to database "testdb" as user "pgsudo". testdb=# select * from pg_publication_tables; pubname | schemaname | tablename ---------+------------+----------- (0 rows)
how about select * from pg_publication ?
also, does select * from pg_stat_subscription show anything on that server?
postgres=# select * from pg_publication; pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate ---------+----------+--------------+-----------+-----------+-----------+------------- (0 rows)
select * from pg_stat_subscription --> NO
|
2

Follow these steps:

  1. Get the desired replication set ID (no condition lists all available):

    SELECT * FROM pglogical.replication_set
WHERE set_name = 'your_replication_set_name';

    See column 'set_id' value (e.g.: 123456789).

  2. List tables under desired replication set given by 'set_id':

    SELECT * FROM pglogical.replication_set_table
WHERE set_id = 123456789;

    You will get all tables under the required replication set ID.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.