APPX 4.2.9, PostgreSQL, and UnixODBC on RHEL 5/Scientific Linux 5/Oracle Linux 5/CentOS 5

Overview

This document will demonstrate how to configure APPX 4.2.9 and PostgreSQL on a 64 bit or 32 bit Red Hat 5 based Linux server using 32 bit unixODBC drivers.

32 Bit

Installing PostgreSQL

To install PostgreSQL, use the package manager to install postgresql and postgresql-server.

postgresql.i386 : PostgreSQL client programs and libraries.
postgresql-server.i386 : The programs needed to create and run a PostgreSQL
                         : server.

Installing UnixODBC

To install unixODBC, install unixODBC.i386 using the package manager. Also, install the postgresql-odbc.i386 package to get the connector drivers.

unixODBC.i386 : A complete ODBC driver manager for Linux.
postgresql-odbc.i386 : PostgreSQL ODBC driver.

Configuring odbc.ini and odbcinst.ini

The odbcinst.ini file must be configured to point to the 32 bit PostgreSQL ODBC Connector driver. In my testing the default configuration was correct.

The odbc.ini file, however, was blank by default. Here is how mine is set up. You'll need to fill in your servername, username, password, and port, along with any other information that may need to be changed to match your system.

[root@slinux ~]# cat /etc/odbc.ini
[postgresql]
Description        = postgresql
Driver             = postgresql
Trace              = Yes
TraceFile          = sql.log
Database           = test
Servername         = 
Username           = 
Password           = 
Port               = 
Protocol           = 6.4
ReadOnly           = No
RowVersioning      = No
ShowSystemTables   = No
ShowOidColumn      = No
FakeOidIndex       = No
ConnSettings       =
[root@slinux ~]#

Configuring appx.env

The appx.env file has a line beginning with APPX_ODBC_LIB, which points to the MySQL UnixODBC Connector driver. It can be remarked out. APPX should not have any trouble locating the driver without it.

#APPX_ODBC_LIB=/usr/lib/libmyodbc5.so

Configuring pg_hba.conf and postgresql.conf

To make unixODBC and PostgreSQL work, I had to change both pg_hba.conf and postgresql.conf, which are both found in /var/lib/pgsql/data/. I had to change the end of pg_hba.conf to look like this:

[root@slinux ~]# tail /var/lib/pgsql/data/pg_hba.conf 
# on a non-local interface via the listen_addresses configuration parameter,
# or via the -i or -h command line switches.
#



# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    all         all         ::1/128               trust
host    all         all         192.168.1.0/24        trust
local   all         all                               trust
[root@slinux ~]#

The postgresql.conf file has a line that looks like this:

#listen_addresses = 'localhost'        # what IP address(es) to listen on;

I copied the line and changed 'localhost' to '*', and un-remarked it, so it looks like this now:

#listen_addresses = 'localhost'        # what IP address(es) to listen on;
listen_addresses = '*'                 # what IP address(es) to listen on;

64 Bit

Installing PostgreSQL

To install PostgreSQL, use the package manager to install postgresql and postgresql-server.

postgresql.x86_64 : PostgreSQL client programs and libraries.
postgresql-server.x86_64 : The programs needed to create and run a PostgreSQL
                         : server.

Installing UnixODBC

To install unixODBC, install unixODBC.i386 using the package manager. The 32 bit version is needed, so be sure to specify that when installing it.

unixODBC.i386 : A complete ODBC driver manager for Linux.

Installing the ODBC Connector

The ODBC connector might not be included in the package manager by default. If it is, it would be named something like postgresql-odbc.i386. If not, it can be downloaded and installed from the internet. I went to Scientific Linux's website and found the package in the downloads section. You can download it here, but you may want to look on your distribution's website if you're not using Scientific Linux 5.6.

Once the rpm is downloaded, install it with yum to take care of any dependencies automatically. The command is:

yum localinstall path-to-file

Configuring odbc.ini and odbcinst.ini

The odbcinst.ini file must be configured to point to the 32 bit PostgreSQL ODBC Connector driver. In my testing the default configuration was correct.

The odbc.ini file, however, was blank by default. Here is how mine is set up. You'll need to fill in your servername, username, password, and port, along with any other information that may need to be changed to match your system.

[root@slinux ~]# cat /etc/odbc.ini
[postgresql]
Description        = postgresql
Driver             = postgresql
Trace              = Yes
TraceFile          = sql.log
Database           = test
Servername         = 
Username           = 
Password           = 
Port               = 
Protocol           = 6.4
ReadOnly           = No
RowVersioning      = No
ShowSystemTables   = No
ShowOidColumn      = No
FakeOidIndex       = No
ConnSettings       =
[root@slinux ~]#

Configuring appx.env

The appx.env file has a line beginning with APPX_ODBC_LIB, which points to the MySQL UnixODBC Connector driver. It can be remarked out. APPX should not have any trouble locating the driver without it.

#APPX_ODBC_LIB=/usr/lib/libmyodbc5.so

Configuring pg_hba.conf and postgresql.conf

To make unixODBC and PostgreSQL work, I had to change both pg_hba.conf and postgresql.conf, which are both found in /var/lib/pgsql/data/. I had to change the end of pg_hba.conf to look like this:

[root@slinux ~]# tail /var/lib/pgsql/data/pg_hba.conf 
# on a non-local interface via the listen_addresses configuration parameter,
# or via the -i or -h command line switches.
#



# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    all         all         ::1/128               trust
host    all         all         192.168.1.0/24        trust
local   all         all                               trust
[root@slinux ~]#

The postgresql.conf file has a line that looks like this:

#listen_addresses = 'localhost'        # what IP address(es) to listen on;

I copied the line and changed 'localhost' to '*', and un-remarked it, so it looks like this now:

#listen_addresses = 'localhost'        # what IP address(es) to listen on;
listen_addresses = '*'                 # what IP address(es) to listen on;

Testing/Troubleshooting

32 Bit

ISQL can be used to test the connection between PostgreSQL and unixODBC using the 32-bit ODBC Connector driver. ISQL isn't necessary for APPX to connect to PostgreSQL, but it can be a useful tool for testing the driver APPX will be using. A successful test should look something like this:

[root@slinux ~]# isql postgresql
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
[root@slinux ~]#

64 Bit

ISQL can be used to test the connection between PostgreSQL and unixODBC using the 32-bit ODBC Connector driver. ISQL isn't necessary for APPX to connect to PostgreSQL, but it can be a useful tool for testing the driver APPX will be using. You will need to have the 32-bit ISQL file for it to be a valid test, otherwise it will be testing the 64-bit driver, which is not used in APPX. To find out the bitness of ISQL, run the file command on it. This is how it should look(ELF 32-bit):

[root@slinux bin]# file isql
isql.32: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped
[root@slinux bin]#

If it says ELF 64-bit, the 32 bit version needs to be installed. A successful test should look something like this:

[root@slinux ~]# isql postgresql
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
[root@slinux ~]#