Re: Installing and configuring postgreSQL and ODBC on Red

Pavel,

Do you have any objections if I add this as a howto on the ODBC website?

Regards, Dave

> -----Original Message-----
> From: Pavel Cenek [mailto:pavel(dot)cenek(at)itek(dot)norut(dot)no]
> Sent: 12 February 2002 09:57
> To: pgsql-odbc(at)postgresql(dot)org
> Subject: [ODBC] Installing and configuring postgreSQL and
> ODBC on Red Hat Linux
>
>
> 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(at)fi(dot)muni(dot)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(at)fi(dot)muni(dot)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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html