postgres15 主备 pgsql主备

pg数据库主从配置实践

 

一、前期准备

 

1.1主库准备：

1.1.1 结构备份

生成结构脚本，通过navicat客户端进行

1.1.2 重要表数据备份

重要表数据导出sql文件，通过navicat客户端进行

 

1.2 从库准备：

1.2.1 准备1个干净的pg库

版本和主库保持一致

或者把已有库删除重建。

因之前已安装有pg，此次进行重新安装，步骤如下：

#删除旧库
yum erase postgresql-server
# 删除旧库的数据文件
rm /var/lib/pgsql/10 -rf
# 重新安装pg数据库
yum install postgresql10-server
#初始化和启动服务
service postgresql-10 initdb
service postgresql-10 start
# 新建数据库，并配置访问权限
su postgres
psql
CREATE DATABASE skynet OWNER postgres;
GRANT ALL PRIVILEGES ON DATABASE skynet TO postgres;
\q

 

二、执行主从备份配置

 

2.1 修改主库配置：

2.1.1 添加复制用户

如果不添加，也可用管理员用户postgres来进行主从复制操作

su postgres
psql 
create user skynet_repl REPLICATION LOGIN ENCRYPTED PASSWORD '1412341';

 

2.1.2 修改hba权限

回到root用户，修改hba配置文件，设置哪些ip可以访问主库的服务

cd /var/lib/pgsql/10/data

vim pg_hba.conf
#最底部添加,此处配置3个网段的ip都可以通过密码访问
host replication skynet_repl 10.1.1.0/24 md5 
host replication skynet_repl 10.1.25.0/24 md5 
host replication skynet_repl 10.1.5.0/24 md5

 

2.1.3 修改数据库配置

cd /var/lib/pgsql/10/data

cp postgresql.conf postgresql.conf.bak # 复制1个备份

vim postgresql.conf.bak #按下面配置修改对应项目，因pg还未重启为避免停止失败，修改备份文件，实际操作中直接修改 postgresql.conf 也可以

listen_address = '*'

wal_level = replica
archive_mode = on 
# 该目录要真实存在,如果不存在要创建，并且配置目录的用户为postgres
archive_command = 'cp %p /var/lib/pgsql/10/archive/%f '
max_wal_senders= 10 
wal_keep_segments=1024
hot_standby = on

 

2.2 主库停止并备份重启

以下操作在主库服务器进行

2.2.1 停止服务

#停止主库服务
service postgresql-10 status
service postgresql-10 stop

 

2.2.2 备份文件

对主库文件进行压缩备份，防止万一出问题。一般来说，不进行此操作也可以

zip data_20200508.zip data/ -r

2.2.3 配置修改

#将原文件备份为.old
mv postgresql.conf postgresql.conf.old
#将修改后的.bak配置文件(该文件上面步骤2.1.3提前修改好了)恢复为最终要使用的文件
mv postgresql.conf.bak postgresql.conf
chown postgres:postgres postgresql.conf

2.2.4 重启主库服务

service postgresql-10 status
service postgresql-10 start

主库重启成功后，需检查数据库重启是否成功。

并通过客户端连接主数据库，检查下是否正常（能否连接、数据是否丢失等）

 

2.3 从库复制并重启

以下操作在从库服务器进行

2.3.1 停止从库

service postgresql-10 stop

 

2.3.2 复制历史文件

su postgres
cd /var/lib/pgsql/10
# 将从库原data目录改名，或者删除
mv data data-bak
mkdir data
pg_basebackup -h 10.1.1.23 -U skynet_repl -p 5432 -F p -X s -v -P -R -D /var/lib/pgsql/10/data/ -l postgres32
# 按提示输入skynet_repl 用户的密码，密码在上面步骤中创建复制用户时设置

 

执行上述命令后，会进行数据库文件复制操作，一段时间后，提示复制成功

pg_basebackup: base backup completed

 

pg_basebackup命令中的参数说明：

-h 指定连接的数据库的主机名或IP地址，这里就是主库的ip
-U 指定连接的用户名，此处是我们刚才创建的专门负责流复制的repl用户
-F 指定生成备份的数据格式，支持p（plain原样输出）或者t（tar格式输出）
-X 表示备份开始后，启动另一个流复制连接从主库接收WAL日志，有 f(fetch)和s (stream）两种方式，建议使用s方式
-P 表示显示数据文件、表空间传输的近似百分比 允许在备份过程中实时的打印备份的进度
-v 表示启用verbose模式，命令执行过程中会打印各阶段日志，建议启用
-R 表示会在备份结束后自动生成recovery.conf文件，这样也就避免了手动创建
-D 指定把备份写到哪个目录，这里尤其要注意一点就是做基础备份之前从库的数据目录（/data/postgresql/data）目录需要手动清空
-l 表示指定个备份的标识，运行命令后可以看到进度提示

 

2.3.3 修改data文件的权限

上述步骤的执行用户如果不是postgres用户，则复制的文件的所有者就不会是postgres，所以需要修改data目录的拥有者。

chown -R postgres:postgres /var/lib/pgsql/10/data/
#如果使用Postgres用户复制依然会存在权限问题，需执行下面命令
chmod 0700 /var/lib/pgsql/10/data/

 

2.3.4 修改恢复配置

执行2.3.1步骤后，从库的data目录下会自动生成recovery.conf文件，修改内容如下：

standby_mode = 'on'
primary_conninfo = 'user=skynet_repl password=121234
host=10.1.1.23 port=5432 sslmode=disable sslcompression=0 target_session_attrs=any'
## 添加如下信息
recovery_target_timeline = 'latest'

 

上述配置中primary_conninfo 项目默认会生成 ikrbsrvname=postgres ，需要进行删除，否则从库启动后log日志中会提示：

无法连接到主用服务器：非法联接选项 "ikrbsrvname"

 

2.3.5 启动从库

service postgresql-10 status
service postgresql-10 start

 

检查从库启动是否成功，通过客户端连接，连接的用户和密码与主库一致，并且从库是只读的。建议再查看从库的启动日志pgstartup.log以及data/log/下的日志文件，核实启动中是否有其他问题

 

2.3.6 设置从库为开机启动

cd /etc/init.d
chkconfig
chkconfig postgresql-10 on

 

三、检查结果

在主库进行一些sql操作，如增加、删除、修改数据

到从库检查相应的记录是否同步进行了变更

 

通过可通过进程查看，执行命令：ps -ef | grep wal

在主库服务器可见2个进程：

postgres 8679 24233 0 08:34 ? 00:00:00 postgres: wal sender process skynet_repl 10.1.5.97(48288) streaming 5C/45FE0000 
postgres 24239 24233 0 May08 ? 00:00:12 postgres: wal writer process 

 

在从库服务器可见1个进程：

postgres 24287 24277 1 08:34 ? 00:00:00 postgres: wal receiver process streaming 5C/45380000

 

 

四、参考文档：

 

4.1 主从库识别

1、select pg_is_in_recovery(); 结果是f则为主库，t为备库

2、Select pid, application_name, client_addr, client_port, state, sync_state from pg_stat_replication 查询到结果为主库，查询不到结果为备库。

结果中：sync_state = async 表示是异步复制，异步复制对主库性能影响最低。

pid application_name client_addr client_port state sync_state 
8909 walreceiver 10.1.5.97 48312 streaming async

 

4.2 多备和级联备份

PostgreSQL的流复制最多支持1主8备、支持级联复制（主->备1，备1->备2）

 

4.3 主从库切换（未实际测试）

如果主库挂了，需要紧急使用从库来作为主库，以便服务能尽快恢复，操作方法如下：

1、从库上执行 pg_ctl promote 命令激活备库

激活后，可以看到recovery.conf变成recovery.done

此时从库变成可读写状态，可修改应用程序的数据库地址为从库地址

2、将主库改为从库

在原主库上新增recovery.conf文件，配置复制源头ip等信息

重启原主库，则原主库会变成只读从库

 

五、其他备份

 

5.1 全量热备

此操作要求数据库不停机，进行备份。有两种方式：

5.1.1、早期版本（9.0以前）：

在数据库服务器上执行如下命令即可：

# 在数据库psql命令行执行,标志要开始进行备份了,此时数据库的改动不会保存到data目录里
select pg_start_backup('backup0001')
# 在数据库服务器执行(非psql),直接将data目录复制到其他目录即可
scp -r /opt/pgsql/data/* 10.10.10.2:/opt/pgsql/data/
# 在数据库psql命令行执行,标志要备份结束了，过程中的数据库变动会更新到data目录中
select pg_stop_backup()

 

5.1.2、新版本（9.0及以后）：

通过pg_basebackup命令复制文件，同上述2.3.2步骤原理一样

pg_basebackup -h 10.10.10.1 -U u_standby -F p -x -P -R -D /var/lib/pgsql/9.5/data/ -1 rep_backup 
#-R 表示会在备份结束后自动生成recovery.conf文件，这样就避免了手动创建。

 

5.1.3、脚本备份

使用 pg_dump 命令可将结构、数据等信息备份成sql文件

 

5.2 全量冷备

冷备就是数据库服务器停止后的备份，则直接复制data目录即可

 

5.3 增量备份

5.3.1 备份增量文件

前提是数据库必须开启增量日志机制，参照2.1.3步骤配置后，在增量日志目录下会不断的生成16M大小的日志文件，文件数量与上述配置有关系

 

增量备份通过定时任务复制上述文件到其他目录或服务器即可实现增量数据的备份

5.3.2 恢复增量文件(未实际测试)

增量恢复的前提是已经有1个全量数据库，通过增量将数据库恢复成完整的状态。所以需要知道全量库的数据时间，然后进行如下操作：

data目录下修改或新增recovery.conf，内容如下：

restore_command = 'cp /mnt/server/archivedir/%f %p > /var/lib/pgsql/9.6/recovery.log 2>&1 '
recovery_target_time = '2017-11-12 16:20:00'
# 或者如下
# recovery_target_timeline = 'latest'

 

然后重启数据库，则pg会根据recovery.conf配置将增量文件根据时间戳导入到全量库中，恢复完毕后，recovery.conf会自动更名为recovery.done

 

5.4 延迟复制（未实际测试）

延迟复制，就是从库复制主库数据时稍晚点复制，这样万一主库有误操作，如不小心删除了关键数据，从库中该数据还没删除，可以从从库中进行读取备份恢复

5.4.1 启动延迟复制

修改recovery.conf文件，增加一行配置：（如不想延迟复制，则去掉该行配置）

recovery_min_apply_delay = '8h'

 

在从库执行sql查询延迟时间：

SELECT pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp(),now() ;

 

5.4.2 停止复制

SELECT pg_wal_replay_pause();

 

5.4.3 恢复复制

select pg_wal_replay_resume();