再不了解PostgreSQL，你就晚了之PostgreSQL主从流复制部署
前言
在MySQL被收购之后，虽然有其替代品为: MariaDB,但是总感觉心里有点膈应。大家发现了另一款开源的数据库: PostgreSQL。
虽然centos自带版本9.2也可以用，但是最近的几次升级支持了更多更新的内容，比如，PostgreSQL有一个MySQL无法比拟的优势，那就是PostGIS，PostGIS可以完美支持空间数据存储和空间分析；从PostgreSQL9.3起就内置了JSON数据类型，而9.4又开始支持JSONB，这标志着PostgreSQL实际上已经是一个关系型数据库和NoSQL数据库的结合体了。
虽然postgresql是一个关系型数据库，但是近几次更新PostgreSQL的NoSQL性能有益到甚至超过了MongoDB。我们可以从下图数据库Rank榜上观察到PostgreSQL排在第四位,和MongoDB的位置不相上下。可见PostgreSQL在开发人员的喜爱度上,可信赖度上和社区文档的查询上都是不错的。
Centos7 安装最新版postgresql10
更新源
云服务器系统: CentOS 7.2 x86_64架构
地址: https://www.postgresql.org/download/linux/redhat/
这里我选择PostgreSQL10,CentOS 7平台, x86_64架构,就会出现相应的yum源了。
在centos系统中执行以下命令:
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
安装PostgreSQL
先查看PostgreSQL源
我们需要安装的是这三个个。postgresql10-devel.x86_64, postgresql10-contrib.x86_64,postgresql10-server.x86_64
yum install postgresql10-client postgresql10-server postgresql10-contrib postgresql10-devel
初始化数据库
PostgreSQL安装目录是/usr/pgsql-10,而PostgreSQL的数据目录是/var/lib/pgsql/版本号(这里为10)
在这里，如果在装系统开始分配var空间足够大则可以继续，如果分配var空间不够，我们需要更改数据目录，在这里，我们假设var空间足够大。直接开始初始化。
/usr/pgsql-10/bin/postgresql-10-setup initdb
显示这个代表初始化成功
启功数据库并设置开机启动
sudo systemctl start postgresql-10
sudo systemctl enable postgresql-10.service
登录PostgreSQL并设置密码
postgresql在安装时默认添加用户postgres
输入
su - postgres
psql
进入数据库
命令界面长这样
我们来设置密码:
退出: q
列出所有库 l
列出所有用户 du
列出库下所有表 d
支持密码和远程登陆
修改密码验证
默认情况下postgresql是不用密码不支持远程登录的。我们需要修改配置文件
vi /var/lib/pgsql/10/data/pg_hba.conf
原本长成这样
我们需要改成
保存退出
关于退出vim的问题，这里还有一个小趣闻：
StackOverflow骄傲的宣布： 已经帮助187万不知如何从VIM退出的程序员
在这里我引用上面的高赞回答，帮助大家脱离这个187万群体。
修改远程访问
vi /var/lib/pgsql/10/data/postgresql.conf
往下拉我们会看到
需要改成
在vim中搜索想要查找的文字，可以用 /*, n 表示下翻页，N表示上翻页
重启postgresql
systemctl restart postgresql-10
登录postgresql
postgresql在安装时默认添加用户postgres
输入
su - postgres
psql
进入数据库
此时已经可以进行验证密码
远程连接数据库
我们使用Navicat Premium 12来验证连接
此时我们可以看到相关数据了
PostgreSQL主从流复制部署
简介
postgres在9.0之后引入了主从的流复制机制，所谓流复制，就是从服务器通过tcp流从主服务器中同步相应的数据。这样当主服务器数据丢失时从服务器中仍有备份。
与基于文件日志传送相比，流复制允许保持从服务器更新。 从服务器连接主服务器，其产生的流WAL记录到从服务器， 而不需要等待主服务器写完WAL文件。
PostgreSQL流复制默认是异步的。在主服务器上提交事务和从服务器上变化可见之间有一个小的延迟，这个延迟远小于基于文件日志传送，通常1秒能完成。如果主服务器突然崩溃，可能会有少量数据丢失。
同步复制必须等主服务器和从服务器都写完WAL后才能提交事务。这样在一定程度上会增加事务的响应时间。
配置同步复制仅需要一个额外的配置步骤： synchronous_standby_names必须设置为一个非空值。synchronous_commit也必须设置为on。
这里部署的是异步的流复制。
主从服务器所在节点的系统、环境等最好一致。PostgreSQL版本也最好一致，否则可能会有问题。
安装部署
先假定在192.168.20.93和192.168.20.94均安装PostgreSQL,具体情况按照实际IP地址。
具体安装部署步骤见：上述步骤
2.1 主服务器
主服务器为192.168.20.93
1.首先需要创建一个数据库用户进行主从同步。创建用户replica，并赋予登录和复制的权限。
postgres# CREATE ROLE replica login replication encrypted password 'replica'；
2.修改pg_hba.conf，允许replica用户来同步。
在pg_hba.conf里增加两行：
host all all 192.168.20.94/32 trust #允许94连接到主服务器
host replication replica 192.168.20.94/32 md5 #允许94使用replica用户来复制
这样，就设置了replica这个用户可以从192.168.20.93进行流复制请求。
注： 第二个字段必须要填replication
4.修改postgresql.conf
listen_addresses = '*' # 监听所有IP
archive_mode = on # 允许归档
archive_command = 'cp %p /opt/pgsql/pg_archive/%f' # 用该命令来归档logfile segment
wal_level = hot_standby
max_wal_senders = 32 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个wal_keep_segments = 256 ＃ 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s ＃ 设置流复制主机发送数据的超时时间
max_connections = 100 # 这个设置要注意下，从库的max_connections必须要大于主库的
配置完两个文件后重启服务器。
systemctl restart postgresql-10
3.测试94能否连接93数据库。在94上运行如下命令：
psql -h 192.168.20.93 -U postgres
看看是否能进入数据库。若可以，则正常。
2.2 从服务器
1.从主节点拷贝数据到从节点
su - postgres
rm -rf /var/lib/pgsql/10/data/* #先将data目录下的数据都清空
pg_basebackup -h 192.168.20.93 -U replica -X stream -P # 从93拷贝数据到94（基础备份）
2.配置recovery.conf
复制/usr/pgsql-9.4/share/recovery.conf.sample 到 /opt/pgsql/data/recovery.conf
cp /usr/pgsql-10/share/recovery.conf.sample /usr/pgsql-10/share/recovery.conf
修改recovery.conf
standby_mode = on # 说明该节点是从服务器
primary_conninfo = 'host=192.168.20.93 port=5432 user=replica password=replica' # 主服务器的信息以及连接的用户
recovery_target_timeline = 'latest'
3.配置postgresql.conf
wal_level = hot_standby
max_connections = 1000 ＃ 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on ＃ 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 10s # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈
配置完后重启从服务器
systemctl restart postgresql-10
- 验证是否部署成功
在主节点上执行：
select client_addr,sync_state from pg_stat_replication;
结果如下：
postgres=# select client_addr,sync_state from pg_stat_replication;
| client_addr | sync_state |
|---|---|
| 192.168.20.94 | async |
(1 行记录)
说明94是从服务器，在接收流，而且是异步流复制。
此外，还可以分别在主、从节点上运行 ps aux | grep postgres 来查看进程：
主服务器（93）上：
postgres 262270 0.0 0.0 337844 2832 ? Ss 10:14 0:00 postgres: wal sender process replica 192.168.20.94(13059) streaming 0/A002A88
可以看到有一个 wal sender 进程。
从服务器（94）上：
postgres 569868 0.0 0.0 384604 2960 ? Ss 10:14 0:02 postgres: wal receiver process streaming 0/A002B60
可以看到有一个 wal receiver 进程。
至此，PostgreSQL主从流复制安装部署完成。
在主服务器上插入数据或删除数据，在从服务器上能看到相应的变化。从服务器上只能查询，不能插入或删除。
版权声明: 作者：穆书伟
github出处：https://github.com/sanshengshui
个人博客出处：https://www.mushuwei.cn/
