验证postgres读写分离 pgsql 读写分离

前言

简单记录一下postgresql主从的实现方式之一——基于Standby的异步流复制，这是PostgreSQL9.x版本（2010.9）之后提供的一个很nice的功能，类似的功能在Oracle中是11g之后才提供的active dataguard和SQL Server 2012版本之后才提供的日志传送，此处再次为pg鼓掌，确实是一个很棒的开源数据库。废话不多说，本篇blog就详细记录一下在pg9.5中实现Hot Standby异步流复制的完整配置过程和注意事项。

Standby数据库原理

简单介绍一些基础概念与原理，首先我们做主从同步的目的就是实现db服务的高可用性，通常是一台主数据库提供读写，然后把数据同步到另一台从库，然后从库不断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），下面在搭建过程中再做相关具体说明，关于一些基础概念和原理先介绍到这里。

详细配置

下面开始实战，首先准备两台服务器，我这里开了2个虚拟机做测试，分别是：

1. 主库（master） centos-release-7-3.1511 192.168.42.71 postgresql 9.5
2. 从库（standby） centos-release-7-3.1511 192.168.42.70 postgresql 9.5

一、主库（master）安装 并 配置postgresql 

（1）安装postgresql

  1.添加RPM
   $ sudo yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm
   # 2.安装PostgreSQL 9.5
   $ sudo yum install postgresql95-server postgresql95-contrib
   # 3.初始化数据库（切记：从库不需要初始化数据库）
   $ sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb
   # 4.设置开机自启动
   $ sudo systemctl enable postgresql-9.5.service
   #5.启动服务
   $ sudo systemctl start postgresql-9.5.service

#6.停止服务

$sudo systemctl stop postgresql-9.5.service

   安装完成默认会做三件事：

   1. 创建 `postgres` 的 Linux 用户；
   2. 创建 `postgres` 不带密码的默认数据库管理员账户；
   3. 创建 `postgres` 系统数据库。

(2)修改配置文件

cd  /var/lib/pgsql/9.5/data

vim pg_hba.conf 增加以下配置：（切记：必须要配置为从库的ip/32）

host all all 0.0.0.0 0.0.0.0 md5
host replication postgres 192.168.42.70/32 md5      #这句话的意思允许从数据库连接主数据库去拖wal日志数据

vim postgresql.conf

Listen_adresses = '*'
wal_level = hot_standby                #主从设置为热血模式，流复制必选

max_wal_senders=2 #流复制允许连接进程
wal_keep_segments =64 
max_connections = 1000 默认参数，非主从配置相关参数，表示到数据库的连接数

第一个不用说了，wal_level表示启动搭建Hot Standby，max_wal_senders则需要设置为一个大于0的数，它表示主库最多可以有多少个并发的standby数据库，而最后一个wal_keep_segments也应当设置为一个尽量大的值，以防止主库生成WAL日志太快，日志还没有来得及传送到standby就被覆盖，但是需要考虑磁盘空间允许，一个WAL日志文件的大小是16M： 

如上图，一个WAL日志文件是16M，如果wal_keep_segments设置为64，也就是说将为standby库保留64个WAL日志文件，那么就会占用16*64=1GB的磁盘空间，所以需要综合考虑，在磁盘空间允许的情况下设置大一些，就会减少standby重新搭建的风险。接下来还需要在主库创建一个超级用户来专门负责让standby连接去拖WAL日志：

(3)启动 主 数据库

$ sudo systemctl start postgresql-9.5.service

二、从库（standby）安装 并 配置postgresql 

（1）从库安装完成后，不初始化，若已经初始化，删除其/var/lib/pgsql/9.5/data目录

执行，以下命令将主数据库的/var/lib/pgsql/9.5/data的目录同步过来。

pg_basebackup -h 192.168.42.71 -U postgres -F p -x -P -R -D /var/lib/pgsql/9.5/data/ -l postgresbackup20190129

下面简单做一下参数说明（可以通过pg_basebackup --help进行查看），-h指定连接的数据库的主机名或IP地址，这里就是主库的ip。-U指定连接的用户名，此处是我们刚才创建的专门负责流复制的repl用户。-F指定了输出的格式，支持p（原样输出）或者t（tar格式输出）。-x表示备份开始后，启动另一个流复制连接从主库接收WAL日志。-P表示允许在备份过程中实时的打印备份的进度。-R表示会在备份结束后自动生成recovery.conf文件，这样也就避免了手动创建。-D指定把备份写到哪个目录，这里尤其要注意一点就是做基础备份之前从库的数据目录（/usr/local/postgresql/data）目录需要手动清空。-l表示指定一个备份的标识，运行命令后看到如下进度提示就说明生成基础备份成功： 

如上图，由于我们在pg_hba.conf中指定的md5认证方式，所以需要输入主数据库postgres用户的密码postgres。

（2）修改配置文件

vi /postgres/data/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 #默认参数，非主从配置相关参数，表示到数据库的连接数，一般从库做主要的读服务时，设置值需要高于主库

(3)创建恢复文件recovery.conf（如果我们在执行pg_basebackup的时候指定了-R 参数，可以忽略此步骤 ）

recovery.conf    #在做基础备份时，也可通过-R参数在备份结束后自动生产一个recovery.conf文件
standby_mode = on  #指明从库身份
primary_conninfo = 'host=10.0.120.150 port=5432 user=repl password=repl1234'      #连接到主库信息
recovery_target_timeline = 'latest'     #同步到最新数据
#trigger_file = ‘/postgres/data/trigger_activestandby’

指定触发文件，文件存在时，将触发从库提升为主库，前提是必须设置”standby_mode = on”；如果不设置此参数，也可采用”pg_ctl promote“触发从库切换成主库

（4）启动从数据库，可能会报权限不正确的错误

[root@scoder19 9.3]# service postgresql-9.3 start
Starting postgresql-9.3 service: [FAILED]
日志文件（/var/lib/pgsql/9.3/pgstartup.log）：报错如下：

原因是应该是 文件夹 “/var/lib/pgsql/9.3/data”权限错误， 权限应该是 rwx (0700)。
解决方法：
切换到 具有root权限的用户，
先把文件夹 “/var/lib/pgsql/9.5/data” 的用户所属组，给postgres 用户：
进入/var/lib/pgsql/9.5目录
cd /var/lib/pgsql/9.5
chown -R postgres:postgres data
把data目前的所有文件及子目录文件权限改成： rwx (0700)
chmod -R 0700 data
重启PostgreSQL 数据库，问题解决。

三 、验证主从是否配置成功

（1）查看主库sender进程

（2）查看从库receiver进程

 （3）向主库中创建表，并插入数据，查看从库是否同步过去 

su postgres
psql
select * from pg_stat_replication;
create table test(id int primary key,name vatchar(20),salary real);
insert into test values(10,'i love you',10000.00);
insert into test values(2,'li si',12000.00);

从库只读，不可插入数据，修改，删除数据

 到此主从复制建立完成。

四  、主从切换（未完待续）

（1）通过自带的函数，是备机则是t

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
 ——————-
 f