PG 高可用集群部署

说明

规划

操作系统使用 CentOS 7.9
测试服务器IP及用户如下:

hostanme	IP	用户	用户目录	部署服务
c1	192.168.212.81	postgres	/app/postgres	etcd+pg16+Patroni
c2	192.168.212.82	postgres	/app/postgres	etcd+pg16+Patroni
c3	192.168.212.83	postgres	/app/postgres	etcd+pg16+Patroni

前期环境准备

创建 postgres 用户

c1/c2/c3 三个节点均执行

1
2
3
4
5
6
7
8


# 创建 /app 目录
mkdir /app

# 创建用户
useradd  -r -m -b /app postgres

# 设置密码
passwd postgres

部署 ETCD

git:https://github.com/etcd-io/etcd

安装

三个节点均执行，使用 postgres 用户
下载

1

wget https://github.com/etcd-io/etcd/releases/download/v3.5.17/etcd-v3.5.17-linux-amd64.tar.gz

安装

1
2
3
4
5
6
7
8
9


# 创建安装目录
mkdir etcd
# 解压
tar -zxvf etcd-v3.5.17-linux-amd64.tar.gz -C etcd
# 建立软连接
ln -sfn /app/postgres/etcd/etcd-v3.5.17-linux-amd64/ /app/postgres/etcd/etcd
# 查看 etcd 版本
./etcd/etcd/etcd -version
./etcd/etcd/etcdctl version

更改配置文件

创建用到的目录

三个节点均执行

1
2
3
4


# 创建配置文件目录
mkdir etcd/etc/
# 创建数据目录
mkdir etcd/data/

c1 更改配置文件

1
2
3
4
5
6
7
8


$ vi etcd/etc/etcd.yaml
name: 'node01'
data-dir: '/app/postgres/etcd/data'
listen-client-urls: 'http://0.0.0.0:2379'
advertise-client-urls: 'http://192.168.212.81:2379'
listen-peer-urls: 'http://192.168.212.81:2380'
initial-advertise-peer-urls: 'http://192.168.212.81:2380'
initial-cluster: 'node01=http://192.168.212.81:2380,node02=http://192.168.212.82:2380,node03=http://192.168.212.83:2380'

c2 更改配置文件

1
2
3
4
5
6
7
8


$ vi etcd/etc/etcd.yaml
name: 'node02'
data-dir: '/app/postgres/etcd/data'
listen-client-urls: 'http://0.0.0.0:2379'
advertise-client-urls: 'http://192.168.212.82:2379'
listen-peer-urls: 'http://192.168.212.82:2380'
initial-advertise-peer-urls: 'http://192.168.212.82:2380'
initial-cluster: 'node01=http://192.168.212.81:2380,node02=http://192.168.212.82:2380,node03=http://192.168.212.83:2380'

c3 更改配置文件

1
2
3
4
5
6
7
8


$ vi etcd/etc/etcd.yaml
name: 'node03'
data-dir: '/app/postgres/etcd/data'
listen-client-urls: 'http://0.0.0.0:2379'
advertise-client-urls: 'http://192.168.212.83:2379'
listen-peer-urls: 'http://192.168.212.83:2380'
initial-advertise-peer-urls: 'http://192.168.212.83:2380'
initial-cluster: 'node01=http://192.168.212.81:2380,node02=http://192.168.212.82:2380,node03=http://192.168.212.83:2380'

启动

三个节点均执行

1

nohup /app/postgres/etcd/etcd/etcd --config-file=/app/postgres/etcd/etc/etcd.yaml &

查看状态

1
2
3
4


$ ./etcd/etcd/etcdctl --endpoints=127.0.0.1:2379 member list
b2a6822b7dd7ecc, started, node01, http://192.168.212.81:2380, http://192.168.212.81:2379, false
27b166626a40beb5, started, node03, http://192.168.212.83:2380, http://192.168.212.83:2379, false
e455e548ca14710c, started, node02, http://192.168.212.82:2380, http://192.168.212.82:2379, false

部署一主两从流复制

安装 postgresql

三个节点均需操作

yum 安装依赖的包

1

yum -y install gcc make icu libicu libicu-devel readline-devel zlib-devel

配置目录及变量

1
2
3
4


mkdir -p /app/postgres/pgdata/pg16
mkdir -p /app/postgres/pgdata/pgwal/archive_wals
mkdir -p /app/postgres/pg/{pghome,patroni}
chmod -R 700 /app/postgres/{pg,pgdata}

配置环境变量

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


$ vi ~/.bashrc
export PGHOME=/app/postgres/pg/pghome
export PGDATA=/app/postgres/pgdata/pg16
export PGPORT=5432
export PGDATABASE=postgres
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH

# apply 变量
source .bashrc

编译安装

下载

1

wget https://ftp.postgresql.org/pub/source/v16.6/postgresql-16.6.tar.gz

编译安装

1
2
3
4
5
6
7


tar -zxvf postgresql-16.6.tar.gz
cd postgresql-16.6/
./configure --prefix=/app/postgres/pg/pghome --with-pgport=5432
gmake world -j2
gmake install
cd contrib 
make && make install

主节点初始化pg、更改配置

注意：仅主节点执行

初始化数据库

1

initdb -D $PGDATA -E UTF8 --locale=en_US.UTF-8 -k

更改配置文件

$PGDATA/postgresql.conf

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112


# 备份
$ mv $PGDATA/postgresql.conf $PGDATA/postgresql.bk
# 重新写入
$ vi $PGDATA/postgresql.conf
listen_addresses = '0.0.0.0'
port = '5432'
########################
#### 此部分为网站生成推荐参数
max_connections = 1500
shared_buffers = 3GB
effective_cache_size = 9GB
maintenance_work_mem = 768MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 524kB
huge_pages = off
min_wal_size = 1GB
max_wal_size = 4GB
# 允许的最大工作进程数量，8 适合于核心数一致
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
##############################
# 保留给超级用户的连接数，确保在负载高时超级用户仍然能够连接。
superuser_reserved_connections=10
# 每个事物可以持有的最大锁数量，64通常够用
max_locks_per_transaction = '64'
# 允许的最大准备事务数量，0 表示禁用准备事务
# 0 意味着禁用此功能，适用于不需要分布式事务的场景
# 如果需要支持分布式事务，建议将其设置为大于 0 的值，通常设置为 2 倍于预期最大连接数
max_prepared_transactions='0'
# 最多可以有 10 个复制槽
max_replication_slots = '10'
# 允许的最大 WAL 发送器数量，适用于流复制环境
max_wal_senders = '50'
# 禁用提交时间戳跟踪，默认是关闭的，可以减少性能开销
track_commit_timestamp = 'off'

####################################
# 日志相关
# 开启日志收集
logging_collector = on
# 指定日志输出格式为 CSV
log_destination=csvlog
# 日志文件的名称格式，其中 %a 表示星期
log_filename = 'postgresql-%a.log'
# 记录所有连接到数据库的日志
log_connections = on
# 记录所有断开连接的日
log_disconnections = on
# 记录检查点事件的日志，有助于性能调优
log_checkpoints = on
# 记录锁等待事件的日志
log_lock_waits = on
# 仅记录 DDL 语句（数据定义语言，如 CREATE、ALTER、DROP）
log_statement = ddl
# 日志轮转
log_truncate_on_rotation = on
# 日志文件的最大存活时间，单位为分钟，1440 分钟即 24 小时
log_rotation_age = 1440
####################################

###########################
# war log 相关配置
# 设置 WAL 日志级别为 "replica"，支持流复
wal_level = 'replica'
# 启用 WAL 日志提示，允许在某些情况下的优化
wal_log_hints = 'on'
# 启用归档模式，允许归档 WAL 日志
archive_mode = 'on'
# 指定归档命令，将 WAL 日志复制到指定路径
archive_command = 'cp %p /app/postgres/pgdata/pgwal/archive_wals/%f'
# 归档的超时时间，单位为秒，1800 秒即 30 分钟
archive_timeout = '1800s'
###############################

# 指定集群的名称，便于标识
cluster_name = 'PGCluster'
# 启用热备用，允许在备份服务器上执行只读查询
hot_standby = 'on'
# 设置同步提交模式，确保远程写入完成后再确认事务
synchronous_commit = remote_write
# 指定两个从节点，任何一个刷新成功，主库就认为成功
## 这个 db1 和 db2 对应从库配置文件里的 application_name
# synchronous_standby_names = 'ANY 1 (db02, db03)'
# 指定主机身份验证文件的路径
hba_file = '/app/postgres/pgdata/pg16/pg_hba.conf'
# 指定用户身份验证文件的路径
ident_file = '/app/postgres/pgdata/pg16/pg_ident.conf'
# 指定恢复命令，从归档中恢复 WAL 文件
restore_command = 'cp /app/postgres/pgdata/pgwal/archive_wals/%f %p'
# 指定恢复目标时间线为最新的
recovery_target_timeline = 'latest'


###################################
# 配置文件自带
# 设置日志记录的时区
log_timezone = 'Asia/Shanghai'
# 配置日期和时间的显示格式,此设置影响查询结果中的日期格式。
# so 表示采用 ISO 8601 标准（如 YYYY-MM-DD）
# mdy 表示月份、日期和年份的顺序（如 MM/DD/YYYY）。
datestyle = 'iso, mdy'
# 设置数据库的默认时区。所有时间戳数据类型
timezone = 'Asia/Shanghai'
# 设置默认的文本搜索配置，影响文本搜索的行为和结果
default_text_search_config = 'pg_catalog.english'
#####################################

$PGDATA/pg_hba.conf

1
2
3
4
5
6


$ vi $PGDATA/pg_hba.conf

# 内网网段内允许从库连接
host replication repl 192.168.212.0/24 md5
# 所有网段允许连接
host all all 0.0.0.0/0 md5

启动主库

1

pg_ctl start

创建复制槽

1
2
3
4
5
6
7


-- 创建复制角色
create role repl login replication encrypted password 'rPQQdmJ6ZUYvEG9e';
-- 创建复制槽
select * from pg_create_physical_replication_slot('slot_db02');
select * from pg_create_physical_replication_slot('slot_db03');
-- 检查
select slot_name,slot_type from pg_replication_slots;

构建流复制

拉取数据

1

pg_basebackup -D $PGDATA -Fp -Xs -v -P -R -h 192.168.212.81 -p 5432 -U repl

配置 db2

更改 postgresql.auto.conf
- 增加 application_name ，针对 synchronous_standby_names 要配置 ANY 时生效
- primary_slot_name 指定指定的复制槽名:slot_db02

1
2
3


$ vi  $PGDATA/postgresql.auto.conf
primary_conninfo = 'user=repl password=rPQQdmJ6ZUYvEG9e channel_binding=disable host=192.168.212.81 port=5432 sslmode=disable sslcompression=0 sslcertmode=disable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable application_name=db02'
primary_slot_name = 'slot_db02'

配置 db3

更改 postgresql.auto.conf
- 增加 application_name ，针对 synchronous_standby_names 要配置 ANY 时生效
- primary_slot_name 指定指定的复制槽名:slot_db03

1
2
3


$ vi  $PGDATA/postgresql.auto.conf
primary_conninfo = 'user=repl password=rPQQdmJ6ZUYvEG9e channel_binding=disable host=192.168.212.81 port=5432 sslmode=disable sslcompression=0 sslcertmode=disable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable application_name=db03'
primary_slot_name = 'slot_db03'

启动从库，检查从库状态

两个从库执行

1

pg_ctl start

查看主从状态

主库

1
2


# 连接数据库
psql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88


-- 开启换行显示
postgres=# \x
Expanded display is on.
-- 查看从库节点
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 1568
usesysid         | 16388
usename          | repl
application_name | db03
client_addr      | 192.168.212.83
client_hostname  |
client_port      | 34130
backend_start    | 2025-02-10 10:41:46.419486+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/D000148
write_lsn        | 0/D000148
flush_lsn        | 0/D000148
replay_lsn       | 0/D000148
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2025-02-10 10:49:18.760612+08
-[ RECORD 2 ]----+------------------------------
pid              | 1567
usesysid         | 16388
usename          | repl
application_name | db02
client_addr      | 192.168.212.82
client_hostname  |
client_port      | 55766
backend_start    | 2025-02-10 10:41:41.667841+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/D000148
write_lsn        | 0/D000148
flush_lsn        | 0/D000148
replay_lsn       | 0/D000148
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2025-02-10 10:49:18.7555+08
-- 主库查看备库落后WAL的字节数
postgres=# select pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) from pg_stat_replication;
-[ RECORD 1 ]---+--
pg_wal_lsn_diff | 0
-[ RECORD 2 ]---+--
pg_wal_lsn_diff | 0
-- 查看复制槽状态
-[ RECORD 1 ]-------+----------
slot_name           | slot_db02
plugin              |
slot_type           | physical
datoid              |
database            |
temporary           | f
active              | t
active_pid          | 1567
xmin                |
catalog_xmin        |
restart_lsn         | 0/D000148
confirmed_flush_lsn |
wal_status          | reserved
safe_wal_size       |
two_phase           | f
conflicting         |
-[ RECORD 2 ]-------+----------
slot_name           | slot_db03
plugin              |
slot_type           | physical
datoid              |
database            |
temporary           | f
active              | t
active_pid          | 1568
xmin                |
catalog_xmin        |
restart_lsn         | 0/D000148
confirmed_flush_lsn |
wal_status          | reserved
safe_wal_size       |
two_phase           | f
conflicting         |

从库

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


-- t 表示为是从库
postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
-- 查看 WAL 日志接收情况
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 2601
status                | streaming
receive_start_lsn     | 0/D000000
receive_start_tli     | 1
written_lsn           | 0/E000000
flushed_lsn           | 0/E000000
received_tli          | 1
last_msg_send_time    | 2025-02-10 11:12:42.983258+08
last_msg_receipt_time | 2025-02-10 11:12:42.988852+08
latest_end_lsn        | 0/E000000
latest_end_time       | 2025-02-10 11:09:05.679155+08
slot_name             | slot_db02
sender_host           | 192.168.212.81
sender_port           | 5432
conninfo              | user=repl password=******** channel_binding=disable dbname=replication host=192.168.212.81 port=5432 application_name=slot_db02 fallback_application_name=PGCluster sslmode=disable sslcompression=0 sslcertmode=disable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable

更改主库，设置任意数据库接收后即提交

更改主库，设置两个主库任意一个库接收数据后才提交

1
2
3


$ vi $PGDATA/postgresql.conf
# 增加此配置，db01 或 db02 任意一个节点接受数据后才提交
synchronous_standby_names = 'ANY 1 (db02, db03)'

编译安装 python 3.7

三个节点均执行
root/sudo 用户安装编译 Python 依赖的包

1

yum install libffi-devel openssl-devel

包下载地址：https://www.python.org/downloads/
下载包

1

wget https://www.python.org/ftp/python/3.7.17/Python-3.7.17.tar.xz

编译

1
2
3
4
5


tar -xvf Python-3.7.17.tar.xz
cd Python-3.7.17
./configure --prefix=/app/postgres/python/3.7.17 --enable-optimizations
make -j4
make install

配置 Patroni

安装 Patroni

三个节点均执行
创建 Patroni 用的 python 虚拟目录

1

/app/postgres/python/3.7.17/bin/python3 -m venv /app/postgres/pg/patroni/patroni-venv

安装 Patroni

1
2
3


/app/postgres/pg/patroni/patroni-venv/bin/python -m pip install --upgrade pip
/app/postgres/pg/patroni/patroni-venv/bin/python -m pip install psycopg2_binary
/app/postgres/pg/patroni/patroni-venv/bin/python -m pip install patroni[etcd]

更改配置文件

每个节点单独操作

c1 节点

1
2
3


$ vi ~/.pgpass
192.168.212.81:5432:*:repl:rPQQdmJ6ZUYvEG9e
$ chmod 0600 .pgpass

1
2


mkdir -p /app/postgres/pg/patroni/etc/
vi /app/postgres/pg/patroni/etc/postgres-pg01.yml

postgres-pg01.yml 增加如下配置

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146


# db01配置
scope: test_pg_cluster # 集群名
namespace: /service/
name: pg_patroni_01 # 节点名，三个节点不能相同

restapi:
  listen: 192.168.212.81:8008 # IP地址或者主机名，根据自己的情况设置
  connect_address: 192.168.212.81:8008 # IP地址或者主机名，根据自己的情况设置

etcd3:  
  hosts: #需自行设定，请勿照抄
  - 192.168.212.81:2379
  - 192.168.212.82:2379
  - 192.168.212.83:2379

bootstrap:  
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:        
        listen_addresses: '*'                  
        port: 5432                             
        max_connections: 1000                  
        superuser_reserved_connections: 3      
        unix_socket_directories: '.'
        unix_socket_permissions: 0700
        tcp_keepalives_idle: 60
        tcp_keepalives_interval: 10
        tcp_keepalives_count: 10
        shared_buffers: 4GB                      
        maintenance_work_mem: 2GB                  
        dynamic_shared_memory_type: posix
        vacuum_cost_delay: 0
        bgwriter_delay: 10ms
        bgwriter_lru_maxpages: 1000
        bgwriter_lru_multiplier: 10.0
        bgwriter_flush_after: 0                    
        max_worker_processes: 8
        max_parallel_workers_per_gather: 12
        old_snapshot_threshold: -1
        backend_flush_after: 0        
        synchronous_commit: off
        full_page_writes: on
        wal_buffers: 128MB
        wal_writer_delay: 10ms
        wal_writer_flush_after: 0
        checkpoint_timeout: 30min
        max_wal_size: 8GB
        min_wal_size: 2GB
        checkpoint_completion_target: 0.05
        checkpoint_flush_after: 0        
        max_wal_senders: 8
        random_page_cost: 1.3
        parallel_tuple_cost: 0
        parallel_setup_cost: 0
        effective_cache_size: 2GB
        log_destination: 'csvlog'
        logging_collector: on
        log_truncate_on_rotation: on
        log_checkpoints: on
        log_connections: on
        log_disconnections: on
        log_error_verbosity: verbose
        log_timezone: 'PRC'
        vacuum_defer_cleanup_age: 0
        wal_level: hot_standby
        hot_standby: on
        hot_standby_feedback: off
        wal_writer_delay: 10ms
        max_wal_senders: 10
        max_standby_archive_delay: 300s
        max_standby_streaming_delay: 300s
        autovacuum: on
        log_autovacuum_min_duration: 0
        autovacuum_max_workers: 4  
        autovacuum_naptime: 45s
        autovacuum_vacuum_scale_factor: 0.1
        autovacuum_analyze_scale_factor: 0.1
        autovacuum_freeze_max_age: 1600000000
        autovacuum_multixact_freeze_max_age: 1600000000
        vacuum_freeze_table_age: 1500000000
        vacuum_multixact_freeze_table_age: 1500000000
        datestyle: 'iso, mdy'
        timezone: 'PRC'
        lc_messages: 'C'
        lc_monetary: 'C'
        lc_numeric: 'C'
        lc_time: 'C'
        default_text_search_config: 'pg_catalog.english'
        shared_preload_libraries: 'pg_stat_statements'
        max_replication_slots: 10
        archive_command: cp %p /app/postgres/pgdata/pgwal/archive_wals/%f
        archive_mode: true

  initdb:
  - encoding: UTF8
  - data-checksums
  # 以下请根据自己的情况设置IP地址
  pg_hba:
  - host replication replica 192.168.212.0/24 md5
  - host all all 0.0.0.0/0 md5
  - host all all 127.0.0.1/32 md5

  users:
    admin:
      password: admin%
      options:
        - createrole
        - createdb

postgresql:
  listen: 0.0.0.0:5432
  # IP地址或者主机名，根据自己的情况设置
  connect_address: 192.168.212.81,127.0.0.1

  data_dir: /app/postgres/pgdata/pg16
  pgpass: /app/postgres/.pgpass
  authentication:
    replication:
      username: repl
      password: rPQQdmJ6ZUYvEG9e
    superuser:
      username: postgres
      password: postgres  
  
  parameters:    
    unix_socket_directories: '..'
  
  #没有vip，暂时屏蔽
  #callbacks:
    #on_start: /home/postgres/sbin/patroni_callback.sh
    #on_stop: /home/postgres/sbin/patroni_callback.sh
    #on_role_change: /home/postgres/sbin/patroni_callback.sh

  use_unix_socket: true

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

c2 节点

1
2
3


$ vi ~/.pgpass
192.168.212.82:5432:*:repl:rPQQdmJ6ZUYvEG9e
$ chmod 0600 .pgpass

1
2


mkdir -p /app/postgres/pg/patroni/etc/
vi /app/postgres/pg/patroni/etc/postgres-pg02.yml

postgres-pg02.yml 增加如下配置

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146


# db02配置
scope: test_pg_cluster # 集群名
namespace: /service/
name: pg_patroni_02 # 节点名，三个节点不能相同

restapi:
  listen: 192.168.212.82:8008 # IP地址或者主机名，根据自己的情况设置
  connect_address: 192.168.212.82:8008 # IP地址或者主机名，根据自己的情况设置

etcd3:  
  hosts: #需自行设定，请勿照抄
  - 192.168.212.81:2379
  - 192.168.212.82:2379
  - 192.168.212.83:2379

bootstrap:  
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:        
        listen_addresses: '*'                  
        port: 5432                             
        max_connections: 1000                  
        superuser_reserved_connections: 3      
        unix_socket_directories: '.'
        unix_socket_permissions: 0700
        tcp_keepalives_idle: 60
        tcp_keepalives_interval: 10
        tcp_keepalives_count: 10
        shared_buffers: 4GB                      
        maintenance_work_mem: 2GB                  
        dynamic_shared_memory_type: posix
        vacuum_cost_delay: 0
        bgwriter_delay: 10ms
        bgwriter_lru_maxpages: 1000
        bgwriter_lru_multiplier: 10.0
        bgwriter_flush_after: 0                    
        max_worker_processes: 8
        max_parallel_workers_per_gather: 12
        old_snapshot_threshold: -1
        backend_flush_after: 0        
        synchronous_commit: off
        full_page_writes: on
        wal_buffers: 128MB
        wal_writer_delay: 10ms
        wal_writer_flush_after: 0
        checkpoint_timeout: 30min
        max_wal_size: 8GB
        min_wal_size: 2GB
        checkpoint_completion_target: 0.05
        checkpoint_flush_after: 0        
        max_wal_senders: 8
        random_page_cost: 1.3
        parallel_tuple_cost: 0
        parallel_setup_cost: 0
        effective_cache_size: 2GB
        log_destination: 'csvlog'
        logging_collector: on
        log_truncate_on_rotation: on
        log_checkpoints: on
        log_connections: on
        log_disconnections: on
        log_error_verbosity: verbose
        log_timezone: 'PRC'
        vacuum_defer_cleanup_age: 0
        wal_level: hot_standby
        hot_standby: on
        hot_standby_feedback: off
        wal_writer_delay: 10ms
        max_wal_senders: 10
        max_standby_archive_delay: 300s
        max_standby_streaming_delay: 300s
        autovacuum: on
        log_autovacuum_min_duration: 0
        autovacuum_max_workers: 4  
        autovacuum_naptime: 45s
        autovacuum_vacuum_scale_factor: 0.1
        autovacuum_analyze_scale_factor: 0.1
        autovacuum_freeze_max_age: 1600000000
        autovacuum_multixact_freeze_max_age: 1600000000
        vacuum_freeze_table_age: 1500000000
        vacuum_multixact_freeze_table_age: 1500000000
        datestyle: 'iso, mdy'
        timezone: 'PRC'
        lc_messages: 'C'
        lc_monetary: 'C'
        lc_numeric: 'C'
        lc_time: 'C'
        default_text_search_config: 'pg_catalog.english'
        shared_preload_libraries: 'pg_stat_statements'
        max_replication_slots: 10
        archive_command: cp %p /app/postgres/pgdata/pgwal/archive_wals/%f
        archive_mode: true

  initdb:
  - encoding: UTF8
  - data-checksums
  # 以下请根据自己的情况设置IP地址
  pg_hba:
  - host replication replica 192.168.212.0/24 md5
  - host all all 0.0.0.0/0 md5
  - host all all 127.0.0.1/32 md5

  users:
    admin:
      password: admin%
      options:
        - createrole
        - createdb

postgresql:
  listen: 0.0.0.0:5432
  # IP地址或者主机名，根据自己的情况设置
  connect_address: 192.168.212.82,127.0.0.1

  data_dir: /app/postgres/pgdata/pg16
  pgpass: /app/postgres/.pgpass
  authentication:
    replication:
      username: repl
      password: rPQQdmJ6ZUYvEG9e
    superuser:
      username: postgres
      password: postgres  
  
  parameters:    
    unix_socket_directories: '..'
  
  #没有vip，暂时屏蔽
  #callbacks:
    #on_start: /home/postgres/sbin/patroni_callback.sh
    #on_stop: /home/postgres/sbin/patroni_callback.sh
    #on_role_change: /home/postgres/sbin/patroni_callback.sh

  use_unix_socket: true

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

c3 节点

1
2
3


$ vi ~/.pgpass
192.168.212.83:5432:*:repl:rPQQdmJ6ZUYvEG9e
$ chmod 0600 .pgpass

1
2


mkdir -p /app/postgres/pg/patroni/etc/
vi /app/postgres/pg/patroni/etc/postgres-pg03.yml

postgres-pg02.yml 增加如下配置

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146


# db01配置
scope: test_pg_cluster # 集群名
namespace: /service/
name: pg_patroni_03 # 节点名，三个节点不能相同

restapi:
  listen: 192.168.212.83:8008 # IP地址或者主机名，根据自己的情况设置
  connect_address: 192.168.212.83:8008 # IP地址或者主机名，根据自己的情况设置

etcd3:  
  hosts: #需自行设定，请勿照抄
  - 192.168.212.81:2379
  - 192.168.212.82:2379
  - 192.168.212.83:2379

bootstrap:  
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:        
        listen_addresses: '*'                  
        port: 5432                             
        max_connections: 1000                  
        superuser_reserved_connections: 3      
        unix_socket_directories: '.'
        unix_socket_permissions: 0700
        tcp_keepalives_idle: 60
        tcp_keepalives_interval: 10
        tcp_keepalives_count: 10
        shared_buffers: 4GB                      
        maintenance_work_mem: 2GB                  
        dynamic_shared_memory_type: posix
        vacuum_cost_delay: 0
        bgwriter_delay: 10ms
        bgwriter_lru_maxpages: 1000
        bgwriter_lru_multiplier: 10.0
        bgwriter_flush_after: 0                    
        max_worker_processes: 8
        max_parallel_workers_per_gather: 12
        old_snapshot_threshold: -1
        backend_flush_after: 0        
        synchronous_commit: off
        full_page_writes: on
        wal_buffers: 128MB
        wal_writer_delay: 10ms
        wal_writer_flush_after: 0
        checkpoint_timeout: 30min
        max_wal_size: 8GB
        min_wal_size: 2GB
        checkpoint_completion_target: 0.05
        checkpoint_flush_after: 0        
        max_wal_senders: 8
        random_page_cost: 1.3
        parallel_tuple_cost: 0
        parallel_setup_cost: 0
        effective_cache_size: 2GB
        log_destination: 'csvlog'
        logging_collector: on
        log_truncate_on_rotation: on
        log_checkpoints: on
        log_connections: on
        log_disconnections: on
        log_error_verbosity: verbose
        log_timezone: 'PRC'
        vacuum_defer_cleanup_age: 0
        wal_level: hot_standby
        hot_standby: on
        hot_standby_feedback: off
        wal_writer_delay: 10ms
        max_wal_senders: 10
        max_standby_archive_delay: 300s
        max_standby_streaming_delay: 300s
        autovacuum: on
        log_autovacuum_min_duration: 0
        autovacuum_max_workers: 4  
        autovacuum_naptime: 45s
        autovacuum_vacuum_scale_factor: 0.1
        autovacuum_analyze_scale_factor: 0.1
        autovacuum_freeze_max_age: 1600000000
        autovacuum_multixact_freeze_max_age: 1600000000
        vacuum_freeze_table_age: 1500000000
        vacuum_multixact_freeze_table_age: 1500000000
        datestyle: 'iso, mdy'
        timezone: 'PRC'
        lc_messages: 'C'
        lc_monetary: 'C'
        lc_numeric: 'C'
        lc_time: 'C'
        default_text_search_config: 'pg_catalog.english'
        shared_preload_libraries: 'pg_stat_statements'
        max_replication_slots: 10
        archive_command: cp %p /app/postgres/pgdata/pgwal/archive_wals/%f
        archive_mode: true

  initdb:
  - encoding: UTF8
  - data-checksums
  # 以下请根据自己的情况设置IP地址
  pg_hba:
  - host replication replica 192.168.212.0/24 md5
  - host all all 0.0.0.0/0 md5
  - host all all 127.0.0.1/32 md5

  users:
    admin:
      password: admin%
      options:
        - createrole
        - createdb

postgresql:
  listen: 0.0.0.0:5432
  # IP地址或者主机名，根据自己的情况设置
  connect_address: 192.168.212.83,127.0.0.1

  data_dir: /app/postgres/pgdata/pg16
  pgpass: /app/postgres/.pgpass
  authentication:
    replication:
      username: repl
      password: rPQQdmJ6ZUYvEG9e
    superuser:
      username: postgres
      password: postgres  
  
  parameters:    
    unix_socket_directories: '..'
  
  #没有vip，暂时屏蔽
  #callbacks:
    #on_start: /home/postgres/sbin/patroni_callback.sh
    #on_stop: /home/postgres/sbin/patroni_callback.sh
    #on_role_change: /home/postgres/sbin/patroni_callback.sh

  use_unix_socket: true

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

启动

每个节点单独操作
c1

1
2


mkdir -p /app/postgres/pg/patroni/log/
/app/postgres/pg/patroni/patroni-venv/bin/patroni /app/postgres/pg/patroni/etc/postgres-pg01.yml >> /app/postgres/pg/patroni/log/patroni.log 2>&1 &

1
2


mkdir -p /app/postgres/pg/patroni/log/
/app/postgres/pg/patroni/patroni-venv/bin/patroni /app/postgres/pg/patroni/etc/postgres-pg02.yml >> /app/postgres/pg/patroni/log/patroni.log 2>&1 &

1
2


mkdir -p /app/postgres/pg/patroni/log/
/app/postgres/pg/patroni/patroni-venv/bin/patroni /app/postgres/pg/patroni/etc/postgres-pg03.yml >> /app/postgres/pg/patroni/log/patroni.log 2>&1 &

问题处理

patroni 命令由于 urllib3 报错

报错信息如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


Traceback (most recent call last):
  File "./patroni", line 5, in <module>
    from patroni.__main__ import main
  File "/app/postgres/pg/patroni/patroni-venv/lib/python3.7/site-packages/patroni/__main__.py", line 17, in <module>
    from patroni.tags import Tags
  File "/app/postgres/python/patroni-venv/lib/python3.7/site-packages/patroni/tags.py", line 6, in <module>
    from patroni.utils import parse_bool, parse_int
  File "/app/postgres/pg/patroni/patroni-venv/lib/python3.7/site-packages/patroni/utils.py", line 31, in <module>
    from urllib3.response import HTTPResponse
  File "/app/postgres/pg/patroni/patroni-venv/lib/python3.7/site-packages/urllib3/__init__.py", line 42, in <module>
    "urllib3 v2.0 only supports OpenSSL 1.1.1+, currently "
ImportError: urllib3 v2.0 only supports OpenSSL 1.1.1+, currently the 'ssl' module is compiled with 'OpenSSL 1.0.2k-fips  26 Jan 2017'. See: https://github.com/urllib3/urllib3/issues/2168

解决方案：降级 urllib 版本
- 可以查看对应版本 patroni 的 requirements.txt 查看推荐的版本
  - https://github.com/patroni/patroni/blob/master/requirements.txt

1

/app/postgres/pg/patroni/patroni-venv/bin/python -m pip install urllib3==1.19.1

文章目录

PG 高可用集群部署

说明

规划

前期环境准备

创建 postgres 用户

部署 ETCD

安装

更改配置文件

创建用到的目录

c1 更改配置文件

c2 更改配置文件

c3 更改配置文件

启动

部署一主两从流复制

安装 postgresql

yum 安装依赖的包

配置目录及变量

编译安装

主节点初始化pg、更改配置

初始化数据库

更改配置文件

启动主库

构建流复制

拉取数据

配置 db2

配置 db3

启动从库，检查从库状态

更改主库，设置任意数据库接收后即提交

编译安装 python 3.7

配置 Patroni

安装 Patroni

更改配置文件

c1 节点

c2 节点

c3 节点

启动

问题处理

patroni 命令由于 urllib3 报错

参考