CODEWITHSUNDEEP
postgresqllogical-replication
Dushyant Sapra
Dushyant Sapra

Reputation: 625

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

Upvotes: 2

Views: 8731

Answers (2)

Zvonko
Zvonko

Reputation: 301

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.

Upvotes: 2

EWJ00
EWJ00

Reputation: 419

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

Upvotes: 4

Related Questions