Postgresql主从配置

迷南。 2023-07-15 04:54 50阅读 0赞

**一、   ****简介**

PostgreSql在9.0之后引入了主从的流复制机制，所谓流复制，就是从服务器通过tcp流从主服务器中同步相应的数据。这样当主服务器数据丢失时从服务器中仍有备份。

与基于文件日志传送相比，流复制允许保持从服务器更新。 从服务器连接主服务器，其产生的流WAL记录到从服务器， 而不需要等待主服务器写完WAL文件(关于Wal可以看看官方的说法[https://www.postgresql.org/docs/10/static/different-replication-solutions.html][https_www.postgresql.org_docs_10_static_different-replication-solutions.html])。

PostgreSQL流复制默认是异步的。在主服务器上提交事务和从服务器上变化可见之间有一个小的延迟，这个延迟远小于基于文件日志传送，通常1秒能完成。如果主服务器突然崩溃，可能会有少量数据丢失。

同步复制必须等主服务器和从服务器都写完WAL后才能提交事务。这样在一定程度上会增加事务的响应时间。

配置同步复制仅需要一个额外的配置步骤： [synchronous\_standby\_names][synchronous_standby_names]必须设置为一个非空值。synchronous\_commit也必须设置为on。

注: 主从服务器所在节点的系统、环境等最好一致。PostgreSQL版本也最好一致，否则可能会有问题。

**二、   ****安装部署**（使用Ubuntu的普通用户安装部署）

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> 角色 </td> 
 <td style="vertical-align:top;"> IP地址 </td> 
 <td style="vertical-align:top;"> 系统版本 </td> 
 <td style="vertical-align:top;"> 数据库版本 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 主服务器 </td> 
 <td style="vertical-align:top;"> 192.168.100.71 </td> 
 <td style="vertical-align:top;"> Ubuntu18.04&nbsp; amd64 </td> 
 <td style="vertical-align:top;"> PostgreSQL10 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 从服务器 </td> 
 <td style="vertical-align:top;"> 192.168.100.70 </td> 
 <td style="vertical-align:top;"> Ubuntu18.04&nbsp; amd64 </td> 
 <td style="vertical-align:top;"> PostgreSQL10 </td> 
 </tr> 
 </tbody> 
</table>

** **

**注：18.04版本默认的PostgreSQL版本是10，要安装其他版本可以采用编译的方式或者apt-get 指定版本号安装**

** **

1、两台服务器都要安装PostgreSQL

sudo apt-get updatel

sudo apt-get install postgresql

sudo vi /etc/postgresql/10/main/postgresql.conf

在文件末尾添加或者找到该行，将peer改为trust

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>local</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>all</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>all</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>trust</code> </td> 
 </tr> 
 </tbody> 
</table>

重启PostgreSQL服务

sudo service postgresql restartt

这样PostgreSQL就安装完毕

2、配置主服务器

切换成postgres用户

sudo su – postgres

进入数据库，创建一个普通的数据库用户用户进行主从同步（只需赋予登录和复制的权限即可）

![format_png][]

postgres=\# create role guoxm login replication encrypted password 'guoxm'

然后退出

postgres=\# \\q

在exit退出到原用户

然后，修改postgresql的pg\_hba.conf，配置运行刚刚创建的guoxm用户可以进行连接

sudo vi /etc/postgresql/10/main/pg\_hba.conf

在文件尾添加如下两行

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 </td> 
 <td> <code>host&nbsp;&nbsp;&nbsp;</code><code>all</code>&nbsp;<code>all</code>&nbsp;&nbsp;<code>192.168</code><code>.</code><code>100.70</code><code>/</code><code>32</code>&nbsp;&nbsp;&nbsp;<code>trust&nbsp;&nbsp; #运行</code><code>70</code><code>服务器连接到本机</code> &nbsp; <code>host&nbsp;&nbsp;&nbsp; replication&nbsp; guoxm&nbsp;&nbsp;&nbsp;&nbsp;</code><code>192.168</code><code>.</code><code>100.70</code><code>/</code><code>32</code>&nbsp;&nbsp;&nbsp;<code>md</code><code>5</code>&nbsp;&nbsp;&nbsp;<code>#运行guoxm用户在</code><code>70</code><code>上复制本机数据</code> </td> 
 </tr> 
 </tbody> 
</table>

注：第二个的角色必须是replication

最后，再次配置postgresql.conf

sudo vi /etc/postgresql/10/main/ postgresql.conf

这次需要配置的是主机的角色

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 </td> 
 <td> <code>listen_addresses = ‘*’&nbsp;&nbsp; #监听所有ip</code> &nbsp; <code>archive_mode = on&nbsp;&nbsp; #开启归档模式</code> &nbsp; <code>archive_command = ‘cp %p /var/lib/postgresql/</code><code>10</code><code>/main/%f’&nbsp;&nbsp; #归档命令</code> &nbsp; <code>wal_level = hot_standby&nbsp;&nbsp;&nbsp; #热备模式</code> &nbsp; <code>max_wal_senders =&nbsp;</code><code>1</code>&nbsp;&nbsp;&nbsp;<code>#最多有</code><code>1</code><code>个流复制连接</code> &nbsp; <code>wal_sender_timeout =&nbsp;</code><code>60</code><code>s&nbsp;&nbsp;&nbsp; #流复制超时时间</code> &nbsp; <code>max_connections =&nbsp;</code><code>100</code>&nbsp;&nbsp;&nbsp;<code>#最大连接时间，必须要小于从库的配置</code> </td> 
 </tr> 
 </tbody> 
</table>

保存退出后，重启数据库服务

sudo service postgresql restartt

3、配置从服务器

首先，先测试一下是否能连接主服务器

psql -h 192.168.100.70  -U  postgres

如果可以，说明主机配置正常，否则检查一下主机的pg\_hba.conf配置

进入到postgres用户

sudo su - postgres

先清空main目录下的数据（main文件夹就是PostgreSQL数据的存储文件夹）

rm -rf 10/main/\*

或者

rm -rf /var/lib/postgresql/10/main/\*

因为PostgreSQL的home目录就在/var/lib/postgresql中

所以上面两条命令到达的效果是一样的

然后，开始从主服务器上copy数据到本机上，这一步叫做“基础备份”

pg\_basebackup -h 192.168.100.71 -U guoxm –D 10/main/ -X stream –P

或者

pg\_basebackup -F p --progress -D 10/main/ -h 192.168.100.71 -p 5432 -U guoxm --password

pg\_basebackup是PostgreSQL自带的基础备份工具

这样，基础备份就完成了

然后，创建recovery.conf用于从库恢复从主库获取的数据

vi recovery.conf

然后编辑如下配置

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 </td> 
 <td> <code>standby_mode = on&nbsp;&nbsp; #表示该节点是从库</code> &nbsp; <code>primary_conninfo = ‘host=</code><code>192.168</code><code>.</code><code>100.71</code>&nbsp;&nbsp;<code>post=</code><code>5432</code>&nbsp;&nbsp;<code>user=guoxm&nbsp; password=guoxm’#从机信息和连接用户</code> &nbsp; <code>recovery_target_timelint = ‘latest’ #说明恢复到最新状态</code> </td> 
 </tr> 
 </tbody> 
</table>

保持并退出后，

复制或在移动到main文件夹中

cp recovery.conf   10/main/

或者

mv recovery.conf   10/main/

然后，exit退出到原用户

最后，配置从机的postgresql.conf文件

sudo vi /etc/postgresql/10/main/postgresql.conf

在文件末尾添加如下配置

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 </td> 
 <td> <code>wal_level = hot_standby&nbsp;&nbsp;&nbsp; #热备模式</code> &nbsp; <code>max_connections =&nbsp;</code><code>300</code>&nbsp;&nbsp;&nbsp;<code>#最大连接时间，必须要小于从库的配置</code> &nbsp; <code>hot_standby = on #说明这台机器不仅用于数据归档，还可以用于数据查询</code> &nbsp; <code>max_standby_streaming_delay =&nbsp;</code><code>30</code><code>s #流备份的最大延迟时间</code> &nbsp; <code>wal_receiver_status_interval =&nbsp;</code><code>10</code><code>s&nbsp; #向主机汇报本机状态的间隔时间</code> &nbsp; <code>hot_standby_feedback = on #r出现错误复制，向主机反馈</code> </td> 
 </tr> 
 </tbody> 
</table>

保存并退出

到这里，从机配置完成

重启数据库服务

sudo service postgresql restart

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

在主机上，切换到postgres用户

sudo su – postgres

进入数据库

![format_png 1][]

执行如下查询

select client\_addr,sync\_state from pg\_stat\_replication;

如果看到如下查询结果

![format_png 2][]

说明192.168.100.70是从服务器，在接收流，而且是异步流复制。说明主从配置成功

注：必须切换到postgres用户下进行查询

此外，还可以查看相关进程来验证是否配置成功

在主机、从机上分别执行

ps aux | grep postgres

主机上可以看到正在向从机发送数据流

![format_png 3][]

从机上可以看到正在接收数据流

![format_png 4][]

到此，主从配置完成；

参考链接：

[https://www.jianshu.com/p/2d07339774c0][https_www.jianshu.com_p_2d07339774c0]

[https://www.cnblogs.com/yjf512/p/4499547.html][https_www.cnblogs.com_yjf512_p_4499547.html]

[https_www.postgresql.org_docs_10_static_different-replication-solutions.html]: https://www.postgresql.org/docs/10/static/different-replication-solutions.html
[synchronous_standby_names]: https://link.jianshu.com/?t=http:/www.postgres.cn/docs/9.4/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES
[format_png]: /images/20230529/2e46994fd2d7494da437c3a67aa99f9b.png
[format_png 1]: /images/20230529/d1767f7aa4b44453b0033f1a30ad739e.png
[format_png 2]: /images/20230529/80441f933f9e40fba1edae2f7b6b7703.png
[format_png 3]: /images/20230529/5fd5d54628db4094aa4953ea54f0838b.png
[format_png 4]: /images/20230529/e132dbed9e9a4dae8037914856b54f38.png
[https_www.jianshu.com_p_2d07339774c0]: https://www.jianshu.com/p/2d07339774c0
[https_www.cnblogs.com_yjf512_p_4499547.html]: https://www.cnblogs.com/yjf512/p/4499547.html