How to set Postgresql parameters in a Patroni cluster
Article ID: 295226
Updated On:
Products
VMware Tanzu for Postgres
Issue/Introduction
In a Patroni cluster, if we set parameters directly in postgresql.conf, it might not be working and the settings will even be rolled back after Postgresql instance restart. So we can use the patronictl edit-config command to do the settings.
Resolution
Example:
1. Cluster information:
-bash-4.2$ patronictl -c /var/lib/pgsql/patroni_etcd_conf.d/postgres_member3.yaml list +-----------------+-------------+-------------------+--------+---------+----+-----------+-----------------+ | Cluster | Member | Host | Role | State | TL | Lag in MB | Pending restart | +-----------------+-------------+-------------------+--------+---------+----+-----------+-----------------+ | patroni_cluster | postgresql1 | 172.28.8.251:7432 | | running | 22 | 0 | * | | patroni_cluster | postgresql2 | 172.28.8.251:7433 | | running | 22 | 0 | | | patroni_cluster | postgresql3 | 172.28.8.251:7434 | Leader | running | 22 | | | +-----------------+-------------+-------------------+--------+---------+----+-----------+-----------------+
2. Firstly, we can check the current settings using the command "patronictl -c /var/lib/pgsql/patroni_etcd_conf.d/postgres_member3.yaml edit-config":
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
parameters:
hot_standby: 'on'
max_replication_slots: 8
max_wal_senders: 8
wal_keep_segments: 20
use_pg_rewind: true
use_slots: true
retry_timeout: 10
ttl: 30
3. From the above result, the part under "postgresql -> parameters" section is where Postgresql parameter resides. Then we can add some configurations there and then save the changes:
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
parameters:
hot_standby: 'on'
max_replication_slots: 8
max_wal_senders: 8
wal_keep_segments: 20
logging_collector: on
log_directory: 'my_pg_log'
use_pg_rewind: true
use_slots: true
retry_timeout: 10
ttl: 30
4. Then we can reload the changes using the command "patronictl reload [OPTIONS] CLUSTER_NAME [MEMBER_NAMES]", and we can even specify only some of the Postgresql instances to reload, it is for all by default:
-bash-4.2$ patronictl -c /var/lib/pgsql/patroni_etcd_conf.d/postgres_member3.yaml reload patroni_cluster +-----------------+-------------+-------------------+--------+---------+----+-----------+-----------------+ | Cluster | Member | Host | Role | State | TL | Lag in MB | Pending restart | +-----------------+-------------+-------------------+--------+---------+----+-----------+-----------------+ | patroni_cluster | postgresql1 | 172.28.8.251:7432 | | running | 22 | 0 | * | | patroni_cluster | postgresql2 | 172.28.8.251:7433 | | running | 22 | 0 | | | patroni_cluster | postgresql3 | 172.28.8.251:7434 | Leader | running | 22 | | | +-----------------+-------------+-------------------+--------+---------+----+-----------+-----------------+ Are you sure you want to reload members postgresql2, postgresql3, postgresql1? [y/N]: y Reload request received for member postgresql2 and will be processed within 10 seconds Reload request received for member postgresql3 and will be processed within 10 seconds Reload request received for member postgresql1 and will be processed within 10 seconds
5. Then we check the data directory and do not find "my_pg_log" directory, that's because the parameters we just changed need a instance restart to make its change effective:
-bash-4.2$ ll total 116 -rw-------. 1 postgres postgres 225 Oct 1 21:46 backup_label.old drwx------. 5 postgres postgres 41 Oct 1 21:46 base drwx------. 2 postgres postgres 4096 Oct 1 22:38 global -rw-------. 1 postgres postgres 312 Oct 1 22:44 patroni.dynamic.json drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_commit_ts drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_dynshmem -rw-------. 1 postgres postgres 4659 Oct 1 21:46 pg_hba.conf -rw-------. 1 postgres postgres 4659 Oct 1 22:38 pg_hba.conf.backup -rw-------. 1 postgres postgres 1636 Oct 1 21:46 pg_ident.conf -rw-------. 1 postgres postgres 1636 Oct 1 22:38 pg_ident.conf.backup drwx------. 4 postgres postgres 68 Oct 1 21:51 pg_logical drwx------. 4 postgres postgres 36 Oct 1 21:46 pg_multixact drwx------. 2 postgres postgres 18 Oct 1 22:38 pg_notify drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_replslot drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_serial drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_snapshots drwx------. 2 postgres postgres 6 Oct 1 22:38 pg_stat drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_stat_tmp drwx------. 2 postgres postgres 18 Oct 1 21:51 pg_subtrans drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_tblspc drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_twophase -rw-------. 1 postgres postgres 3 Oct 1 21:46 PG_VERSION drwx------. 3 postgres postgres 116 Oct 1 21:47 pg_wal drwx------. 2 postgres postgres 18 Oct 1 21:46 pg_xact -rw-------. 1 postgres postgres 88 Oct 1 21:46 postgresql.auto.conf -rw-------. 1 postgres postgres 23990 Oct 1 21:46 postgresql.base.conf -rw-------. 1 postgres postgres 23990 Oct 1 22:38 postgresql.base.conf.backup -rw-r--r--. 1 postgres postgres 647 Oct 1 22:46 postgresql.conf -rw-r--r--. 1 postgres postgres 592 Oct 1 22:38 postgresql.conf.backup -rw-------. 1 postgres postgres 437 Oct 1 22:38 postmaster.opts -rw-------. 1 postgres postgres 86 Oct 1 22:38 postmaster.pid -rw-------. 1 postgres postgres 292 Oct 1 22:38 recovery.conf -rw-------. 1 postgres postgres 292 Oct 1 21:46 recovery.done
6. After restarting any of the Postgresql instances in the Patroni cluster (we only need to stop one instance and Patroni will bring it up automatically), we can see the directory "my_pg_log":
-bash-4.2$ pg_ctl stop -D ./ waiting for server to shut down.... done server stopped -bash-4.2$ ll total 120 -rw-------. 1 postgres postgres 225 Oct 1 21:46 backup_label.old drwx------. 5 postgres postgres 41 Oct 1 21:46 base -rw-------. 1 postgres postgres 50 Oct 1 22:58 current_logfiles drwx------. 2 postgres postgres 4096 Oct 1 22:58 global drwx------. 2 postgres postgres 46 Oct 1 22:58 my_pg_log -rw-------. 1 postgres postgres 312 Oct 1 22:44 patroni.dynamic.json drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_commit_ts drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_dynshmem -rw-------. 1 postgres postgres 4659 Oct 1 21:46 pg_hba.conf -rw-------. 1 postgres postgres 4659 Oct 1 22:58 pg_hba.conf.backup -rw-------. 1 postgres postgres 1636 Oct 1 21:46 pg_ident.conf -rw-------. 1 postgres postgres 1636 Oct 1 22:58 pg_ident.conf.backup drwx------. 4 postgres postgres 68 Oct 1 21:51 pg_logical drwx------. 4 postgres postgres 36 Oct 1 21:46 pg_multixact drwx------. 2 postgres postgres 18 Oct 1 22:58 pg_notify drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_replslot drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_serial drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_snapshots drwx------. 2 postgres postgres 6 Oct 1 22:58 pg_stat drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_stat_tmp drwx------. 2 postgres postgres 18 Oct 1 21:51 pg_subtrans drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_tblspc drwx------. 2 postgres postgres 6 Oct 1 21:46 pg_twophase -rw-------. 1 postgres postgres 3 Oct 1 21:46 PG_VERSION drwx------. 3 postgres postgres 116 Oct 1 21:47 pg_wal drwx------. 2 postgres postgres 18 Oct 1 21:46 pg_xact -rw-------. 1 postgres postgres 88 Oct 1 21:46 postgresql.auto.conf -rw-------. 1 postgres postgres 23990 Oct 1 21:46 postgresql.base.conf -rw-------. 1 postgres postgres 23990 Oct 1 22:58 postgresql.base.conf.backup -rw-r--r--. 1 postgres postgres 647 Oct 1 22:58 postgresql.conf -rw-r--r--. 1 postgres postgres 647 Oct 1 22:58 postgresql.conf.backup -rw-------. 1 postgres postgres 437 Oct 1 22:58 postmaster.opts -rw-------. 1 postgres postgres 86 Oct 1 22:58 postmaster.pid -rw-------. 1 postgres postgres 292 Oct 1 22:58 recovery.conf -rw-------. 1 postgres postgres 292 Oct 1 21:46 recovery.done
Feedback
Yes
No
Powered by
