How to set Postgresql parameters in a Patroni cluster
search cancel

How to set Postgresql parameters in a Patroni cluster

book

Article ID: 295226

calendar_today

Updated On: 02-10-2022

Products

VMware Tanzu Greenplum

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