title: pyodbc + PostgreSQL ODBC: connection string works, DSN doesn't
body: <p>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)</p>
  <p>Here are some of the things I have tried:
  installed unixODBC-devel</p>
  <p>added a DSN to the user sourcename /home/localUsername/.odbc.ini file as follows:</p>
  <pre><code>[DSNName]
  Description=Postgres Connection to Database
  Driver=/usr/pgsql-10/lib/psqlodbc.so
  Server=servername
  Database=dbname
  PWD=pass
  UID=username
  </code></pre>
  <p>Running odbcinst -q -d returns:
  [PostgreSQL]</p>
  <p>python script I have tried (although using interpreter for now)</p>
  <pre><code>con = odbc.connect(&quot;DSN=DSNName&quot;)
  con = odbc.connect(&quot;Driver={PostgreSQL};Uid=username;Pwd=pass; Server=servername;Port=5432)
  con = odbc.connect(&quot;Driver={PostgreSQL Unicode(x64)};Uid=username;Pwd=pass; Server=servername;Port=5432)
  </code></pre>
  <p>I get one of three errors depending on which driver I try:
  For the Driver using Unicode(x32) I get:</p>
  <pre><code>pyodbc.Error ('01000', &quot;[01000] [unixODBC][Driver Manager]can't open lib 'PostgreSQL Unicode(x32)' : file not found ...
  </code></pre>
  <p>I figure that means this driver is not installed which is fine.</p>
  <p>For the DSN approach I get:</p>
  <pre><code>pyodbc.OperationalError: ('08001', '[08001] FATAL: role:&quot;localUsername&quot; does not exists\n (101) (SQLDriverConnect)')
  </code></pre>
  <p>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.</p>
  <p>For the third option (PostgreSQL):</p>
  <pre><code>pyodbc.OperationalError 08001 FATAL: database &quot;dbname&quot; does not exist
  </code></pre>
  <p>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.</p>
  <p>If you need additional details let me know and I'll try to add them.</p>
  <p>Edit:
  Have python (and Django) on one server. DB is on another.</p>
  <p>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</p>
  <p>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?</p>
  <p>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.</p>
  <p>Edit 2:
  I got the <code>pyodbc.connect('Driver={PostgreSQL};Server=servNm;Uid=uid;pwd=pwd;Database=db')</code> 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: <code>/etc/odbcinst.ini</code> list the following drivers which I have tried all three in my DSN file:</p>
  <pre><code>/usr/pgsql-10/lib/psqlodbc.so
  /usr/pgsql-10/lib/psqlodbca.so
  /usr/pgsql-10/lib/psqlodbcw.so
  </code></pre>
  <p>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.</p>
  <pre><code>[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
  </code></pre>