postgresql主从备份_postgresql 主从复制并切换

1 环境

192.168.19.145 Kylin 3.3 mysqlhq  9.5.2  psql_master

192.168.19.227 Kylin 3.3 mysql3    9.5.2  psql_standby

postgres=# select version();

version

-------------------------------------------------------------------------------------------------------------

PostgreSQL 9.5.2 on x86_64-kylin-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

prot=5432

2 yum 安装pgsql

[root@mysqlhq ~]# yum list | grep postgresql

[root@mysqlhq ~]# yum install postgresql-server postgresql-contrib

[root@mysqlhq ~]# service postgresql initdb

[root@mysqlhq ~]# systemctl start postgresql

[root@mysqlhq ~]# systemctl status postgresql

[root@mysqlhq ~]# netstat -lnt|grep 5432

tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN

[root@mysqlhq ~]# ps -ef|grep postgres

[root@mysqlhq ~]# su - postgres

-bash-4.2$ psql

psql (9.5.2)

Type "help" for help.

3 修改主库的配置文件

#default 路径///var/lib/pgsql/data

[root@mysqlhq ~]# cd /var/lib/pgsql/data/

[root@mysqlhq data]# cp postgresql.conf postgresql.conf.bk20181213

[root@mysqlhq data]# vim postgresql.conf

listen_addresses = ‘*‘

max_connections = 1000

wal_level = hot_standby

#synchronous_commit = on(同步复制，-- 实时，如果需要异步改为off)

checkpoint_timeout = 5min

archive_mode = on

archive_command = ‘/bin/date‘

max_wal_senders = 2

wal_keep_segments = 16

hot_standby = on

max_standby_archive_delay = 300s

max_standby_streaming_delay = 30s

wal_receiver_status_interval = 1s

hot_standby_feedback = on

wal_receiver_timeout = 60s

-bash-4.2$ cat /var/lib/pgsql/data/pg_hba.conf | grep -v ‘^#‘ | grep -v ‘^$‘

local all all peer

host all all 127.0.0.1/32 trust

host all all 10.15.7.115/32 trust

host all all 192.168.19.145/32 md5

host all all ::1/128 ident

host replication repuser 192.168.19.227/32 md5

[root@mysqlhq data]# su - postgres

Last login: Tue Dec 11 16:34:47 CST 2018 on pts/3

-bash-4.2$ psql

psql (9.5.2)

Type "help" for help.

postgres=# create role repuser login replication encrypted password ‘pgreplication‘;

CREATE ROLE

-bash-4.2$ /usr/bin/pg_ctl stop

waiting for server to shut down.... done

server stopped

-bash-4.2$ /usr/bin/pg_ctl start

server starting

-bash-4.2$ netstat -lnt|grep 5432

tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN

tcp6 0 0 :::5432 :::* LISTEN

查看日志

tail -f -n 100 /var/lib/pgsql/data/pg_log/postgresql-Thu.log

从库配置

1 基础备份

从库安装完成后，不初始化，若已经初始化，删除其data目录

[root@mysql3 pgsql]# rm -rf data/

[root@mysql3 pgsql]# /usr/bin/pg_basebackup -D /var/lib/pgsql/data -F p -X stream -R -v -P -h 192.168.19.145 -p 5432 -U repuser

口令:

事务日志起始于时间点: 0/7000028, 基于时间表1

pg_basebackup: 启动后台 WAL 接收进程

73823/73876 kB (100%), 2/2 表空间

pg_basebackup: 无法得到来自服务器的事务日志终止位置: ERROR: could not open file "./postgresql.conf.bk20181213": Permission denied

删除文件./postgresql.conf.bk20181213，这些配置文件的备份需要放在非data目录

-R 备份后对文件recovery.conf进行写操作

删除数据目录，重新pg_basebackup

[root@mysql3 pgdata]# /usr/bin/pg_basebackup -D /var/lib/pgsql/data -F p -X stream -R -v -P -h 192.168.19.145 -p 5432 -U repuser

口令:

事务日志起始于时间点: 0/9000028, 基于时间表1

pg_basebackup: 启动后台 WAL 接收进程

73859/73859 kB (100%), 2/2 表空间

transaction log end point: 0/90000F8

pg_basebackup: 等待后台进程结束流操作...

pg_basebackup: base backup completed

2 配置备库参数postgresql.conf

[root@mysql3 data]# vim postgresql.conf

#在基础备份时，初始化文件是从主库复制来的，所以配置文件一致，注释掉

wal_level，

max_wal_senders

wal_keep_segments等参数

打开如下参数：

hot_standby = on   #在备份的同时允许查询

max_standby_streaming_delay = 30s#可选，流复制最大延迟

wal_receiver_status_interval = 10s#可选，从向主报告状态的最大间隔时间

hot_standby_feedback = on#可选，查询冲突时向主反馈

max_connections = 1000#默认参数，非主从配置相关参数，表示到数据库的连接数，一般从库做主要的读服务时ÿ