PostgreSQL 复制教程
1. 核心概念
PostgreSQL 复制是指将数据从一个 PostgreSQL 数据库服务器（主服务器）复制到一个或多个 PostgreSQL 数据库服务器（从服务器）的过程。通过复制，可以提高系统的可用性、可靠性和扩展性。
1.1 复制类型
PostgreSQL 支持多种复制方式：
- 物理复制（流复制）：基于 WAL（Write-Ahead Log）日志的复制，复制整个数据库集群的二进制数据
- 逻辑复制：基于逻辑变更的复制，可以复制特定的表或数据库
- 级联复制：从服务器作为其他从服务器的主服务器
- 同步复制：主服务器等待从服务器确认后再提交事务
- 异步复制：主服务器无需等待从服务器确认即可提交事务
1.2 复制架构
- 主服务器（Primary）：负责处理所有写操作和读操作（默认情况下）
- 从服务器（Standby）：复制主服务器的数据，可用于处理读操作
- WAL 日志：Write-Ahead Log，记录所有数据库变更的日志
- WAL 发送器：主服务器上的进程，负责发送 WAL 日志到从服务器
- WAL 接收器：从服务器上的进程，负责接收 WAL 日志并应用到本地数据库
1.3 复制原理
1.3.1 物理复制原理
- 主服务器执行写操作，将变更记录到 WAL 日志
- WAL 发送器将 WAL 日志发送到从服务器
- 从服务器的 WAL 接收器接收 WAL 日志并写入本地 WAL 日志
- 从服务器应用 WAL 日志中的变更，保持数据与主服务器一致
1.3.2 逻辑复制原理
- 主服务器执行写操作，将变更记录到 WAL 日志
- 逻辑解码插件将 WAL 日志解码为逻辑变更
- 发布者将逻辑变更发送到订阅者
- 订阅者应用逻辑变更，保持数据与主服务器一致
1.4 复制模式
- 异步复制：主服务器无需等待从服务器确认，性能更好，但可能会丢失数据
- 同步复制：主服务器等待从服务器确认后再提交事务，数据安全性更高，但性能可能会受到影响
- 半同步复制：主服务器等待至少一个从服务器确认后再提交事务，平衡了性能和数据安全性
2. 安装与配置
2.1 安装 PostgreSQL
2.1.1 在 Ubuntu/Debian 系统上安装
# 更新包列表
sudo apt update
# 安装 PostgreSQL
sudo apt install -y postgresql postgresql-contrib
# 启动 PostgreSQL 服务
sudo systemctl start postgresql
# 设置 PostgreSQL 服务开机自启
sudo systemctl enable postgresql2.1.2 在 CentOS/RHEL 系统上安装
# 安装 PostgreSQL 仓库
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 安装 PostgreSQL
sudo yum install -y postgresql14-server postgresql14-contrib
# 初始化数据库
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
# 启动 PostgreSQL 服务
sudo systemctl start postgresql-14
# 设置 PostgreSQL 服务开机自启
sudo systemctl enable postgresql-142.1.3 从源码编译安装
# 下载 PostgreSQL 源码
wget https://ftp.postgresql.org/pub/source/v14.5/postgresql-14.5.tar.gz
tar -xzf postgresql-14.5.tar.gz
cd postgresql-14.5
# 安装依赖
sudo apt install -y build-essential libreadline-dev zlib1g-dev libssl-dev
# 配置编译选项
./configure --prefix=/usr/local/pgsql --with-openssl
# 编译并安装
make
sudo make install
# 创建用户和数据目录
sudo useradd -m postgres
sudo mkdir -p /usr/local/pgsql/data
sudo chown -R postgres:postgres /usr/local/pgsql
# 切换到 postgres 用户并初始化数据库
sudo su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
# 启动 PostgreSQL 服务
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start
# 添加 PostgreSQL 到 PATH
echo 'export PATH=/usr/local/pgsql/bin:$PATH' >> ~/.bashrc
source ~/.bashrc2.2 配置物理复制
2.2.1 配置主服务器
修改 postgresql.conf 文件：
sudo nano /etc/postgresql/14/main/postgresql.conf添加或修改以下配置：
# 监听地址 listen_addresses = '*' # WAL 级别 wal_level = replica # 最大 WAL 发送进程数 max_wal_senders = 10 # 最大复制槽数量 max_replication_slots = 10 # wal_keep_size = 2GB # 同步复制配置（可选） synchronous_commit = on synchronous_standby_names = '*'修改 pg_hba.conf 文件，添加复制用户的访问权限：
sudo nano /etc/postgresql/14/main/pg_hba.conf添加以下配置：
# 允许复制用户从从服务器访问 host replication replica 192.168.1.0/24 md5创建复制用户：
sudo -u postgres psql -- 创建复制用户 CREATE USER replica WITH REPLICATION PASSWORD 'your_strong_password'; -- 退出 \q重启主服务器：
sudo systemctl restart postgresql
2.2.2 配置从服务器
停止从服务器：
sudo systemctl stop postgresql清空从服务器的数据目录：
sudo rm -rf /var/lib/postgresql/14/main/*使用 pg_basebackup 从主服务器复制数据：
sudo -u postgres pg_basebackup -h 主服务器IP -U replica -D /var/lib/postgresql/14/main -P -v -R参数说明：
-h：主服务器 IP 地址-U：复制用户-D：从服务器数据目录-P：显示进度-v：详细输出-R：自动生成 recovery.conf 文件
修改从服务器的 postgresql.conf 文件：
sudo nano /etc/postgresql/14/main/postgresql.conf添加或修改以下配置：
# 监听地址 listen_addresses = '*' # 最大 WAL 接收进程数 max_worker_processes = 10 # 热备模式 hot_standby = on # 允许从服务器接受连接 max_connections = 100修改从服务器的 recovery.conf 文件（PostgreSQL 12+ 版本为 postgresql.auto.conf）：
sudo nano /var/lib/postgresql/14/main/recovery.conf确保以下配置正确：
standby_mode = 'on' primary_conninfo = 'host=主服务器IP port=5432 user=replica password=your_strong_password' trigger_file = '/var/lib/postgresql/14/main/trigger'启动从服务器：
sudo systemctl start postgresql
2.2.3 验证复制状态
在主服务器上查看复制状态：
sudo -u postgres psql -- 查看复制槽状态 SELECT * FROM pg_replication_slots; -- 查看 WAL 发送器状态 SELECT * FROM pg_stat_replication; -- 退出 \q在从服务器上查看复制状态：
sudo -u postgres psql -- 查看从服务器状态 SELECT * FROM pg_stat_wal_receiver; -- 查看复制延迟 SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), pg_last_wal_receive_lsn()) AS lag_bytes; -- 退出 \q
2.3 配置逻辑复制
2.3.1 配置主服务器
修改 postgresql.conf 文件：
sudo nano /etc/postgresql/14/main/postgresql.conf添加或修改以下配置：
# WAL 级别 wal_level = logical # 最大 WAL 发送进程数 max_wal_senders = 10 # 最大复制槽数量 max_replication_slots = 10 # 最大逻辑复制工作进程数 max_worker_processes = 10 max_logical_replication_workers = 4重启主服务器：
sudo systemctl restart postgresql创建发布：
sudo -u postgres psql -- 创建发布 CREATE PUBLICATION my_publication FOR TABLE users, products; -- 或发布所有表 -- CREATE PUBLICATION my_publication FOR ALL TABLES; -- 退出 \q
2.3.2 配置从服务器
创建订阅：
sudo -u postgres psql -- 创建订阅 CREATE SUBSCRIPTION my_subscription CONNECTION 'host=主服务器IP port=5432 dbname=postgres user=replica password=your_strong_password' PUBLICATION my_publication; -- 退出 \q验证逻辑复制状态：
sudo -u postgres psql -- 查看订阅状态 SELECT * FROM pg_stat_subscription; -- 退出 \q
3. 基本使用
3.1 连接到 PostgreSQL
3.1.1 使用 psql 命令行工具
# 连接到主服务器
psql -h 主服务器IP -U postgres -d 数据库名
# 连接到从服务器
psql -h 从服务器IP -U postgres -d 数据库名3.1.2 使用 Node.js 客户端
const { Client } = require('pg');
// 连接到主服务器
const primaryClient = new Client({
host: '主服务器IP',
port: 5432,
user: 'postgres',
password: 'your_strong_password',
database: 'postgres'
});
// 连接到从服务器
const standbyClient = new Client({
host: '从服务器IP',
port: 5432,
user: 'postgres',
password: 'your_strong_password',
database: 'postgres'
});
// 连接到数据库
async function connect() {
try {
await primaryClient.connect();
await standbyClient.connect();
console.log('Connected to PostgreSQL servers');
} catch (err) {
console.error('Error connecting to PostgreSQL:', err);
}
}
connect();3.2 数据操作
3.2.1 写操作
写操作应该在主服务器上执行：
# 连接到主服务器
psql -h 主服务器IP -U postgres -d testdb
-- 创建表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE
);
-- 插入数据
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');
-- 更新数据
UPDATE users SET name = '李四' WHERE id = 1;
-- 删除数据
DELETE FROM users WHERE id = 1;
-- 退出
\q3.2.2 读操作
读操作可以在主服务器或从服务器上执行：
# 连接到从服务器
psql -h 从服务器IP -U postgres -d testdb
-- 查询数据
SELECT * FROM users;
-- 退出
\q3.3 管理复制
3.3.1 查看复制状态
# 连接到主服务器
psql -h 主服务器IP -U postgres
-- 查看 WAL 发送器状态
SELECT * FROM pg_stat_replication;
-- 查看复制槽状态
SELECT * FROM pg_replication_slots;
-- 退出
\q
# 连接到从服务器
psql -h 从服务器IP -U postgres
-- 查看 WAL 接收器状态
SELECT * FROM pg_stat_wal_receiver;
-- 查看复制延迟
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), pg_last_wal_receive_lsn()) AS lag_bytes;
-- 退出
\q3.3.2 执行故障转移
当主服务器发生故障时，需要将从服务器提升为新的主服务器：
在从服务器上创建触发文件：
sudo -u postgres touch /var/lib/postgresql/14/main/trigger验证从服务器是否已提升为主服务器：
psql -h 从服务器IP -U postgres -- 查看服务器状态 SELECT pg_is_in_recovery(); -- 退出 \q如果返回
false，表示从服务器已成功提升为主服务器。将其他从服务器重新指向新的主服务器：
# 修改 recovery.conf 文件 sudo nano /var/lib/postgresql/14/main/recovery.conf # 修改 primary_conninfo primary_conninfo = 'host=新主服务器IP port=5432 user=replica password=your_strong_password' # 重启从服务器 sudo systemctl restart postgresql
3.3.3 重新同步从服务器
当从服务器与主服务器数据不一致时，需要重新同步：
停止从服务器：
sudo systemctl stop postgresql清空从服务器的数据目录：
sudo rm -rf /var/lib/postgresql/14/main/*从主服务器复制数据：
sudo -u postgres pg_basebackup -h 主服务器IP -U replica -D /var/lib/postgresql/14/main -P -v -R启动从服务器：
sudo systemctl start postgresql
4. 高级功能
4.1 同步复制
同步复制可以确保数据的安全性，但会影响性能：
4.1.1 配置同步复制
修改主服务器的 postgresql.conf 文件：
sudo nano /etc/postgresql/14/main/postgresql.conf添加或修改以下配置：
# 同步复制 synchronous_commit = on # 同步从服务器名称 synchronous_standby_names = 'standby1,standby2'修改从服务器的 recovery.conf 文件，添加应用名称：
sudo nano /var/lib/postgresql/14/main/recovery.conf添加以下配置：
primary_conninfo = 'host=主服务器IP port=5432 user=replica password=your_strong_password application_name=standby1'重启主服务器和从服务器：
sudo systemctl restart postgresql
4.1.2 验证同步复制
# 连接到主服务器
psql -h 主服务器IP -U postgres
-- 查看复制状态
SELECT * FROM pg_stat_replication WHERE sync_state = 'sync';
-- 退出
\q4.2 逻辑复制
逻辑复制可以复制特定的表或数据库，更加灵活：
4.2.1 创建发布
# 连接到主服务器
psql -h 主服务器IP -U postgres
-- 创建发布
CREATE PUBLICATION my_publication FOR TABLE users, products;
-- 查看发布
SELECT * FROM pg_publication;
-- 查看发布的表
SELECT * FROM pg_publication_tables;
-- 退出
\q4.2.2 创建订阅
# 连接到从服务器
psql -h 从服务器IP -U postgres
-- 创建订阅
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=主服务器IP port=5432 dbname=postgres user=replica password=your_strong_password'
PUBLICATION my_publication;
-- 查看订阅
SELECT * FROM pg_subscription;
-- 查看订阅状态
SELECT * FROM pg_stat_subscription;
-- 退出
\q4.2.3 管理发布和订阅
# 连接到主服务器
psql -h 主服务器IP -U postgres
-- 添加表到发布
ALTER PUBLICATION my_publication ADD TABLE orders;
-- 从发布中移除表
ALTER PUBLICATION my_publication DROP TABLE products;
-- 删除发布
DROP PUBLICATION my_publication;
-- 退出
\q
# 连接到从服务器
psql -h 从服务器IP -U postgres
-- 刷新订阅
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;
-- 禁用订阅
ALTER SUBSCRIPTION my_subscription DISABLE;
-- 启用订阅
ALTER SUBSCRIPTION my_subscription ENABLE;
-- 删除订阅
DROP SUBSCRIPTION my_subscription;
-- 退出
\q4.3 级联复制
级联复制可以减轻主服务器的负担，适用于大型集群：
4.3.1 配置级联复制
配置主服务器：
sudo nano /etc/postgresql/14/main/postgresql.conf # 添加以下配置 wal_level = replica max_wal_senders = 10 max_replication_slots = 10配置第一级从服务器：
sudo nano /var/lib/postgresql/14/main/recovery.conf # 添加以下配置 standby_mode = 'on' primary_conninfo = 'host=主服务器IP port=5432 user=replica password=your_strong_password' primary_slot_name = 'slot1' hot_standby = on max_wal_senders = 10 max_replication_slots = 10配置第二级从服务器：
sudo nano /var/lib/postgresql/14/main/recovery.conf # 添加以下配置 standby_mode = 'on' primary_conninfo = 'host=第一级从服务器IP port=5432 user=replica password=your_strong_password' primary_slot_name = 'slot2' hot_standby = on在主服务器上创建复制槽：
psql -h 主服务器IP -U postgres -- 创建复制槽 SELECT * FROM pg_create_physical_replication_slot('slot1'); -- 退出 \q在第一级从服务器上创建复制槽：
psql -h 第一级从服务器IP -U postgres -- 创建复制槽 SELECT * FROM pg_create_physical_replication_slot('slot2'); -- 退出 \q
4.4 复制监控
4.4.1 使用 pg_stat_replication 视图
# 连接到主服务器
psql -h 主服务器IP -U postgres
-- 查看复制状态
SELECT
client_addr,
application_name,
state,
sync_state,
write_lag,
flush_lag,
replay_lag
FROM pg_stat_replication;
-- 退出
\q4.4.2 使用 pg_stat_wal_receiver 视图
# 连接到从服务器
psql -h 从服务器IP -U postgres
-- 查看 WAL 接收器状态
SELECT
status,
receive_start_lsn,
received_lsn,
last_msg_send_time,
last_msg_receipt_time,
latest_end_lsn,
latest_end_time
FROM pg_stat_wal_receiver;
-- 退出
\q4.4.3 使用 Prometheus 和 Grafana 监控
安装 PostgreSQL Exporter：
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.13.2/postgres_exporter-0.13.2.linux-amd64.tar.gz tar -xzf postgres_exporter-0.13.2.linux-amd64.tar.gz cd postgres_exporter-0.13.2.linux-amd64创建监控用户：
psql -h 主服务器IP -U postgres -- 创建监控用户 CREATE USER pg_exporter WITH PASSWORD 'your_strong_password'; GRANT CONNECT ON DATABASE postgres TO pg_exporter; GRANT SELECT ON pg_stat_database TO pg_exporter; GRANT SELECT ON pg_stat_replication TO pg_exporter; GRANT SELECT ON pg_stat_wal_receiver TO pg_exporter; -- 退出 \q创建 .pgpass 文件：
echo '*:*:*:pg_exporter:your_strong_password' > .pgpass chmod 600 .pgpass启动 PostgreSQL Exporter：
export DATA_SOURCE_NAME="postgresql://pg_exporter:your_strong_password@localhost:5432/postgres" ./postgres_exporter配置 Prometheus，添加 PostgreSQL Exporter 作为目标。
配置 Grafana，导入 PostgreSQL 监控面板。
4.5 备份与恢复
4.5.1 使用 pg_dump 备份
# 备份整个数据库
pg_dump -h 主服务器IP -U postgres -d testdb -F c -f testdb.backup
# 备份特定表
pg_dump -h 主服务器IP -U postgres -d testdb -t users -F c -f users.backup
# 备份 schema
pg_dump -h 主服务器IP -U postgres -d testdb -s -F c -f schema.backup4.5.2 使用 pg_restore 恢复
# 恢复整个数据库
pg_restore -h 主服务器IP -U postgres -d testdb -F c testdb.backup
# 恢复特定表
pg_restore -h 主服务器IP -U postgres -d testdb -t users -F c users.backup
# 恢复 schema
pg_restore -h 主服务器IP -U postgres -d testdb -s -F c schema.backup4.5.3 使用 WAL 归档进行时间点恢复
配置 WAL 归档：
sudo nano /etc/postgresql/14/main/postgresql.conf # 添加以下配置 wal_level = replica archive_mode = on archive_command = 'cp %p /path/to/archive/%f'创建归档目录：
sudo mkdir -p /path/to/archive sudo chown -R postgres:postgres /path/to/archive执行基础备份：
sudo -u postgres pg_basebackup -h 主服务器IP -U replica -D /path/to/backup -P -v执行时间点恢复：
# 修改 recovery.conf 文件 sudo nano /path/to/backup/recovery.conf # 添加以下配置 restore_command = 'cp /path/to/archive/%f %p' recovery_target_time = '2023-01-01 12:00:00' # 启动服务器 sudo -u postgres pg_ctl -D /path/to/backup start
5. 最佳实践
5.1 部署建议
- 服务器配置：主服务器和从服务器应使用相同的硬件配置，确保性能一致
- 网络配置：确保主服务器和从服务器之间网络畅通，网络延迟应尽可能低
- 存储配置：使用 SSD 存储提高性能，特别是对于写密集型应用
- 防火墙：确保开放必要的端口（默认 5432）
- 部署位置：将主服务器和从服务器部署在不同的物理机器或虚拟机上，避免单点故障
- 地理分布：对于关键应用，考虑跨数据中心部署，提高灾备能力
5.2 性能优化
- WAL 配置：根据实际需求调整
wal_level、max_wal_senders等参数 - 内存配置：为 PostgreSQL 分配足够的内存，建议将
shared_buffers设置为系统内存的 25% - 工作进程：根据 CPU 核心数调整
max_worker_processes参数 - 连接池：使用连接池管理客户端连接，避免频繁创建和销毁连接
- 索引优化：为常用查询创建合适的索引，避免全表扫描
- 分区表：对于大型表，使用分区表提高查询性能
- 读写分离：将读操作分发到从服务器，提高系统整体性能
5.3 安全配置
- 启用认证：启用 PostgreSQL 的认证机制，设置强密码
- 限制访问：通过
pg_hba.conf文件限制只接受特定 IP 的连接 - 使用 TLS：在生产环境中启用 TLS 加密
- 定期更新：及时更新 PostgreSQL 版本，修复安全漏洞
- 权限管理：根据最小权限原则，为不同用户分配适当的角色
- 审计日志：启用审计日志，记录重要操作
5.4 监控与维护
- 监控指标：监控服务器状态、复制延迟、内存使用、CPU 使用率、磁盘空间等指标
- 日志分析：定期分析 PostgreSQL 日志，及时发现问题
- 定期备份：制定合理的备份策略，确保数据安全
- 容量规划：根据数据增长趋势，提前规划存储容量
- 健康检查：定期执行
VACUUM和ANALYZE操作，维护数据库健康状态 - 索引维护：定期重建索引，提高查询性能
6. 故障排查
6.1 常见问题
- 复制延迟过高：检查网络连接，确认从服务器硬件配置是否足够，查看是否有大量写操作
- 复制中断：检查网络连接，查看主服务器和从服务器的日志文件，确认复制用户权限是否正确
- 从服务器无法启动：检查
recovery.conf文件配置是否正确，查看从服务器的日志文件 - 主服务器故障：执行故障转移，将从服务器提升为新的主服务器
- 数据不一致：重新同步从服务器，确保数据与主服务器一致
- 性能问题：检查索引使用情况，确认硬件配置是否足够，查看是否有慢查询
6.2 排查工具
- psql 命令行工具：用于执行管理命令和查看状态
- pgAdmin：图形化管理工具，提供可视化监控
- PostgreSQL Exporter + Prometheus + Grafana：用于监控 PostgreSQL 性能指标
- 日志文件：包含详细的操作和错误信息
- pg_stat_ 视图*：用于查看 PostgreSQL 的各种状态信息
6.3 示例排查过程
问题：复制延迟过高
排查步骤：
查看复制状态：
psql -h 主服务器IP -U postgres -- 查看复制状态 SELECT client_addr, application_name, state, sync_state, write_lag, flush_lag, replay_lag FROM pg_stat_replication; -- 退出 \q查看从服务器状态：
psql -h 从服务器IP -U postgres -- 查看 WAL 接收器状态 SELECT * FROM pg_stat_wal_receiver; -- 查看复制延迟 SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), pg_last_wal_receive_lsn()) AS lag_bytes; -- 退出 \q检查网络连接：
ping 主服务器IP traceroute 主服务器IP iperf3 -c 主服务器IP检查主服务器负载：
top iostat -x vmstat检查从服务器负载：
top iostat -x vmstat查看日志文件：
sudo tail -f /var/log/postgresql/postgresql-14-main.log解决方法：
- 增加从服务器硬件资源
- 优化主服务器写操作
- 检查网络连接，减少网络延迟
- 调整 PostgreSQL 配置参数，如
max_wal_senders、work_mem等
7. 实用案例
7.1 构建高可用 PostgreSQL 集群
场景：构建一个高可用的 PostgreSQL 集群，用于存储企业核心数据。
解决方案：
部署架构：
- 1 个主服务器
- 2 个从服务器
- 1 个负载均衡器（如 HAProxy）
- 节点分布在 3 台不同的物理机器上
配置步骤：
- 在每台机器上安装 PostgreSQL
- 配置主服务器
- 配置从服务器
- 配置负载均衡器
- 配置自动故障转移
配置负载均衡器（HAProxy）：
sudo nano /etc/haproxy/haproxy.cfg添加以下配置：
frontend postgresql bind *:5432 mode tcp option tcplog default_backend postgresql_servers backend postgresql_servers mode tcp balance roundrobin option tcp-check tcp-check expect string PostgreSQL server primary 主服务器IP:5432 check server standby1 从服务器1IP:5432 check server standby2 从服务器2IP:5432 check配置自动故障转移（使用 Patroni）：
# 安装 Patroni pip install patroni psycopg2-binary # 创建 Patroni 配置文件 sudo nano /etc/patroni.yml添加以下配置：
scope: postgres namespace: /db/ name: postgresql0 restapi: listen: 0.0.0.0:8008 connect_address: 主服务器IP:8008 etcd: host: etcd服务器IP:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 postgresql: use_pg_rewind: true initdb: - encoding: UTF8 - data-checksums pg_hba: - host replication replicator 127.0.0.1/32 md5 - host replication replicator 主服务器IP/32 md5 - host replication replicator 从服务器1IP/32 md5 - host replication replicator 从服务器2IP/32 md5 - host all all 0.0.0.0/0 md5 postgresql: listen: 0.0.0.0:5432 connect_address: 主服务器IP:5432 data_dir: /var/lib/postgresql/14/main pgpass: /tmp/pgpass authentication: replication: username: replicator password: your_strong_password superuser: username: postgres password: your_strong_password parameters: unix_socket_directories: '/var/run/postgresql' tags: nofailover: false noloadbalance: false clonefrom: false nosync: false启动 Patroni：
sudo -u postgres patroni /etc/patroni.yml
7.2 使用 PostgreSQL 复制实现读写分离
场景：使用 PostgreSQL 复制实现读写分离，提高系统整体性能。
解决方案：
部署架构：
- 1 个主服务器
- 2 个从服务器
- 1 个负载均衡器（如 HAProxy）
配置负载均衡器（HAProxy）：
sudo nano /etc/haproxy/haproxy.cfg添加以下配置：
# 写操作前端 frontend postgresql_write bind *:5432 mode tcp option tcplog default_backend postgresql_primary # 读操作前端 frontend postgresql_read bind *:5433 mode tcp option tcplog default_backend postgresql_standbys # 主服务器后端 backend postgresql_primary mode tcp balance roundrobin option tcp-check tcp-check expect string PostgreSQL server primary 主服务器IP:5432 check # 从服务器后端 backend postgresql_standbys mode tcp balance roundrobin option tcp-check tcp-check expect string PostgreSQL server standby1 从服务器1IP:5432 check server standby2 从服务器2IP:5432 check客户端配置：
const { Client } = require('pg'); // 写操作客户端 const writeClient = new Client({ host: '负载均衡器IP', port: 5432, // 写操作端口 user: 'postgres', password: 'your_strong_password', database: 'testdb' }); // 读操作客户端 const readClient = new Client({ host: '负载均衡器IP', port: 5433, // 读操作端口 user: 'postgres', password: 'your_strong_password', database: 'testdb' }); // 连接到数据库 async function connect() { try { await writeClient.connect(); await readClient.connect(); console.log('Connected to PostgreSQL servers'); } catch (err) { console.error('Error connecting to PostgreSQL:', err); } } // 写操作 async function writeOperation() { try { const result = await writeClient.query( 'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id', ['张三', 'zhangsan@example.com'] ); console.log('Write operation successful:', result.rows[0].id); return result.rows[0].id; } catch (err) { console.error('Error in write operation:', err); throw err; } } // 读操作 async function readOperation(userId) { try { const result = await readClient.query( 'SELECT * FROM users WHERE id = $1', [userId] ); console.log('Read operation successful:', result.rows[0]); return result.rows[0]; } catch (err) { console.error('Error in read operation:', err); throw err; } } // 使用示例 async function main() { await connect(); // 执行写操作 const userId = await writeOperation(); // 执行读操作 const user = await readOperation(userId); // 关闭连接 await writeClient.end(); await readClient.end(); } main();
7.3 使用 PostgreSQL 逻辑复制实现数据同步
场景：使用 PostgreSQL 逻辑复制实现不同数据库之间的数据同步。
解决方案：
部署架构：
- 源数据库（主服务器）
- 目标数据库（从服务器）
配置源数据库：
sudo nano /etc/postgresql/14/main/postgresql.conf # 添加以下配置 wal_level = logical max_wal_senders = 10 max_replication_slots = 10 max_worker_processes = 10 max_logical_replication_workers = 4 # 重启源数据库 sudo systemctl restart postgresql # 创建发布 psql -h 源数据库IP -U postgres -- 创建发布 CREATE PUBLICATION my_publication FOR TABLE users, products; -- 退出 \q配置目标数据库：
# 创建目标表 psql -h 目标数据库IP -U postgres -- 创建表 CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) UNIQUE ); CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100), price DECIMAL(10, 2) ); -- 创建订阅 CREATE SUBSCRIPTION my_subscription CONNECTION 'host=源数据库IP port=5432 dbname=postgres user=replica password=your_strong_password' PUBLICATION my_publication; -- 查看订阅状态 SELECT * FROM pg_stat_subscription; -- 退出 \q验证数据同步：
# 在源数据库中插入数据 psql -h 源数据库IP -U postgres -- 插入数据 INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com'); INSERT INTO products (name, price) VALUES ('产品 1', 100.00); -- 退出 \q # 在目标数据库中查看数据 psql -h 目标数据库IP -U postgres -- 查看数据 SELECT * FROM users; SELECT * FROM products; -- 退出 \q
8. 总结
PostgreSQL 复制是构建高可用 PostgreSQL 集群的关键技术，通过物理复制或逻辑复制，可以提高系统的可用性、可靠性和扩展性。本教程详细介绍了 PostgreSQL 复制的核心概念、部署配置、使用方法和最佳实践，帮助开发者快速掌握 PostgreSQL 复制的使用技巧，构建稳定可靠的 PostgreSQL 高可用集群。
在实际应用中，应根据具体需求和场景，选择合适的复制方式，合理配置 PostgreSQL 参数，确保系统的稳定性和性能。同时，定期进行集群维护和监控，及时发现和解决问题，确保 PostgreSQL 集群持续稳定运行。