Does anyone know if it's currently possible to have multiple hosts and ports in psycopg2 connection string for PostgreSQL databases?

postgresql psycopg2://<username>:<password>@host.domain.name/database is what I have - is it possible to specify multiple hosts if I have primary/secondary setup for failovers?

Regards,

CodePudding user response：

From your example it looks like you are using SQLAlchemy. I do not have that set up but I can show thepsycopg2 portion. In this case Postgres 14 is listening on port 5432 and Postgres 12 on 5442:

import psycopg2

con = psycopg2.connect("postgresql://postgres@localhost:5432,localhost:5442/test")
cur = con.cursor()  
cur.execute("select version()")
cur.fetchone()                                                                                                                                                            
('PostgreSQL 14.3 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit',)

--sudo systemctl stop postgres14

con = psycopg2.connect("postgresql://postgres@localhost:5432,localhost:5442/test")
cur = con.cursor()  
cur.execute("select version()")
cur.fetchone()                      
('PostgreSQL 12.11 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit',)

For more information see libpq connection string, in particular 34.1.1.3. Specifying Multiple Hosts and the paragraph just above it. It will not automatically fail over.

CodePudding user response：

I believe this is due to an incorrect documentation.

1. this link suggests that using psycopg2 I have to specify the format as username:password@/database?host=name1:port&host=name2&host=name3

2. The above will always give error, and also will not fall back on alternative hosts. It will take a random host on the list. You need to provide target_session_attrs parameter as per this documentation

3. So the correct way (at least one option) is to use the below format postgresql psycopg2://username:password@/<db_name>?host=<primary_db_host>&host=<secondary_db_host>&port=<port_no>&target_session_attrs=primary

Please note (I didn't test these)

1. It might not work if you have primary/secondary hosts running off of different ports

2. Since I am using psycopg2 through SQLAlchemy, I only tried this format, but other format might work too