PostgreSQL 17 主从部署、配置优化及备份脚本最佳实践

以下是关于 PostgreSQL 17 主从部署、配置优化及备份脚本的最佳实践：

主从部署

1. 环境准备

• 操作系统：确保主从节点操作系统版本一致，例如都使用 CentOS 7 或 Ubuntu 20.04。
• PostgreSQL 版本：主从节点都安装 PostgreSQL 17。可以通过官方仓库或源码进行安装。
• 网络连通性：主从节点之间网络可达，并且可以开放必要的端口（默认 5432）。

2. 主节点配置

• 编辑postgresql.conf

# 允许远程连接
listen_addresses = '*'
# 增加最大连接数
max_connections = 200
# 开启wal日志归档
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 32

• 编辑pg_hba.conf

# 允许从节点以复制用户身份连接
host    replication     replica_user    <从节点IP>/32        md5

• 重启主节点服务

 

sudo systemctl restart postgresql-17

• 创建复制用户

CREATE ROLE replica_user WITH REPLICATION LOGIN PASSWORD 'your_password';

3. 从节点配置

• 初始化数据目录

sudo -u postgres initdb -D /var/lib/pgsql/17/data

• 使用pg_basebackup复制主节点数据

sudo -u postgres pg_basebackup -h <主节点IP> -U replica_user -D /var/lib/pgsql/17/data -P -R

• 编辑postgresql.conf

# 只读模式
hot_standby = on

• 启动从节点服务

sudo systemctl start postgresql-17

配置优化

1. 内存相关参数

• shared_buffers：建议设置为系统内存的 25% - 50%。例如，如果系统有 8GB 内存，可以设置为：

shared_buffers = '2GB'

• work_mem：根据查询复杂度和系统内存情况调整，一般设置为几十 MB 到几百 MB。

work_mem = '64MB'

• maintenance_work_mem：用于维护操作的内存，如VACUUM和CREATE INDEX，可设置为几百 MB 到几 GB。

maintenance_work_mem = '512MB'

2. 并行查询参数

• max_parallel_workers：根据 CPU 核心数设置，一般为核心数的一半左右。

 

max_parallel_workers = 8

• max_parallel_workers_per_gather：每个查询可使用的最大并行工作进程数。

max_parallel_workers_per_gather = 4

3. 日志相关参数

• wal_buffers：设置为shared_buffers的 1/32 到 1/8。

wal_buffers = '64MB'

• synchronous_commit：如果对数据一致性要求不是特别高，可以设置为off或local以提高性能。

synchronous_commit = off

备份脚本

1. 全量备份脚本

#!/bin/bash

# 备份目录
BACKUP_DIR="/var/backups/postgresql"
# 数据库用户名
DB_USER="postgres"
# 数据库名称
DB_NAME="your_database"
# 日期格式
DATE=$(date +%Y%m%d%H%M%S)

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行全量备份
pg_dump -U $DB_USER -d $DB_NAME -F c -f $BACKUP_DIR/full_backup_$DATE.dump

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "全量备份成功：$BACKUP_DIR/full_backup_$DATE.dump"
else
    echo "全量备份失败"
fi

# 删除7天前的备份
find $BACKUP_DIR -name "full_backup_*.dump" -mtime +7 -delete

将上述脚本保存为full_backup.sh，并赋予执行权限：

chmod +x full_backup.sh

可以使用cron定时执行备份脚本，例如每天凌晨 2 点执行：

 

 

0 2 * * * /path/to/full_backup.sh

2. 增量备份脚本（基于 WAL 归档）

#!/bin/bash

# 备份目录
BACKUP_DIR="/var/backups/postgresql/wal_backups"
# WAL归档目录
ARCHIVE_DIR="/var/lib/pgsql/17/archive"
# 日期格式
DATE=$(date +%Y%m%d%H%M%S)

# 创建备份目录
mkdir -p $BACKUP_DIR

# 复制WAL文件到备份目录
cp $ARCHIVE_DIR/* $BACKUP_DIR/

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "增量备份成功：$BACKUP_DIR"
else
    echo "增量备份失败"
fi

# 删除7天前的WAL备份
find $BACKUP_DIR -name "*.wal" -mtime +7 -delete

将上述脚本保存为incremental_backup.sh，并赋予执行权限：

 

chmod +x incremental_backup.sh

可以根据需要设置cron定时任务，例如每小时执行一次：

 

 

0 * * * * /path/to/incremental_backup.sh

通过以上步骤，你可以完成 PostgreSQL 17 的主从部署、配置优化，并实现定期的全量和增量备份。