1.概念

数据库热备：数据库热备是指为主数据库的创建、维护和监控一个或多个备用数据库，它们时刻处于开机状态，同主机保持同步。当主机失灵时，可以随时启用热备数据库来代替，以保护数据不受故障、灾难、错误和崩溃的影响。

流复制（streaming replication）：PostgreSQL提供的一种服务器间的数据复制方式。这种方式下，后备服务器连接到主服务器，主服务器则在 WAL 记录产生时即将它们以流式传送给后备服务器而不必等到 WAL 文件被填充。

2.环境说明

服务器

host	IP	cpu	内存	硬盘
master	172.28.130.118	8核	32G	200G
slave	172.28.130.120	8核	32G	200G

操作系统

[root@master ~]# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)

Postgres Sql
版本: 9.6

3. 安装 Postgres sql 9.6

3.1 添加RPM

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

注意：
非centos操作系统，或者以上安装源地址无效，可以去postgres sql的官网获取最新的安装源和安装脚本，地址如下：
https://www.postgresql.org/download/linux/redhat/

image.png

3.2 在主从节点，安装PostgreSQL

yum install postgresql96-server postgresql96-contrib

3.3 初始化数据库

默认数据文件存储目录

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

自定义数据文件存储目录

mkdir -p /data/db

chown postgres:postgres /data/db

设置环境变量
在/etc/profile文件最后加上以下配置

export PATH=/usr/pgsql-9.6/bin:$PATH
export LD_LIBRARY_PATH=/usr/pgsql-9.6/lib
export PGDATA=/data/db

在命令行执行以下命令，让环境变量生效

source /etc/profile

初始化数据库

su postgres
initdb -D /data/db

配置service中的数据文件路径
修改/usr/lib/systemd/system/postgresql-9.6.service

Environment=PGDATA=/var/lib/pgsql/9.6/data/

改为

Environment=PGDATA=/data/db

3.4 开启远程访问

修改postgresql.conf文件(该文件在数据文件目录下),将listen_addresses改为

listen_addresses='*'

3.5 设置开机自启动

systemctl enable postgresql-9.6.service

3.6 启动服务

systemctl start postgresql-9.6.service

3.7 打开防火墙

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

4.配置集群

4.1 主节点配置

4.1.1 创建用于流复制的用户

登陆Pgsql

psql -h 127.0.0.1 -U postgres

进入pgsql命令行，执行创建pgsql用户命令

create user repuser with login replication password '123456';

4.1.2 修改`pg_hba.conf`文件

#允许所有用户通过密码远程访问
host    all             all             0.0.0.0/0               md5
#允许172.28.130.0 ip段的客户端通过用户repuser登陆，用于流复制
host     replication     repuser         172.28.130.0/24         md5

4.1.3 在主节点的`postgresql.conf` 中设置参数

max_wal_senders = 10
wal_level = replica
wal_log_hints = on
wal_keep_segments = 10
wal_receiver_status_interval = 5s
hot_standby_feedback = on
max_replication_slots = 6

这些参数中的含义如下：

max_wal_senders表示来自后备服务器或流式基础备份客户端的并发连接的最大数量；
wal_level 表示日志级别，对于流复制，它的值应设置为replica；
wal_log_hints = on表示，在PostgreSQL服务器一个检查点之后页面被第一次修改期间，把该磁盘页面的整个内容都写入 WAL，即使对所谓的提示位做非关键修改也会这样做；
wal_keep_segments 指定在后备服务器需要为流复制获取日志段文件的情况下，pg_wal（PostgreSQL 9.6 以下版本的是pg_xlog）目录下所能保留的过去日志文件段的最小数目。
log_connections 表示是否在日志中记录客户端对服务器的连接；
wal_receiver_status_interval 指定在后备机上的 WAL 接收者进程向主服务器或上游后备机发送有关复制进度的信息的最小周期；
hot_standby_feedback 指定一个热后备机是否将会向主服务器或上游后备机发送有关于后备机上当前正被执行的查询的反馈，这里设置为on。

关于详细内容，可以参考postgresql官方文档。

4.1.4 重启之后，为主服务器和后备服务器创建复制槽(这一步不是必须的)

select * from pg_create_physical_replication_slot('postgresql_node101');

select * from pg_create_physical_replication_slot(' postgresql_node102');

复制槽（replication slot）的作用是：

a. 在流复制中，当一个备节点断开连接是时，备节点通过hot_standby_feedback 提供反馈数据数据会丢失。当备节点重新连接时，它可能因为被主节点发送清理记录而引发查询冲突。复制槽即使在备节点断开时仍然会记录下备节点的xmin（复制槽要需要数据库保留的最旧事务ID）值，从而确保不会有清理冲突。

b. 当一个备节点断开连接时，备节点需要的WAL文件信息也丢失了。如果没有复制槽，当备节点重连时，我们可能已经丢弃了所需要的WAL文件，因此需要完全重建备节点。而复制槽确保这个节点保留所有下游节点需要的wal文件。

4.2 配置从节点

4.2.1 停止从节点服务

systemctl stop postgresql-9.6.service

4.2.2 删除从节点的数据目录,创建空的数据目录

清空

rm -rf /data/db

mkdir -p /data/db

chown postgres:postgres /data/db
chmod 700 /data/db

注意：这里数据目录需要设置为700权限，否则，pgsql启动不了

4.2.3 备份

su - postgres
pg_basebackup -Xs -d "hostaddr=172.28.130.118 port=5432 user=repuser password=123456" -D /data/db -v -Fp

-Xs 表示复制方式是流式的（stream），这种方式不会复制在此次备份开始前，已经归档完成的WAL文件
-d 后面是一个连接字符串，其中“hostaddr= 172.28.130.118”表示主服务器的ip地址是172.28.130.118
“port=5432”表示数据库的端口是5432
“user=repuser”表示用于流复制的用户是repuser
“password=123456”表示密码是123456
“-D /data/db” 表示将备份内容输入到本地的 /data/db目录
“-v”表示打印详细信息，–Fp 表示复制结果输出位普通（plain）文件

4.2.4 配置从节点

1). 修改postgresql.conf 文件

hot_standby = on

2). 配置 recovery.conf：
复制文件

cp /usr/pgsql-9.6/share/recovery.conf.sample /data/db/recovery.conf

设置参数：

recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.28.130.118 port=5432 user=repuser password=123456'
primary_slot_name = 'postgresql_node102'
trigger_file = 'tgfile'

这些参数的含义如下：

recovery_target_timeline 表示恢复到数据库时间线的上的什么时间点，这里设置为latest，即最新。
standby_mode 表示是否将PostgreSQL服务器作为一个后备服务器启动，这里设置为on，即后备模式。
primary_conninfo指定后备服务器用来连接主服务器的连接字符串，其中“host= 172.28.130.118”表示主服务器的ip地址是172.28.130.118，“port=5432”表示数据库的端口是5432，“user=repuser”表示用于流复制的用户是repuser， “password=123456”表示密码是123456。
primary_slot_name 指定通过流复制连接到主服务器时使用一个现有的复制槽来控制上游节点上的资源移除。这里我们指定3.2.1节创建的postgresql_node102。如果没有在主服务器上创建复制槽，则不配置此参数。
trigger_file指定一个触发器文件，该文件的存在会结束后备机中的恢复，使它成为主机。

4.2.5 启动备节点服务：

systemctl start postgresql-9.6.service

5. 主备环境检测

5.1 在主节点上创建一个表,并插入数据：

 create table student  (id int, name text);
 insert into student  (id, name)  values  (1,'tom');

5.2 在从节点上检测

select * from student;

结果如下:

id | name

----+------

1 | tom

主节点数据同步到了备机。

同时，在备节点上写数据会失败：

 insert into student (id, name)  values  (2,'amy');

执行插入语句有出现一下错误提示：
ERROR: cannot execute INSERT in a read-only transaction

5.3 其他检查

主库上执行如下命令返回f，备库上返回t。

select pg_is_in_recovery();

执行如下命令查看快照，它返回主库记录点、备库记录点；主库每增加一条写入，记录点的值就会加1。

select txid_current_snapshot();

执行如下命令可以查看主备同步状态。

select * from pg_stat_replication;

字段state显示的同步状态有：startup（连接中）、catchup（同步中）、streaming（同步）；字段sync_state显示的模式有：async（异步）、sync（同步）、potential（虽然现在是异步模式，但是有可能升级到同步模式）。

6. 主备环境的切换

6.1 激活备节点，使之成为新的主结点

pg_ctl promote -D $PGDATA

结果是：

waiting for server to promote........ done
server promoted

6.2 查看新主节点的状态

pg_controldata | grep cluster

Database cluster state: in production

插入一条数据：

insert into student (id, name)  values  (2,'amy');

INSERT 0 1

6.3 停止旧的主结点

pg_ctl stop -m fast -D $PGDATA

结果：

waiting for server to shut down.... done

server stopped

6.4 在停止的旧主结点上执行恢复数据的操作

pg_rewind --target-pgdata $PGDATA --source-server='host= 172.28.130.118 port=5432 user=postgres dbname=postgres' -P

结果如下：

connected to server

servers diverged at WAL location 0/2B000230 on timeline 4

rewinding from last common checkpoint at 0/2A000098 on timeline 4

reading source file list

reading target file list

reading WAL in target

need to copy 57 MB (total source directory size is 143 MB)

58749/58749 kB (100%) copied

creating backup label and updating control file

syncing target data directory

Done!

表示从新主节点上成功获取WAL日志。

6.5 重新配置旧主结点的 `recovery.conf`

recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'hostaddr= 172.28.130.120 port=5432 user=repuser password=123456'
primary_slot_name = 'postgresql_node101'

6.6 在旧主结点上执行下面的命令，重新启动该节点

systemctl start postgresql-9.6.service

在旧主结点上验证：

 insert into student (id, name)  values  (3,'lily');

ERROR: cannot execute INSERT in a read-only transaction

Postgres SQL集群搭建-主备

Postgres SQL集群搭建-主备

1.概念

2.环境说明

3. 安装 Postgres sql 9.6

3.1 添加RPM

3.2 在主从节点，安装PostgreSQL

3.3 初始化数据库

默认数据文件存储目录

自定义数据文件存储目录

3.4 开启远程访问

3.5 设置开机自启动

3.6 启动服务

3.7 打开防火墙

4.配置集群

4.1 主节点配置

4.1.1 创建用于流复制的用户

4.1.2 修改`pg_hba.conf`文件

4.1.3 在主节点的`postgresql.conf` 中设置参数

4.1.4 重启之后，为主服务器和后备服务器创建复制槽(这一步不是必须的)

4.2 配置从节点

4.2.1 停止从节点服务

4.2.2 删除从节点的数据目录,创建空的数据目录

4.2.3 备份

4.2.4 配置从节点

4.2.5 启动备节点服务：

5. 主备环境检测

5.1 在主节点上创建一个表,并插入数据：

5.2 在从节点上检测

5.3 其他检查

6. 主备环境的切换

6.1 激活备节点，使之成为新的主结点

6.3 停止旧的主结点

6.4 在停止的旧主结点上执行恢复数据的操作

6.5 重新配置旧主结点的 `recovery.conf`

6.6 在旧主结点上执行下面的命令，重新启动该节点

Postgres SQL集群搭建-主备

1.概念

2.环境说明

3. 安装 Postgres sql 9.6

3.1 添加RPM

3.2 在主从节点，安装PostgreSQL

3.3 初始化数据库

默认数据文件存储目录

自定义数据文件存储目录

3.4 开启远程访问

3.5 设置开机自启动

3.6 启动服务

3.7 打开防火墙

4.配置集群

4.1 主节点配置

4.1.1 创建用于流复制的用户

4.1.2 修改pg_hba.conf文件

4.1.3 在主节点的postgresql.conf 中设置参数

4.1.4 重启之后，为主服务器和后备服务器创建复制槽(这一步不是必须的)

4.2 配置从节点

4.2.1 停止从节点服务

4.2.2 删除从节点的数据目录,创建空的数据目录

4.2.3 备份

4.2.4 配置从节点

4.2.5 启动备节点服务：

5. 主备环境检测

5.1 在主节点上创建一个表,并插入数据：

5.2 在从节点上检测

5.3 其他检查

6. 主备环境的切换

6.1 激活备节点，使之成为新的主结点

6.3 停止旧的主结点

6.4 在停止的旧主结点上执行恢复数据的操作

6.5 重新配置旧主结点的 recovery.conf

6.6 在旧主结点上执行下面的命令，重新启动该节点

4.1.2 修改`pg_hba.conf`文件

4.1.3 在主节点的`postgresql.conf` 中设置参数

6.5 重新配置旧主结点的 `recovery.conf`