Installing and configuring postgreSQL and ODBC on Red Hat Linux - Mailing list pgsql-odbc

     Hi everybody,

This document is NOT a question. I only want it to be placed in mailing list
archives for postgreSQL beginners, who are facing the same problems as I did.

This text was written as part of the documentation to a project and I decided
to publish it, because it can help some other people to overcome some
obstacles during instalation and configuration Postgres and mainly ODBC on
Red Hat Linux. I had some problems and as I looked on web for solutions I
found out that many people had the same troubles before. But there were never
answers on the same place.

I don't claim that this description cowers all issues, is complete, correct
or useful for you.

If you find this text useful, please drop me a line (xcenek@fi.muni.cz).
It is nice to know that I helped you.

----------------------------------

This document describes how I installed and configured postgreSQL and ODBC on
Red Hat Linux.

Written by: Pavel Cenek (xcenek@fi.muni.cz)
=============================================================================

0. notes
--------
I tried the installation on Red Hat Linux 7.1 with postgreSQL 7.1.3
Some tricks which are not documented on one place (or are not documented at
all) are written here. This file is not intended to replace postgreSQL
documentation. If you have any troubles RTMF (http://www.postgresql.org/idocs)

Actually every rpm has version number connected to the name, I use names
without versions to refer to rpm files in the following text


1. installing and configuring postgreSQL
----------------------------------------
from http://www.postgresql.org or from a mirror site get from the section
"software" following rpms:

postgresql              client binaries, docs, man pages
postgresql-server       server binaries, some docs
postgresql-libs         shared libraries
postgresql-test         files for testing DB functionality
postgresql-devel        header files and developers libraries

Type
    rpm -i <list of rpms>           for installing
or
    rpm -U <list of rpms>           for upgrade.


After installing we ensure that postgres will be started at startup of the
linux machine.

Rename
    /etc/rc.d/rcL.d/KXYpostgresql
to
    /etc/rc.d/rcL.d/SXYpostgresql
for every runlevel L on which you want postgres to be started (XY is a number)

If you want to access the DB via TCP/IP (i think ODBC does it this way, but I
can be wrong), do the folowing:
in file
    /etc/rc.d/init.d/postgresql
add to line looking like this
    su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -D $PGDATA -p
/usr/bin/postmaster start  > /dev/null 2>&1" < /dev/null
parameters -o -i
    su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -D $PGDATA -o -i -p
/usr/bin/postmaster start  > /dev/null 2>&1" < /dev/null

The last thing to do is to set up access rights in file
    /var/lib/pgsql/data/pg_hba.conf

Type
    /etc/rc.d/init.d/postgresql start
to start the DB server.

You can go to the directory
    /usr/lib/pgsql/test/regress
and run tests of DB functionality (read README in that directory)


2. installing and configuring ODBC
----------------------------------
from http://www.postgresql.org or from a mirror site get from the section
"software" following rpm:

postgresql-odbc       libraries and ini files

Type
    rpm -i <rpm name>           for installing
or
    rpm -U <rmp name>           for upgrade.

Type
    psql -d template1 -f /usr/share/pgsql/odbc.sql
to add some extra sql definitions to template1 table.

You are in the PostgreSQL interactive terminal.

Type
    create type lo (
      internallength=4,
      externallength=10,
      input=int4in,
      output=int4out,
      default='',
      passedbyvalue
    );
to add support for blobs accessible via ODBC to template1 table.

Quit the psql
    \q

Every created database inherits all definitions form template1, so you should
have all definitions in every DB you ever create.


3. installing and configuring iODBC Driver Manager
--------------------------------------------------
from http://www.iodbc.org/ get from the section "software download" iODBC
Driver Manager, i.e. following rpms:

libiodbc              iODBC Driver Manager Runtime Package (libraries,
                        config files)
libiodbc-devel        iODBC Developers Kit (odbc header files and developers
                        libraries)

Type
    rpm -i <list of rpms>           for installing
or
    rpm -U <list of rpms>           for upgrade.


Now we have to configure /etc/odbc.ini and and /etc/odbcinst.ini

if you use via ODBC only postgres, you can replace /etc/odbcinst.ini with
/etc/pgsql/odbcinst.ini
    cp /etc/pgsql/odbcinst.ini /etc/odbcinst.ini

Othervise you have to edit /etc/odbcinst.ini with use of information from
/etc/pgsql/odbcinst.ini

/etc/odbc.ini is special chapter... Be careful, this file is PARTIALLY case
sensitive. It means some attributes are, some are not.

If you have torubles with connection to postgres via ODBC, check if parameters
in your /etc/odbc.ini have the same case as in the following example:

------ /etc/odbc.ini file begin -----
;
;  odbc.ini
;
[ODBC Data Sources]
test = PostgreSQL Test

[test]
Driver=/usr/lib/libpsqlodbc.so
Description=Sample PostgreSQL DSN
DSN=test
Servername=localhost
Username=gin2
Database=mydb
ReadOnly=No
Servertype=postgres
Port=5432
FetchBufferSize=99
ServerOptions=
ConnectOptions=
Options=
ReadOnly=no
Trace=1
TraceFile=/home/gin2/odbc.trace
Debug=1
DebugFile=/home/gin2/odbc.debug
CommLog=1

[Default]
Driver = /usr/lib/libpsqlodbc.so

[ODBC]
InstallDir = /usr/lib/libiodbc.so
------ /etc/odbc.ini file end -----

If you have done everything properly, the installation and setup is now
finished.

4. testing ODBC
---------------
With iODBC Driver Manager package should be distributed a small testing program
odbctest. I haven't found it, so I took its source directly from CVS and
compiled it myself.
This link worked fine for me:
http://www.iodbc.org/cgi-bin/cvsweb.cgi/iODBC/samples/odbctest.c

gcc -c odbctest.c -o odbctest.o
gcc -s -o odbctest odbctest.o -liodbc

notice the -liodbc -- every program using odbc must be linked with iodbc
library. It is also not very well documented (at least I found it nowhere).

You can run the odbctest binary. If you run it without parameters, it asks
you for ODBC connect string. If you enter '?', it lists all dsn names (taken
from /etc/odbc.ini. Be careful, the right ODBC connect string is not "name",
but "dsn=name"!!!

If you have trubles, look into /home/gin2/odbc.trace
If you don't have troubles, continue reading


Congratulations, now everything should work fine :) I hope I saved you couple
of days, which I spent looking for these information.