Postgresql实现主从复制，读写分离

前言

简单记录一下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日志传送到另一台服务器有两种方式，分别是：

WAL日志归档（base-file）

流复制（streaming replication）

第一种是写完一个WAL日志后，才把WAL日志文件拷贝到standby数据库中，简言之就是通过cp命令实现远程备份，这样通常备库会落后主库一个WAL日志文件。而第二种流复制是postgresql9.x之后才提供的新的传递WAL日志的方法，它的好处是只要master库一产生日志，就会马上传递到standby库，同第一种相比有更低的同步延迟，所以我们肯定也会选择流复制的方式。

在实际操作之前还有一点需要说明就是standby的搭建中最关键的一步——在standby中生成master的基础备份。postgresql9.1之后提供了一个很方便的工具—— pg_basebackup，关于它的详细介绍和参数说明可以在官网中查看（pg_basebackup tool），下面在搭建过程中再做相关具体说明，关于一些基础概念和原理先介绍到这里。

详细配置

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

主库（master） centos-release-7-3.1511 192.168.42.71 postgresql 9.5

从库（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

PostgreSQL 数据库

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。