2

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.

Dave M
  • 4,494
  • 21
  • 30
  • 30
ufk
  • 323
  • 3
  • 7
  • 26

1 Answers1

0

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.

ufk
  • 323
  • 3
  • 7
  • 26