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

合集 - 实践项目(5)

1.实践项目-基于K8s平台进行wordpress建站2024-05-312.实践项目-Ansible+Playbook自动化部署服务器上线2024-12-233.实践项目-Web服务器架构调整2024-12-314.实践项目-数据库主从高可用（MySQL-MHA、ProxySQL、mysqldump）01-03

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

收起

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