postgresql基于docker主从复制搭建

一、环境基本信息

操作系统:CentOS 7.3x86_64 
内存:32G
版本:postgresql9.6.3

测试环境
IP:172.16.36.65(主)port 5432
       172.16.36.66(备)port 5433

首先在主库上基于镜像创建postgresql容器
docker run -p 5432:5432 —name postgersdb -v /data/docker/postgres_data:/var/lib/postgresql/data/ -e POSTGRES_PASSWORD=postgres -d 镜像名
这样就安装好了postgresql的容器
/data/docker/postgres_data这个为数据库pgdata容器外目录
cd /data/docker/postgres_data
到数据库pg_data
二、主库配置
2.1修改pg_hba.conf文件

末尾添加
host replication repuser 172.16.36.66/32 md5
host all all all md5

2.2修改postgresql.conf文件
listen_addresses = ‘*’
port = 5432
max_connections = 200
shared_buffers = 5GB
wal_level = hot_standby
fsync = on 
archive_mode = on —归档开启
archive_command = ‘cp %p /usr/local/pgsql/arch/%f’ —归档路径
max_wal_senders = 2
wal_keep_segments = 64
hot_standby = on

2.3创建repuser用户(进容器psql内部或者工具navicat执行)
CREATE USER repuser
REPLICATION
LOGIN
CONNECTION LIMIT 2
ENCRYPTED PASSWORD ‘123456’;

一般我们也可到172.16.36.66备库验证,
psql -h 172.16.36.65 -p 5432 -U repuser -d postgres -W (我这里不验证因为备库是基于主库的镜像快照创建现在不能验证)
修改完所有配置重新启动容器。
三、主库容器备份到备库并恢复
3.1主库上备份容器
备份前做基础备份:
docker exec -it postgresdb /bin/bash进入容器
postgres=# select pg_start_backup(‘bakup’);创建基础备份

退出,在开始主库容器备份

docker commit -p imageID postgersql_65_backup

docker save -o /data/docker/postgersql_65_backup.tar postgersql_65_backup

3.2迁移
scp postgersql_65_backup.tar root@172.16.36.66:/tmp

3.3备库恢复
docker load -i postgersql_65_backup.tar

3.4备库容器启动
在66备库基于postgersql_65_backup镜像启动容器
docker run -p 5433:5432 —name postgersql_65_backup -v /data/docker/postgres_data:/var/lib/postgresql/data/ -e POSTGRES_PASSWORD=postgres -d postgersql_65_backup
四、备库配置
/data/docker/postgres_data
修改postgresql.conf
hot_standby = on
增加recovery.conf配置如下:
standby_mode = ‘on’
primary_conninfo = ‘user=repuser password=123456 host=172.16.16.65 port=5432’
recovery_target_timeline = ‘latest’

重启备库

主库上操作
结束基础备份:
postgres=# select pg_stop_backup();
日志切换:
postgres=# select pg_switch_xlog();
注意:
在启动备库时遇到的问题:
查看日志docker logs 容器ID
cp: cannot stat `/ire_gp/data/archive_wal/000000010000000000000001’: No such file or directory
LOG: streaming replication successfully connected to primary
WARNING: WAL was generated with wal_level=minimal, data may be missing
HINT: This happens if you temporarily set wal_level=minimal without taking a new base backup.
FATAL: hot standby is not possible because wal_level was not set to “hot_standby” on the master server
HINT: Either set wal_level to “hot_standby” on the master, or turn off hot_standby here.
LOG: startup process (PID 12549) exited with exit code 1
LOG: aborting startup due to startup process failure

出现这个原因的问题是当前进行重新执行的WAL文件000000010000000000000001是主机在minimal模式下产生的日志。同时检查配置文件,确认相关的参数正确。通常出现这个原因的问题是在备份文件系统的过程中存在问题,简单就是把主库的postgres_data目录下复制到备库,修改配置备库在重启下。

五、验证
主库上查看发送进程:
[root@65 postgres_data]# ps -ef |grep postgres
systemd+ 3033 3018 0 Sep26 ? 00:00:00 postgres
systemd+ 3079 3033 0 Sep26 ? 00:00:03 postgres: checkpointer process
systemd+ 3080 3033 0 Sep26 ? 00:00:00 postgres: writer process
systemd+ 3081 3033 0 Sep26 ? 00:00:00 postgres: wal writer process
systemd+ 3082 3033 0 Sep26 ? 00:00:00 postgres: autovacuum launcher process
systemd+ 3083 3033 0 Sep26 ? 00:00:00 postgres: stats collector process
systemd+ 15863 3033 0 09:46 ? 00:00:00 postgres: wal sender process repuser 172.16.36.66(46164) streaming 0/E000EB8
systemd+ 16559 3033 0 09:50 ? 00:00:00 postgres: postgres postgres 172.19.14.202(55561) idle
root 18672 7318 0 09:53 pts/0 00:00:00 grep —color=auto postgres
systemd+ 30123 30108 0 Sep22 ? 00:00:04 postgres
systemd+ 30212 30123 0 Sep22 ? 00:00:00 postgres: checkpointer process
systemd+ 30213 30123 0 Sep22 ? 00:00:02 postgres: writer process
systemd+ 30214 30123 0 Sep22 ? 00:00:02 postgres: wal writer process
systemd+ 30215 30123 0 Sep22 ? 00:00:02 postgres: autovacuum launcher process
systemd+ 30216 30123 0 Sep22 ? 00:00:06 postgres: stats collector process
备库上查看接收进程:
[root@66 postgres_data]# ps -ef |grep postgres
systemd+ 33596 33581 0 09:46 ? 00:00:00 postgres
systemd+ 33647 33596 0 09:46 ? 00:00:00 postgres: startup process recovering 00000001000000000000000E
systemd+ 33648 33596 0 09:46 ? 00:00:00 postgres: checkpointer process
systemd+ 33649 33596 0 09:46 ? 00:00:00 postgres: writer process
systemd+ 33650 33596 0 09:46 ? 00:00:00 postgres: wal receiver process streaming 0/E000EB8
systemd+ 33807 33596 0 09:49 ? 00:00:00 postgres: stats collector process
systemd+ 33903 33596 0 09:51 ? 00:00:00 postgres: postgres postgres 172.19.14.202(55568) idle
root 33972 30806 0 09:52 pts/0 00:00:00 grep —color=auto postgres



阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页