实践部署 repmgr+pg9.6构建高可用性集群

àì夳堔傛蜴生んèń 2022-05-22 10:51 255阅读 0赞

环境：

vlnx107001.firstshare.cn      primary

vlnx107002.firstshare.cn      slave

centos7

postresql9.6

repmgr4.0.5

开启防火墙

firewall-cmd --zone=public --add-port=5432/tcp --permanent

重新载入

firewall-cmd --reload

查看

firewall-cmd --zone=public --query-port=5432/tcp

**一** **、pg9.6 安装 **

[https://www.postgresql.org/download/linux/redhat/][https_www.postgresql.org_download_linux_redhat]

1、安装存储库rpm包

yum install [https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86\_64/pgdg-centos96-9.6-3.noarch.rpm][https_download.postgresql.org_pub_repos_yum_9.6_redhat_rhel-7-x86_64_pgdg-centos96-9.6-3.noarch.rpm]

2、安装客户端软件包

yum install postgresql96

3、安装服务器软件包

yum install postgresql96-server

![70][]

4、初始化数据库启用自动启动

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

systemctl enable postgresql-9.6

![70 1][]

systemctl start postgresql-9.6

安装完毕后

默认数据目录为 ：/var/lib/pgsql/9.6/data/

bin二进制文件目录：/usr/pgsql-9.6/bin

**或者**

**直接RPM下载**

如果您不能或不想使用基于yum的安装方法，则yum存储库中的所有RPM都可用于   [直接下载][Link 1]   和手动安装。

[https://yum.postgresql.org/9.6/redhat/rhel-7-x86\_64/repoview/][https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_repoview]

\[root@vlnx107001 zhaowzh\]\# wget [https://yum.postgresql.org/9.6/redhat/rhel-7-x86\_64/postgresql96-9.6.8-1PGDG.rhel7.x86\_64.rpm][https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_postgresql96-9.6.8-1PGDG.rhel7.x86_64.rpm]

\[root@vlnx107001 zhaowzh\]\# wget [https://yum.postgresql.org/9.6/redhat/rhel-7-x86\_64/postgresql96-contrib-9.6.8-1PGDG.rhel7.x86\_64.rpm][https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_postgresql96-contrib-9.6.8-1PGDG.rhel7.x86_64.rpm]

\[root@vlnx107001 zhaowzh\]\# wget [https://yum.postgresql.org/9.6/redhat/rhel-7-x86\_64/postgresql96-libs-9.6.8-1PGDG.rhel7.x86\_64.rpm][https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_postgresql96-libs-9.6.8-1PGDG.rhel7.x86_64.rpm]

\[root@vlnx107001 zhaowzh\]\# wget [https://yum.postgresql.org/9.6/redhat/rhel-7-x86\_64/postgresql96-server-9.6.8-1PGDG.rhel7.x86\_64.rpm][https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_postgresql96-server-9.6.8-1PGDG.rhel7.x86_64.rpm]

\[root@vlnx107001 zhaowzh\]\# rpm -i postgresql96-libs-9.6.8-1PGDG.rhel7.x86\_64.rpm

\[root@vlnx107001 zhaowzh\]\# rpm -i postgresql96-9.6.8-1PGDG.rhel7.x86\_64.rpm

\[root@vlnx107001 zhaowzh\]\# rpm -i postgresql96-contrib-9.6.8-1PGDG.rhel7.x86\_64.rpm

\[root@vlnx107001 zhaowzh\]\# rpm -i postgresql96-server-9.6.8-1PGDG.rhel7.x86\_64.rpm

**二、安装repmgr**

\[root@vlnx107001 zhaowzh\]\# wget [https://yum.postgresql.org/9.6/redhat/rhel-7-x86\_64/repmgr96-4.0.5-1.rhel7.x86\_64.rpm][https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_repmgr96-4.0.5-1.rhel7.x86_64.rpm]

\[root@vlnx107001 zhaowzh\]\# wget [https://yum.postgresql.org/9.6/redhat/rhel-7-x86\_64/repmgr96-debuginfo-4.0.5-1.rhel7.x86\_64.rpm][https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_repmgr96-debuginfo-4.0.5-1.rhel7.x86_64.rpm]

\[root@vlnx107001 zhaowzh\]\# rpm -i repmgr96-4.0.5-1.rhel7.x86\_64.rpm

\[root@vlnx107001 zhaowzh\]\# rpm -i repmgr96-debuginfo-4.0.5-1.rhel7.x86\_64.rpm

** 三、构建 ssh 免密互通(以 vlnx107001上操作为例）**

\[postgres@vlnx107001 ~\]$ ssh-keygen -t rsa

**注** 不要指定密码，即 空密码即可

![70 2][]

将密钥传递到 slave服务器上并验证

\[postgres@vlnx107001 ~\]$ ssh-copy-id -i .ssh/id\_rsa.pub postgres@vlnx107002.firstshare.cn

\[postgres@vlnx107001 ~\]$ ssh [postgres@vlnx107002.firstshare.cn][postgres_vlnx107002.firstshare.cn] date

![70 3][]

**四、编辑 ** **pg\_hba.conf 并重新加载配置**

**local   repmgr          repmgr                                  trust**

**host    repmgr          repmgr          127.0.0.1/32            trust**

**host    repmgr          repmgr          172.31.107.2/32         trust**

**host    repmgr          repmgr          172.31.107.1/32         trust**

**host    postgres        repmgr          172.31.107.2/32         trust**

**local   replication     repmgr                                  trust**

**host    replication     repmgr          127.0.0.1/32            trust**

**host    replication     repmgr          172.31.107.2/32         trust**

**host    replication     repmgr          172.31.107.1/32         trust**

![70 4][]

\[postgres@vlnx107001 data\]$  pg\_ctl -D $PGDATA reload

**五、创建 repmgr账号用来管理 集群维护等操作**

为repmgr元数据创建专用的PostgreSQL超级用户帐户和数据库

\[postgres@vlnx107001 ~\]$ createuser --login --superuser repmgr

\[postgres@vlnx107001 ~\]$ createdb repmgr -O repmgr

验证是否互通

\[postgres@vlnx107002 ~\]$ psql -h vlnx107001.firstshare.cn -U repmgr -d postgres -c "select version()"

\[postgres@vlnx107002 postgresql\]$ psql -h  vlnx107001.firstshare.cn -U repmgr -d repmgr -c "select version()"

![70 5][]

**六、编辑 repmgr.conf 配置文件**

\[root@vlnx107001 ~\]\# vim /etc/repmgr/9.6/repmgr.conf

\#-----------------------------------------------------------------------------

\#zhaowenzhong

\#----------------------------------------------------------------------------

log\_file='/var/log/repmgr/repmgr.log'

pg\_bindir=/usr/pgsql-9.6/bin

promote\_command='/usr/pgsql-9.6/bin/repmgr standby promote -f /etc/repmgr/9.6/repmgr.conf'

follow\_command='/usr/pgsql-9.6/bin/repmgr standby follow -f /etc/repmgr/9.6/repmgr.conf'

cluster=vpgs107001

node\_id=107001

node\_name=vlnx107001

conninfo='host=172.31.107.1 dbname=repmgr user=repmgr'

data\_directory='/var/lib/pgsql/9.6/data/'

service\_start\_command = 'sudo systemctl start postgresql-9.6'

service\_stop\_command = 'sudo systemctl stop postgresql-9.6'

service\_restart\_command = 'sudo systemctl restart postgresql-9.6'

service\_reload\_command = 'sudo systemctl reload postgresql-9.6'

ssh\_options=-o "StrictHostKeyChecking no"

![70 6][]

**七、注册主服务**

要启用repmgr以支持复制群集，主节点必须注册repmgr。这将安装repmgr 扩展和元数据对象，并为主服务器添加元数据记录：

\[postgres@vlnx107001 ~\]$ repmgr -f /etc/repmgr/9.6/repmgr.conf primary register

![70 7][]

验证集群的状态：

\[postgres@vlnx107001 ~\]$ repmgr -f /etc/repmgr/9.6/repmgr.conf cluster show

![70 8][]

repmgr元数据表中

![70 9][]

复制群集中的每个服务器都有自己的记录。如果repmgrd 正在使用，则节点的状态或角色更改时会更新字段upstream\_node\_id，active和 type。

**启动监控**

\[postgres@vlnx107001 ~\]$ repmgrd -f /etc/repmgr/9.6l/repmgr.conf --verbose --monitoring-history > /var/log/repmgr/repmgr.log 2>&1 &

**八、克隆备用服务器**

vlnx107002 上安装 repmgr 省略 （同上）

配置 repmgr.conf

\#----------------------------------------------------------------------------

\#zhaowenzhong

\#----------------------------------------------------------------------------

log\_file='/var/log/repmgr/repmgr.log'

pg\_bindir=/usr/pgsql-9.6/bin

promote\_command='/usr/pgsql-9.6/bin/repmgr standby promote -f /etc/repmgr/9.6/repmgr.conf'

follow\_command='/usr/pgsql-9.6/bin/repmgr standby follow -f /etc/repmgr/9.6/repmgr.conf'

cluster=vpgs107001

node\_id=107002

node\_name=vlnx107002

conninfo='host=172.31.107.2 dbname=repmgr user=repmgr'

data\_directory='/var/lib/pgsql/9.6/data/'

service\_start\_command = 'sudo systemctl start postgresql-9.6'

service\_stop\_command = 'sudo systemctl stop postgresql-9.6'

service\_restart\_command = 'sudo systemctl restart postgresql-9.6'

service\_reload\_command = 'sudo systemctl reload postgresql-9.6'

ssh\_options=-o "StrictHostKeyChecking no"

![70 10][]

使用--dry-run选项检查可以克隆的待机状态：

\[postgres@vlnx107002 ~\]$ repmgr -h vlnx107001.firstshare.cn -U repmgr -d repmgr -f /etc/repmgr/9.6/repmgr.conf -F standby clone --dry-run

![70 11][]

开始克隆

\[postgres@vlnx107002 ~\]$ repmgr -h vlnx107001.firstshare.cn -U repmgr -d repmgr -f /etc/repmgr/9.6/repmgr.conf  standby clone

![70 12][]

![70 13][]

克隆从主PostgreSQL的数据目录中的文件节点 使用PostgreSQL的pg\_basebackup效用。 将会自动创建一个recovery.conf文件，其中包含从此主服务器开始流式传输的正确参数。

**注意： 默认情况下，主数据目录中的任何配置文件都将被复制到备用数据库。通常这些将是postgresql.conf， postgresql.auto.conf，pg\_h** **ba.conf和 pg\_ident.conf。这些可能需要在待机启动之前进行修改**

**开启slave**

\[postgres@vlnx107002 data\]$ /usr/pgsql-9.6/bin/pg\_ctl -D $PGDATA -l /var/lib/pgsql/9.6/data/pg\_log/logfile start

**在备用服务器节点上启动监控进程**

\[postgres@vlnx107002 ~\]$ repmgrd -f /etc/repmgr/9.6l/repmgr.conf --verbose --monitoring-history > /var/log/repmgr/repmgr.log 2>&1 &

![70 14][]

**九、验证是否数据同步**

repmgr=\# SELECT \* FROM pg\_stat\_replication;

![70 15][]

**注册备用服务器：**

\[postgres@vlnx107002 pg\_log\]$ repmgr -f /etc/repmgr/9.6/repmgr.conf standby register

通过在待机状态下执行repmgr cluster show来检查节点是否已注册：

\[postgres@vlnx107002 pg\_log\]$ repmgr -f /etc/repmgr/9.6/repmgr.conf cluster show

![70 16][]

[https_www.postgresql.org_download_linux_redhat]: https://www.postgresql.org/download/linux/redhat/
[https_download.postgresql.org_pub_repos_yum_9.6_redhat_rhel-7-x86_64_pgdg-centos96-9.6-3.noarch.rpm]: https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
[70]: /images/20220522/0255902f4725470db73682c0484c5894.png
[70 1]: /images/20220522/1883b1a630884e788ee9c8b7a9c5d3a4.png
[Link 1]: https://yum.postgresql.org/rpmchart.php
[https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_repoview]: https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/repoview/
[https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_postgresql96-9.6.8-1PGDG.rhel7.x86_64.rpm]: https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/postgresql96-9.6.8-1PGDG.rhel7.x86_64.rpm
[https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_postgresql96-contrib-9.6.8-1PGDG.rhel7.x86_64.rpm]: https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/postgresql96-contrib-9.6.8-1PGDG.rhel7.x86_64.rpm
[https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_postgresql96-libs-9.6.8-1PGDG.rhel7.x86_64.rpm]: https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/postgresql96-libs-9.6.8-1PGDG.rhel7.x86_64.rpm
[https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_postgresql96-server-9.6.8-1PGDG.rhel7.x86_64.rpm]: https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/postgresql96-server-9.6.8-1PGDG.rhel7.x86_64.rpm
[https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_repmgr96-4.0.5-1.rhel7.x86_64.rpm]: https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/repmgr96-4.0.5-1.rhel7.x86_64.rpm
[https_yum.postgresql.org_9.6_redhat_rhel-7-x86_64_repmgr96-debuginfo-4.0.5-1.rhel7.x86_64.rpm]: https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/repmgr96-debuginfo-4.0.5-1.rhel7.x86_64.rpm
[70 2]: /images/20220522/cb1ee2477e374115b734ed71369b0d91.png
[postgres_vlnx107002.firstshare.cn]: mailto:postgres@vlnx107002.firstshare.cn
[70 3]: /images/20220522/e63df24dabbb439ba51c18535614093c.png
[70 4]: /images/20220522/65b891a2bb2d495db6c7bf026a9d0912.png
[70 5]: /images/20220522/c44b23fb7361463c89d394e4a179de90.png
[70 6]: /images/20220522/ff9bf2a15390445990654dd504afe9b4.png
[70 7]: /images/20220522/a03afd5c90e84262939e5c196dd2218a.png
[70 8]: /images/20220522/2a9f5ca2638d47d5afd56171c9b8b26e.png
[70 9]: /images/20220522/0d4ab0faeef6403da6008d8b9d660eb3.png
[70 10]: /images/20220522/6304192dc0fe48208fb3e544d10842e8.png
[70 11]: /images/20220522/473c5f7333b84ee5956eae8caa052998.png
[70 12]: /images/20220522/b84bbc11f98144c5a412a97435f3f0da.png
[70 13]: /images/20220522/0d8c9be3525c4d3b865c8341276a80b7.png
[70 14]: /images/20220522/93af8f159f7144e9aaf05a8a7be74141.png
[70 15]: /images/20220522/d6ab3308389b448caffc57984abfe6ec.png
[70 16]: /images/20220522/9f2be77de1af4121ad42797cce5b112e.png