Jul 022008

PostgreSQL is a powerful, open source relational database system with active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation. For more details and features, click here

To install in openSUSE 11.0

opensuse:~ # yast2 –install postgresql-server

This installs postgreSQL Database server on your openSUSE system.

The files for postgreSQL are installed in


Here you can find the sample files.

Actual data and runtime files are found here


The config files are found in


The files of importance are pg_hba.conf & postgresql.conf

Once installation is complete, start postgreSQl for the first time

opensuse:~ # rcpostgresql start

Set Username & password
Login as postgres user and set the password

opensuse:~ # su postgres -c psql postgres

From the postgresql prompt

postgres=# ALTER USER postgres WITH PASSWORD ‘postgres';

Quit from postgreSql prompt

postgres=# \q

One of the tings that needs to be checked is whether you use IDENT to authenticate users. If you don’t then you need to edit and modify the lines that allow user access to not to use IDENT. Else, you may face with error


“IDENT authentication failed for user”

Hence, if you do not use IDENT, edit the /var/lib/pgsql/data/pg_hba.conf file and change “ident” to “md5″. Say, for instance, the lines below

local   all         all                               ident sameuser
host    all         all          ident sameuser
host    all         all         ::1/128               ident sameuser


local   all         all                               md5 sameuser
host    all         all          md5 sameuser
host    all         all         ::1/128               md5 sameuser

Restart postgreSQL

Once done,restart postgreSQL as follows:

opensuse:~ # rcpostgresql restart

Create and Delete users

To create an user

opensuse:~ # su postgres

postgresql@opensuse:~> createuser -D <username>

for user with password

postgresql@opensuse:~> createuser -D -p <username>

To delete user

postgresql@opensuse:~> dropuser <username>