postgresql主从复制配置

爱被打了一巴掌 2021-12-01 11:48 441阅读 0赞

postgresql主从复制是一种高可用解决方案，可以实现读写分离。postgresql主从复制是基于xlog来实现的，主库开启日志功能，从库根据主库xlog来完成数据的同步。

主从复需要注意的地方：

*  启动从库之前，不能执行初始化。
 *  启动从库之前，需要通过base\_backup从主服务器上同步配置与数据。
 *  启动从库之前，需要对同步之后的配置文件进行修改。
 *  启动从库之前，需要设置一个恢复的配置文件。
 *  从库只能读，不能写。

下面介绍主从复制的实现，这里以两台虚拟机为例，主节点IP是192.168.56.201，从节点IP是192.168.56.202，这里两台机器都是通过源码编译安装的方式安装的postgresql，版本是11.4。编译安装指定的前缀是/usr/local，因此安装完成，可执行程序会在/usr/local/bin目录下。

![20190720165450953.png][]

首先需要在主库上初始化数据库，并启动数据库服务。

启动的时候，不能以root用户来启动。

![20190720170917145.png][]

编译安装不会创建postgres用户，因此我们需要先创建postgres用户和用户组。 我们会将postgresql数据存储路径设置在/home/postgres/data下。

groupadd postgres  
useradd -g postgres postgres

切换用户，然后初始化数据库。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70][]

初始化成功之后，会有个提示，如何启动数据库，按照提示命令，我们启动数据库。

/usr/local/bin/pg\_ctl -D /home/postgres/data -l logfile start

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 1][]

这里启动数据库之后，我们登陆数据库，做两件事情：准备一些数据，将来从节点同步之后，用来做数据验证。创建一个admin/123456的用户，用来做主从复制。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 2][]

这样在主库上的操作就完成了，接下来就是修改配置文件，然后重启主库。

修改pg\_hba.conf，增加刚才创建的用户到文件末尾，method指定为md5，表示密码开启md5验证。

![20190720172018880.png][]

修改postgresql.conf，开启注释，并修改以下配置：

listen\_addresses="\*"  
wal\_level=hot\_standby  
max\_wal\_senders=2  
wal\_keep\_segments=64  
max\_connections=100

重启主库，至此，完成主库的所有准备工作：

![20190720172708936.png][]

/\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

下面就是配置和启动从库了，前面注意事项里面提到，不能初始化从库，因为我们需要首先从主库备份数据，备份之后，从库的/home/postgres/data里面的数据和配置信息就和主库一致了。

首先从库也需要postgres/postgres用户组和用户，先创建：

groupadd postgres  
useradd -g postgres postgres

之后，切换到postgres用户，进行数据备份和启动操作。

首先是利用base\_backup命令进行备份：

/usr/local/bin/pg\_basebackup -h 192.168.56.201 -p 5432 -U admin -F p -P -D /home/postgres/data

![20190720173218958.png][]

因为是从库访问，而且是用的admin用户，因此需要输入密码。这里显示备份成功。

因为配置postgresql.conf是从主库同步过来的，这里需要修改一些配置，改为从库的配置：

\#wal\_level=hot\_standby                      \#从库不需要这个配置  
\#max\_wal\_senders=2                         \#同上  
\#wal\_keep\_segments=64                   \#同上  
hot\_standby=on                                  \#开启hot\_standby模式  
max\_standby\_streaming\_delay=30s  \#可选，流复制最大延迟  
wal\_receiver\_status\_interval=10s       \#可选，向主库报告状态的最大间隔时间  
hot\_standby\_feedback=on                  \#可选，查询冲突时向主库反馈  
max\_connections=1000                      \#最大连接数一般大于主库就行

还需要准备一个恢复配置文件，这个文件在安装postgresql时，会生成到/usr/local/share/postgresql目录下，名字是recovery.conf.sample。我们复制并修改名称为recovery.conf并放置在/home/postgres/data目录下，修改配置：

recovery\_target\_timeline = 'latest'  
standby\_mode = on  
primary\_conninfo = 'host=192.168.56.201 port=5432 user=admin password=123456'

这三个配置很直观，recovery\_target\_timeline='latest'表示恢复最新的数据，standby\_mode表示从节点的角色是备选，primay\_conninfo表示主库连接信息。

至此，从库的配置工作准备完成，接着就可以启动数据库了。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 3][]

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

主从复制到此就配置完成了，接下来就是验证阶段：

1、从主从机器运行的进程验证：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 4][]

主节点服务器会增加一个walsender进程

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 5][]

从节点服务器增加一个walreceiver进程

2、从数据上验证：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 6][]

主库在首次启动的时候，没有做主从配置之前，就插入了4条记录在test数据库xx\_user表中。如今再次插入一条数据，也显示成功，查询会显示5条记录。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 7][]

从库在首次启动之后，数据是从主库备份过来的，第一次进入查找就有4条记录。等主库插入一条记录之后，再次查看是5条记录，从库数据均同步成功，表示主从复制配置正确。

最后我们在从库中做插入操作，显示操作失败，因为从库是只读的，不能做增删改的写操作，只能查询。

3、这里可以从/usr/local/bin/pg\_controldata /home/postgres/data命令的结果状态中可以验证，主从关系，主库的集群状态是in production，从库是in archive recovery，当我们的主库崩溃，我们可以切换从库为主库。这时候主库状态是shut down，而从库是in production。这里我们模拟停掉主库。

pg\_ctl stop -m fast

马上在从库上切换从库为主库：

pg\_ctl promote

这里显示了从库的状态由in archive recovery 变为in production的截图：

正常从库的状态：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 8][]

主库shutdown，从库执行切换主库操作之后：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 9][]

[20190720165450953.png]: /images/20211201/8dea71ade8e4410094484014bc456214.png
[20190720170917145.png]: /images/20211201/1cb30dfb44354262a0fde04286f70258.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70]: /images/20211201/50d41d3be10b430d8b7224c9a969b05b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 1]: /images/20211201/e4bbf7c170284b27aaf5cf022ef245fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 2]: /images/20211201/fe454c0d66374b8abdc3c79d11c2a9b1.png
[20190720172018880.png]: /images/20211201/aace65a420064de6b04f59db5d908cc4.png
[20190720172708936.png]: /images/20211201/d9bc48c085a84a998e35ed2ee76cd8a8.png
[20190720173218958.png]: /images/20211201/84791ef3aa264a76bde2047c029813cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 3]: /images/20211201/cb749d38bfad4afa8fd84c6dd5d745c0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 4]: /images/20211201/e9bf68692f104648b9079268ebf45495.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 5]: /images/20211201/e7af4da8579f462492dff1e21781da41.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 6]: /images/20211201/1b6aa42c2ce245a0af2800299d448d64.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 7]: /images/20211201/48492b6b0b0a4c26b3b8cdbbc0f050fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 8]: /images/20211201/3df4746d501641208bb9af39de7052ec.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 9]: /images/20211201/6e38cee793f940d1bdfefb8befd81941.png