搭建主从复制的PostgreSQL集群

10713 views

 ›
搭建主从复制的PostgreSQL集群

背景

由于项目需要使用PostgreSQL作为持久化数据库，但是在公司内部并没有一个稳定公共的PostgreSQL数据库集群服务来用，因此需要自己搭建一套高可用的PostgreSQL服务。 在这里将搭建的过程记录下来，方便后续查阅。

安装

资源

操作系统

本文中安装Postgres数据库的操作系统为CentOS7.2

机器和IP地址

本文是在2台CentOS7.2的机器上部署主从复制的PostgreSQL，这2台机器的IP为：

主节点： 192.168.0.2
复制节点： 192.168.0.3

数据库用户名和密码

user: xxx
password: xxx

数据目录

/data/postgresql/

数据同步账号密码

user: replica
password: xxx

安装步骤

添加postgresql9.6.3的yum源

yum -y install https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

安装postgresql

yum -y install postgresql96-server postgresql96-contrib

初始化数据库

cd /usr/pgsql-9.6/bin
./postgresql96-setup initdb

将postgresql放到systemctl中

systemctl enable postgresql-9.6
systemctl start postgresql-9.6

确保5432端口可用，可以通过netstat来检查5432端口是否被postgresql占用

netstat -plntu | grep 5432

给postgresql添加密码

su - postgres
psql

\password postgres

根据提示，输入对应的密码

配置主从复制

配置主节点

在主节点上修改postgresql.conf

cd /data/postgresql/
vim postgresql.conf

修改listen_addresses，改为：
listen_addresses = '192.168.0.2'

修改wal_level，改为：
wal_level = hot_standby

修改同步级别synchronous_commit，改为：
synchronous_commit = local

启用存档模式，分别修改archive_mode和archive_command, 改为：
archive_mode = on
archive_command = 'cp %p /data/postgresql/archive/%f'

修改max_wal_senders和wal_keep_segments，改为：
max_wal_senders = 2
wal_keep_segments = 10

修改应用程序名称synchronous_standby_names字段，改为：
synchronous_standby_names = 'pgslave01'

保存并退出。

创建archive目录，并修改权限和用户

mkdir -p /data/postgresql/archive/
chmod 700 -R /data/postgresql/archive/
chown -R postgres:postgres /data/postgresql/archive/

编辑pg_hba.conf文件

添加下面配置到文件尾部：

# Localhost
 host    replication     replica          127.0.0.1/32            md5

 # PostgreSQL Master IP address
 host    replication     replica           192.168.0.2/32            md5

 # PostgreSQL SLave IP address                                                                                                                
 host    replication     replica           192.168.0.3/32            md5

保存退出，配置完毕。重启postgresl

systemctl restart postgresql-9.6

添加一个同步账号，replica

su - postgres
createuser --replication -P replica

根据提示输入密码

配置从节点

停止postgresql

systemctl stop postgresql-9.6.service

进入到postgresql目录，并备份数据

cd /data/
mv postgresql/ postgresql.bak

重新创建postgresql目录，并修改权限和用户

mkdir -p postgresql
chmod -R 700 postgresql
chown -R postgres:postgres postgresql

以postgres用户身份登录，并将所有数据目录从主服务器同步回来

su - postgres
pg_basebackup -h <主节点IP，例如：192.168.0.2> -U replica -D /data/postgresql/ -P --xlog

根据提示输入replica用户的密码。

传输完毕后，可以在/data/postgresql目录下有postgresql.conf文件，修改该文件

cd /data/postgresql/
vim postgresql.conf

修改listen_address为从机的IP，改为:
listen_addresses = '192.168.0.3'

修改hot_standby，改为：                                                                                                                       
hot_standby = on

保存并退出。

在/data/postgresql目录下新建recovery.conf，并添加如下内容：

standby_mode = 'on'
primary_conninfo = 'host=<主节点ip, 例如：192.168.0.2> port=5432 user=replica password=<replica账号的密码> application_name=pgslave01'
trigger_file = '/tmp/postgresql.trigger.5432'

其中<>中的部分需要替换成相应的值。

保存并退出

修改文件的权限和用户

chmod 600 recovery.conf                                                                                                                       
chown postgres:postgres recovery.conf

重启postgresql

systemctl restart postgresql-9.6.service

主从切换

切换前的状态

在master节点执行：

cd /usr/pgsql-9.6/bin
./pg_controldata  /data/postgresql/

可以看到Database cluster state是 in production 这表示正常运行。

在slave节点执行：

cd /usr/pgsql-9.6/bin
./pg_controldata  /data/postgresql/

可以看到Database cluster state是 in archive recovery 这表示slave节点正常运行。

主库故障

停掉主库

su - postgres                                                                                                                                 
cd /usr/pgsql-9.6/bin/
./pg_ctl stop -m fast -D /data/postgresql/

现在再执行pg_controldata

cd /usr/pgsql-9.6/bin
./pg_controldata  /data/postgresql/

可以看到Database cluster state是 shut down 这表示该节点已经关闭

查看从库日志

cd /data/postgresql/pg_log
ls -lrt 可以查看到最新更新的日志，这里是Wed
tail -f postgresql-Wed.log

可以看到提示主节点连不上

激活从库

使用pg_ctl promote激活从库 切换后，从库的recovery.conf文件名字变成了recovery.done

su - postgres

cd /usr/pgsql-9.6/bin/
./pg_ctl promote -D /data/postgresql/

查看从库的日志，状态

cd /data/postgresql/pg_log
ls -lrt 可以查看到最新更新的日志，这里是Wed
tail -f postgresql-Wed.log

可以看到有received promote request 表示从库已经接收到promote请求

执行pg_controldata，查看数据库状态：

cd /usr/pgsql-9.6/bin
./pg_controldata  /data/postgresql/

可以看到Database cluster state是 in production 表示已经成为主节点

后续

a. 可以通过keepalived来做高可用。 https://github.com/francs/PostgreSQL-Keepalived-HA
b. 检测trigger_file存在与否也可完成从库切换主库
c. 从库切换到主库，故障的原主库恢复后，可将其降为备库，主要是设置recovery.conf文件与postgres.conf文件，最差的情况下可清空此时的备库（原主库）>的$PGDATA并重新同步

验证

切换到主节点，并切换到postgres用户

su - postgres

用以下命令检查postgresql的流状态复制

psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
psql -x -c "select * from pg_stat_replication;"

应该看到， 状态值为： streaming， sync_state是sync

在主节点插入数据

su - postgres
psql
CREATE TABLE replica_test (test varchar(100));
INSERT INTO replica_test VALUES ('howtoing.com');

登录到从服务器，并进入到psql

su - postgres
psql

使用sql检查刚才插入的数据是否存在

select * from replica_test;

在从服务器尝试写数据：

INSERT INTO replica_test VALUES ('this is SLAVE');

会提示read-only，没有写入权限。

TIPS

data目录修改

data目录修改需要做如下操作：

  1. 修改systemctl的postgres的脚本，将data目录改为需要的目录
  2. 创建新的目录，并修改目录的权限和owner
  3. 将之前生成的配置文件copy到新目录

postgresql配置说明

wal_level

我们将wal的wal_level改为了: hot_standby，这个配置有minimal, replica, hot_standby和logical4种配置，分别表示：

  1. minimal: 默认的值，它仅写入崩溃或者突发关机时所需要的信息
  2. replica: 增加wal归档信息同时包括只读服务器需要的信息，这个参数是9.6新增参数，将之前的archive和hot_standby合并
  3. hot_standby: 在备用服务器上增加了运行只读查询所需的信息，一般实在流复制的时候使用到。
  4. logical: 主要用于logical decoding 场景, Logical decoding 允许读取 WAL 日志，并将数据变化解析成目标格式

fsync

该参数直接控制日志是否先写入磁盘。默认值是ON。开启该值时表明，更新数据写入磁盘时系统必须等待WAL的写入完成。

wal_writer_buffer

用于存放WAL数据的内存空间（日志缓存区的大小）。最小32K。 执行一个大事务肯定受到影响，应该适当的增大该参数，降低IO。 如果比较多的并发短事务，应该考了设置commit_delay、commit_siblings。

wal_writer_delay

wal_writer_delay表示同步延迟，默认为200ms。即一个wal，最多延迟200ms后会写入到磁盘。

synchronous_commit

该参数表明是否等待WAL完成后才返回给用户事务的状态信息。默认值是ON，表明必须等待WAL完成后才返回事务状态信息。 synchronous_commit = local synchronous_commit可选参数为： on, off, local, remote_write, remote_apply，分别为：

  1. on:

    1.1. 为on且没有开启同步备库的时候,会当wal日志真正刷新到磁盘永久存储后才会返回客户端事务已提交成功
 1.2. 当为on且开启了同步备库的时候(设置了synchronous_standby_names),必须要等事务日志刷新到本地磁盘,并且还要等远程备库也提交到磁盘才能返

    回客户端已经提交

  2. off:

    写到缓存中就会向客户端返回提交成功，但也不是一直不刷到磁盘，延迟写入磁盘,延迟的时间为最大3倍的wal_writer_delay参数的(默认200ms)的时间,所有 如果即使关闭synchronous_commit,也只会造成最多600ms的事务丢失,此事务甚至包括已经提交的事务（会丢数据）,但数据库确可以安全启动,不会发生块折断,只 是丢失了部分数据,但对高并发的小事务系统来说,性能来说提升较大。

  3. remote_write:

    当事务提交时,不仅要把wal刷新到磁盘,还需要等wal日志发送到备库操作系统(但不用等备库刷新到磁盘),因此如果备库此时发生实例中断不会有数据丢失,因 为数据还在操作系统上,而如果操作系统故障,则此部分wal日志还没有来得及写入磁盘就会丢失,备库启动后还需要向主库索取wal日志。

  4. local:

    当事务提交时,仅写入本地磁盘即可返回客户端事务提交成功,而不管是否有同步备库

  5. remote_apply:

    当事务提交时，所有的同步节点需要确认收到记录并apply。所以在复制节点上可以看到这些queries

full_page_writes

是否开启全页写入，此参数是为了防止块折断的一种策略。 关于块折断，每种数据库都会遇到这样的问题，起因是： linux文件系统一个块（简称OS块）一般是4k，而数据库则一般是一个块8k。 当数据库的脏块刷新到磁盘上时，由于底层是两个块组成的。 比如刷第一个OS块到磁盘上了，而当刷第二个OS块的时候发生了停电等突然停机事故，则就发生了块折断。 数据块是否折断是根据块的checksum值来检查的。

为了避免这种事故，pg的机制:

将前滚和回滚所需的数据都写入到了WAL。如果不将整个page写入WAL中，在介质恢复时WAL中记录数据不足以实现完整的恢复。
当checkpoint后的一个块第一次变脏后就要整块写入到wal日志中，后续继续修改此块则只把修改的信息写入wal日志中。
如果在此过程中发生了停电，则实例启动后会从checkpoint检查点，之后开始进行实例恢复。
如果有块折断，则在全页写入的块为基础进行恢复，最后覆盖磁盘上的折断块。
所以当每次checkpoint后如果数据有修改都会进行全页写入，因此控制checkpoint的间隔是否重要。
如果checkpoint_segments设置太小就会造成频繁的checkpoint，进而导致写入了过多的全页。

mysql为了防止块折断采用了double write，oracle采用了redo+undo机制，其中undo记录了前镜像，而redo则既记录了修改数据又记录了undo块。

on可以提高数据库的可靠性，减少数据丢失的概率，但是会产生过多的事务日志，降低数据库的性能。 支持原子写超过BLOCK_SIZE的块设备，在对齐后可以关闭。或者支持cow的文件系统可以关闭。

wal_compression

enable compression of full-page writes 如果是checkpoint后第一次修改页面，则输出整个page的内容(即full page image，简称FPI)。 是否对full-page writes数据进行压缩（即去掉page中没有数据的hole部分）写入。

commit_siblings

延迟提交wal日志的最小并发事务数，决定参数commit_delay是否生效。 假设值是5，表示数据库中正在执行的事务数大于或等于5，该事务提交后，wal日志将会存入wal buffer中，延迟commit_delay时间后再写入磁盘。 如果数据库中正在执行的事务数小于5，这个事务提交后将wal日志直接写入磁盘。

wal_writer_flush_after

wal write的字节数超过配置的阈值时,触发fsync。 IO很好的机器，不需要考虑平滑调度 默认值为1MB，如果设置为0，关闭该特性（9.6版本新增的参数）

其他postgresql参数修改

归档模式

archive_mode = on
对数据库进行周期备份，来防止数据的丢失，这就需要连续归档 它不仅可以用于大型数据库的增量备份和恢复，也可以用于搭建standby镜像备份 开启归档模式，主要涉及到三个参数：wal_level,archive_mode和archive_commandwal_level参数默认为mininal,设置此参数为archive或者之上的级别都可以打>开归档。

归档command

archive_command = 'cp %p /data/postgresql/archive/%f' 当postgresql需要传输归档日志时，会调用archive_command指定的shell命令。 归档文件传输成功时，shell命令要返回0，此时，postgresql会认为归档文件已经传输成功，因此可以删除或者重新循环利用归档文件。 当shell命令返回非0值时，postgresql会保留所有未成功传输的归档日志，并不断尝试重新传输，直到成功。 如果归档命令一直不成功，pg_xlog目录会持续增长，有耗尽服务器存储空间的可能，此时postgresql会PANIC关闭，直到释放存储空间。 另外将归档WAL日志存储在本机上是风险极高，不被推荐的。 postgresql通过archive_command提供了存储WAL日志的灵活性，可以将归档日志存储到挂装的NFS目录，磁带，刻录到光盘，也可以将WAL日志通过ssh/scp，rsync传输到异机保存。

max_wal_senders

一个master最多对应多少个slave。 max_wal_senders = 2

wal_keep_segments

xlog目录中最多容纳多少个wal日志文件，超过了则删掉最初的几个 wal_keep_segments = 10

synchronous_standby_names

postgresql同步standby使用的name synchronous_standby_names = 'pgslave01'