postgresql 12主从复制

PostgreSQL 12主从复制

主从复制

1. 基于文件的日志传送

   创建一个高可用性（HA）集群配置可采用连续归档，集群中主服务器工作在连续归档模式下，备服务器工作在连续恢复模式下（1台或多台可随时接管主服务器），备持续从主服务器读取WAL文件。
   连续归档不需要对数据库表做任何改动，可有效降低管理开销，对主服务器的性能影响也相对较低。
   直接从一个数据库服务器移动WAL记录到另一台服务器被称为日志传送，PostgreSQL通过一次一文件（WAL段）的WAL记录传输实现了基于文件的日志传送。
   1.日志传送所需的带宽取根据主服务器的事务率而变化；
   2.日志传送是异步的，即WAL记录是在事务提交后才被传送，那么在一个窗口期内如果主服务器发生灾难性的失效则会导致数据丢失，还没有被传送的事务将会被丢失；
   3.数据丢失窗口可以通过使用参数archive_timeout进行限制，可以低至数秒，但同时会增加文件传送所需的带宽。

2. 流复制

   PostgreSQL在9.x之后引入了主从的流复制机制，所谓流复制，就是备服务器通过tcp流从主服务器中同步相应的数据，主服务器在WAL记录产生时即将它们以流式传送给备服务器，而不必等到WAL文件被填充。

   1.默认情况下流复制是异步的，这种情况下主服务器上提交一个事务与该变化在备服务器上变得可见之间客观上存在短暂的延迟，但这种延迟相比基于文件的日志传送方式依然要小得多，在备服务器的能力满足负载的前提下延迟通常低于一秒；
   2.在流复制中，备服务器比使用基于文件的日志传送具有更小的数据丢失窗口，不需要采用archive_timeout来缩减数据丢失窗口；
   3.将一个备服务器从基于文件日志传送转变成基于流复制的步骤是：把recovery.conf文件中的primary_conninfo设置指向主服务器；设置主服务器配置文件的listen_addresses参数与认证文件即可。

主备总体结构

PG主备总体框架图：

PG主备流复制的核心部分由walsender，walreceiver和startup三个进程组成。
walsender进程是用来发送WAL日志记录的，执行顺序如下：

PostgresMain()->exec_replication_command()->StartReplication()->WalSndLoop()->XLogSendPhysical()

walreceiver进程是用来接收WAL日志记录的，执行顺序如下：

sigusr1_handler()->StartWalReceiver()->AuxiliaryProcessMain()->WalReceiverMain()->walrcv_receive()

startup进程是用来apply日志的，执行顺序如下：

PostmasterMain()->StartupDataBase()->AuxiliaryProcessMain()->StartupProcessMain()->StartupXLOG()

相关进程详细工作原理 PostgreSQL主备流复制机制

Linux搭建

环境说明

• 操作系统：centos7
• 数据库：postgresql 12
• 主：172.16.123.100
• 从：172.16.123.101

CentOS PostgreSQL 12 安装

参考安装地址

1、安装存储库rpm包

[root@localhost ~]# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2、安装客户端软件包

[root@localhost ~]# yum install postgresql12

3、安装服务端软件包

[root@localhost ~]# yum install postgresql12-server

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

[root@localhost ~]# /usr/pgsql-12/bin/postgresql-12-setup initdb
[root@localhost ~]# systemctl enable postgresql-12
[root@localhost ~]# systemctl start postgresql-12

5、配置防火墙

[root@localhost ~]# firewall-cmd --permanent --add-port=5432/tcp  
[root@localhost ~]# firewall-cmd --permanent --add-port=80/tcp  
[root@localhost ~]# firewall-cmd --reload  

也可以直接关闭
[root@localhost ~]# service firewalld stop

6、修改用户密码

#切换用户 执行后提示符会变为 '-bash-4.2$'
[root@localhost ~]# su - postgres
#登录数据库，执行后提示符变为 'postgres=#'
-bash-4.2$ psql -U postgres
#设置postgres用户密码为postgres
postgres=# ALTER USER postgres WITH PASSWORD 'postgres';
# 退出数据库
    \q 

7、开启远程访问

(-bash-4.2$ 为提示符)

-bash-4.2$ vim /var/lib/pgsql/12/data/postgresql.conf

修改#listen_addresses = ‘localhost’ 为 listen_addresses=’’
当然，此处‘’也可以改为任何你想开放的服务器IP

8、信任远程连接

-bash-4.2$ vim /var/lib/pgsql/12/data/pg_hba.conf

在文件末尾添加

host replication all 172.16.123.101/32 trust #172.16.123.101 为从服务器ip
host all all 0.0.0.0/0 md5

主从安装操作相同

主从配置

一、主库配置

(postgres=# \q退出到-bash-4.2$)

(-bash-4.2$ exit退出到[root@localhost ~]#)

1、创建具有流复制权限的用户

#切换用户 执行后提示符会变为 '-bash-4.2$'
[root@localhost ~]# su - postgres
#登录数据库，执行后提示符变为 'postgres=#'
-bash-4.2$ psql -U postgres
#创建具有流复制权限的用户
postgres=# CREATE ROLE replica login replication encrypted password 'replica的密码';

2、编辑postgresql.conf（/var/lib/pgsql/12/data）

# 新增或修改下列属性设置
# 监听所有IP
listen_addresses = '*'  
# 开启归档
archive_mode = on 
#归档命令
archive_command = 'test ! -f /var/lib/pgsql/12/data/pg_archive/%f && cp %p /var/lib/pgsql/12/data//pg_archive/%f'
# 9.6开始没有hot_standby（热备模式）
wal_level = replica
#最多有2个流复制连接
max_wal_senders = 2   
wal_keep_segments = 16  
#流复制超时时间
wal_sender_timeout = 60s
# 最大连接数，据说从机需要大于或等于该值
max_connections = 100 

3、重启postgres

[root@localhost ~]# systemctl restart postgresql-12.service

二、从库配置

1、验证在从库是否可以访问主节点

[root@localhost ~]# psql -h 172.16.123.100 -U postgres

2、停止从库postgres服务

[root@localhost ~]# systemctl stop postgresql-12.service

3、清空从库数据存储文件夹

[root@localhost ~]# rm -rf /var/lib/pgsql/12/data/*

4、从主服务器上copy数据到从服务器，这一步叫做“基础备份”

这一步一定要用postgres用户进行操作如果是root先切换用户 不然会出现文件夹权限问题导致pg无法启动

[root@localhost ~]# su - postgres

-bash-4.2$ pg_basebackup -h 主节点IP -p 5432 -U replica -Fp -Xs -Pv -R -D /var/lib/pgsql/12/data

5、此时data目录下会出现standby.signal文件，编辑此文件

## 加入
standby_mode = 'on'

6、修改postgresql.conf文件（/var/lib/pgsql/12/data）

#从机信息和连接用户 host=主节点IP port=5432 user=replica password=replica用户的密码
primary_conninfo = 'host=172.16.123.100 port=5432 user=replica password=postgres'
#说明恢复到最新状态
recovery_target_timeline = latest 
#大于主节点，正式环境应当重新考虑此值的大小
max_connections = 120 
#说明这台机器不仅用于数据归档，还可以用于数据查询
hot_standby = on
#流备份的最大延迟时间
max_standby_streaming_delay = 30s 
#向主机汇报本机状态的间隔时间
wal_receiver_status_interval = 10s 
#r出现错误复制，向主机反馈
hot_standby_feedback = on

7、重启从库

[root@localhost ~]# systemctl restart postgresql-12.service

三、验证主从搭建是否成功

连接主库执行

[root@localhost ~]# psql -h 172.16.123.100 -U postgres

postgres=# select client_addr,sync_state from pg_stat_replication;

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

Windows搭建

环境说明

• 操作系统：windows10
• 数据库：postgresql 12
• 主：172.16.123.200
• 从：172.16.123.201

Windows PostgreSQL 12 安装

PostgreSQL 12 安装

主从配置

一、主库配置

** 1、创建具有流复制权限的用户**

可以在navicat中执行也可以在pgadmin中执行sql

CREATE ROLE replica login replication encrypted password 'postgres'

2、修改postgresql.conf文件

文件地址 C:\Program Files\PostgreSQL\12\data 该地址为默认安装目录 按实际安装目录找到该文件

# 新增或修改下列属性设置
# 监听所有IP
listen_addresses = '*'  
# 开启归档
archive_mode = on 
# 9.6开始没有hot_standby（热备模式）
wal_level = replica
#最多有2个流复制连接
max_wal_senders = 2   
wal_keep_segments = 16  
#流复制超时时间
wal_sender_timeout = 60s
# 最大连接数，据说从机需要大于或等于该值
max_connections = 100 

3、重启服务

二、从库配置

1、停止服务

2、删除data文件夹

C:\Program Files\PostgreSQL\12\data 该地址为默认安装目录 按实际安装目录找到该文件

3、从主服务器上copy数据到从服务器，这一步叫做“基础备份

以管理员权限打开cmd 进行C:\Program Files\PostgreSQL\12\bin 目录运行以下命令

pg_basebackup -h 172.16.123.200  -p 5432 -U replica -Fp -Xs -Pv -R -D "C:\Program Files\PostgreSQL\12\data"

运行完以后 修改data文件夹权限 设置完全控制

5、此时data目录下会出现standby.signal文件，编辑此文件

## 加入
standby_mode = 'on'

6、修改postgresql.conf文件（C:\Program Files\PostgreSQL\12\data）

#从机信息和连接用户 host=主节点IP port=5432 user=replica password=replica用户的密码 
primary_conninfo = 'host=172.16.123.200 port=5432 user=replica password=postgres'
#说明恢复到最新状态
recovery_target_timeline = latest 
#大于主节点，正式环境应当重新考虑此值的大小
max_connections = 120 
#说明这台机器不仅用于数据归档，还可以用于数据查询
hot_standby = on
#流备份的最大延迟时间
max_standby_streaming_delay = 30s 
#向主机汇报本机状态的间隔时间
wal_receiver_status_interval = 10s 
#r出现错误复制，向主机反馈
hot_standby_feedback = on

7、重启服务

三、验证主从搭建是否成功

navicat连接主库执行

select client_addr,sync_state from pg_stat_replication;

用navicat测试数据库同步

(windows/linux测试方式一致)

在主库建立test2表 刷新从库也会新增test2表

在主库test2中新增记录 从库test2表也会新增记录

主库删除test2 从库也会删除test2

主库新增 demo数据库 从库也会新增demo数据库