1

I am trying to connect to a Postgres DB built for a Django app. I can connect fine in windows, but when we moved it over to a Linux server for production it stopped working. I tracked it down to pyodbc not working. So in a separate script, I have been trying to get a connection working with no luck. I'm pretty sure the Linux server is running Redhat (yum is the install, but I can double check if it matters)

Here are some of the things I have tried: installed unixODBC-devel

added a DSN to the user sourcename /home/localUsername/.odbc.ini file as follows:

[DSNName]
Description=Postgres Connection to Database
Driver=/usr/pgsql-10/lib/psqlodbc.so
Server=servername
Database=dbname
PWD=pass
UID=username

Running odbcinst -q -d returns: [PostgreSQL]

python script I have tried (although using interpreter for now)

con = odbc.connect("DSN=DSNName")
con = odbc.connect("Driver={PostgreSQL};Uid=username;Pwd=pass; Server=servername;Port=5432)
con = odbc.connect("Driver={PostgreSQL Unicode(x64)};Uid=username;Pwd=pass; Server=servername;Port=5432)

I get one of three errors depending on which driver I try: For the Driver using Unicode(x32) I get:

pyodbc.Error ('01000', "[01000] [unixODBC][Driver Manager]can't open lib 'PostgreSQL Unicode(x32)' : file not found ...

I figure that means this driver is not installed which is fine.

For the DSN approach I get:

pyodbc.OperationalError: ('08001', '[08001] FATAL: role:"localUsername" does not exists\n (101) (SQLDriverConnect)')

This second error seems to make me think (maybe incorrectly) that it is trying to use my localUsername to authenticate to Postgres, when I want to use a special admin username that was setup for the host for now.

For the third option (PostgreSQL):

pyodbc.OperationalError 08001 FATAL: database "dbname" does not exist

I don't understand why that might be? My first thought is Linux wants to use a different port for connection. Locally on windows I can use the 5432 port and it worked fine. So I'm at a loss on how to get it to find the DB assuming the rest is working okay.

If you need additional details let me know and I'll try to add them.

Edit: Have python (and Django) on one server. DB is on another.

Tried running psql -h OSServername -U 'username' with the same: role error/DB not found errors. I feel like I must be needing something after OSServername like 'OsServername/pgAdminServer' but that didn't work

where db 'username' is found by right clicking inside of pgAdmin one of the DB server names and selecting properties. Are the Server names inside pgAdmin different and do I need to somehow use the pgAdmin Server Name as part of the connection string?

As the comments suggest, starting with the psql -h command seems like a good place to start as it gets rid of the python complexity. Once I can get that command working, I might be able to fix the rest. What do I type when my Linux server name (Host name) is 'LinuxName', pgAdmin Server is 'pgAdminServer', the actual DB has a name 'dbName', and the pgAdmin username is 'username'. 'dbName' has an owner 'owner' which is different from the username of the pgServer as well as different from the Linux username I am signed in as. I also validated that the 'pgAdminServer' shows port 5432, so that shouldn't be the issue.

Edit 2: I got the pyodbc.connect('Driver={PostgreSQL};Server=servNm;Uid=uid;pwd=pwd;Database=db') to work. Now just need the last step for the DSN approach. Your dump_dsn worked to find a typo in my dsn file (.odbc.ini in my local home directory). So that helped. Still not finding the DB. File in: /etc/odbcinst.ini list the following drivers which I have tried all three in my DSN file:

/usr/pgsql-10/lib/psqlodbc.so
/usr/pgsql-10/lib/psqlodbca.so
/usr/pgsql-10/lib/psqlodbcw.so

here is the info again from my .odbc.ini file in home/user/.odbc.ini: variables: servNm, uid, db, and pwd match exactly with those found in my pyodbc.connect() string now working.

[DSNName]
Description=Postgres Connection to Database
Driver=/usr/pgsql-10/lib/psqlodbc.so
Server=servNm
CommLog=0
Debug=0
Fetch=100
UniqueIndex=1
UseDeclareFetch=0
Database=db
UID=uid
Username=uid
PWD=pwd
ReadOnly=0
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
brian_ds
  • 317
  • 4
  • 12
  • Are you trying to connect to the same server when you run the code on Windows vs. Linux? Non-existent database/role errors make me suspect not. – Gord Thompson Feb 19 '21 at 22:06
  • related: https://stackoverflow.com/q/11919391/2144390 – Gord Thompson Feb 19 '21 at 22:23
  • @GordThompson I'm pretty sure I am. When we pushed it over to the server, I didn't change the connection script. So it contained exactly the same Connection string. Or am I missing something? As a side note, the DB is on a different Linux server (so Django on one server, DB on another, and local laptop connecting to the DB) – brian_ds Feb 19 '21 at 22:37
  • as an add, I tried typing psql -h LinuxServername -U username and the the password, but that doesn't work either. Is there something I should be adding? When I go into pgAdmin, on the left side I see a dropdown with Servers: DBServerName which of course is different from the OS_Servername (server name not IP address). Do I need both and is there a format? I'll add details to the question – brian_ds Feb 19 '21 at 22:50
  • On Windows are you using a DSN to connect? If so, you can use [dump_dsn](https://github.com/gordthompson/dump_dsn) to dump its contents and then copy them over to a DSN on the Linux box. – Gord Thompson Feb 20 '21 at 00:48
  • For my PostgreSQL test server on 192.168.0.199 (default port 5432) with PostgreSQL username `scott` and database named `test` I just use `psql -h 192.168.0.199 test scott` and it prompts me for the password. – Gord Thompson Feb 20 '21 at 20:26
  • got the connect string working, now just the DSN part. Can you take one more look. I appreciate the help you have already given. – brian_ds Feb 23 '21 at 16:58
  • So when you try `cnxn = pyodbc.connect("DSN=DSNName")` you get 'database "dbname" does not exist' ? – Gord Thompson Feb 23 '21 at 17:12
  • correct. I realized just barely your dump_dsn provides this for the driver on windows. C:\Program Files\psqlODBC\1101\bin\psqlodbc35w.dll -> Could this mean I need an updated driver on linux that matches 1101? – brian_ds Feb 23 '21 at 17:13
  • It's possible, but first try changing your ~/.odbc.ini to use `Driver=PostgreSQL` (similar to the connection string but without the curly brackets) to see if that makes a difference. – Gord Thompson Feb 23 '21 at 17:17
  • still no. If I remove the Username=uid the error changes from db doesn't exist to user doesn't exist. So having Username=uid seems to get to the point where it can use the driver, but still not find the DB. I checked for hidden spaces, and don't see any in the ~.odbc.ini file either for that DSN. There are other DSN in the file (although I have not checked them because they are for Win Servers and this is an admin account i am using) – brian_ds Feb 23 '21 at 17:29
  • Are you able to connect if your ~/.odbc.ini specifies `Driver=PostgreSQL` and `Database=postgres` ? – Gord Thompson Feb 23 '21 at 18:15
  • No. Uid=Username=Database=postgres (with each on a new line) still gives an error. running pyodbc.connect("DSN=DSNName").cursor().execute("select * from test") throws an error: test does not exist. Yet I just went and created that table in my postgres database. I added DSNName into my [Data Sources Name] section as found: https://www.postgresql.org/docs/7.2/odbc-config.html with no change. – brian_ds Feb 23 '21 at 21:32
  • It works!! I made a backup of my old .odbc.ini file and cleared it out and used the exact version from the link above. I changed the configuration to match my settings and it worked. There must have been something wrong with my .odbc.ini file causing problems. Do you know if the [Data Sources Name] section has to be on top? Anyway, thanks. If you want to write up an answer, I'll accept it. I would rather you get some credit for all your time/help. – brian_ds Feb 23 '21 at 21:57
  • "Do you know if the [Data Sources Name] section has to be on top?" - It is not actually required to be in odbc.ini at all. Some applications might use it, but unixODBC doesn't rely on it; when we call `pyodbc.dataSources()` we get the list of DSNs in /etc/odbc.ini and ~/.odbc.ini even if that section is missing from either (or both) of the files. – Gord Thompson Feb 23 '21 at 22:24

2 Answers2

0

Deleting and re-creating the ~/.odbc.ini file appears to have resolved the issue. This makes us suspect that there were one or more unusual characters in the previous version of that file that were causing strange behaviour.

One possible source of such troublesome (and sometimes even invisible!) characters is when copying text from the web. If we copy the following from our web browser …

DRIVER=PostgreSQL Unicode

… and paste it into a blank document in our text editor with the default encoding UTF-8 then everything looks normal. However, if we save that file (as UTF-8) and open it in a hex editor we can see that the space is not a normal space (U+0020) …

enter image description here

… it is a NO-BREAK SPACE (a.k.a. "NBSP", U+00A0, \xc2\xa0 in UTF-8 encoding) so the chances are very good that we would get an error when trying to use that DSN because b'PostgreSQL\xc2\xa0Unicode' is not the same as b'PostgreSQL Unicode'.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
0

I had the exact same problem. I looked for several solutions, but none worked.

The problem was solved more easily than I thought:

1 - Remove all packages about postgresodbc:

$ sudo apt-get remove odbc-postgresql

2 - Install two libs, in the same order below:

$ apt-get install libcppdb-postgresql0 odbc-postgresql

Enjoy!

Doing so worked perfectly here.

joanis
  • 10,635
  • 14
  • 30
  • 40
Pedro Lucas Ferreira
  • 1