Add 3rd server with pglogical replication to a 2 server cluster with stream sync replication and failsafe with patroni

Question

I have 3 servers with Ubuntu 19.10, PostgreSQL 12.2 and Patroni 1.6.4 and I would like to have the following setup:

I want to have two servers with synchronised replication and I want to add a 3rd server with pglogical replication just to accept inserts to specific tables. no DELETE, TRUNCATE or anything else.

I log a huge amount of purchases per day, around 300M. I want to have to servers to already have the purchases history of the last 30 days, and the 3rd server which has a really big storage, will hold all the history of the purchases.

So far I have a patroni cluster with etcd for the 2 servers with the following configuration:

This is for the first and 2nd server I have the following configuration, of course the name of the server differs in each configuration:

scope: patroni_cluster_1 name: server_X restapi: listen: 0.0.0.0:8008 connect_address: X.X.X.X:8008 etcd: hosts: X.X.X.X:2379 protocol: http bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout : 10 maximum_lag_on_failover: 1048576 postgresql: use_pg_rewind: true use_slots: true parameters: wal_keep_segments: 100 initdb: - encoding: UTF8 - data-checksums pg_hba: - host replication replicator 0.0.0.0/0 md5 - host all all 0.0.0.0/0 md5 postgresql: listen: 0.0.0.0:5432 connect_address: X.X.X.X:5432 synchronous_mode: true synchronous_mode_strict: false data_dir: /var/lib/postgresql/patroni_cluster_1/server_X/data bin_dir: /usr/lib/postgresql/12/bin authentication: replication: username: replicator password: XXXX superuser: username: postgres password: XXXX parameters: shared_preload_libraries: pglogical 

I added pglogical as a shared library because of the 3rd server which I would like to know if I can fully setup with Patroni.

I read about how to setup pglogical with https://blog.dbi-services.com/postgresql-logical-replication-with-pglogical/ and I would like to know if I can fully configure the 3rd server with pglogical for inserts only.

I'm new to Patroni and I'm pretty lost so any information regarding this issue would be greatly appreciated.

Answer 1

the only method I found to do that is to create a bash script that executes psql commands that configure pglogical.

so I added a post_bootstrap property to the yaml as stated here: https://github.com/zalando/patroni/blob/master/docs/SETTINGS.rst

the bash script receives the datbase dsn as parameters, I use it to connect to it and configure what I please.

as far as I know there no way to configure pglogical directly from patroni.