PostgreSQL逻辑复制：查询订阅端信息及中断恢复相关问题

1. 订阅端主机名/数据库信息查询，以及发布端如何查看订阅端信息 #

订阅端查询自身主机名与数据库信息 #

• 获取当前连接的数据库名称：

SELECT current_database();

• 获取订阅端数据库的监听地址、端口：

-- 数据库监听IP
SELECT inet_server_addr();
-- 数据库监听端口
SELECT inet_server_port();
-- 查看主机名（若服务器配置允许）
SELECT setting AS hostname FROM pg_settings WHERE name = 'listen_addresses';

订阅端查询发布端详情 #

在订阅端执行以下查询，通过pg_subscription系统视图获取发布端的连接详情，其中subconninfo字段包含发布端的主机、端口、数据库名等核心信息：

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 dbname=postgres | my_sub      | off           | {my_pub}
(1 row)

发布端查询订阅端信息 #

发布端没有直接存储订阅端主机名和数据库信息的系统视图，可通过以下两种方式间接获取：

1. 查询逻辑复制槽信息（订阅端的复制槽名通常与订阅名称一致）：

SELECT * FROM pg_replication_slots WHERE slot_type = 'logical';

2. 查看当前活跃的订阅端连接（仅当订阅端处于连接状态时有效）：

SELECT client_addr AS subscriber_ip, client_port, usename, dbname FROM pg_stat_activity WHERE application_name LIKE '%subscription%';

如果订阅端未连接，只能通过PostgreSQL的历史日志（如postgresql.log）查询订阅端的连接记录。

2. 复制槽删除、复制停止后，恢复时的变更同步与LSN恢复支持 #

复制槽删除后，新变更能否同步 #

复制槽被删除后，发布端会按照WAL保留策略（如wal_keep_size或归档配置）清理对应WAL日志。若恢复复制时，发布端产生的新变更WAL仍未被清理，可通过指定起始LSN同步；若WAL已被清理，这部分变更无法直接同步，需通过全量备份+增量WAL恢复补全数据。

是否支持LSN恢复复制 #

PostgreSQL逻辑复制支持通过指定起始LSN恢复复制，操作步骤如下：

1. 在发布端获取目标起始LSN（或当前LSN）：

SELECT pg_current_wal_lsn();

2. 重新创建订阅时指定起始LSN：

CREATE SUBSCRIPTION my_sub
CONNECTION 'host=source-host port=5432 user=postgres dbname=source_db'
PUBLICATION my_pub
START '0/1234567'; -- 替换为实际需要的LSN值

注意：指定的LSN必须存在于发布端保留的WAL日志中，若对应WAL已被清理，该方式会失败，需先恢复全量备份再同步。

内容的提问来源于stack exchange，提问作者nmakb