Settings up replication between two stolon clusters

[codervinod]

We are trying to have one stolon cluster in each GCP region and have replication amongst these clusters. Read will only go to one stolon cluster , however we want reads to happen locally. Can anyone guide if this is possible?

Submission type

• Bug report
• Request for enhancement (RFE)

Environment

Stolon version

Additional environment information if useful to understand the bug

Expected behaviour you didn't see

Unexpected behaviour you saw

Steps to reproduce the problem

Enhancement Description

[Negashev]

@codervinod You can try use pgpool

[dineshba]

Are you using stable/stolon helm chart ?

[Negashev]

Yes, I add pgpool after stolon

[dineshba]

@Negashev Sorry I didn't understand. Can you please elaborate?

[Negashev]

@dineshba Okay, cards on the table
I make that, read (green) by pgpool from all keeper's and write (red) only to proxy

my configuration for k8s

1. start stolon with 3 keepers

  proxy: 
    replicaCount: "3"
  replicationPassword: "replication"
  superuserPassword: "password"

2. trust all connetions (from pgpool)

stolonctl --cluster-name=stolon --store-backend=kubernetes --kube-resource-kind=configmap update --patch '{ "pgHBA" : [ "host all all 0.0.0.0/0 trust", "host all all ::0/0 trust", "local all all trust", "host replication repluser 0.0.0.0/0 md5", "host replication repluser ::0/0 md5" ] }'

3. deploy postdock/pgpool image (scale 3)
   env's

BACKENDS=0:stolon-proxy::::ALWAYS_MASTER,1:stolon-keeper-0.stolon-keeper-headless::::DISALLOW_TO_FAILOVER,2:stolon-keeper-1.stolon-keeper-headless::::DISALLOW_TO_FAILOVER,3:stolon-keeper-2.stolon-keeper-headless::::DISALLOW_TO_FAILOVER
DB_USERS=stolon:password
REQUIRE_MIN_BACKENDS=2
WAIT_BACKEND_TIMEOUT=5

and health check
bash -c /usr/local/bin/pgpool/has_write_node.sh && /usr/local/bin/pgpool/has_enough_backends.sh

[jskswamy]

@codervinod currently stolon doesn't support connecting to standby replicas, refer #132

I can think of two possible solution

1. Use the python script mentioned here to generate haproxy config and use it to connect to standby replicas
2. Use service discovery as mentioned in Service discovery #557

[jskswamy]

@Negashev as menioned in the FAQ it's not advisable to connect to keeper directly

[sgotti]

Stolon is for postgres high availability, you can do whatever you want to read from slaves based on your application requirements, just ensure that you use the stolon proxy when doing write transactions.

[Paddey]

Hello,
Do i understand right, @codervinod means something like picture. Are any way for Stolon do like below?

Cheers!
Paddey

[rajiteh]

We are interested in a use case like above too. Anyone has any suggestions?

[prabhu43]

@rajiteh @Paddey Yes, this is possible. You can have 1 primary stolon cluster which can take both reads and writes and can setup any number of secondary stolon clusters(standby). And all the keepers of this secondary stolon cluster can only take reads.

To setup secondary stolon cluster on standby mode, initialise stolon cluster on pitr mode.

{
  "initMode": "pitr",
  "pitrConfig": {
    "dataRestoreCommand": "<pg_basebackup command>"
  },
  "role": "standby",
  "standbySettings": {
    "primaryConnInfo": "host=%s port=5432 user=%s password=%s sslmode=disable",
    "primarySlotName": "<name of replication slot in primary stolon cluster>"
  }
}

Note: Before setting up a secondary cluster, You need to create a dedicated replication slot in primary stolon cluster for it and configure it in "primarySlotName"

[Paddey]

Hello,
Thank you for answer. This returns to the cluster where 1 primary and many secondary nodes.
I expect to see something like by one master into 2 geo's Somethng like are 1 master into Stolon 1 geo and 1 master into Stolon 2 geo and any syncs between its.
But looks like it's impossible.
Regards,
Paddey