The following bug has been logged on the website:
Bug reference: 15827
Logged by: Jorge Gustavo Rocha
Email address: jgr@geomaster.pt
PostgreSQL version: 10.8
Operating system: Windows
Description:
Hi,
It is my fist report, so please be gentle. I need some help to identify the
problem properly.
1. Description
I'm using `pg_services.conf` to provide access to a Postgresql database.
The pg_services.conf file is:
[pg_trabalho]
host=192.168.1.24
port=5432
dbname=trabalho
The services are working well, either on Ubuntu and on Windows. I'm using
QGIS to access the database and it works well. QGIS is using cpp code to
access the database.
I can also connect on the command line, using:
psql service=pg_trabalho
as in the following example:
jgr@zoe:~$ psql service=pg_trabalho
psql (10.8 (Ubuntu 10.8-0ubuntu0.18.10.1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
Type "help" for help.
trabalho=>
So, I think the pg_services are properly configured and working.
2. Problem
In Windows, I'm not able to connect using psycopg2. The following code does
not work on Windows.
import psycopg2
print (psycopg2.__version__)
connection = psycopg2.connect(service='pg_trabalho')
# connection = psycopg2.connect(user = "cmb.user", password = "xxxxxxx",
host = "192.168.1.24", port = "5432", database = "trabalho")
cursor = connection.cursor()
print ( connection.get_dsn_parameters(),"\n")
cursor.execute("SELECT version();")
record = cursor.fetchone()
print("Connected to - ", record,"\n")
It works well in Ubuntu, but not on Windows. Both works well if pg_services
are not used, and the connection is made using `connect(user = "cmb.user",
password = "xxxxxxx", host = "192.168.1.24", port = "5432", database =
"trabalho").
3. Error on Windows
This is the error reported in Windows:
2.7.5 (dt dec pq3 ext lo64)
Traceback (most recent call last):
File "C:\OSGEO4~1\apps\Python37\lib\code.py", line 90, in runcode
exec(code, self.locals)
File "<input>", line 1, in <module>
File "<string>", line 4, in <module>
File "C:\OSGEO4~1\apps\Python37\lib\site-packages\psycopg2\__init__.py",
line 130, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not translate host name "192.168.1.24
" to address: Unknown host
4. What I have tried
I report this as a QGIS problem [1], but soon I've realize that it was a
psycopg2 problem.
I report this as a psycopg2 problem [2], but psycopg2 just passes the
parameters down to libpq library and the issue was closed.
From other's feedback, I've tried to use the line separators in
pg_services.conf with \n and \r\n, but the result is the same (because there
is strange newline after the host address).
I've tried with the hostname and IP address.
Definitely, it is not a pg_services.conf syntax, because psql also runs fine
on Windows [3]. Print screen is attached.
I'm using psycopg2 2.7.5, on Windows 10.
Who should I do to help better identify the problem?
[1] https://github.com/qgis/QGIS/issues/30027
[2] https://github.com/psycopg/psycopg2/issues/926
[3] https://gist.github.com/jgrocha/a3a8bc2d2476386450ed4d8d3629fe32
