一、概述 PostgreSQl从9.0版本之后推出一个类似于Oracle的active dataguard和MySql中继日志一样的日志传送。我们借助这个功能就可实现PostgreSql的主从复制。 基本原理就是,通常一台主数据库提供读写,然后把数据同步到另一台从库。从库不断apply从主库接收到的数据,从库不提供写服务,只对外提供读服务。在postgresql中提供读写全功能的服务器称为primary database或master database,在接收主库同步数据的同时又能提供读服务的从库服务器称为hot standby server。 PostgreSQL在数据目录下的pg_xlog子目录中维护了一个WAL日志文件,该文件用于记录数据库文件的每次改变,这种日志文件机制提供了一种数据库热备份的方案,即:在把数据库使用文件系统的方式备份出来的同时也把相应的WAL日志进行备份,即使备份出来的数据块不一致,也可以重放WAL日志把备份的内容推到一致状态。这也就是基于时间点的备份(Point-in-Time Recovery),简称PITR。而把WAL日志传送到另一台服务器有两种方式,分别是: 1. WAL日志归档(base-file) 2. 流复制(streaming replication) 第一种是写完一个WAL日志后,才把WAL日志文件拷贝到standby数据库中,简言之就是通过cp命令实现远程备份,这样通常备库会落后主库一个WAL日志文件。而第二种流复制是postgresql9.x之后才提供的新的传递WAL日志的方法,它的好处是只要master库一产生日志,就会马上传递到standby库,同第一种相比有更低的同步延迟,所以我们肯定也会选择流复制的方式。 在实际操作之前还有一点需要说明就是standby的搭建中最关键的一步——在standby中生成master的基础备份。postgresql9.1之后提供了一个很方便的工具—— pg_basebackup,关于它的详细介绍和参数说明可以在官网中查看( pg_basebackup tool),下面在搭建过程中再做相关具体说明。 二、基础环境介绍系统平台:CentOS release 6.6 (Final) Postgresql:postgresql-9.6.6 SELINUX=disabled Iptables关闭 主库(master)IP:192.168.221.161 从库(standby)IP:192.168.221.160 三、主库配置1. 在主库增加同步的用户名与密码 1
2
3
4
5
6
7
| [postgres@MidApp ~]$ psql
psql (9.6.6)
Type "help" for help.
postgres=# CREATE ROLE repluser REPLICATION LOGIN PASSWORD '123456';
CREATE ROLE
postgres=#
|
2. 修改/home/postgres/pgsql/data/pg_hba.conf,最后一行添加 1
2
3
4
5
6
7
| [iyunv@MidApp tmp]# tail -6 /home/postgres/pgsql/data/pg_hba.conf
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres trust
#host replication postgres 127.0.0.1/32 trust
#host replication postgres ::1/128 trust
Host replication repluser 192.168.221.160/32 md5
|
这行配置意思是允许用户repluser从192.168.221.160这台主机上以md5 加密的形式发起到本数据库的流复制连接 3. 在主配置文件下配置下面几个参数 1
2
3
4
5
| listen_address = ‘*’(默认localhost)
wal_level = hot_standby(默认是minimal)
max_wal_senders=5(默认是0)
wal_keep_segments=64(默认是0)
synchronous_standby_names = 'standby01'
|
第一个参数表示监听所有IP;第二个参数表示启动hot standby;第三个参数表示主库可以有多少个并发的standby数据库,这里设置为5;第四个参数表示一个WAL日志文件大小,默认为16M 第五个参数指定同步复制的Standby名称(从库的recovery.conf中有要定义的地方,不过这一个参数可以不设置) 4. 重启主库,让配置生效。 如果启动有报错,可以去日志排查。 四、从库配置首先要保证主库、从库之间的同步之前的环境的是一致的,这样才方便做同步。我因为之前就在从库机器上配置过PG数据库,所以一开始走了不少弯路。最后把PG的家目录清空,重新再来一次才算成功。 1. 在从库上通过pg_basebackup命令行工具生成基础备份,命令如下,看到100%说明备份成功 1
2
3
4
| [iyunv@DB tmp]# pg_basebackup -h 192.168.221.161 -F p -P -D /home/postgres/pgsql/data -p5432 -U repluser --password
Password:
22802/22802 kB (100%), 1/1 tablespace
NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to comple
|
参数说明:-h 指定连接的数据库IP; -F 指定输出的格式,支持p(plain原样输出)或者t(tar格式输出) -P 在备份过程中实时打印备份进度 -D 指定备份的目录 -U 指定连接的用户名 -p 指定要连接的端口 --password 指定要连接的用户密码 其他参数介绍: -R 会在备份后自动生成recovery.conf文件,我也是事后才知道这个参数 -l 指定一个备份的标识 特别备注:我这里没有加-R参数,所以要手动拷贝一下recovery.conf 1
| cp /home/postgres/pgsql/share/recovery.conf.sample /home/postgres/pgsql/data/recovery.conf
|
添加以下信息: 1
2
| standby_mode = on
primary_conninfo = 'application_name=standby01 user=repluser password=123456 host=192.168.221.161 port=5432 sslmode=disable sslcompression=1'
|
2. 修改从库的主配置文件/home/postgres/pgsql/data/postgresql.conf
将hot_standby改为启用状态 3. 接下来可以启动从库了 1
2
3
| [iyunv@DB tmp]# /etc/init.d/postgresql start
Starting PostgreSQL: -bash: /home/postgres/pgsql/data/serverlog: Permission denied
ok
|
第一次启动报错,这是因为上面生成备份的命令我使用root用户执行的,导致PG家目录的属性变成了root,所以要重新设置权限 1
| chown -R postgres:postgres /home/postgres/pgsql/*
|
再次启动正常,查看进程也OK 1
2
3
4
5
6
7
8
9
10
11
| [iyunv@DB tmp]# /etc/init.d/postgresql start
Starting PostgreSQL: ok
[iyunv@DB tmp]# ps -ef | grep postg
root 52577 82731 0 15:12 pts/0 00:00:00 su - postgres
postgres 52578 52577 0 15:12 pts/0 00:00:00 -bash
postgres 74295 1 0 20:01 ? 00:00:00 /home/postgres/pgsql/bin/postmaster -D /home/postgres/pgsql/data
postgres 74296 74295 0 20:01 ? 00:00:00 postgres: startup process recovering 000000010000000000000003
postgres 74297 74295 5 20:01 ? 00:00:00 postgres: wal receiver process streaming 0/3000140
postgres 74298 74295 0 20:01 ? 00:00:00 postgres: checkpointer process
postgres 74299 74295 0 20:01 ? 00:00:00 postgres: writer process
postgres 74300 74295 0 20:01 ? 00:00:00 postgres: stats collector process
|
五、结果验证1. 在主库通过select usename,application_name,client_addr,state from pg_stat_replication查询一下: 1
2
3
4
5
6
7
8
9
10
11
| [postgres@MidApp ~]$ psql
psql (9.6.6)
Type "help" for help.
postgres=# select usename,application_name,client_addr,state from pg_stat_replication;
usename | application_name | client_addr | state
----------+------------------+-----------------+-----------
repluser | standby01 | 192.168.221.160 | streaming
(1 row)
postgres=#
|
可以看到192.168.221.160上的repluser在通过流复制的方式同步主库的数据 2. 创建表验证一下 主库上建表,并插入数据验证 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| postgres=# create table test01(id int primary key,note text);
CREATE TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | test01 | table | postgres
(1 row)
postgres=# insert into test01 values(1,'1111111');
INSERT 0 1
postgres=# select * from test01;
id | note
----+---------
1 | 1111111
(1 row)
|
在从库上查看: 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| [postgres@DB data]$ psql
psql (9.6.6)
Type "help" for help.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | test01 | table | postgres
(1 row)
postgres=# select * from test01;
id | note
----+---------
1 | 1111111
(1 row)
|
尝试插入数据看一下: 1
2
| postgres=# insert into test01 values(2,'2222222');
ERROR: cannot execute INSERT in a read-only transaction
|
可以看到,从库可以查看从主库同步过来的数据,但并不能写数据。 六、总结以上是搭建Postgresql主从同步的全过程,一路踩了好多坑,记录下了,希望能帮助到别人
|