'How to select tables under replication in Postgres or List databases under replication in Postgres

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

postgresqllogical-replication


Solution 1:[1]

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

Solution 2:[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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Suraj Rao

Related Questions