I have 3 db servers on docker containers, and i used to connect like this from my python app: psycopg2.connect(host="db-0", user="user", password="password", database="data") This works,but if i add more then 1 host it fails.

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:



psql 11.5 on docker containers db-0 and db-1 I used pip install psycopg2 to install.

  • You can't connect to multiple servers like that. You need to do a connect() for each host – Philᵀᴹ Oct 27 '19 at 16:39
  • @Philᵀᴹ im not trying to connect to them all, i have psql HA and if psql master is changing i want my API to try the next server if one isnt working, – Batchen Regev Oct 27 '19 at 16:46
  • 1
    What happens when you use psycopg2.connect("host=db-0,db-1 user=user password=password database=data")? Passing as one string is what the documentation indicates is needed when using a libpq connection string. See initd.org/psycopg/docs/module.html – Colin 't Hart Oct 27 '19 at 17:07
  • @Philᵀᴹ It should be possible -- at least I can't see in the documentation that it's not possible. I think it requires the connection string passed as a single argument, rather than using keywords. – Colin 't Hart Oct 27 '19 at 17:09
  • 1
    Try removing the space between hostnames in host="db-0, db-1" – mustaccio Oct 27 '19 at 18:50

Specify a single libpq style connection string:

psycopg2.connect("host=db-0,db-1 user=user password=password dbname=data")
| improve this answer | |
  • Doesnt work: psycopg2.OperationalError: could not translate host name "db-0,db-1" to address: Temporary failure in name resolution – Batchen Regev Oct 30 '19 at 10:37

Solved the problem by installing :

pip3 install postgres

and then command :

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

And now command works but it doesnt really route requests to the next db host list. i already commanted it to psycopg2 git i hope someday they will fix it. so here is my work around :

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

so all my insert \ delete\ update query's goes to this in a function. (def db_master) And all other "select" query's goes 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')
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.