Open in app

Sign in

Write

Sign in

PostgreSQL — ETCD — Patroni Cluster

Umut Tekin
13 min readApr 20, 2022

--

Hi,

In this blog I am going to explain how to build a PostgreSQL database cluster with ETCD and PostgreSQL. There are a many examples on the internet, but I am also going to try to give some insights on the details.

Let’ s get started!

In order to create this environment of course I am using Docker :).

The content of my Dockerfile(I will write later about how we can build our own image from a Dockerfile):

FROM debian:latest
RUN apt update -y && apt install vim systemctl iputils-ping init net-tools -y && apt-get clean all
EXPOSE 22
ENTRYPOINT [“/sbin/init”]
CMD [“systemctl”]

Then, run three instances:

docker run -d — name b1 myimage
docker run -d — name b2 myimage
docker run -d — name b3 myimage

The IP addresses for the containers:

docker inspect b1|egrep \”IPAddress| head -1
“IPAddress”: “172.17.0.8”
docker inspect b2|egrep \”IPAddress| head -1
“IPAddress”: “172.17.0.9”
docker inspect b3|egrep \”IPAddress| head -1
“IPAddress”: “172.17.0.10”

After connecting all three containers I am executing following commands respectively:

apt install etcd -y
mkdir -p /data/etcd/wal
mkdir -p /data/etcd/log
chown -R etcd:etcd /data

First, we installed etcd and then created related files that we will use for configuration and logs. Afterwards, we can change our configuration file. In order to copy and paste a yaml file we need to switch “paste” mode in “vi/vim” with using “:set paste” command.
The reasons why we cannot copy and paste successfully in vi are auto insert comment and auto indenting. For details: https://unix.stackexchange.com/questions/199203/why-does-vim-indent-pasted-code-incorrectly

vi /data/etcd-config.yml
:set paste

The content of first node’ s configuration file:

name: 'p1'
data-dir: /data/etcd
wal-dir: /data/etcd/wal
snapshot-count: 10000
quota-backend-bytes: 0
listen-peer-urls: http://172.17.0.8:2380
listen-client-urls: http://172.17.0.8:2379
max-snapshots: 5
max-wals: 5
cors:
initial-advertise-peer-urls: http://172.17.0.8:2380
advertise-client-urls: http://172.17.0.8:2379
initial-cluster: p1=http://172.17.0.8:2380,p2=http://172.17.0.9:2380,p3=http://172.17.0.10:238
initial-cluster-token: 'etcd-cluster'
initial-cluster-state: 'new'
enable-v2: true
debug: false
logger: zap
log-outputs: ['stdout', '/data/etcd/log/etcd.log','stderr','/data/etcd/log/error.log']

The content of second node’ s configuration file:

name: 'p2'
data-dir: /data/etcd
wal-dir: /data/etcd/wal
snapshot-count: 10000
quota-backend-bytes: 0
listen-peer-urls: http://172.17.0.9:2380
listen-client-urls: http://172.17.0.9:2379
max-snapshots: 5
max-wals: 5
cors:
initial-advertise-peer-urls: http://172.17.0.9:2380
advertise-client-urls: http://172.17.0.9:2379
initial-cluster: p1=http://172.17.0.8:2380,p2=http://172.17.0.9:2380,p3=http://172.17.0.10:2380
initial-cluster-token: 'etcd-cluster'
initial-cluster-state: 'new'
enable-v2: true
debug: false
logger: zap
log-outputs: ['stdout', '/data/etcd/log/etcd.log','stderr','/data/etcd/log/error.log']

You can do the third one :).

We can edit our service files:

vi /lib/systemd/system/etcd.service

The contents of service file are same for all three instances:

[Unit]
Description=etcd — highly-available key value store
Documentation=https://etcd.io/docs
Documentation=man:etcd
After=network.target
Wants=network-online.target

[Service]
Environment=DAEMON_ARGS=
Environment=ETCD_NAME=%H
Environment=ETCD_CONFIG_FILE=/data/etcd-config.yml
Environment=ETCD_DATA_DIR=/var/lib/etcd/default
EnvironmentFile=-/etc/default/%p
Type=notify
User=etcd
PermissionsStartOnly=true
#ExecStart=/bin/sh -c "GOMAXPROCS=$(nproc) /usr/bin/etcd $DAEMON_ARGS"
ExecStart=/usr/bin/etcd $DAEMON_ARGS - config-file $ETCD_CONFIG_FILE
Restart=on-abnormal
#RestartSec=10s
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target
Alias=etcd2.service

The important lines in this file are

Environment=ETCD_CONFIG_FILE=/data/etcd-config.yml
ExecStart=/usr/bin/etcd $DAEMON_ARGS — config-file $ETCD_CONFIG_FILE

We start etcd using our own configuration file we created. The environment file specified in “EnvironmentFile=-/etc/default/%p” is not a configuration file. It contains environment file that contains environment variables you define.
The “-” sign beginning of the command checks the file if exists and if the file does not exist, then it does not throw an error. “%p” points out the file name “etcd” in the “/etc/default” directory. Because the service is “etcd.service”, so it looks for a file with named without extension.

We are ready to start our etcd services:

systemctl daemon-reload
systemctl start etcd

Check status of the cluster and list member:

etcdctl -C http://172.17.0.8:2379,http://172.17.0.9:2379,http://172.17.0.10:2379 member list
 
16d847c91e5c79e8: name=p1 peerURLs=http://172.17.0.8:2380 clientURLs=http://172.17.0.8:2379 isLeader=true
4d06357bf07648db: name=p3 peerURLs=http://172.17.0.10:2380 clientURLs=http://172.17.0.10:2379 isLeader=false
72267c2c850a86cc: name=p2 peerURLs=http://172.17.0.9:2380 clientURLs=http://172.17.0.9:2379 isLeader=false
etcdctl -C http://172.17.0.8:2379,http://172.17.0.9:2379,http://172.17.0.10:2379 cluster-health
 
member 16d847c91e5c79e8 is healthy: got healthy result from http://172.17.0.8:2379
member 4d06357bf07648db is healthy: got healthy result from http://172.17.0.10:2379
member 72267c2c850a86cc is healthy: got healthy result from http://172.17.0.9:2379
cluster is healthy

First part is completed, we can proceed to the second part.

I am using https://www.postgresql.org/download/ to install latest version of the PostgreSQL. You can follow the steps for your own architecture.

In order to use a specific data directory(@ all three instances):

mkdir -p /var/lib/postgresql/14/data
chown -R postgres:postgres /var/lib/postgresql
chmod 750 /var/lib/postgresql/14/data

I will start PostgreSQL cluster using Patroni.

apt install patroni -y

Let’ s edit our Patroni configuration file.

vi /etc/patroni/config.yml
:set paste

For the first node:

scope: pgsql
namespace: /pgsql/
name: pgsql_node1
restapi:
 listen: 172.17.0.8:8008
 connect_address: 172.17.0.8:8008
etcd:
 host: 172.17.0.8:2379
bootstrap:
 dcs:
  ttl: 30
  loop_wait: 10
  retry_timeout: 10
  maximum_lag_on_failover: 1048576
  postgresql:
   use_pg_rewind: true
 initdb:
 - encoding: UTF8
 - data-checksums
 pg_hba:
 - host replication repl 0.0.0.0/0 md5
 - host all all 0.0.0.0/0 md5
postgresql:
 listen: 0.0.0.0:5432
 connect_address: 172.17.0.8:5432
 data_dir: /var/lib/postgresql/14/data
 bin_dir: /usr/lib/postgresql/14/bin
 config_dir: /etc/postgresql/14/data/
 authentication:
  replication:
   username: repl
   password: '123123'
  superuser:
   username: postgres
   password: '123123'
tags:
 nofailover: false
 noloadbalance: false
 clonefrom: false
 nosync: false

For the second instance:

scope: pgsql
namespace: /pgsql/
name: pgsql_node2
restapi:
 listen: 172.17.0.9:9009
 connect_address: 172.17.0.9:9009
etcd:
 host: 172.17.0.9:2379
bootstrap:
 dcs:
  ttl: 30
  loop_wait: 10
  retry_timeout: 10
  maximum_lag_on_failover: 1049576
  postgresql:
   use_pg_rewind: true
 initdb:
 - encoding: UTF9
 - data-checksums
 pg_hba:
 - host replication repl 0.0.0.0/0 md5
 - host all all 0.0.0.0/0 md5
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 172.17.0.9:5432
  data_dir: /var/lib/postgresql/14/data
  bin_dir: /usr/lib/postgresql/14/bin
  config_dir: /etc/postgresql/14/data/
  authentication:
  replication:
   username: repl
   password: '123123'
  superuser:
   username: postgres
   password: '123123'
tags:
 nofailover: false
 noloadbalance: false
 clonefrom: false
 nosync: false

Again, you can do the third one :).

One of the other important thing here is “password” block. If you are using numbers in your very strong password like I do, then you need enclose your password quotes.

Let’ s start Patroni services :).

systemctl start patroni
systemctl status patroni
patroni.service — Runners to orchestrate a high-availability PostgreSQL
Loaded: loaded (/lib/systemd/system/patroni.service, disabled)
Active: failed (failed)

When I checked the log I saw this error:

creating directory /var/lib/postgresql/14/data … initdb: error: could not create directory “/var/lib/postgresql/14/data”: Permission denied
pg_ctl: database system initialization failed
2022–04–19 14:11:26,581 INFO: removing initialize key after failed attempt to bootstrap the cluster
Traceback (most recent call last):
File “/usr/bin/patroni”, line 33, in <module>
sys.exit(load_entry_point(‘patroni==2.0.2’, ‘console_scripts’, ‘patroni’)())
File “/usr/lib/python3/dist-packages/patroni/__init__.py”, line 170, in main
return patroni_main()
File “/usr/lib/python3/dist-packages/patroni/__init__.py”, line 138, in patroni_main
abstract_main(Patroni, schema)
File “/usr/lib/python3/dist-packages/patroni/daemon.py”, line 100, in abstract_main
controller.run()
File “/usr/lib/python3/dist-packages/patroni/__init__.py”, line 108, in run
super(Patroni, self).run()
File “/usr/lib/python3/dist-packages/patroni/daemon.py”, line 59, in run
self._run_cycle()
File “/usr/lib/python3/dist-packages/patroni/__init__.py”, line 111, in _run_cycle
logger.info(self.ha.run_cycle())
File “/usr/lib/python3/dist-packages/patroni/ha.py”, line 1457, in run_cycle
info = self._run_cycle()
File “/usr/lib/python3/dist-packages/patroni/ha.py”, line 1351, in _run_cycle
return self.post_bootstrap()
File “/usr/lib/python3/dist-packages/patroni/ha.py”, line 1247, in post_bootstrap
self.cancel_initialization()
File “/usr/lib/python3/dist-packages/patroni/ha.py”, line 1240, in cancel_initialization
raise PatroniFatalException(‘Failed to bootstrap cluster’)
patroni.exceptions.PatroniFatalException: ‘Failed to bootstrap cluster’

or you might have this:

2022–04–19 13:58:23,658 ERROR: Exception during execution of long running task bootstrap
Traceback (most recent call last):
File “/usr/lib/python3/dist-packages/patroni/async_executor.py”, line 97, in run
wakeup = func(*args) if args else func()
File “/usr/lib/python3/dist-packages/patroni/postgresql/bootstrap.py”, line 292, in bootstrap
return do_initialize(config.get(method)) and self._postgresql.config.append_pg_hba(pg_hba) \
File “/usr/lib/python3/dist-packages/patroni/postgresql/config.py”, line 422, in append_pg_hba
with open(self._pg_hba_conf, ‘a’) as f:
FileNotFoundError: [Errno 2] No such file or directory: ‘/etc/postgresql/14/data/pg_hba.conf’
2022–04–19 13:58:23,667 INFO: removing initialize key after failed attempt to bootstrap the cluster
2022–04–19 13:58:23,676 INFO: renaming data directory to /var/lib/postgresql/14/data_2022–04–19–13–58–23
2022–04–19 13:58:23,678 ERROR: Could not rename data directory /var/lib/postgresql/14/data
Traceback (most recent call last):

Both case stem from wrong permission set. On the previous steps instead of

chown postgres:postgres /var/lib/postgresql/14/data

we should have executed

chown -R postgres:postgres /var/lib/postgresql

After fixing privileges:

systemctl start patroni
systemctl status patroni
patroni.service — Runners to orchestrate a high-availability PostgreSQL
Loaded: loaded (/lib/systemd/system/patroni.service, disabled)
Active: active (running)

Check the logs again:

2022–04–19 14:15:06,854 INFO: No PostgreSQL configuration items changed, nothing to reload.
2022–04–19 14:15:06,866 INFO: Lock owner: None; I am pgsql_node1
2022–04–19 14:15:06,876 INFO: trying to bootstrap a new cluster
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 locales
COLLATE: C
CTYPE: C.UTF-8
MESSAGES: C
MONETARY: C
NUMERIC: C
TIME: C
The default text search configuration will be set to "english".
Data page checksums are enabled.
creating directory /var/lib/postgresql/14/data … ok
creating subdirectories … ok
selecting dynamic shared memory implementation … posix
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default time zone … Etc/UTC
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
initdb: 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/lib/postgresql/14/bin/pg_ctl -D /var/lib/postgresql/14/data -l logfile start
2022–04–19 14:15:11.429 UTC [12919] LOG: starting PostgreSQL 14.2 (Debian 14.2–1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1–6) 10.2.1 20210110, 64-bit
2022–04–19 14:15:11.430 UTC [12919] LOG: listening on IPv4 address "0.0.0.0", port 5432
2022–04–19 14:15:11,430 INFO: postmaster pid=12919
2022–04–19 14:15:11.436 UTC [12919] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022–04–19 14:15:11.446 UTC [12921] LOG: database system was shut down at 2022–04–19 14:15:08 UTC
2022–04–19 14:15:11.448 UTC [12922] FATAL: the database system is starting up
localhost:5432 - rejecting connections
2022–04–19 14:15:11.459 UTC [12919] LOG: database system is ready to accept connections
localhost:5432 - accepting connections
2022–04–19 14:15:11,482 INFO: establishing a new patroni connection to the postgres cluster
2022–04–19 14:15:11,495 INFO: running post_bootstrap
2022–04–19 14:15:11,543 WARNING: Could not activate Linux watchdog device: "Can't open watchdog device: [Errno 2] No such file or directory: '/dev/watchdog'"
2022–04–19 14:15:11,570 INFO: initialized a new cluster
2022–04–19 14:15:21,549 INFO: Lock owner: pgsql_node1; I am pgsql_node1
2022–04–19 14:15:21,581 INFO: no action. i am the leader with the lock
2022–04–19 14:15:21,611 INFO: Lock owner: pgsql_node1; I am pgsql_node1
2022–04–19 14:15:21,625 INFO: no action. i am the leader with the lock

A few examples:

patronictl -c /etc/patroni/config.yml list
+ Cluster: pgsql (7088318277869490800) — — — — -+ — — + — — — — — -+
| Member | Host | Role | State | TL | Lag in MB |
+ — — — — — — -+ — — — — — — -+ — — — — -+ — — — — -+ — — + — — — — — -+
| pgsql_node1 | 172.17.0.8 | Leader | running | 1 | |
| pgsql_node2 | 172.17.0.9 | Replica | running | 1 | 0 |
| pgsql_node3 | 172.17.0.10 | Replica | running | 1 | 0 |
+ — — — — — — -+ — — — — — — -+ — — — — -+ — — — — -+ — — + — — — — — -+

If you have problem with initiating one of your standy nodes you can use:

patronictl -c /etc/patroni/config.yml list
+ Cluster: pgsql (7088318277869490800) - - - - - - -+ - - + - - - - - -+
| Member | Host | Role | State | TL | Lag in MB |
+ - - - - - - -+ - - - - - - + - - - - -+ - - - - - - - + - - + - - - - - -+
| pgsql_node1 | 172.17.0.8 | Leader | running | 1 | |
| pgsql_node2 | 172.17.0.9 | Replica | start failed | | unknown |
| pgsql_node3 | 172.17.0.10| Replica | running | 1 | 0 |
+ - - - - - - -+ - - - - - - + - - - - -+ - - - - - - - + - - + - - - - - -+

patronictl -c /etc/patroni/config.yml reinit pgsql
+ Cluster: pgsql (7088318277869490800) - - - - -+ - - + - - - - - -+
| Member | Host | Role | State | TL | Lag in MB |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+
| pgsql_node1 | 172.17.0.8 | Leader | running | 1 | |
| pgsql_node2 | 172.17.0.9 | Replica | running | 1 | 0 |
| pgsql_node3 | 172.17.0.10 | Replica | running | 1 | 0 |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+
Which member do you want to reinitialize [pgsql_node1, pgsql_node2, pgsql_node3]? []: pgsql_node2
Are you sure you want to reinitialize members pgsql_node2? [y/N]: y
Success: reinitialize for member pgsql_node2

patronictl -c /etc/patroni/config.yml list
+ Cluster: pgsql (7088318277869490800) - - - - - - - - - + - - + - - - - - -+
| Member | Host | Role | State | TL | Lag in MB |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - - - - - - + - - + - - - - - -+
| pgsql_node1 | 172.17.0.8 | Leader | running | 1 | |
| pgsql_node2 | 172.17.0.9 | Replica | creating replica | | unknown |
| pgsql_node3 | 172.17.0.10 | Replica | running | 1 | 0 |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - - - - - - + - - + - - - - - -+

patronictl -c /etc/patroni/config.yml list
+ Cluster: pgsql (7088318277869490800) - - - - -+ - - + - - - - - -+
| Member | Host | Role | State | TL | Lag in MB |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+
| pgsql_node1 | 172.17.0.8 | Leader | running | 1 | |
| pgsql_node2 | 172.17.0.9 | Replica | running | 1 | 0 |
| pgsql_node3 | 172.17.0.10 | Replica | running | 1 | 0 |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+

In order to switchover:

patronictl -c /etc/patroni/config.yml switchover
Master [pgsql_node1]:
Candidate ['pgsql_node2', 'pgsql_node3'] []: pgsql_node2
When should the switchover take place (e.g. 2022–04–19T15:45 ) [now]:
Current cluster topology
+ Cluster: pgsql (7088318277869490800) - - - - -+ - - + - - - - - -+
| Member | Host | Role | State | TL | Lag in MB |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+
| pgsql_node1 | 172.17.0.8 | Leader | running | 1 | |
| pgsql_node2 | 172.17.0.9 | Replica | running | 1 | 0 |
| pgsql_node3 | 172.17.0.10 | Replica | running | 1 | 0 |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+
Are you sure you want to switchover cluster pgsql, demoting current master pgsql_node1? [y/N]: y
2022–04–19 14:45:11.95836 Successfully switched over to "pgsql_node2"
+ Cluster: pgsql (7088318277869490800) - - - - -+ - - + - - - - - -+
| Member | Host | Role | State | TL | Lag in MB |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+
| pgsql_node1 | 172.17.0.8 | Replica | stopped | | unknown |
| pgsql_node2 | 172.17.0.9 | Leader | running | 1 | |
| pgsql_node3 | 172.17.0.10 | Replica | running | 1 | 0 |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+

patronictl -c /etc/patroni/config.yml list
+ Cluster: pgsql (7088318277869490800) - - - - -+ - - + - - - - - -+
| Member | Host | Role | State | TL | Lag in MB |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+
| pgsql_node1 | 172.17.0.8 | Replica | stopped | | unknown |
| pgsql_node2 | 172.17.0.9 | Leader | running | 2 | |
| pgsql_node3 | 172.17.0.10 | Replica | running | 1 | 0 |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+

patronictl -c /etc/patroni/config.yml list
+ Cluster: pgsql (7088318277869490800) - - - - -+ - - + - - - - - -+
| Member | Host | Role | State | TL | Lag in MB |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+
| pgsql_node1 | 172.17.0.8 | Replica | running | 2 | 0 |
| pgsql_node2 | 172.17.0.9 | Leader | running | 2 | |
| pgsql_node3 | 172.17.0.10 | Replica | running | 2 | 0 |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+

In order to restart any of the nodes:

patronictl -c /etc/patroni/config.yml restart pgsql pgsql_node3
+ Cluster: pgsql (7088318277869490800) — — — — -+ — — + — — — — — -+
| Member | Host | Role | State | TL | Lag in MB |
+ — — — — — — -+ — — — — — — -+ — — — — -+ — — — — -+ — — + — — — — — -+
| pgsql_node1 | 172.17.0.8 | Replica | running | 2 | 0 |
| pgsql_node2 | 172.17.0.9 | Leader | running | 2 | |
| pgsql_node3 | 172.17.0.10 | Replica | running | 2 | 0 |
+ — — — — — — -+ — — — — — — -+ — — — — -+ — — — — -+ — — + — — — — — -+
When should the restart take place (e.g. 2022–04–19T15:48) [now]:
Are you sure you want to restart members pgsql_node3? [y/N]: y
Restart if the PostgreSQL version is less than provided (e.g. 9.5.2) []:
Success: restart on member pgsql_node3

patronictl -c /etc/patroni/config.yml list
+ Cluster: pgsql (7088318277869490800) - - - - -+ - - + - - - - - -+
| Member | Host | Role | State | TL | Lag in MB |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+
| pgsql_node1 | 172.17.0.8 | Replica | running | 2 | 0 |
| pgsql_node2 | 172.17.0.9 | Leader | running | 2 | |
| pgsql_node3 | 172.17.0.10 | Replica | running | 2 | 0 |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+

In ordert to list switchover history:

patronictl -c /etc/patroni/config.yml history
+ — — + — — — — — -+ — — — — — — — — — — — — — — — + — — — — — — — — — — — — — — — — — +
| TL | LSN | Reason | Timestamp |
+ — — + — — — — — -+ — — — — — — — — — — — — — — — + — — — — — — — — — — — — — — — — — +
| 1 | 218104200 | no recovery target specified | 2022–04–19T14:45:11.425132+00:00 |
+ — — + — — — — — -+ — — — — — — — — — — — — — — — + — — — — — — — — — — — — — — — — — +

Last but not least we can edit our database configuration. Initially my configuration:

patronictl -c /etc/patroni/config.yml show-config
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
 use_pg_rewind: true
retry_timeout: 10
ttl: 30

Two database parameters before change:

postgres=# show work_mem;
work_mem
— — — — 
4MB
(1 row)

postgres=# show max_connections;
max_connections
- - - - - - - -
100
(1 row)

I tried to edit configuration and got this error:

patronictl -c /etc/patroni/config.yml edit-config
Traceback (most recent call last):
File "/usr/bin/patronictl", line 33, in <module>
sys.exit(load_entry_point('patroni==2.0.2', 'console_scripts', 'patronictl')())
File "/usr/lib/python3/dist-packages/click/core.py", line 829, in __call__
return self.main(*args, **kwargs)
File "/usr/lib/python3/dist-packages/click/core.py", line 782, in main
rv = self.invoke(ctx)
File "/usr/lib/python3/dist-packages/click/core.py", line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/usr/lib/python3/dist-packages/click/core.py", line 1066, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/usr/lib/python3/dist-packages/click/core.py", line 610, in invoke
return callback(*args, **kwargs)
File "/usr/lib/python3/dist-packages/click/decorators.py", line 33, in new_func
return f(get_current_context().obj, *args, **kwargs)
File "/usr/lib/python3/dist-packages/patroni/ctl.py", line 1248, in edit_config
show_diff(before_editing, after_editing)
File "/usr/lib/python3/dist-packages/patroni/ctl.py", line 1101, in show_diff
markup_to_pager(PatchStream(buf), opts)
File "/usr/lib/python3/dist-packages/cdiff.py", line 639, in markup_to_pager
pager = subprocess.Popen(
File "/usr/lib/python3.9/subprocess.py", line 951, in __init__
self._execute_child(args, executable, preexec_fn, close_fds,
File "/usr/lib/python3.9/subprocess.py", line 1823, in _execute_child
raise child_exception_type(errno_num, err_msg, err_filename)
FileNotFoundError: [Errno 2] No such file or directory: 'less'

Because I am using Debian Docker image and it is included basic packages we need to install less.

apt install less -y
patronictl -c /etc/patroni/config.yml edit-config
 - -
 +++
 @@ -2,5 +2,12 @@
 maximum_lag_on_failover: 1048576
 postgresql:
 use_pg_rewind: true
 + parameters:
 + work_mem: 50MB
 + max_connections: 200
 + pg_hba:
 + - local all postgres ident
 + - host all all 0.0.0.0/0 md5
 + - host replication replicator 0.0.0.0/0 md5
 retry_timeout: 10
 ttl: 30
Apply these changes? [y/N]: y
 Configuration changed
 
 patronictl -c /etc/patroni/config.yml show-config
 loop_wait: 10
 maximum_lag_on_failover: 1048576
 postgresql:
 parameters:
 work_mem: 50MB
 max_connections: 200
 pg_hba:
 - local all postgres ident
 - host all all 0.0.0.0/0 md5
 - host replication replicator 0.0.0.0/0 md5
 use_pg_rewind: true
 retry_timeout: 10
 ttl: 30
+ Cluster: pgsql (7088318277869490800) - - - - -+ - - + - - - - - -+ - - - - - - - - -+
| Member | Host | Role | State | TL | Lag in MB | Pending restart |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+ - - - - - - - - -+
| pgsql_node1 | 172.17.0.8 | Leader | running | 7 | | * |
| pgsql_node2 | 172.17.0.9 | Replica | running | 7 | 0 | * |
| pgsql_node3 | 172.17.0.10 | Replica | running | 7 | 0 | * |
+ - - - - - - -+ - - - - - - -+ - - - - -+ - - - - -+ - - + - - - - - -+ - - - - - - - - -+

Two database parameters after change:

postgres=# show max_connections;
max_connections
— — — — — — — -
200
(1 row)

postgres=# show work_mem;
work_mem
- - - - - 
50MB
(1 row)

And we are done for now :).

Sorry about the YAML files, indentation is devastated here. Checkout all YAML files: https://github.com/kr4k3nh4ck/Notes/tree/main/postgresql_etcd_patroni

Please let me know if there is something wrong or needs to change!

Postgresql
Postgresql Database
Postgresql Replication
Etcd
Patroni

--

--

Umut Tekin

Written by Umut Tekin

24 Followers

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams