Connect connects to PostgreSQL databases through the PostgreSQL ODBC. Install and configure the PostgreSQL client software on the system on which the DMX client is installed. To establish a connection to the PostgreSQL database, install the PostgreSQL ODBC driver and create, configure, and test the ODBC data source name (DSN).
PostgreSQL client software installation
Windows systems
For client software installation on Windows, perform the following steps:
- Download https://www.postgresql.org/download/windows/ and select the PostgreSQL version.
- Download PostgreSQL Database software for Windows file, for example:
postgresql-12.5-1-windows-x64.exe - To install only the client, select only “Command Line Tools” in the Select Components Window of the PostgreSQLinstallation procedure.
- Verify that the PostgreSQL psql client utility is in a directory specified in the PATH.
UNIX systems
For client software installation on Unix, perform the following steps:
- Install the PostgreSQL client software.
- Access the download site for the target platform. For example, for Redhat Enterprise Linux: https://www.postgresql.org/download/linux/redhat/
- Follow instructions to install the “
postgresql-client”.
- Verify that the PostgreSQL psql client utility is in a directory specified in the PATH.
PostgreSQL ODBC driver installation and configuration
Windows systems
For driver installation and configuration on Windows, perform the following steps:
- Download and install the PostgreSQL ODBC driver using https://odbc.postgresql.org/.
- Download the following zip file (
psqlodbc_12_02_0000-x86.zip) which contains psqlodbc_x86.msi. - Run psqlodbc_x86.msi to install the ODBC driver libraries needed to interface with PostgreSQL.
- Verify that the ODBC driver libraries, which are dynamic linked libraries with the extension, are installed successfully.
- Create and configure the ODBC DSN.
- When creating a system DSN entry for the ODBC connection, note the settings in the “Datasource” “Advance Options” pages. There are three pages of settings for the PostgreSQL ODBC driver version 12.
- The PostgreSQL ODBC driver by default will change the datatype of long character
columns to character large object. This may cause the unexpected fatal error,
“COLDTTY (link to error message in help)” when attempting in a Connect ETL task to
map a varchar column of length 1000, for example, to a varchar column in another
database. The following settings will avoid these types of errors.
- Use Declare/Fetch is selected
- Show Boolean Column As String is unselected
- Max Varchar (Default 255) is populated with the value 65530
- Text As LongVarChar is unselected, causing PostgreSQL text columns to have a varchar type rather than a character large object type.
Linux/UNIX systems
For Linux/UNIX systems, the PostgresSQL ODBC driver is an operating system install option. For example, for Centos, it may be found here:
https://centos.pkgs.org/8/postgresql-12-x86_64/postgresql12-odbc-12.00.0000-1PGDG.rhel8.x86_64.rpm.html.
- Once the driver is installed, set up the ODBC configuration and environment as described in the Defining ODBC data sources help topic.
- The PostgreSQL driver configuration, should include the settings discussed in
step 5 Windows systems.
Sample odbc.ini entries
[ODBC Data Sources] … ssregressionPostgreSQL = PostgreSQL database ssregressionPostgreSQL … [ssregressionPostgreSQL] Description=postgresql driver Driver=/usr/pgsql-12/lib/psqlodbc.so TraceFile=/tmp/trace_psqlodbc.log Trace=No Database=ssregression Servername=postgresrvr.mydomain.us.myorg.com Port=5432 Protocol= ReadOnly=No RowVersioning=No ShowSystemTables=No ShowOidColumn=No FakeOidIndex=No ConnSettings= Debug=0 CommLog=0 MaxVarcharSize=65530 UseDeclareFetch=1 TextAsLongVarchar=0 BoolsAsChar=0 Fetch=10000 UseServerSidePrepare=1Sample odbcinst.ini entries[ODBC Drivers] PostgreSQL = Installed … [PostgreSQL] Description=ODBC for PostgreSQL Driver=/usr/pgsql-12/lib/psqlodbc.so Setup=/usr/lib64/libodbcpsqlS.so