PostgreSQL的一主一从集群搭建部署

合集 - PostgreSQL(20)

1.postgres V15.7源码安装2024-08-112.postgres V16.3源码安装2024-08-113.PostgreSQL一主一从高可用搭建2024-08-11

4.PostgreSQL的一主一从集群搭建部署2024-08-11

5.PostgreSQL的同步异步复制简介及互相转换2024-08-116.PostgreSQL的逻辑备份与物理备份2024-08-117.PostgreSQL的物理备份2024-08-118.PostgreSQL-连续归档和时间点恢复2024-08-119.PostgreSQL的体系结构2024-08-1110.PostgreSQL的WAL日志详解2024-08-1111.PostgreSQL的数据类型2024-08-1112.PostgreSQL的查询详解2024-08-1113.多版本并发控制2024-08-1114.物理存储结构2024-08-1115.文件级系统备份2024-08-1116.SQL转储2024-08-1117.数据库参数2024-08-1118.PostgreSQL自带逻辑复制简单使用2024-08-1819.搭建PostgreSQL高可用集群（基于Patroni+Etcd）2024-08-1620.vacuum-ppt文档2024-12-24

收起

1. PostgreSQL的一主一从集群搭建部署

1. 目标

​ 掌握Postgresql数据库主从部署搭建配置

2. 脉络

• 部署规划
• PostgreSQL单节点安装
• PostgreSQL主从部署配置
• 主从同步验证

3. 部署搭建

3.1 基础准备

1.1 创建两台虚拟机

虚拟机名	IP	hostname	主从划分
test11	192.168.198.146	node236	主节点
test12	192.168.198.144	node237	从节点1

1.2 创建两台虚拟机

对于本文部署涉及到的软件版本如下表所示。

操作系统发行版本	CentOS 7
操作系统版本	Linux
PostgreSQL	9.2.24
本地虚拟化软件	VMware® Workstation

3.2 安装软件包

3.2.1 查看软件版本

安装之前首先查看软件是否已经安装。输入命令：

rpm -qa | grep postgresql

一般来讲，从虚拟机安装系统，自然是不会安装PostgreSQL。因此输出为空。

安装软件包有多种方式可选，对于PostgreSQL本文选用的方式是尽量选择yum安装默认版本的软件，以便后期维护。

2.2.2 安装与初始化

两台机器安装过程相同。

（1）安装postgresql postgresql-server

yum install -y postgresql postgresql-server

（2）查看安装结果。

rpm -qa | grep postgresql

[root@localhost ~]# rpm -qa | grep postgresql
postgresql-libs-9.2.24-9.el7_9.x86_64
postgresql-server-9.2.24-9.el7_9.x86_64
postgresql-9.2.24-9.el7_9.x86_64

（3）初始化

service postgresql initdb

（4）启动

systemctl start postgresql

（5）设置为开机自启

systemctl enable postgresql.service

（6）开放端口(如果防火墙已经关闭则可以省略)

iptables -I INPUT -p tcp --dport 5432 -j ACCEPT

（6）验证安装结果，首先输入 su - postgres切换用户，然后输入psql，如下图所示，则安装成功。

[root@localhost ~]# su - postgres
-bash-4.2$ psql
psql (9.2.24)
输入 "help" 来获取帮助信息.
postgres=# 

（7）对posgres用户和数据库管理员设置密码。

# 切换到root用户设置postgres用户密码
passwd postgres
# 在输入两次新密码即可


# 进入posgresql数据中
su - postgres
psql
postgres=# alter user postgres with password '654321';
ALTER ROLE

3.2 主节点配置

（1）创建用于主从访问的用户

# su - postgres
-bash-4.2$ psql
postgres=# create role repl login replication encrypted password 'postgres';

（2）在pg_hba.conf和postgresql.conf中增加配置内容

pg_hba.conf是对访问权限的以下配置，需要根据实际情况配置。其中192.168.198.143是从节点的IP，用于主从的配置。repl是刚刚创建的用户。最后一行是用于子网内客户端的登录。

# vi /var/lib/pgsql/data/pg_hba.conf
host    replication    repl    192.168.198.143/32    trust
host    replication    repl    192.168.198.146/32    trust
host    all    		  all      192.168.198.0/24     md5
host    all	    	  all      0.0.0.0/0            md5

postgresql.conf是postgresql本身配置。

# vi /var/lib/pgsql/data/postgresql.conf

# 要修改的地方
# 监听地址
listen_addresses = '*'
#  写入WAL的级别(minimal:不能通过基础备份和wal日志恢复数据库; replica: 支持wal归档和复制; logical: 在replica级别添加了逻辑解码所需的信息)
wal_level = hot_standby
# 允许最多的流复制连接发送数量， 根据从节点数量来设定
max_wal_senders= 6
# 设置流复制保留的最多的xlog数目
wal_keep_segments = 10240
# 设置流复制发送数据的超时时间
wal_sender_timeout = 60s 
# 最大连接数量，根据从节点与客户端连接数来设定
max_connections = 512

参数说明：

listen_addresses = '*'	可以连接服务器使用的IP，一般初始值为 localhost或者local，意味着只有本机可以连接数据库。这里一般设置为 " * " ，允许全部的IP连接数据库。
wal_level = hot_standby	wal_level中有三个主要的参数：minimal、archive和hot_standby1.minimal是默认的值，它仅写入崩溃或者突发关机时所需要的信息（不建议使用）。2.archive是增加wal归档所需的日志（最常用）。3.hot_standby是在备用服务器上增加了运行只读查询所需的信息，一般是在流复制的时候使用到。
max_wal_senders= 6	最多多少个流复制链接
wal_keep_segments = 10240	流复制保留最多的xlog数，xlog这个日志是记录的Postgresql的WAL信息
max_connections = 512	允许的最大并发连接数，简单来说就是同时多少客户端能连接你的数据库。

（3）重启主节点

建议先停止，再启动，而不是重启。之后再验证一下是否启动成功

systemctl stop postgresql
systemctl start postgresql

2.4 从节点配置

从节点的操作建议全部在postgres用户下进行。

（1）切换postgres用户

su - postgres

（2）对主节点的数据进行备份，其中192.168.198.143对应主机IP，repl是上一节主节点创建的用户。

# 删除原有的data目录下数据
-bash-4.2$ rm -rf /var/lib/pgsql/data/*
# 从主库中拷贝数据
-bash-4.2$ pg_basebackup -h 192.168.198.143 -U repl -D /var/lib/pgsql/data -X stream -P

（3）拷贝recovery.conf，编辑recovery.conf内容，其中192.168.198.143对应主机IP，repl是上一节主机创建的用户。

-bash-4.2$ cp /usr/share/pgsql/recovery.conf.sample /var/lib/pgsql/data/recovery.conf
-bash-4.2$ vi /var/lib/pgsql/data/recovery.conf

# 要修改的值
# 从机不仅用于数据归档，也可用于数据查询
standby_mode = on
primary_conninfo = 'host=192.168.198.143 port=5432 user=repl password=postgres'
recovery_target_timeline = 'latest'
# 根据实际应用情况， 设定最大连接数
max_connections = 1000
# 如果有错误的数据复制，是否向主进行反馈
hot_standby_feedback = on 
# 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
wal_receiver_status_interval = 10s 
# 数据流备份的最大延迟时间
max_standby_streaming_delay = 3

参数说明。

standby_mode	开启standby模式
primary_conninfo	主节点信息
recovery_target_timeline = 'latest'	指定恢复到一个特定的时间线中。默认值是沿着基础备份建立时的当前时间线恢复。将这个参数设置为latest会恢复到该归档中能找到的最新的时间线。
trigger_file = '/tmp/trigger_file0'	'/tmp/trigger_file0'是一个自定义的文件，在后面主从切换的时候能够用得上。倘若检测到该文件的创建，则PostgreSQL由主节点的状态切换为主。如果下文在failover_stream.sh采用文件触发的办法，则必须配置此项，若采用命令触发方法则无需配置此项，但配置此项对命令方式没有影响。

（4）在postgresql.conf中添加一行，用于开启standby模式。

-bash-4.2$ vi /var/lib/pgsql/data/postgresql.conf
# 要修改的值
hot_standby = on

（5）退出postgres用户，重启PostgreSQL

systemctl stop postgresql
systemctl start postgresql

4. 验证主从

（1）在主节点写数据，从节点读数据。

在主节点，切换到psql界面

create database test;

可以看见提示创建成功。

在从节点上查看分别在创建之前和创建之后查看数据库。可以看见，数据库同步了。

# 主库
                                 资料库列表
   名称    |  拥有者  | 字元编码  | 校对规则 | Ctype |       存取权限        
-----------+----------+-----------+----------+-------+-----------------------
 postgres  | postgres | SQL_ASCII | C        | C     | 
 template0 | postgres | SQL_ASCII | C        | C     | =c/postgres          +
           |          |           |          |       | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C        | C     | =c/postgres          +
           |          |           |          |       | postgres=CTc/postgres
 test      | postgres | SQL_ASCII | C        | C     | 
(4 行记录)


# 从库
                                 资料库列表
   名称    |  拥有者  | 字元编码  | 校对规则 | Ctype |       存取权限        
-----------+----------+-----------+----------+-------+-----------------------
 postgres  | postgres | SQL_ASCII | C        | C     | 
 template0 | postgres | SQL_ASCII | C        | C     | =c/postgres          +
           |          |           |          |       | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C        | C     | =c/postgres          +
           |          |           |          |       | postgres=CTc/postgres
 test      | postgres | SQL_ASCII | C        | C     | 
(4 行记录)

（2）在从节点写数据

postgres=# create database test2;
ERROR:  cannot execute CREATE DATABASE in a read-only transaction

可以看见，提示，从节点是个只读数据库。

5. 总结

• 通过PostgreSQL主从同步部署， 能够有效保障主节点数据的安全， 即便主库归档日志损坏， 也可以通过从节点恢复获取数据，主从部署还可以有效减少主节点的负载压力， 将集中读取的数据通过从节点处理，减少主节点的IO瓶颈和CPU负载， 如果一台从节点不够， 也可以参照以上部署方式，扩展多个从节点， 从而提升整体数据库吞吐性能。
• 虽然PostgreSQL原生对多主集群模式没有较好的支持，对于复杂的海量数据的业务， 我们可以从架构设计上做改进， 将复杂的业务进行拆分， 设为多个微服务， 搭建多个PostgreSQL主从服务群， 分散负载， 消除瓶颈，从而有效支撑海量数据的业务服务。