Connect to PostgreSQL from Linux or Mac with ODBC

Introduction

This article explains how to connect to PostgreSQL using unixODBC and the official PostgreSQL ODBC driver psqlodbc. It includes installation and configuration instructions for Ubuntu, CentOS and the Mac.

It covers the following topics:

  1. How to connect to PostgreSQL (with code samples).
  2. How to setup ODBC:
    • Install unixODBC on Linux:

      This is needed because to manage ODBC on Linux. The unixODBC install also include utilities isql and iusql.

    • Install the psqlodbc PostgreSQL ODBC driver:

      This is the official ODBC driver manager and driver from the PostgreSQL. You can configure ODBC using only odbc.ini, which contains the ODBC DSN details. You can optionally using odbcinst.ini as well but it is not needed.

    • Configure ODBC using the odbc.ini file:

      We recommend using system DSNs (available to all users), which use the /usr/odbc.ini file. User DSNs can be setup in the $HOME/.odbc.ini file.

Connect to PostgreSQL [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples. The code samples will work with all operating systems.

Follow these steps to connect:

  1. Create a new channel to try out the code:
    • Create a channel like this:
      • Name: DB PostgreSQL Connection (or similar)
      • Source: From Translator
      • Destination: To Channel
    • Alternatively use an existing channel:

      Your channel will need a From/To Translator or a Filter component.

  2. Connect to a database using ODBC:
    • Paste this code into the Translator:

      If your script uses connection object methods conn:query{} or conn:execute{}to connect to a remote PostgreSQL database, the location of the remote host is specified as part of the name parameter in the db.connect{ } command. If you use conn:merge{} to connect the remote host is specified as part of the name parameter in the conn:merge{} command itself.

      function main()
            local conn = db.connect{   
            api=db.POSTGRES, 
            name='your_server_name',
            user='your_login', 
            password='secret',
            use_unicode = true,
            live = true
         }
      
         conn:execute{sql='SELECT * FROM <your table>', live=true}
      end
    • The name is the same as the server entry in the odbc.ini file.
  3. Adapt the code to your requirements.

Setup ODBC [top]

Note: Iguana may not always support the very latest version of the PostgreSQL ODBC drivers, so you can simply download an earlier version if necessary. This is particularly true if you are not able to use the the latest version of Iguana.

If you need to know which versions of MySQL are supported by the version of Iguana you are using please contact us at support@interfaceware.com.

CentOS:

Note: The procedure and screenshots in this section are from CentOS 7 — and may differ somewhat if you are using a different version of CentOS , or another Red Hat based Linux.

  1. Install the unixODBC driver manager:
    1. Terminal Command: Get the latest package info:
      yum check-update
    2. Terminal Command: Install unixodbc:
      sudo yum install unixODBC.x86_64
      sudo yum install unixODBC-devel.x86_64
  2. Install the 64 bit PostgreSQL Connector/ODBC drivers:
    1. Terminal Command: Install PostgreSQL Connector/ODBC:
      sudo yum install postgresql-odbc.x86_64
  3. Check file names and create symbolic links to the versioned file names if needed:
    • Terminal Command: Check to see if the expected driver file exist:
      ls /usr/lib64/psqlodbcw* -l
    • And as we can see the the expected driver file exists:
      postgresql check odbc
  4. Create the odbc.ini file entries:

    Be aware that the ODBC parameters in odbc.ini entries can vary for different databases. In particular PostgreSQL and Oracle use Servername to identify the server name, whereas many other databases simply use Server (for example Microsoft SQL Server and MySQL). It is important to use the correct parameters for your database or the ODBC connection will not work correctly.

    1. Format your entries like this: :
      • Bare minimum 64 bit driver:
        [My PostgreSQL Connection name]
        Driver     = /usr/lib64/psqlodbcw.so
        Setup      = /usr/lib64/libodbcpsqlS.so
        Servername = your_server_name
        Port       = 5432
      • 64 bit with user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My PostgreSQL Connection name]
        Driver     = /usr/lib64/psqlodbcw.so
        Setup      = /usr/lib64/libodbcpsqlS.so
        Servername = your_server_name <network name or IP address for the server>
        Port       = 5432 <5432 = default - a different port can be used>
        Database   = dbname <if omitted the default database will be used>
        Username   = dbusername <if omitted then integrated security is used - needs kerberos>
        Password   = dbpasswor
  5. Check the location of ODBC ini files on your system:
    1. Terminal Command: Check the locations:
      odbcinst -j
    2. In this case the ODBC ini files are in the default locations:
      • System ODBC ini file: /etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/home/parallels/.odbc.ini)
        odbc ini locations
  6. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
        Note: You need administrator permission to edit this file.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -l -f template_file
    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -h -f template_file
    3. An ODBC ini file will look something like this after a new entry is added:
      new odbc.ini postgres entry
  7. If the connection does not work speak to your DBA (database administrator) to troubleshoot the settings.

Ubuntu:

Note: The procedure and screenshots in this section are from Ubuntu 18.04 — and may differ somewhat if you are using a different version of Ubuntu, or another Debian based Linux.

  1. Install the unixODBC driver manager:
    1. Terminal Command: Get the latest package info:
      sudo apt update
    2. Terminal Command: Install unixodbc:
      sudo apt install unixodbc
      sudo apt install unixodbc-dev
  2. Install the 64 bit PostgreSQL Connector/ODBC drivers:
    1. Terminal Command: Install PostgreSQL Connector/ODBC:
      sudo apt install odbc-postgresql
  3. Check file names and create symbolic links to the versioned file names if needed:
    1. Terminal Command: Check if the expected files exist:
      ls /usr/lib64/psqlodbcw* -l
    2. And as we can see the the expected files exist:
      postgresql check files
  4. Create the odbc.ini file entries:

    Be aware that the ODBC parameters in odbc.ini entries can vary for different databases. In particular PostgreSQL and Oracle use Servername to identify the server name, whereas many other databases simply use Server (for example Microsoft SQL Server and MySQL). It is important to use the correct parameters for your database or the ODBC connection will not work correctly.

    1. Format your entries like this:
      • Bare minimum UNICODE driver (psqlodbcw.so):
        Note: Change [My PostgreSQL UNICODE Connection name] to to the name you prefer for your ODBC connection.

        [My PostgreSQL UNICODE Connection name]
        Driver     = /usr/local/lib/psqlodbcw.so
        Servername = your_server_name
        Port       = 5432
      • Bare minimum ANSI driver (psqlodbca.so):
        [My PostgreSQL ANSI Connection name]
        Driver     = /usr/local/lib/psqlodbca.so
        Servername = your_server_name
        Port       = 5432
      • UNICODE with user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My PostgreSQL UNICODE Connection name]
        Driver     = /usr/local/lib/psqlodbcw.so
        Servername = your_server_name <network name or IP address for the server>
        Port       = 5432 <5432 = default - a different port can be used>
        Database   = dbname <if omitted the default database will be used>
        Username   = dbusername <if omitted then integrated security is used - needs kerberos>
        Password   = dbpassword
      • ANSI with user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My PostgreSQL ANSI Connection name]
        Driver     = /usr/local/lib/psqlodbca.so
        Servername = your_server_name <network name or IP address for the server>
        Port       = 5432 <5432 = default - a different port can be used>
        Database   = dbname <if omitted the default database will be used>
        Username   = dbusername <if omitted then integrated security is used - needs kerberos>
        Password   = dbpassword
  5. Check the location of ODBC ini files on your system:
    1. Terminal Command: Check the locations:
      odbcinst -j
    2. In this case the ODBC ini files are in the default locations:
      • System ODBC ini file: /etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/home/parallels/.odbc.ini)
        odbc ini locations
  6. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
        Note: You need administrator permission to edit this file.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -h -f template_file
    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -l -f template_file
    3. An ODBC ini file will look something like this after a new entry is added:
      new odbc.ini postgres entry
  7. If the connection does not work speak to your DBA (database administrator) to troubleshoot the settings.

Mac:

We demonstrate two ways of installing PostgreSQL ODBC drivers:

  1. Install the free official PostgreSQL psqlodbc ODBC drivers using homebrew:

    This method requires you to install the open source homebrew package manager (similar to apt or yum). Once you have installed homebrew you install the psqlodbc package. This package installs the drivers and several dependencies, like: PostgreSQL database, unixODBC, Readline, Openssl, Libtool, etc.

  2. Install a free trial of the Devart PostgreSQL ODBC drivers:

    The Devart ODBC drivers can be installed without homebrew and without any dependencies. The downside is that they are not free — though there is one month free trial.

Note: The procedure and screenshots in this section are from macOS Mojave — and may differ somewhat if you are using a different version of macOS.

  1. Install the official PostgreSQL psqlodbc ODBC drivers:

    This is the first option you have for installing ODBC drivers. You can skip this step and install the Devart ODBC drivers in step (2) if you prefer.

    1. Go to the homebrew website.
    2. Terminal command: Run the Install Homebrew command (from the top of the page).

      I could have copied the command here but it may change in the future — so it is safer just to copy from the homebrew site. If you already have homebrew installed you can skip this step.

    3. Terminal commands: Check that homebrew is up to date and healthy:
      brew update
      brew doctor
    4. Terminal command: Install psqlodbc:

      Using homebrew to install psqlodbc may not install the most recent version of the drivers — in this case it installed 10.03.0000 whereas the latest version is 11.00.0000.

      brew install psqlodbc
    5. Use Finder to confirm the installation worked, you should see the following:

      The /usr/local directory is hidden in Finder, so use Go>Go to Folder and enter “/usr/local” to view it.

      • A Cellar directory is created to store homebrew installed items.
      • The psqlodbc directory and other directories for several dependencies:
      • The psqlodbc drivers:
        confirm homebrew
      • Symbolic links (aliases) for psqlodbc drivers in /usr/local/lib:
        psqlodbc confirm
  2. Setup the unixODBC driver manager:

    We need to use unixODBC because the default Apple driver manager does not work with some databases, see Setup unixODBC on the Mac for more information.

  3. Create the odbc.ini file entries:

    Be aware that the ODBC parameters in odbc.ini entries can vary for different databases. In particular MySQL and Microsoft SQL Server use Server to identify the server name, whereas some other databases use Servername (for example Oracle and PostgreSQL). It is important to use the correct parameters for your database or the ODBC connection will not work correctly.

    1. Format your entries like this:
      • Bare minimum UNICODE driver (libmyodbc8w.so):
        Note: Change [My PostgreSQL UNICODE Connection name] to the name you prefer for your ODBC connection.

        [My PostgreSQL UNICODE Connection name] 
        Driver     = /usr/local/lib/psqlodbcw.so
        Servername = your_server_name 
        Port       = 5432
      • Bare minimum ANSI driver (libmyodbc8a.so):
        [My PostgreSQL ANSI Connection name] 
        Driver     = /usr/local/lib/psqlodbca.so
        Servername = your_server_name 
        Port       = 5432
      • UNICODE with user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My PostgreSQL UNICODE Connection name] 
        Driver       = /usr/local/lib/psqlodbcw.so
        Servername   = your_server_name <network name or IP address for the server> 
        Port         = 5432 <5432 = default - a different port can be used> 
        Database     = dbname <if omitted the default database will be used> 
        Username     = dbusername <if omitted then integrated security is used - needs kerberos> 
        Password     = dbpassword
      • ANSI with user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My PostgreSQL ANSI Connection name] 
        Driver       = /usr/local/lib/psqlodbca.so
        Servername   = your_server_name <network name or IP address for the server> 
        Port         = 5432 <5432 = default - a different port can be used> 
        Database     = dbname <if omitted the default database will be used> 
        Username     = dbusername <if omitted then integrated security is used - needs kerberos> 
        Password     = dbpassword
  4. Check the location of ODBC ini files on your system:
    1. Terminal Command: Check the locations:
      odbcinst -j
    2. In this case the ODBC ini files are in the default locations:
      • System ODBC ini file: /usr/local/etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/Users/julianmuir/.odbc.ini):
        odbcinst -j
  5. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified in step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:

          odbcinst -i -d -l -f template_file

    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:

          odbcinst -i -d -h -f template_file

    3. An ODBC ini file will look something like this after a new entry is added:
      oddb.ini new entry
  6. If you have problems configuring the ODBC driver or connection speak to your DBA (database administrator).

Troubleshooting [top]

  • Problems with connection settings:

    Speak to your DBA (database administrator) for general connection issues like: User name and password, Database Server network name etc.

  • Iguana may not always support the very latest version of the psqlodbc ODBC drivers:

    Please contact us at support@interfaceware.com if you need to know the latest supported version of the psqlodbc ODBC drivers.

  • Versioned Packages:

    When a package installs a shared library, it can append a version number, so double check the actual library names and if needed symlink them to the library names shown in the previous section (libtdsodbc.so & libtdsS.so), for example:

    sudo ln -s /usr/lib/x86_64-linux-gnu/libodbc.so.1 /usr/lib/x86_64-linux-gnu/libodbc.so

If you need more help please contact us at support@interfaceware.com.

More Information [top]

Leave A Comment?