记一次 Centos7.x PostgreSql 数据库主从同步环境搭建、配置

桃扇骨 2023-03-01 09:05 88阅读 0赞

一、数据库安装

根据自身环境需要选择安装、操作

1、yum 指定目录安装

[https://blog.csdn.net/llwy1428/article/details/105143053][https_blog.csdn.net_llwy1428_article_details_105143053]

2、yum 直接安装

[https://blog.csdn.net/llwy1428/article/details/102486414][https_blog.csdn.net_llwy1428_article_details_102486414]

3、编译安装

[https://blog.csdn.net/llwy1428/article/details/95444151][https_blog.csdn.net_llwy1428_article_details_95444151]

4、PostgreSql 基本操作

[https://blog.csdn.net/llwy1428/article/details/102598732][https_blog.csdn.net_llwy1428_article_details_102598732]

5、Centos7 yum 安装、配置 PgAdmin4

[https://blog.csdn.net/llwy1428/article/details/102486511][https_blog.csdn.net_llwy1428_article_details_102486511]

6、Centos7 PostgreSql 数据库安装扩展

[https://blog.csdn.net/llwy1428/article/details/105167524][https_blog.csdn.net_llwy1428_article_details_105167524]

7、Centos7 PostgreSql 数据库使用FDW扩展

[https://blog.csdn.net/llwy1428/article/details/106291669][https_blog.csdn.net_llwy1428_article_details_106291669]

8、Centos7 postgresql v11 安装时序数据库 TimescaleDB

[https://blog.csdn.net/llwy1428/article/details/106357900][https_blog.csdn.net_llwy1428_article_details_106357900]

9、Windows10 使用 PgAdmin 备份 PostgreSql 数据库

[https://blog.csdn.net/llwy1428/article/details/107031697][https_blog.csdn.net_llwy1428_article_details_107031697]

二、虚拟机安装配置

1、选择（或安装）两台 Centos7.x 虚拟机

系统下载：[http://archive.kernel.org/centos-vault/7.4.1708/isos/x86\_64/][http_archive.kernel.org_centos-vault_7.4.1708_isos_x86_64]

2、系统安装：[https://blog.csdn.net/llwy1428/article/details/89328381][https_blog.csdn.net_llwy1428_article_details_89328381]

3、网络配置：[https://blog.csdn.net/llwy1428/article/details/85058028][https_blog.csdn.net_llwy1428_article_details_85058028]

说明：此处以 192.168.11.11 为主数据库节点；192.168.11.12 为从数据库节点

4、防火墙：[https://blog.csdn.net/llwy1428/article/details/99676257][https_blog.csdn.net_llwy1428_article_details_99676257]

两台虚拟机防火墙均开启端口：5432

]# firewall-cmd --add-port=5432/tcp --permanent
    ]# firewall-cmd --reload

三、安装、配置

说明：此处以 192.168.11.11 为主数据库；192.168.11.12 为从数据库

1、安装数据库

在主节点安装 PostgreSql 数据库

安装过程：[https://blog.csdn.net/llwy1428/article/details/105143053][https_blog.csdn.net_llwy1428_article_details_105143053]

2、在主数据库 192.168.11.11 创建用来备份的账号并设置密码

系统切换为 postgres 用户

[root@localhost ~]# su - postgres

打开命令行

-bash-4.2$ psql

创建数据库用户并设置密码（用户名中字母要小写）

CREATE USER 【用户名】 REPLICATION LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD '【密码】';

postgres=# CREATE USER dbbak REPLICATION LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD '123456';

3、修改 postgresql.conf 配置文件

需要修改的参数：

（1）wal\_level                    流复制最高等级replica

（2）max\_wal\_senders      流复制进程数，一般为主机

（3）wal\_keep\_segments  保持WAL个数、根据是否有批量写入可适当增减

（4）hot\_standby                是/否 允许在恢复期间进行查询

[其它参数讲解][Link 1]

切换用户并编辑配置文件

[root@localhost ~]# su - postgres
    -bash-4.2$ vim /data/pgdata/postgresql.conf

wal\_level = replica

![20200723150115429.png][]

max\_wal\_senders = 10

wal\_keep\_segments = 64

hot\_standby = on

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70][]

4、编辑 hosts 映射

[root@localhost ~]# vim /etc/hosts

![20200723151251804.png][]

5、修改 pg\_hda.conf 配置文件

-bash-4.2$ vim /data/pgdata/pg_hba.conf

添加热备用户  dbbak  的连接配置

注意：此处应限制为从节点的连接，该配置的第三列配置为前文创建的热备用户：“dbbak”

host    replication     dbbak           db.slave.bak           md5

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 1][]

说明：最后一行第四列是在  /etc/hosts 中配置的从节点的  主机名和 IP 的映射

6、重启主服务器的数据库服务

[root@localhost ~]# systemctl restart postgresql-11

7、在从节点上安装 PostgreSql 数据库服务

创建目录并赋予权限（不需要初始化数据库）

8、使用命令，将主节点的数据文件同步到从节点

/usr/pgsql-11/bin/pg\_basebackup -h 【主服务器地址】 -p 【主服务器端口】 -D 【从服务器数据目录】 -P -U 【主服务器热备用户】

从节点切换用户

[root@localhost ~]# su - postgres

在从节点上执行

-bash-4.2$ /usr/pgsql-11/bin/pg_basebackup -h 192.168.11.11 -p 5432 -D /data/pgdata/ -P -U dbbak

输入主节点服务器的密码

Password: \*\*\*\*\*\*\*\*\*\*\*

查看从节点的数据目录：

-bash-4.2$ ll /data/pgdata/

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 2][]

9、修改从节点上的 postgresql.conf 配置文件（可选）

-bash-4.2$ vim /data/pgdata/postgresql.conf

修改从节点数据库端口为 5433

![20200723162255157.png][]

10、创建配置文件 recovery.conf

-bash-4.2$ vim /data/pgdata/recovery.conf

写入内容

standby_mode ='on'
    primary_conninfo ='host=192.168.11.11 port=5432 user=dbbak password=123456'
    trigger_file ='/tmp/pgsql_master'

说明：'host=192.168.11.11 port=5432 user=dbbak password=123456' 是主节点的热备用户及连接信息

11、从节点启动数据库服务

[root@localhost ~]# systemctl start postgresql-11

12、分表在主从节点上检查数据库的启动状态：

主节点：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 3][]

从节点：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 4][]

13、在主节点执行命令检查搭建结果：

[root@localhost ~]# su - postgres
    -bash-4.2$ psql
    postgres=# \x on
    postgres=# select * from pg_stat_activity where usename = 'dbbak';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 5][]

四、测试：

1、在主节点创建数据库：

postgres=# create database dbtest owner postgres;

![20200723172459262.png][]

2、在从节点查看：

![20200723172548850.png][]

至此，Centos7.x  PostgreSql  数据库  主从同步环境搭建、配置操作完毕！

[https_blog.csdn.net_llwy1428_article_details_105143053]: https://blog.csdn.net/llwy1428/article/details/105143053
[https_blog.csdn.net_llwy1428_article_details_102486414]: https://blog.csdn.net/llwy1428/article/details/102486414
[https_blog.csdn.net_llwy1428_article_details_95444151]: https://blog.csdn.net/llwy1428/article/details/95444151
[https_blog.csdn.net_llwy1428_article_details_102598732]: https://blog.csdn.net/llwy1428/article/details/102598732
[https_blog.csdn.net_llwy1428_article_details_102486511]: https://blog.csdn.net/llwy1428/article/details/102486511
[https_blog.csdn.net_llwy1428_article_details_105167524]: https://blog.csdn.net/llwy1428/article/details/105167524
[https_blog.csdn.net_llwy1428_article_details_106291669]: https://blog.csdn.net/llwy1428/article/details/106291669
[https_blog.csdn.net_llwy1428_article_details_106357900]: https://blog.csdn.net/llwy1428/article/details/106357900
[https_blog.csdn.net_llwy1428_article_details_107031697]: https://blog.csdn.net/llwy1428/article/details/107031697
[http_archive.kernel.org_centos-vault_7.4.1708_isos_x86_64]: https://blog.csdn.net/llwy1428/article/details/106730436
[https_blog.csdn.net_llwy1428_article_details_89328381]: https://blog.csdn.net/llwy1428/article/details/89328381
[https_blog.csdn.net_llwy1428_article_details_85058028]: https://blog.csdn.net/llwy1428/article/details/85058028
[https_blog.csdn.net_llwy1428_article_details_99676257]: https://blog.csdn.net/llwy1428/article/details/99676257
[Link 1]: https://blog.csdn.net/zhaowenzhong/article/details/51899405?utm_medium=distribute.pc_relevant.none-task-blog-baidujs-1
[20200723150115429.png]: /images/20230209/e08b697afdd84497a7bc533ae5fabbaf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200723150452738.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4,size_16,color_FFFFFF,t_70
[20200723151251804.png]: https://img-blog.csdnimg.cn/20200723151251804.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200723151348957.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200723162036286.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4,size_16,color_FFFFFF,t_70
[20200723162255157.png]: https://img-blog.csdnimg.cn/20200723162255157.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200723173526889.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200723173652951.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200723172107314.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4,size_16,color_FFFFFF,t_70
[20200723172459262.png]: https://img-blog.csdnimg.cn/20200723172459262.png
[20200723172548850.png]: https://img-blog.csdnimg.cn/20200723172548850.png