8.148 Connecting to a PostgreSQL Database with an ODBC Driver

This documentation shows how to set up and configure Moab to connect to a PostgreSQL database using the ODBC driver. This document assumes the necessary ODBC drivers have already been installed and configured.

Occasionally vacuuming your PostgreSQL database could improve Moab performance. See the PostgreSQL documentation for information on how to vacuum your database.

To set up and configure Moab to connect to a PostgreSQL database using the ODBC driver, do the following:

This solution has been tested and works with the following file version:

odbc-postgresql - 1:08.03.0200-1.2

Configure the PostgreSQL and ODBC driver. odbcinst.ini file must be contained in /etc.
Run the following commands to find the PostgreSQL ODBC client driver and setup file. You could also query the libodbcpsql package that was installed.
```
[root]# updatedb 
```
```
[root]# locate psqlodbc
```
```
[root]# locate libodbcpsql
```
```
[PostgreSQL]
Description = PostgreSQL ODBC driver
Driver = /usr/lib/odbc/psqlodbca.so
Setup = /usr/lib/odbc/libodbcpsqlS.so
Debug = 0
CommLog = 1
UsageCount = 2
```
Configure Moab to use the PostgreSQL ODBC driver. Moab uses an ODBC datastore file to connect to PostgreSQL using ODBC. This file must be located in the Moab home directory (/opt/moab by default) and be named dsninfo.dsn, which is used by Moab. If the following content, which follows the standard ODBC driver file syntax, is not already included in the /etc/odbc.ini file, make sure that you include it. Also, include the same content in the dsninfo.dsn file.
```
[ODBC]
Driver = PostgreSQL
Description = PostgreSQL Data Source
Servername = localhost
Port = 5432
Protocol = 8.4
UserName = postgres
Password = moab
Database = Moab
```
The user should have read/write privileges on the Moab database.

The preceding example file tells ODBC to use the PostgreSQL driver, postgres user, moab password, and to connect to PostgreSQL running on the localhost on port 5432. ODBC uses this information and connects to the database called Moab.

Test the ODBC to PostgreSQL connection by running the isql command, which reads the /etc/odbc.ini file. If connected, you should be able to run the help command.

If you encounter any errors using the isql command, there was a problem setting up the ODBC to MySQL connection. Try the following debugging steps to resolve the issue:
1. The odbcinst.ini and odbc.ini files are usually assumed to be located in /etc, but that is not always true. Use the odbcinst -j command to determine the assumed location of the files in your configuration.
```
[root#] odbcinst -j
```
```
unixODBC 2.2.12
```
```
DRIVERS............: /etc/unixODBC/odbcinst.ini
```
```
SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini
```
```
USER DATA SOURCES..: /home/adaptive/.odbc.ini
```
2. Because odbcinst.ini and odbc.ini are expected in /etc/unixODBC, not /etc, move them from /etc to /etc/unixODBC.
3. Use the strace command to determine where isql expects the odbc.ini and odbcinst.ini files. Note the location in which isql expects these files.
```
$ strace isql -v ODBC
```
Create the database in PostgreSQL using the moab-db-postgresql.sh setup script contained in the contrib/sql directory.
This contrib/sql directory is either in the $MOABHOMEDIR (if you used RPM method) or in the expanded tarball directory (if you used Manual method).
- Run the script and provide the DB username that will attach to the Moab database (you must supply a DB username or the script will exit). The default admin user is postgres, but you can make a new user at this time:
```
> ./moab-db-postgresql.sh postgres
Create db user "postgres" in postgreSQL? (y/n)>
```
- The script asks if you want to create the DB user you specified in postgreSQL. If the DB user already exists, answer 'n'. Otherwise, the DB user is created and it asks for the new user's password.
- The script then creates the database "Moab".
- Finally, as the DB user you provided, the script imports the DB schema from moab-db-postgresql-create.sql into the Moab database.

Download and install Moab. Install and configure Moab as normal but add the following in the Moab configuration file (moab.cfg):

USEDATABASE             ODBC
# Turn on stat profiling
USERCFG[DEFAULT]        ENABLEPROFILING=TRUE
GROUPCFG[DEFAULT]       ENABLEPROFILING=TRUE
QOSCFG[DEFAULT]         ENABLEPROFILING=TRUE
CLASSCFG[DEFAULT]       ENABLEPROFILING=TRUE
ACCOUNTCFG[DEFAULT]     ENABLEPROFILING=TRUE
NODECFG[DEFAULT]        ENABLEPROFILING=TRUE

With the ODBC driver configured, the database created, and Moab configured to use the database, start Moab for it to begin storing information in the created database.
> moabd is a safe and recommended method of starting Moab if things are not installed in their default locations.