PostgreSQL repmgr where to send queries

user_8165583 0
Viewed 549

I'm using Postgres 11.5 and docker. I have started 3 containers - 1 master and 2 slaves.

pg-0 master:

docker run --detach --name pg-0 \
   --network my-network \
   --env REPMGR_PARTNER_NODES=pg-0,pg-1,pg-2 \
   --env REPMGR_NODE_NAME=pg-0 \
   --env REPMGR_NODE_NETWORK_NAME=pg-0 \
   --env REPMGR_PRIMARY_HOST=pg-0 \
   --env REPMGR_PASSWORD=repmgrpass \
   --env POSTGRESQL_PASSWORD=secretpass \
   bitnami/postgresql-repmgr:latest

pg-1 slave:

docker run --detach --name pg-1 \
  --network my-network \
  --env REPMGR_PARTNER_NODES=pg-0,pg-1,pg-2 \
  --env REPMGR_NODE_NAME=pg-1 \
  --env REPMGR_NODE_NETWORK_NAME=pg-1 \
  --env REPMGR_PRIMARY_HOST=pg-0 \
  --env REPMGR_PASSWORD=repmgrpass \
  --env POSTGRESQL_PASSWORD=secretpass \
  bitnami/postgresql-repmgr:latest

pg-2 slave:

docker run --detach --name pg-2 \
  --network my-network \
  --env REPMGR_PARTNER_NODES=pg-0,pg-1,pg-2 \
  --env REPMGR_NODE_NAME=pg-2 \
  --env REPMGR_NODE_NETWORK_NAME=pg-2 \
  --env REPMGR_PRIMARY_HOST=pg-0 \
  --env REPMGR_PASSWORD=repmgrpass \
  --env POSTGRESQL_PASSWORD=secretpass \
  bitnami/postgresql-repmgr:latest

Replication works fine between containers, and also if I stop master pg-0 then pg-1 \ pg-2 takes over to be the master.

My issue is, if I have python application on a different container that connect to db pg-0 master What do i do when the master is changing to pg-1 or pg-2? I don't want db connection to try to connect to all 3 servers, I'm sure there is a better way.

The way I connect from python app is using the container name - host =pg-0 What do to when db master host name container changed?

EDIT : this what i try in API 

>>> psycopg2.connect(host="db-0, db-1", user="user", password="password", database="data")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.7/site-packages/psycopg2/__init__.py", line 126, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not translate host name "db-0, db-1" to address: Temporary failure in name resolution

my package is psycopg2-2.8.4 and using python3.7

docker psql python-3.x postgresql
2 Answers

Solved the problem by installing: 

pip3 install postgres

and then command:

psycopg2.connect(host="db-0, db-1", user="user", password="password", database="data")

Worked fine but didn't really route to the next host if first the host is down. Here is my workaround:

It queries all db containers in a loop, and if one of db returns in the SELECT pg_is_in_recovery(); query False, then it means it is the master.

So all my insert/delete/update queries go to this in a function. (def db_master), and all other "select" queries go to the same loop in a different function that checks only the connection. (def db):

import psycopg2

hosts = ["db-0", "db-1"]

for i in hosts:
    conn = psycopg2.connect(
            dbname='data', user='user', host=i, password='password')
    cur = conn.cursor()
    cur.execute("SELECT pg_is_in_recovery();")
    result = cur.fetchall()[0][0]
    if result is False:
        master = i
    conn = psycopg2.connect(
            dbname='data', user='user', host=master, password='password')
user_81655830

What do i do when the master is changing to pg-1 or pg-2?

If your python app is connected to master and master stops you should catch relevant exception and reconnect to the new master. I would suggest to add retries to re-connection attempts as new master election/manual promotion can take time. Of course, some logic should be added for detecting the new master in the app if your goal is to be connected to master only.

The way i connect from python app is using the container name - host =pg-0 What do to when db master host name container changed ?

The only case of container master hostname change i can think of is master container crash/stop. Then new master container goes up and app may reconnect to it as suggested above.

user_12643040
Related