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.
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.