链接

https://www.cnblogs.com/ExMan/p/11247163.html
https://blog.csdn.net/weixin_46396833/article/details/112790600
https://www.cnblogs.com/hxlasky/p/12208561.html
https://www.cnblogs.com/zhoujie/p/pgsql.html 操作命令

原文链接

https://blog.csdn.net/zxfmamama/article/details/121008549
https://blog.csdn.net/weixin_39540651/article/details/106122610

一、方案

1.1 方案效果

流复制数据同步: 通过postgresql数据库配置来实现
虚拟ip自动切换: 通过pgpool-ii 配置实现
数据库主备角色切换: 通过pgpool-ii 监测机 + 执行 postgresql 中的promote命令来实现

某一个 postgresql 数据库挂掉 (多台数据库启动后其中一台作为主机,其余作为备机构成一个数据库集群);
(1). 如果是主机primary,集群检测到挂掉会通过配置的策略重新选一个备机standby切换为主机primary, 整个集群仍旧保证可用, 当原主机恢复服务后, 重新作为一个新备机standby,同步完数据后加入集群
(2). 如果是备机standby,对整个集群无可见影响, 当备机恢复服务后,从主库同步完数据后,恢复正常状态加入集群;
某一台机器上的pgpool-ii 程序挂掉;
pgpool-ii 是一个介于postgresql 服务器和postgresql数据库之间的中间件, 提供了链接池(Connection Pooling),看门狗(WatchDog),复制,负载均衡,缓存等功能(具体的可以查看官方文档);
通过pgpool-ii 维护的虚拟ip, 向外界提供一个始终可用的访问地址, 屏蔽掉具体的主机数据库地址概念;
通过pgpool-ii 程序来自动处理宕机后相关方案。
(1). 监测每个pgpool-ii进程的状态, 监测到挂掉之后,及时"切换"虚拟ip所在的主机以保证可用性(有些人叫IP漂移);
(2). 整个集群始终对外提供一个唯一的,可用的虚拟IP 来提供访问;
(3). 监测每个主机postgresql数据库的状态, 以即使切换数据库的主备角色;
某一台主机直接宕机;
(1). 当pgpool-ii监测主机挂掉之后, 需要进行数据库角色的切换和ip的切换两个操作(如果需要)

1.1 主机配置

主从	系统	IP	安装软件
主	CentOS Linux release 7.9.2009	192.168.8.10	Postgresql 12.3 + pgpool-ii 4.1
从	CentOS Linux release 7.9.2009	192.168.8.20	Postgresql 12.3 + pgpool-ii 4.1
从	CentOS Linux release 7.9.2009	192.168.8.30	Postgresql 12.3 + pgpool-ii 4.1
VIP		192.168.8.33	通过一个虚拟的IP统一对外提供访问

二、安装

2.1 postgresql-12安装(3台机器均安装)

2.1.1 yum在线安装

#yum源
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#安装(这里是版本为12的postgresql)
sudo yum install -y postgresql12-server
systemctl stop firewalld
systemctl disable firewalld

2.1.2 rpm离线安装

具体安装参考https://blog.csdn.net/zxfmamama/article/details/121008549

mkdir /opt/rpm/
curl -O http://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/postgresql12-12.3-1PGDG.rhel7.x86_64.rpm
curl -O http://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/postgresql12-contrib-12.3-1PGDG.rhel7.x86_64.rpm
curl -O http://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/postgresql12-libs-12.3-1PGDG.rhel7.x86_64.rpm
curl -O http://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/postgresql12-server-12.3-1PGDG.rhel7.x86_64.rpm
# 上传文件到服务器之后, 执行安装命令
rpm -ivh postgresql*.rpm

执行完安装之后(查看状态可跳过, 直接进行数据库初始化):

会帮我们创建一个postgresql-12服务, 此时未进行数据库初始化, 还无法访问.
会帮我们创建一个postgres/postgres 的用户，密码相同.
此时使用systemctl status postgresql-12 查看服务状态:
● postgresql-12.service - PostgreSQL 12 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: https://www.postgresql.org/docs/12/static/

我们可以找到默认配置文件地址: /usr/lib/systemd/system/postgresql-12.service

如果cat命令查看配置文件, 我们可以得到一些基础信息:
数据库数据目录: Environment=PGDATA=/var/lib/pgsql/12/data/
postgresql安装目录: PGHOME=/usr/pgsql-12/

2.1.3 源码安装（本人使用）

mkdir /opt/tar
yum install -y vim lrzsz tree wget gcc gcc-c++ make readline-devel readline zlib-devel zlib ncurses-devel
wget http://ftp.postgresql.org/pub/source/v12.3/postgresql-12.3.tar.gz
tar -zxf postgresql-12.3.tar.gz
cd postgresql-12.3
./configure --prefix=/usr/local/postgresql
make && make install

#创建data和log目录
mkdir /usr/local/postgresql/log
mkdir /usr/local/postgresql/data


#配置环境变量
cat << eof >> /etc/profile
export PGHOME=/usr/local/postgresql
export PGDATA=/usr/local/postgresql/data
export PATH=\$PATH:\$HOME/.local/bin:\$HOME/bin:\$PGHOME/bin
eof
source /etc/profile

#添加用户并授权
useradd postgres
chown -R postgres:root /usr/local/postgresql/

#初始化数据库
注意：不能在 root 用户下初始数据库，否则会报错
su postgres
/usr/local/postgresql/bin/initdb -D /usr/local/postgresql/data/

输出以下信息表示成功
###
[postgres@localhost postgresql]$ /usr/local/postgresql/bin/initdb -D /usr/local/postgresql/data/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Shanghai
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/postgresql/bin/pg_ctl -D /usr/local/postgresql/data/ -l logfile start
###

启停及基本使用参阅
https://blog.csdn.net/martinlinux/article/details/121364635

三、配置

3.1 配置开启远程连接

cd /usr/local/postgresql/data
cp postgresql.conf postgresql.conf-bak
cp pg_hba.conf pg_hba.conf-bak

vim postgresql.conf
listen_addresses = '*'
port = 5432

vim pg_hba.conf
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
加入以下一行
host    all             all             0.0.0.0/0               md5

3.2 启动并修改密码

postgres用户启动
pg_ctl start -l /usr/local/postgresql/log/pg_server.log -D /usr/local/postgresql/data/

登录
psql -U postgres
ALTER USER postgres WITH PASSWORD 'postgres'  #设置postgres用户密码为postgres

3.3 postgresql-12 流复制配置

3.3.1 流复制原理简述

流复制讲解链接：https://blog.csdn.net/weixin_39540651/article/details/106122610
流复制大约是从pg9版本之后使用, 流复制其原理为：备库不断的从主库同步相应的数据，并在备库apply每个WAL record，这里的流复制每次传输单位是WAL日志的record。(关于预写式日志WAL,是一种事务日志的实现)
1.事务commit后，日志在主库写入wal日志，还需要根据配置的日志同步级别，等待从库反馈的接收结果。
2.主库通过日志传输进程将日志块传给从库，从库接收进程收到日志开始回放，最终保证主从数据一致性。

PostgreSQL通过wal日志来传送的方式有两种：基于文件的日志传送和流复制。
不同于基于文件的日志传送，流复制的关键在于“流”，所谓流，就是没有界限的一串数据，类似于河里的水流，是连成一片的。因此流复制允许一台后备服务器比使用基于文件的日志传送更能保持为最新的状态。
比如我们有一个大文件要从本地主机发送到远程主机，如果是按照“流”接收到的话，我们可以一边接收，一边将文本流存入文件系统。这样，等到“流”接收完了，硬盘写入操作也已经完成。

3.3.2 流复制同步级别

PostgreSQL通过配置synchronous_commit (enum)参数来指定事务的同步级别。我们可以根据实际的业务需求，对不同的事务，设置不同的同步级别。

synchronous_commit = off # synchronization level; # off, local, remote_write, or on

remote_apply：事务commit或rollback时，等待其redo在primary、以及同步standby(s)已持久化，并且其redo在同步standby*(s)已apply。
off 表示commit 时不需要等待wal 持久化。
local 表示commit 是只需要等待本地数据库的wal 持久化。
remote_write 表示commit 需要等待本地数据库的wal 持久化，同时需要等待sync standby节点wal write buffer完成(不需要持久化)。
on 表示commit 需要等待本地数据库的wal 持久化，同时需要等待sync standby节点wal持久化。

不同的事务同步级别对应的数据安全级别越高，对应的对性能影响也就越大。上述从上至下安全级别越来越低

3.3.3 注意事项

postgresql-12版本不再支持通过recovery.conf的方式进行主备切换，如果数据目录中存在recovery.conf，则数据库无法启动;
新增 recovery.signal 标识文件，表示数据库处于 recovery 模式;
新增加 standby.signal 标识文件，表示数据库处于 standby 模式(这个需要重点关注一下);
以前版本中 standby_mode 参数不再支持;
recovery.conf文件取消, 合并到了postgresql.conf文件中;
配置中war_level存储级别, postgresql-9.6以后有改变:

等级	说明
minimal	不能通过基础备份和wal日志恢复数据库
replica	9.6新增,将之前版本的 archive 和 hot_standby合并, 该级别支持wal归档和复制
logical	在replica级别的基础上添加了支持逻辑解码所需的信息

3.4 配置流复制

3.4.1 创建流复制用户, 提供给备库远程访问, 用来获取流(主库配置)

su - postgres

创建目录（所有节点）
# postgres
su - postgres
mkdir  /usr/local/postgresql/archivedir

psql #登录
#创建repuser用户，用户备库访问主库
postgres=# create role repuser login replication encrypted password 'repuser';
CREATE ROLE
postgres=# \q

3.4.2 修改pg_hba.conf配置文件

cd /usr/local/postgresql/data
vim pg_hba.conf
#加入下边一句
host  replication     repuser        0.0.0.0/0             md5

3.4.3 修改postgresql.conf配置文件

cd /usr/local/postgresql/data
vim  postgresql.conf
archive_mode = on
archive_command = 'cp "%p" "/usr/local/postgresql/archivedir/"'
# 最大连接数，从库的max_connections必须要大于主库的
max_connections = 100       
#参数表明是否等待wal日志buffer写入磁盘再返回用户事物状态信息。默认值是ON，同步流复制模式需要打开。
#但是选择on打开之后从库宕机会影响主库写入。
synchronous_commit = off
# *=all，意思是所有slave都被允许以同步方式连接到master，但同一时间只能有一台slave是同步模式。# 另外可以指定slave，将值设置为slave的application_name即可。
synchronous_standby_names = '*'
#控制wal存储的级别，默认值是最小的（minimal）。解释见目录：3.3.3 注意事项
wal_level = replica
#这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
max_wal_senders = 2   
max_replication_slots = 10 
#用于指定pg_wal目录中保存的过去的wal文件（wal 段）的最小数量，以防备用服务器在进行流复制时需要，默认值0。
wal_keep_segments = 16  
#控制流复制超时时间，中断那些停止活动超过指定毫秒数的复制连接。这对发送服务器检测一个后备机崩溃或网络中断有用。设置为0将禁用该超时机制。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。默认值是 60 秒。
wal_sender_timeout = 60s 
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
# 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
wal_receiver_status_interval = 10s 
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈

修改完成重载
pg_ctl -D /usr/local/postgresql/data/ reload

3.4.4 从库配置（所有从库配置）

1.清空从库数据存储文件夹
rm -rf /usr/local/postgresql/data/*

2.从主服务器上copy数据到从服务器，这一步叫做“基础备份”
pg_basebackup -h 192.168.8.10 -p 5432 -U repuser -Fp -Xs -Pv -R -D /usr/local/postgresql/data/
-h –指定作为主服务器的主机。
-D –指定数据目录。
-U –指定连接用户。
-P –启用进度报告。
-v –启用详细模式。
-R–启用恢复配置的创建：创建一个standby.signal文件，并将连接设置附加到数据目录下的postgresql.auto.conf。
-X–用于在备份中包括所需的预写日志文件（WAL文件）。流的值表示在创建备份时流式传输WAL。
-C –在开始备份之前，允许创建由-S选项命名的复制插槽。
-S –指定复制插槽名称。

3.此时data目录下会出现standby.signal文件，编辑此文件
vim standby.signal
## 加入
standby_mode = 'on'

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

5.启动从库
pg_ctl start -l /usr/local/postgresql/log/pg_server.log -D /usr/local/postgresql/data/

6.登录主库并查询
[postgres@localhost data]$ psql 
psql (12.3)
Type "help" for help.
postgres=# select client_addr,sync_state from pg_stat_replication;
 client_addr  | sync_state 
--------------+------------
 192.168.8.20 | sync
 192.168.8.30 | potential
(2 rows)
postgres=#  \x # 切换垂直显示
Expanded display is on.
postgres=# select * from pg_stat_replication;
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 1615
usesysid         | 16384
usename          | repuser
application_name | walreceiver
client_addr      | 192.168.8.20
client_hostname  | 
client_port      | 43536
backend_start    | 2022-01-06 21:32:51.301058+08
backend_xmin     | 496
state            | streaming
sent_lsn         | 0/C000060
write_lsn        | 0/C000060
flush_lsn        | 0/C000060
replay_lsn       | 0/C000060
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 1
sync_state       | sync
reply_time       | 2022-01-06 21:33:31.877547+08
-[ RECORD 2 ]----+------------------------------
pid              | 1617
usesysid         | 16384
usename          | repuser
application_name | walreceiver
client_addr      | 192.168.8.30
client_hostname  | 
client_port      | 54644
backend_start    | 2022-01-06 21:32:53.401188+08
backend_xmin     | 496
state            | streaming
sent_lsn         | 0/C000060
write_lsn        | 0/C000060
flush_lsn        | 0/C000060
replay_lsn       | 0/C000060
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 1
sync_state       | potential
reply_time       | 2022-01-06 21:33:33.625504+08

3.4.5 验证主从复制

#主创建数据库
psql
postgres=# create database test_repl;
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test_repl | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
(4 rows)

#从库查看
psql
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test_repl | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
(4 rows)