实践项目-数据库主从高可用（PostgreSQL、Pgpool2、pg_dumpall）

标签：dumpall postgresql Pgpool2 192.168 更新 PostgreSQL backup pg

(250103)

实践目标

备份策略：定期全量备份和增量备份，备份文件异地存储。
恢复测试：定期在测试库上恢复备份，确保备份文件可用。
权限管理：严格控制数据库访问权限，避免误操作。
变更管理：所有数据库变更需经过审批，并在非高峰时段执行。
监控告警：实时监控数据库状态，设置告警机制，及时发现并处理问题。
同步延迟：监控主从同步延迟，确保数据一致性。
备份验证：定期验证备份文件的完整性和可恢复性。
安全防护：加强数据库安全防护，防止数据泄露和攻击。

环境

操作系统：Debian 12.8
节点规划：
主库：192.168.100.1
从库1：192.168.100.2
从库2：192.168.100.3
MHA 管理节点：192.168.100.4
读写分离中间件：192.168.100.5
备份服务器：192.168.100.6
测试库：192.168.100.7
(默认进行过ssh-copy-id)

PostgreSQL编译安装

（所有节点）

# 安装编译 PostgreSQL 所需的依赖
sudo apt-get update
sudo apt-get install -y build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev

# 下载 PostgreSQL 源码包
wget https://ftp.postgresql.org/pub/source/v17.2/postgresql-17.2.tar.gz
tar -zxvf postgresql-17.2.tar.gz
cd postgresql-17.2

# 编译安装 PostgreSQL
./configure --prefix=/usr/local/postgresql --with-openssl
make
sudo make install

# 创建 PostgreSQL 用户和目录
sudo adduser postgres
sudo mkdir -p /usr/local/postgresql/data
sudo chown postgres:postgres /usr/local/postgresql/data

（主库）
初始化数据库

sudo -u postgres /usr/local/postgresql/bin/initdb -D /usr/local/postgresql/data

启动PostgreSQL

sudo -u postgres /usr/local/postgresql/bin/pg_ctl -D /usr/local/postgresql/data -l logfile start

主从搭建

配置从库
在从库1（192.168.100.2）和从库2（192.168.100.3）上，停止 PostgreSQL 服务并清空数据目录：

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/15/main/*

使用pg_basebackup从主库同步数据：

pg_basebackup -h 192.168.100.1 -U replica -D /var/lib/postgresql/15/main -P -R

编辑配置文件/etc/postgresql/15/main/postgresql.conf确保从库处于只读模式：

hot_standby = on

启动 PostgreSQL 服务：

sudo systemctl start postgresql

检查复制状态

在主库上检查复制状态：

SELECT * FROM pg_stat_replication;

在从库上检查复制状态：

SELECT * FROM pg_stat_wal_receiver;

读写分离

编译安装 Pgpool2
在读写分离中间件节点（192.168.100.5）上编译安装 Pgpool2。

# 安装依赖
sudo apt-get install -y libpq-dev libssl-dev

#下载 Pgpool-II 源码包：
wget https://www.pgpool.net/download.php?f=pgpool-II-4.5.2.tar.gz
tar -xvf pgpool-II-4.5.2.tar.gz
cd pgpool-II-4.5.2

# 编译并安装 Pgpool2
./configure --prefix=/usr/local/pgpool
make
sudo make install

配置 Pgpool-II
/usr/local/pgpool/etc/pgpool.conf

listen_addresses = '*'
backend_hostname0 = '192.168.100.1'
backend_port0 = 5432
backend_weight0 = 0
backend_hostname1 = '192.168.100.2'
backend_port1 = 5432
backend_weight1 = 1
backend_hostname2 = '192.168.100.3'
backend_port2 = 5432
backend_weight2 = 1

启动 Pgpool-II 服务

/usr/local/pgpool/bin/pgpool -n -D -d > /var/log/pgpool.log 2>&1 &

测试读写分离

通过 Pgpool-II 连接 PostgreSQL：

psql -h 192.168.100.5 -U postgres

执行以下 SQL 语句，验证读写分离是否生效
写操作（应路由到主库）：

INSERT INTO test_table (name) VALUES ('test');

读操作（应路由到从库）：

SELECT * FROM test_table;

备份与恢复方案

全量备份
使用pg_dumpall进行全量备份：

pg_dumpall -U postgres -f /backup/postgresql/full_backup_$(date +%F).sql

增量备份
通过 WAL 日志实现增量备份。
/etc/postgresql/15/main/postgresql.conf

archive_mode = on
archive_command = 'cp %p /backup/postgresql/wal/%f'

恢复备份

全量恢复

psql -U postgres -f /backup/postgresql/full_backup_{{date}}.sql

增量恢复
将 WAL 日志文件复制到 pg_wal 目录，PostgreSQL 会自动应用这些日志。

将备份数据定期恢复到测试库

恢复流程
将全量备份文件恢复到测试库：

psql -U postgres -f /backup/postgresql/full_backup_{{date}}.sql

将增量备份文件（WAL 日志）恢复到测试库：

cp /backup/postgresql/wal/* /var/lib/postgresql/15/main/pg_wal/

自动化恢复脚本
/usr/local/bin/pg_restore_test.sh：

#!/bin/bash
# PostgreSQL 测试库恢复脚本


# 定义变量
BACKUP_DIR="/backup/postgresql"
TEST_DB_HOST="192.168.100.7"
TEST_DB_USER="postgres"
LOG_FILE="/var/log/pg_restore_test.log"

# 记录日志
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - \$1" >> $LOG_FILE
}

# 恢复全量备份
log "Starting PostgreSQL full backup restore..."
psql -h $TEST_DB_HOST -U $TEST_DB_USER -f $BACKUP_DIR/full_backup_$(date +%F).sql

# 检查恢复结果
if [ $? -eq 0 ]; then
    log "PostgreSQL full backup restore completed successfully."
else
    log "PostgreSQL full backup restore failed."
    exit 1
fi

# 恢复增量备份
log "Starting PostgreSQL incremental backup restore..."
cp $BACKUP_DIR/wal/* /var/lib/postgresql/15/main/pg_wal/

# 检查恢复结果
if [ $? -eq 0 ]; then
    log "PostgreSQL incremental backup restore completed successfully."
else
    log "PostgreSQL incremental backup restore failed."
    exit 1
fi

log "PostgreSQL test database restore process completed."

设置定时任务，每天凌晨 3 点执行：

chmod +x /usr/local/bin/pg_restore_test.sh

corntab -e
0 3 * * * /usr/local/bin/pg_restore_test.sh

人工更新数据库的流程及制度

更新流程

更新申请
申请人：开发人员或运维人员。
申请内容：包括更新的 SQL 语句、更新原因、更新时间等。
申请方式：通过工单系统或邮件提交申请。

更新审批
审批人：数据库管理员（DBA）或相关负责人。
审批内容：审核 SQL 语句的正确性、更新对系统的影响等。
审批方式：通过工单系统或邮件进行审批。

更新执行
执行人：数据库管理员（DBA）或授权人员。
执行步骤：
备份数据：在执行更新前，先对相关表或数据库进行备份。
执行更新：在非高峰时段执行更新操作。
验证更新：检查更新结果，确保数据一致性和业务正常运行。
记录日志：记录更新操作的详细信息，包括执行时间、执行人、更新内容等。
更新回滚
回滚条件：如果更新导致数据不一致或业务异常，立即执行回滚操作。

回滚步骤：

停止更新：立即停止所有相关操作。
恢复数据：使用备份文件恢复数据。
验证恢复：检查数据恢复结果，确保业务正常运行。
记录日志：记录回滚操作的详细信息，包括回滚时间、回滚人、回滚内容等。

更新制度

更新权限
权限分配：只有经过授权的数据库管理员（DBA）或相关人员才能执行更新操作。
权限管理：定期审查和更新权限分配，确保权限最小化。
更新时间
非高峰时段：更新操作应在非高峰时段进行，避免影响业务正常运行。
紧急更新：对于紧急更新，需经过特别审批，并在执行前通知相关人员。
更新记录
记录内容：每次更新操作都需详细记录，包括更新内容、执行时间、执行人、审批人等。
记录保存：更新记录应长期保存，便于后续审计和查询。
更新培训
培训内容：定期对相关人员进行数据库更新操作的培训，确保操作规范和安全。
培训记录：记录培训内容和参与人员，确保培训效果。

标签：dumpall,postgresql,Pgpool2,192.168,更新,PostgreSQL,backup,pg
From： https://www.cnblogs.com/mugetsukun/p/18649912