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

/usr/share/postgresql/

Here you can find the sample files.

Actual data and runtime files are found here

/var/lib/pgsql/

The config files are found in

/var/lib/pgsql/data

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';
ALTER ROLE

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

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         127.0.0.1/32          ident sameuser
host    all         all         ::1/128               ident sameuser

to

local   all         all                               md5 sameuser
host    all         all         127.0.0.1/32          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>