Have you ever asked yourself, how to create a second Patroni PostgreSQL cluster on an existing server using the existing etcd? My first idea was to study the documentation of Patroni, but unfortunately without big success. This post should help to identify the changes you have to do on the hosts to run two parallel Patroni clusters using an existing etcd.

Starting Point

First we want to have a short look on the existing infrastructure to have a better overview where we are starting.

There is a Patroni installation and etcd already existing on the servers. As well as one PostgreSQL cluster streaming from primary to replica. We are not using two replicas in this example, but it works the same for numerous replicas.

etcd

As the etcd is already running on the hosts, let’s start with this one. And here we already have good news! You don’t have to change anything on etcd side. Just leave your configuration as it is.

postgres@postgres_primary:/home/postgres/.local/bin/ [PGTEST] cat /u01/app/postgres/local/dmk/dmk_postgres/etc/etcd.conf
name: postgres-primary
data-dir: /u02/postgres/pgdata/etcd
initial-advertise-peer-urls: http://192.168.22.33:2380
listen-peer-urls: http://192.168.22.33:2380
listen-client-urls: http://192.168.22.33:2379,http://localhost:2379
advertise-client-urls: http://192.168.22.33:2379
initial-cluster: postgres-primary=http://192.168.22.33:2380, postgres-stby=http://192.168.22.34:2380, postgres-stby2=http://192.168.22.35:2380

patroni.yml

Let’s go on with the patroni.yml. As there is already a Patroni running on that server you need to create another patroni.yml, let’s say patroni_pgtest.yml. To keep it simple and not reinventing the wheel, just copy your existing yml file

postgres@postgres_primary:/home/postgres/ [PGTEST] cd /u01/app/postgres/local/dmk/dmk_postgres/etc
postgres@postgres_primary:/u01/app/postgres/local/dmk/dmk_postgres/etc/ [PGTEST] cp patroni.yml patroni_pgtest.yml

Once we have the new patroni_pgtest.yml we need to adjust some entries in this file. Most important entries to change are “namespace” and “scope”. Without changing this, your new Patroni service won’t create a new PostgreSQL cluster

scope: PGTEST
namespace: /pgtest/
name: pgtest1

Next parameters to change are the restapi ones. You can keep the IP address, but you have to adjust the port. Otherwise the service will start with an: “Address already in use” error.

restapi:
  listen: 192.168.22.33:8009
  connect_address: 192.168.22.33:8009

Once this is done, of course the PostgreSQL parameters need to be adjusted to not use the same port and clustername as the already existing cluster. Further the PGDATA directory needs to be adjusted.

...
...
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        ...
        ...
        port: 5410
...
...
postgresql:
  listen: 192.168.22.33:5410
  connect_address: 192.168.22.33:5410
  data_dir: /u02/postgres/pgdata/13/PGTEST/
...
...

Patroni service

Now that we changed all our parameters, we can create a second Patroni service named patroni_pgtest.service. Be sure to point to the correct patroni_pgtest.yml

postgres@postgres_primary:/home/postgres/ [PGTEST] sudo vi /etc/systemd/system/patroni_pgtest.service
#
# systemd integration for patroni
#

[Unit]
Description=dbi services patroni service
After=etcd.service syslog.target network.target

[Service]
User=postgres
Type=simple
ExecStart=/u01/app/postgres/local/dmk/bin/patroni /u01/app/postgres/local/dmk/etc/patroni_pgtest.yml
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=process
Restart=no

[Install]
WantedBy=multi-user.target

Now we can start and enable the service

postgres@postgres_primary:/home/postgres/ [PGTEST] sudo systemctl start patroni_pgtest.service
postgres@postgres_primary:/home/postgres/ [PGTEST] sudo systemctl status patroni_pgtest.service
● patroni_pgtest.service - dbi services patroni service
   Loaded: loaded (/etc/systemd/system/patroni_pgtest.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2020-12-22 20:07:46 CET; 9h ago
 Main PID: 4418 (patroni)
   CGroup: /system.slice/patroni.service
           ├─4418 /usr/bin/python2 /u01/app/postgres/local/dmk/dmk_postgres/bin/patroni /u01/app/postgres/local/dmk/dmk_postgres/etc/patroni_pgtest.yml
           ├─5258 /u01/app/postgres/product/PG13/db_1/bin/postgres -D /u02/pgdata/13/PG1/ --config-file=/u02/postgres/pgdata/13/PG1/postgresql.conf --listen_addresses=192.168.22.33 --max_worker_processes=8 --max_locks_per_tra...
           ├─5282 postgres: PG1: logger process
           ├─5292 postgres: PG1: checkpointer process
           ├─5294 postgres: PG1: writer process
           ├─5296 postgres: PG1: stats collector process
           ├─6171 postgres: PG1: postgres postgres 192.168.22.33(50492) idle
           ├─6473 postgres: PG1: wal writer process
           ├─6474 postgres: PG1: autovacuum launcher process

Dec 23 05:36:21 postgres_primary patroni[4418]: 2020-12-23 05:36:21,032 INFO: Lock owner: postgres_primary; I am postgres_primary
Dec 23 05:36:21 postgres_primary patroni[4418]: 2020-12-23 05:36:21,047 INFO: no action.  i am the leader with the lock

postgres@postgres_primary:/home/postgres/ [PGTEST] sudo systemctl enable patroni_pgtest.service

As the cluster is running on the primary server now, you can do the exactly same steps on your replica server(s). Be sure to set all ports and IPs correctly.

Conclusion

Even if it seems to be easy to setup a second Patroni on a server, it took some time to found out, what exactly needs to be changes. But once you know all that, it’s really simple. Just keep in mind that you have to use a port for your PostgreSQL cluster that is not used at the moment.
Furthermore if you are using our DMK on your host, be sure to use ‘patronictl list’ calling the correct configuration file and the complete path for patronictl. DMK gives you an alias for patronictl which will only work for the first Patroni cluster created on the server.

postgres@postgres_primary:/home/postgres/ [PGTEST] cd .local/bin
postgres@postgres_primary:/home/postgres/.local/bin [PGTEST] patronictl -c /u01/app/postgres/local/dmk/etc/patroni_pgtest.yml list
+------------+------------------+---------------+--------+---------+-----+-----------+
| Cluster    |     Member       |      Host     |  Role  |  State  |  TL | Lag in MB |
+------------+------------------+---------------+--------+---------+-----+-----------+
|   PGTEST   | postgres_primary | 192.168.22.33 | Leader | running | 528 |       0.0 |
|   PGTEST   | postgres_replica | 192.168.22.34 |        | running | 528 |       0.0 |
+------------+------------------+---------------+--------+---------+-----+-----------+

In case you’re not using DMK, you have to add the configuration file in any case. You also have to set the correct PATH variable or use the complete path to call patronictl.