find target of postgres logical replication setup

Question

In a postgres logical replication setup, how do I know the hostname and db of the subscriber? I can find the publisher details from the subscriber but how do I find subscriber details from publisher?

postgres=> select * from pg_subscription;
  oid  | subdbid | subname | subowner | subenabled | subbinary | substream |                                                       subconninfo                                           
            | subslotname | subsynccommit | subpublications 
-------+---------+---------+----------+------------+-----------+-----------+-------------------------------------------------------------------------------------------------------------
------------+-------------+---------------+-----------------
 24599 |   14717 | my_sub  |    16397 | t          | f         | f         | host=source-database.xxxx.us-east-2.rds.amazonaws.com port=5432 user=postgres password=xxx dbna
me=postgres | my_sub      | off           | {my_pub}
(1 row)

Another question - when replication slot is dropped and replication is stopped, if any changes happen on the publisher end, will these changes be replicated to subscriber when the replication is resumed? Is there any option to resume replication using LSN?

Answer 1

In POV of primary he has a logical slot and any client/subscriber can connect to the slot with proper commands.You can get current consuming client/subscriber details from select * from pg_stat_activity where application_name='slot_name';.Also we can drop slot in master irrespective of subscriber.But to drop subscription you must have the slot in master...otherwise you need to disable the subscription and drop it.

When slot is dropped you cannot continue logical replication.After dropping slot if there are some changes in primary that you want in subscriber you have drop the table and re-create it in subscriber and establish new logical replication connection so that initial sync will solve your problem.

Answer 2

In order to get port and host details you need to set both instances as publisher and subscriber. Here is the demo example which might help you: Lets assume you have same tables in both instances. Let say there are two instances pg1 and pg2

pg1:

CREATE PUBLICATION pub1
FOR TABLE test;

pg2:

CREATE PUBLICATION pub2
FOR TABLE test;

pg1:

CREATE SUBSCRIPTION sub1
  CONNECTION 'host=pg2 port=5431 user=repuser password=welcome1 dbname=postgres'
  PUBLICATION pub2
  WITH (origin = none, copy_data = false);

pg2:

CREATE SUBSCRIPTION sub2
  CONNECTION 'host=pg1 port=5432 user=repuser password=welcome1 dbname=postgres'
  PUBLICATION hrpub1
  WITH (origin = none, copy_data = true);

Then from pg1 you can find port and host of pg2

postgres=# SELECT * FROM pg_subscription;
  oid  | subdbid | subskiplsn | subname | subowner | subenabled | subbinary | substream | subtwophasestate | subdisableonerr | subpa
sswordrequired | subrunasowner |                               subconninfo                               | subslotname | subsynccomm
it | subpublications | suborigin 
-------+---------+------------+---------+----------+------------+-----------+-----------+------------------+-----------------+------
---------------+---------------+-------------------------------------------------------------------------+-------------+------------
---+-----------------+-----------
 24588 |       5 | 0/0        | hrsub1  |       10 | t          | f         | f         | d                | f               | t    
               | f             | host=localhost port=5431 user=repuser password=welcome1 dbname=postgres | hrsub1      | off        
   | {hrpub2}        | none
(1 row)

and same from pg2 to pg1

postgres=# SELECT * FROM pg_subscription;
  oid  | subdbid | subskiplsn | subname | subowner | subenabled | subbinary | substream | subtwophasestate | subdisableonerr | subpa
sswordrequired | subrunasowner |                               subconninfo                               | subslotname | subsynccomm
it | subpublications | suborigin 
-------+---------+------------+---------+----------+------------+-----------+-----------+------------------+-----------------+------
---------------+---------------+-------------------------------------------------------------------------+-------------+------------
---+-----------------+-----------
 16402 |       5 | 0/0        | hrsub2  |       10 | t          | f         | f         | d                | f               | t    
               | f             | host=localhost port=5432 user=repuser password=welcome1 dbname=postgres | hrsub2      | off        
   | {hrpub1}        | none
(1 row)