PostgreSQL 基础主从配置

核心概念

1. 主从复制架构

PostgreSQL主从复制是一种高可用性架构，包含一个主库和一个或多个从库：

主库（Master）：处理所有写操作和部分读操作，生成WAL日志
从库（Slave）：从主库接收WAL日志并应用，提供只读服务
WAL日志：预写式日志，记录所有数据库修改操作
流复制：从库通过TCP连接实时接收主库的WAL日志

2. 主从复制的作用

高可用性：主库故障时可切换到从库，减少 downtime
读写分离：从库处理只读查询，分担主库压力
数据备份：从库可作为数据备份，用于恢复
负载均衡：分发读请求到多个从库，提高系统整体性能

3. 主从复制的类型

PostgreSQL支持多种主从复制类型：

物理复制：直接复制数据库物理文件，从库与主库完全一致
逻辑复制：基于逻辑变更复制，支持跨版本复制和选择性复制
异步复制：主库无需等待从库确认，性能高但可能丢失数据
同步复制：主库需等待从库确认，数据安全但性能较低

主从配置准备工作

1. 环境准备

角色	IP地址	操作系统	PostgreSQL版本	数据目录
主库	192.168.1.100	Ubuntu 22.04	15	/var/lib/postgresql/15/main
从库	192.168.1.101	Ubuntu 22.04	15	/var/lib/postgresql/15/main

2. 网络配置

确保主从库之间网络畅通，可通过ping命令验证：

bash

# 在主库上测试连接从库
ping 192.168.1.101

# 在从库上测试连接主库
ping 192.168.1.100

3. 安装PostgreSQL

在主从库上安装相同版本的PostgreSQL：

bash

# 更新包列表
sudo apt update

# 安装PostgreSQL 15
sudo apt install -y postgresql-15 postgresql-contrib-15

# 启动PostgreSQL服务
sudo systemctl start postgresql

# 设置开机自启
sudo systemctl enable postgresql

主库配置

1. 修改主库配置文件

bash

# 编辑主库postgresql.conf文件
sudo nano /etc/postgresql/15/main/postgresql.conf

添加或修改以下配置：

txt

# 启用归档和流复制
wal_level = replica          # 至少为replica级别
archive_mode = on            # 启用归档模式
archive_command = 'cp %p /pg_backups/wal/%f'  # 归档命令

# 配置WAL发送进程
max_wal_senders = 10         # 最大WAL发送进程数
wal_keep_size = 2GB          # 保留的WAL大小，防止从库落后太多
max_replication_slots = 10   # 最大复制槽数量

# 配置检查点（可选，优化性能）
checkpoint_timeout = 30min   # 检查点超时时间
max_wal_size = 4GB           # 最大WAL大小
checkpoint_completion_target = 0.9  # 检查点完成目标比例

2. 创建归档目录

bash

# 创建WAL归档目录
sudo mkdir -p /pg_backups/wal

# 设置目录权限
sudo chown postgres:postgres /pg_backups/wal
sudo chmod 700 /pg_backups/wal

3. 修改pg_hba.conf

bash

# 编辑pg_hba.conf文件
sudo nano /etc/postgresql/15/main/pg_hba.conf

添加从库的复制连接权限：

txt

# 允许从库的复制连接
host replication replication_user 192.168.1.101/32 md5

4. 创建复制用户

bash

# 切换到postgres用户
sudo -u postgres psql

sql

-- 创建复制用户
CREATE USER replication_user REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD 'replication_pass';

-- 退出psql\q

5. 重启主库服务

bash

sudo systemctl restart postgresql

从库配置

1. 停止从库服务

bash

sudo systemctl stop postgresql

2. 清理从库数据目录

bash

# 清理从库数据目录
sudo rm -rf /var/lib/postgresql/15/main/*

3. 从主库创建基础备份

使用pg_basebackup工具从主库创建基础备份：

bash

# 切换到postgres用户
sudo -u postgres pg_basebackup -h 192.168.1.100 -U replication_user -D /var/lib/postgresql/15/main -F p -X stream -R

参数说明：

-h：主库IP地址
-U：复制用户
-D：目标数据目录
-F p：plain格式，直接写入数据目录
-X stream：流式复制WAL日志
-R：自动生成recovery.signal和primary_conninfo文件

4. 修改从库配置文件

bash

# 编辑从库postgresql.conf文件
sudo nano /etc/postgresql/15/main/postgresql.conf

添加或修改以下配置：

txt

# 从库配置
hot_standby = on            # 启用热备模式，允许从库处理只读查询
max_standby_streaming_delay = 30s  # 最大备用流延迟
hot_standby_feedback = on   # 从库向主库反馈长查询信息，防止VACUUM删除未使用的行

# 优化从库性能（可选）
max_worker_processes = 8     # 最大工作进程数
max_parallel_workers_per_gather = 4  # 每个查询的最大并行工作进程数

5. 启动从库服务

bash

sudo systemctl start postgresql

主从复制验证

1. 检查主库复制状态

bash

# 切换到postgres用户
sudo -u postgres psql

sql

-- 查看WAL发送进程状态
SELECT * FROM pg_stat_replication;

-- 查看复制槽状态（如果使用了复制槽）
SELECT * FROM pg_replication_slots;

-- 退出psql\q

2. 检查从库复制状态

bash

# 切换到postgres用户
sudo -u postgres psql

sql

-- 检查是否处于恢复模式
SELECT pg_is_in_recovery();

-- 查看WAL接收进程状态
SELECT * FROM pg_stat_wal_receiver;

-- 查看复制延迟
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

-- 退出psql\q

3. 测试数据同步

在主库上创建测试数据，验证从库是否同步：

bash

# 在主库上操作
sudo -u postgres psql -c "CREATE DATABASE test_db;"
sudo -u postgres psql -d test_db -c "CREATE TABLE test_table (id serial PRIMARY KEY, name varchar(50));"
sudo -u postgres psql -d test_db -c "INSERT INTO test_table (name) VALUES ('test1'), ('test2'), ('test3');"

在从库上验证数据是否同步：

bash

# 在从库上操作
sudo -u postgres psql -c "\l"  # 查看是否有test_db数据库
sudo -u postgres psql -d test_db -c "SELECT * FROM test_table;"  # 查看test_table数据

主从复制监控

1. 实时监控复制状态

sql

-- 在主库上监控
SELECT 
    application_name,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    (sent_lsn - replay_lsn) AS replay_lag
FROM pg_stat_replication;

-- 在从库上监控
SELECT 
    pg_is_in_recovery(),
    pg_last_wal_receive_lsn(),
    pg_last_wal_replay_lsn(),
    now() - pg_last_xact_replay_timestamp() AS replication_delay;

2. 日志监控

bash

# 查看主库日志
sudo tail -f /var/log/postgresql/postgresql-15-main.log

# 查看从库日志
sudo tail -f /var/log/postgresql/postgresql-15-main.log

3. 使用pg_stat_replication视图

pg_stat_replication视图提供了详细的复制状态信息：

application_name：从库的应用名称
state：复制状态（streaming, startup等）
sync_state：同步状态（async, sync, potential等）
sent_lsn：主库已发送的WAL位置
write_lsn：从库已写入的WAL位置
flush_lsn：从库已刷新到磁盘的WAL位置
replay_lsn：从库已应用的WAL位置

主从复制管理

1. 查看复制延迟

sql

-- 在从库上查看复制延迟
SELECT 
    now() - pg_last_xact_replay_timestamp() AS replication_delay;

-- 在主库上查看复制延迟
SELECT 
    application_name,
    (sent_lsn - replay_lsn) / 1024 / 1024 AS replay_lag_mb
FROM pg_stat_replication;

2. 重启复制

如果复制中断，可以重启从库服务恢复：

bash

sudo systemctl restart postgresql

3. 添加新的从库

按照从库配置步骤，从主库创建基础备份并配置即可添加新的从库。

4. 主从切换

当主库故障时，需要将从库提升为主库：

bash

# 在从库上操作
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/15/main

最佳实践

1. 配置建议

主库配置：
- 设置合适的wal_keep_size，防止从库落后太多
- 启用复制槽，确保从库不会丢失WAL日志
- 定期备份主库，防止数据丢失
从库配置：
- 启用hot_standby，充分利用从库资源
- 设置合理的max_standby_streaming_delay，平衡性能和一致性
- 启用hot_standby_feedback，防止主库VACUUM删除未使用的行

2. 监控建议

定期检查复制状态，设置复制延迟告警阈值
监控WAL日志生成和应用速率
监控主从库的磁盘空间，特别是WAL归档目录
监控主从库的系统资源使用情况

3. 运维建议

定期测试故障切换：确保主库故障时能顺利切换到从库
文档化配置：详细记录主从配置和管理流程
版本一致性：主从库使用相同的PostgreSQL版本
网络隔离：主从库之间使用专用网络连接
安全配置：限制复制用户的访问权限，使用强密码

常见问题（FAQ）

Q1：主从复制延迟过高怎么办？

A1：可以从以下几个方面排查：

检查网络带宽和延迟
检查主库负载情况，特别是WAL生成速率
检查从库性能，特别是WAL应用速率
调整从库的max_worker_processes和max_parallel_workers_per_gather参数
考虑使用级联复制，减少主库的复制压力

Q2：从库无法连接到主库怎么办？

A2：检查以下几点：

主库是否已启动并正常运行
主库的pg_hba.conf是否允许从库连接
复制用户的密码是否正确
主库的max_wal_senders参数是否足够
网络连接是否正常，防火墙是否开放5432端口

Q3：如何增加从库的数量？

A3：可以按照以下步骤增加从库：

停止新从库的PostgreSQL服务
清理新从库的数据目录
使用pg_basebackup从主库创建基础备份
配置新从库的postgresql.conf
启动新从库的PostgreSQL服务
验证复制状态

Q4：主库故障后如何切换到从库？

A4：可以按照以下步骤切换：

确认主库无法恢复
在从库上执行pg_ctl promote命令提升为主库
更新应用的数据库连接字符串，指向新主库
重新配置其他从库连接到新主库
修复原主库后，作为从库重新加入集群

Q5：如何监控主从复制的健康状态？

A5：可以使用以下方法：

定期查询pg_stat_replication和pg_stat_wal_receiver视图
使用Prometheus + Grafana监控复制状态
配置Zabbix或Nagios监控复制延迟
定期检查数据库日志中的复制相关信息

Q6：主从复制是否支持跨版本？

A6：物理复制通常要求主从库版本相同，或从库版本高于主库（支持小版本升级）。逻辑复制支持跨版本复制，但需要注意版本兼容性。

Q7：如何配置多个从库？

A7：可以按照从库配置步骤，为每个从库创建基础备份并配置。在主库的pg_hba.conf中添加每个从库的复制权限，主库的max_wal_senders参数需要设置足够大，以支持所有从库的连接。

Q8：从库可以处理写操作吗？

A8：默认情况下，从库处于只读模式，无法处理写操作。如果需要从库处理写操作，可以考虑使用逻辑复制或多主复制架构，但需要注意数据一致性和冲突处理。

主从复制的应用场景

高可用性系统：确保数据库服务持续可用
读写分离架构：提高系统整体性能
数据备份与恢复：从库作为数据备份，用于灾难恢复
数据分析场景：从库用于数据仓库和数据分析
地理分布式架构：在不同地理位置部署从库，提供本地访问

通过合理配置和管理PostgreSQL主从复制，可以提高系统的可用性、性能和可靠性，满足不同业务场景的需求。

PostgreSQL 基础主从配置 ​

核心概念 ​

1. 主从复制架构 ​

2. 主从复制的作用 ​

3. 主从复制的类型 ​

主从配置准备工作 ​

1. 环境准备 ​

2. 网络配置 ​

3. 安装PostgreSQL ​

主库配置 ​

1. 修改主库配置文件 ​

2. 创建归档目录 ​

3. 修改pg_hba.conf ​

4. 创建复制用户 ​

5. 重启主库服务 ​

从库配置 ​

1. 停止从库服务 ​

2. 清理从库数据目录 ​

3. 从主库创建基础备份 ​

4. 修改从库配置文件 ​

5. 启动从库服务 ​

主从复制验证 ​

1. 检查主库复制状态 ​

2. 检查从库复制状态 ​

3. 测试数据同步 ​

主从复制监控 ​

1. 实时监控复制状态 ​

2. 日志监控 ​

3. 使用pg_stat_replication视图 ​

主从复制管理 ​

1. 查看复制延迟 ​

2. 重启复制 ​

3. 添加新的从库 ​

4. 主从切换 ​

最佳实践 ​

1. 配置建议 ​

2. 监控建议 ​

3. 运维建议 ​

常见问题（FAQ） ​

Q1：主从复制延迟过高怎么办？ ​

Q2：从库无法连接到主库怎么办？ ​

Q3：如何增加从库的数量？ ​

Q4：主库故障后如何切换到从库？ ​

Q5：如何监控主从复制的健康状态？ ​

Q6：主从复制是否支持跨版本？ ​

Q7：如何配置多个从库？ ​

Q8：从库可以处理写操作吗？ ​

主从复制的应用场景 ​

PostgreSQL 基础主从配置

核心概念

1. 主从复制架构

2. 主从复制的作用

3. 主从复制的类型

主从配置准备工作

1. 环境准备

2. 网络配置

3. 安装PostgreSQL

主库配置

1. 修改主库配置文件

2. 创建归档目录

3. 修改pg_hba.conf

4. 创建复制用户

5. 重启主库服务

从库配置

1. 停止从库服务

2. 清理从库数据目录

3. 从主库创建基础备份

4. 修改从库配置文件

5. 启动从库服务

主从复制验证

1. 检查主库复制状态

2. 检查从库复制状态

3. 测试数据同步

主从复制监控

1. 实时监控复制状态

2. 日志监控

3. 使用pg_stat_replication视图

主从复制管理

1. 查看复制延迟

2. 重启复制

3. 添加新的从库

4. 主从切换

最佳实践

1. 配置建议

2. 监控建议

3. 运维建议

常见问题（FAQ）

Q1：主从复制延迟过高怎么办？

Q2：从库无法连接到主库怎么办？

Q3：如何增加从库的数量？

Q4：主库故障后如何切换到从库？

Q5：如何监控主从复制的健康状态？

Q6：主从复制是否支持跨版本？

Q7：如何配置多个从库？

Q8：从库可以处理写操作吗？

主从复制的应用场景