Patroni + Etcd 实现高可用之三: 参数配置

前两篇文章介绍了Patroni + Etcd高可用部署和高可用测试,其中Patroni用来接管对PostgreSQL实例的管理,例如PostgreSQL的初始化、启动、停止、参数配置等,并将配置信息存储到etcd集群。

如果想修改PostgreSQL实例的参数,需通过Patroni统一管理,单独修改postgresql.conf参数不再生效,本文介绍Patroni + Etcd高可用方案中PostgreSQL的参数设置。

通过Patroni统一管理PostgreSQL的配置参数具有以下优点:

  • 对于不需要重启生效的参数,可在任意Patroni节点配置,修改后对其它节点也生效。
  • 所有Patroni节点都可以进行参数配置。
  • 减少配置工作量,减少人为配置错误。

通过Patroni统一管理PostgreSQL的配置参数也有让人不习惯的地方,例如:

  • Patroni新生成postgresql.base.conf配置文件,并将原有postgresql.conf的配置全部导入到此文件,之后重新生成postgresql.conf配置文件。
  • postgresql.conf配置文件不可手工修改,由Patroni管理。

总之,Patroni接管PostgreSQL的参数配置,对PostgreSQL维护体系侵入较大,使用时需注意。

通过etcdctl查看参数

查看数据库参数配置的方法有多种,单实例或主从架构下,可登录数据库查看,也可通过postgresql.conf查看。

Patroni + Etcd高可用方案中可以登录数据库查看,但每个节点的postgresql.conf或postgresql.base.conf文件中参数的配置并不一定生效。

由于Patroni参数存储在etcd中,因此可通过etcdctl命令查看,也可通过patronictl命令查看。

下面介绍下etcdctl查看数据库配置参数,在.bash_profile中设置以下环境变量:

1
2
export ETCDCTL_API=2
export ENDPOINTS="http://192.168.2.11:2379,http://192.168.2.12:2379,http://192.168.2.13:2379"

由于安装的是etcd3.4,etcd3.4默认使用API 3 版本,Patroni1.6 不支持 API 3版本,因此,使用API 2版本,设置ETCDCTL_API环境变量,同时设置ENDPOINTS环境变量。

使用etcdctl查看配置参数,如下:

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
[pg12@ydtf01 patroni]$ etcdctl --endpoints=$ENDPOINTS get /service/pg_ydtf/config | jq .
{
"loop_wait": 10,
"master_start_timeout": 300,
"maximum_lag_on_failover": 1048576,
"postgresql": {
"use_pg_rewind": true,
"use_slots": true,
"parameters": {
"archive_command": "/bin/date",
"archive_mode": true,
"autovacuum": true,
"autovacuum_max_workers": 3,
"bgwriter_delay": "10ms",
"checkpoint_timeout": "30min",
"full_page_writes": true,
"hot_standby": true,
"listen_addresses": "*",
"log_autovacuum_min_duration": -1,
"log_checkpoints": true,
"log_connections": true,
"log_destination": "csvlog",
"log_directory": "log",
"log_file_mode": 384,
"log_filename": "postgresql-%Y-%m-%d_%H%M%S.log",
"log_min_messages": "warning",
"log_rotation_age": "1d",
"log_rotation_size": "10MB",
"log_timezone": "Asia/Shanghai",
"log_truncate_on_rotation": false,
"logging_collector": true,
"maintenance_work_mem": "128MB",
"max_connections": 1000,
"max_wal_senders": 10,
"max_wal_size": "1GB",
"min_wal_size": "80MB",
"port": 1921,
"random_page_cost": 2,
"seq_page_cost": 1,
"shared_buffers": "128MB",
"superuser_reserved_connections": 3,
"synchronous_commit": false,
"unix_socket_directories": "/tmp",
"unix_socket_group": "",
"unix_socket_permissions": 511,
"wal_keep_segments": 256,
"wal_level": "logical",
"work_mem": "4MB"
}
},
"retry_timeout": 10,
"synchronous_mode": false,
"ttl": 30
}

通过patronictl查看配置参数

patronictl命令也可以查看数据库配置参数,如下:

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
[pg12@ydtf01 patroni]$ patronictl -c /home/pg12/patroni/pg_ydtf01.yml show-config
loop_wait: 10
master_start_timeout: 300
maximum_lag_on_failover: 1048576
postgresql:
parameters:
archive_command: /bin/date
archive_mode: true
autovacuum: true
autovacuum_max_workers: 3
bgwriter_delay: 10ms
checkpoint_timeout: 30min
full_page_writes: true
hot_standby: true
listen_addresses: '*'
log_autovacuum_min_duration: -1
log_checkpoints: true
log_connections: true
log_destination: csvlog
log_directory: log
log_file_mode: 384
log_filename: postgresql-%Y-%m-%d_%H%M%S.log
log_min_messages: warning
log_rotation_age: 1d
log_rotation_size: 10MB
log_timezone: Asia/Shanghai
log_truncate_on_rotation: false
logging_collector: true
maintenance_work_mem: 128MB
max_connections: 1000
max_wal_senders: 10
max_wal_size: 1GB
min_wal_size: 80MB
port: 1921
random_page_cost: 2.0
seq_page_cost: 1.0
shared_buffers: 128MB
superuser_reserved_connections: 3
synchronous_commit: false
unix_socket_directories: /tmp
unix_socket_group: ''
unix_socket_permissions: 511
wal_keep_segments: 256
wal_level: logical
work_mem: 4MB
use_pg_rewind: true
use_slots: true
retry_timeout: 10
synchronous_mode: false
ttl: 30

之后就可以编辑以上参数或者新增其它参数。

通过patronictl调整配置参数

若想修改数据库参数,使用patronictl命令即可,例如,调整wal_keep_segments为512,如下:

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
[pg12@ydtf01 ~]$ patronictl -c /home/pg12/patroni/pg_ydtf01.yml edit-config

loop_wait: 10
master_start_timeout: 300
maximum_lag_on_failover: 1048576
postgresql:
parameters:
archive_command: /bin/date
archive_mode: true
autovacuum: true
autovacuum_max_workers: 3
bgwriter_delay: 10ms
checkpoint_timeout: 30min
full_page_writes: true
hot_standby: true
listen_addresses: '*'
log_autovacuum_min_duration: -1
log_checkpoints: true
log_connections: true
log_destination: csvlog
log_directory: log
log_file_mode: 384
log_filename: postgresql-%Y-%m-%d_%H%M%S.log
log_min_messages: warning
log_rotation_age: 1d
log_rotation_size: 10MB
log_timezone: Asia/Shanghai
log_truncate_on_rotation: false
logging_collector: true
maintenance_work_mem: 128MB
max_connections: 1000
max_wal_senders: 10
max_wal_size: 1GB
min_wal_size: 80MB
port: 1921
random_page_cost: 2.0
seq_page_cost: 1.0
shared_buffers: 128MB
superuser_reserved_connections: 3
synchronous_commit: false
unix_socket_directories: /tmp
unix_socket_group: ''
unix_socket_permissions: 511
wal_keep_segments: 512
wal_level: logical
work_mem: 4MB
use_pg_rewind: true
use_slots: true
retry_timeout: 10
synchronous_mode: false
ttl: 30

修改之后,点保存,出现如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[pg12@ydtf01 ~]$ patronictl -c /home/pg12/patroni/pg_ydtf01.yml edit-config
---
+++
@@ -8,7 +8,7 @@
max_parallel_workers: 8
max_parallel_workers_per_gather: 2
max_worker_processes: 8
- wal_keep_segments: 256
+ wal_keep_segments: 512
wal_level: logical
retry_timeout: 10
synchronous_mode: false

Apply these changes? [y/N]: y
Configuration changed

登录pg_ydtf01、pg_ydtf02、pg_ydtf03节点查看 wal_keep_segments 参数,发现都已生效,连patronictl reload命令也省了,可以通过查看数据库的日志确认,如下:

1
2
2020-06-02 09:05:23.909 CST,,,14664,,5ed59c2b.3948,5,,2020-06-02 08:24:11 CST,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,""
2020-06-02 09:05:23.910 CST,,,14664,,5ed59c2b.3948,6,,2020-06-02 08:24:11 CST,,0,LOG,00000,"parameter ""wal_keep_segments"" changed to ""512""",,,,,,,,,""

对于需要重启生效的参数,需要重启数据库才能生效,为了减少对生产的影响,可以逐个节点重启,也可以在停机窗口通过patronictl restart对整个集群进行重启,如下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[pg12@ydtf02 patroni]$ patronictl -c /home/pg12/patroni/pg_ydtf02.yml restart pg_ydtf
+ Cluster: pg_ydtf (6823331807018309697) +---------+----+-----------+-----------------+
| Member | Host | Role | State | TL | Lag in MB | Pending restart |
+-----------+-------------------+--------+---------+----+-----------+-----------------+
| pg_ydtf01 | 192.168.2.11:1921 | | running | 36 | 0 | * |
| pg_ydtf02 | 192.168.2.12:1921 | Leader | running | 36 | | * |
| pg_ydtf03 | 192.168.2.13:1921 | | running | 36 | 0 | * |
+-----------+-------------------+--------+---------+----+-----------+-----------------+
When should the restart take place (e.g. 2020-06-01T23:13) [now]:
Are you sure you want to restart members pg_ydtf02, pg_ydtf03, pg_ydtf01? [y/N]: y
Restart if the PostgreSQL version is less than provided (e.g. 9.5.2) []:
Success: restart on member pg_ydtf02
Success: restart on member pg_ydtf03
Success: restart on member pg_ydtf01

注意,确认PostgreSQL版本这行不要填写,否则会报错,详见文章末尾。

如何仅调整指定节点参数?

上述提到了PostgreSQL的配置参数统一由Patroni管理,单独修改节点的postgresql.conf参数不再生效。

如果想差异化配置其中一个节点的参数,可通过ALTER SYSTEM命令动态配置,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[pg12@ydtf01 pg_root]$ psql
psql (12.2)
Type "help" for help.

postgres=# show wal_keep_segments ;
wal_keep_segments
-------------------
128
(1 row)

postgres=# ALTER SYSTEM SET wal_keep_segments =200;
ALTER SYSTEM

postgres=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

postgres=# show wal_keep_segments ;
wal_keep_segments
-------------------
200
(1 row)

常见错误

错误一: etcdctl无法查到key

etcdctl无法列出目录,也无法查到key,如下:

1
2
3
4
5
6
7
8
[pg12@ydtf01 ~]$ export ETCDCTL_API=3;
[pg12@ydtf01 ~]$ etcdctl --endpoints="http://192.168.2.11:2379,http://192.168.2.12:2379,http://192.168.2.13:2379" ls /service/pg_ydtf
Error: unknown command "ls" for "etcdctl"
Run 'etcdctl --help' for usage.
Error: unknown command "ls" for "etcdctl"

[pg12@ydtf01 ~]$ etcdctl --endpoints="http://192.168.2.11:2379,http://192.168.2.12:2379,http://192.168.2.13:2379" get /service/pg_ydtf/config
[pg12@ydtf01 ~]$

解决方法: 由于patroni 1.6 不支持 etcdctl 的 API 3版本,将环境变量ETCDCTL_API设置成2即可,如下:

1
2
3
[pg12@ydtf01 ~]$ export ETCDCTL_API=2;
[pg12@ydtf01 ~]$ etcdctl --endpoints="http://192.168.2.11:2379,http://192.168.2.12:2379,http://192.168.2.13:2379" get /service/pg_ydtf/config
{"loop_wait": 10, "master_start_timeout": 300, "maximum_lag_on_failover": 1048576, "postgresql": {"parameters": {"listen_addresses": "*", "max_connections": 2000, "max_parallel_workers": 8, "max_parallel_workers_per_gather": 2, "max_worker_processes": 8, "wal_keep_segments": 512, "wal_level": "logical"}}, "retry_timeout": 10, "synchronous_mode": false, "ttl": 30}

错误二: patronictl重启集群报错

使用patronictl重启集群时,在填写PostgreSQL版本时,若填写12.2,则报错,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[pg12@ydtf01 ~]$ patronictl -c /home/pg12/patroni/pg_ydtf01.yml restart pg_ydtf
+ Cluster: pg_ydtf (6823331807018309697) +---------+----+-----------+-----------------+
| Member | Host | Role | State | TL | Lag in MB | Pending restart |
+-----------+-------------------+--------+---------+----+-----------+-----------------+
| pg_ydtf01 | 192.168.2.11:1921 | | running | 36 | 0 | * |
| pg_ydtf02 | 192.168.2.12:1921 | Leader | running | 36 | | * |
| pg_ydtf03 | 192.168.2.13:1921 | | running | 36 | 0 | * |
+-----------+-------------------+--------+---------+----+-----------+-----------------+
When should the restart take place (e.g. 2020-06-01T23:11) [now]:
Are you sure you want to restart members pg_ydtf01, pg_ydtf02, pg_ydtf03? [y/N]: y
Restart if the PostgreSQL version is less than provided (e.g. 9.5.2) []: 12.2
Failed: restart for member pg_ydtf01, status code=503, (restart conditions are not satisfied)
Failed: restart for member pg_ydtf02, status code=503, (restart conditions are not satisfied)
Failed: restart for member pg_ydtf03, status code=503, (restart conditions are not satisfied)

解决方法:PostgreSQL版本这行空着敲回车即可。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%