在本地主机搭建PostgreSQL只读副本用于报表查询

我已经在本地主机完成PostgreSQL主库搭建，现在想在同一主机上创建一个只读副本，专门用来跑报表查询，以此提升报表性能、减轻主库的读压力。

核心需求和疑问：

• 只读副本的postgresql.conf等配置该怎么设置？
• 选哪种复制方式合适（比如流式复制）？
• 怎么确保副本和主库始终同步？
• 本地同主机部署有哪些注意事项和优化点？

另外我之前尝试逻辑复制但失败了，操作步骤如下：

wal_level = logical

CREATE PUBLICATION "PrimaryDBPub" FOR ALL TABLES
WITH (publish = 'insert, update, delete, truncate');

SELECT pg_create_logical_replication_slot('Replicationslot', 'pgoutput');

CREATE SUBSCRIPTION CflSub
CONNECTION 'dbname=PrimaryDB user=db_replication_user password=spts@3311 host=localhost port=5432'
PUBLICATION PrimaryDBPub
WITH (slot_name = 'Replicationslot',create_slot = false);

一、先分析你逻辑复制失败的可能原因 #

本地同主机用逻辑复制其实不是最优选择（逻辑复制更适合跨版本、部分表复制场景），但你的操作里可能存在这些问题：

• 订阅创建时指定create_slot=false，若主库的槽是刚创建的，订阅端可能无法正确关联；
• 复制用户db_replication_user缺少必要权限（需REPLICATION权限，以及所有发布表的SELECT权限）；
• 主库pg_hba.conf未配置允许该用户从本地连接；
• 主库存在大表时，逻辑复制初始化阶段可能因锁或超时失败。

二、推荐用**流式复制（物理复制）**做本地只读副本 #

本地同主机场景下，物理复制性能更高、配置更简单，完全满足报表只读需求，步骤如下：

1. 主库配置修改 #

（1）postgresql.conf关键配置

# 开启归档和复制相关参数
wal_level = replica  # 物理复制用replica即可，比logical更轻量
max_wal_senders = 5  # 允许的复制连接数，本地副本设1-5足够
wal_keep_size = 1GB  # 保留的WAL日志大小，防止副本跟不上时日志被清理
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'  # 先创建好指定的归档目录
hot_standby = on  # 主库也允许只读查询（可选）

（2）pg_hba.conf添加复制用户权限

添加两行允许本地复制用户连接的规则：

host    replication     db_replication_user     127.0.0.1/32     scram-sha-256
host    replication     db_replication_user     ::1/128          scram-sha-256

（3）创建复制用户

CREATE ROLE db_replication_user WITH REPLICATION LOGIN PASSWORD 'spts@3311';

（4）重启主库使配置生效

sudo systemctl restart postgresql

2. 只读副本搭建（本地同主机） #

（1）临时停止主库，做基础备份

sudo systemctl stop postgresql

（2）复制主库数据目录到副本目录

假设主库数据目录是/var/lib/postgresql/15/main，副本目录设为/var/lib/postgresql/15/replica：

sudo cp -R /var/lib/postgresql/15/main /var/lib/postgresql/15/replica
# 删除副本目录里的postmaster.pid文件，避免端口冲突
sudo rm /var/lib/postgresql/15/replica/postmaster.pid

（3）修改副本的postgresql.conf

# 副本核心配置
port = 5433  # 本地副本必须用不同端口，避免和主库冲突
hot_standby = on  # 允许副本接受只读查询
max_standby_streaming_delay = 30s  # 副本延迟超过30s时暂停只读查询，优先同步
wal_receiver_status_interval = 10s  # 定期上报同步状态

（4）配置副本连接主库的参数（PostgreSQL 12+）

PostgreSQL 12及以上版本无需recovery.conf，改为以下两步：

• 在副本数据目录创建standby.signal文件：

sudo touch /var/lib/postgresql/15/replica/standby.signal

• 在副本的postgresql.conf添加主库连接信息：

primary_conninfo = 'host=localhost port=5432 user=db_replication_user password=spts@3311 application_name=report_replica'
primary_slot_name = 'report_replica_slot'  # 用复制槽防止主库WAL被提前清理

（5）创建主库的复制槽（推荐）

启动主库后创建复制槽：

sudo systemctl start postgresql

SELECT pg_create_physical_replication_slot('report_replica_slot');

（6）启动副本

sudo systemctl start postgresql@15-replica  # 服务名根据你的系统配置调整

3. 确保副本与主库同步的方法 #

• 查看副本同步状态：在副本执行SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();，若pg_last_wal_receive_lsn和pg_last_wal_replay_lsn接近主库的pg_current_wal_lsn()，说明同步正常；
• 主库开启WAL归档，确保副本断开后能通过归档日志追平数据；
• 配置复制槽，主库不会清理副本未接收的WAL日志；
• 本地同主机无网络延迟，只要副本服务正常，同步基本不会出问题。

4. 本地部署的注意事项与优化 #

• 端口冲突：副本必须使用与主库不同的端口，比如主库5432、副本5433；
• 资源隔离：给副本预留足够CPU、内存，可在副本postgresql.conf中调整shared_buffers、work_mem等参数，避免和主库抢资源；
• 只读权限控制：给报表用户设置只读权限，防止副本被误写：

CREATE ROLE report_user WITH LOGIN PASSWORD 'report_pass';
GRANT CONNECT ON DATABASE your_db TO report_user;
GRANT USAGE ON SCHEMA public TO report_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO report_user;

• 监控同步状态：在主库执行SELECT * FROM pg_stat_replication;，查看副本连接和同步情况；
• 自动清理：给副本配置合适的autovacuum参数，避免报表查询产生的垃圾数据堆积。

三、如果一定要用逻辑复制的修正方案 #

若坚持使用逻辑复制，修正以下几点：

1. 确保主库pg_hba.conf允许复制用户本地连接；
2. 给复制用户添加所有发布表的SELECT权限：

GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_replication_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO db_replication_user;

3. 创建订阅时可去掉create_slot=false，让订阅自动关联槽（或确认主库的槽确实存在且可用）；
4. 保留copy_data=true（默认值），确保订阅初始化时同步现有数据：

CREATE SUBSCRIPTION CflSub
CONNECTION 'dbname=PrimaryDB user=db_replication_user password=spts@3311 host=localhost port=5432'
PUBLICATION PrimaryDBPub
WITH (slot_name = 'Replicationslot');

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