Tutorial to Install and Configure PostgreSQL in OpenSUSE 12 Part1

Government | Commercial

Step by Step Tutorial to Install and Configure PostgreSQL in OpenSUSE 12 Part1

Brief PostgreSQL Description:
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

Like for any other RDBMS system even for the fast and furious Oracle the default values might not be the best or optimal settings for your development or production environments or projects. So here are few pre-installation adjustments I did for our Jira bug and issues tacking system. These values might be a too much for some of you out there but I tend to follow the Architects and Civil Engineers approach of designing and planning for long terms, for instance when they plan a road or a complex system of bridges interconnecting towns or cities they would spent good time in the design so the roads and bridges to be built would provide with at 15 to 20 years of smooth traffic capacity. So in away I tend to make a relation with this point when I design and implement new RDBMS systems either using Oracle or PostgreSQL.

Any ways here we go:

1) Pre-installation Steps:
Since I already know I’ll be editing the “postgresql.conf” once the installation of PostgreSQL 9 is completed then let’s edit the shmmax value if there is none then add a line as follow:

kernel.shmmax = 95000000

postgres@opsujira:~> cat /etc/sysctl.conf 
kernel.shmmax = 95000000

To install in OpenSUSE 12.1
This installs postgreSQL Database server on opsujira system.

Quick OS info: few ways to do option this:

opsujira:/home/abaez # cat /etc/issue
Welcome to opsujira 12.1 "Asparagus" - Kernel r (l).
opsujira:/home/abaez # cat /etc/SuSE-release
opsujira 12.1 (x86_64)
VERSION = 12.1
CODENAME = Asparagus
opsujira:/home/abaez #

This would implement the basic server PostgreSQL package and

opsujira:~ # yast2 –I postgresql-server

Once that is completed you need to install additional packages as need for your environment or project, in our case I implemented the following:
The Programs/packages listed here are needed to create and Run a PostgreSQL Server and client applications:

pgaccess - Database Management Tool for PostgreSQL
pgadmin3 - Management and Administration Tools for the PostgreSQL Database
phpPgAdmin - Administration of PostgreSQL over the web
postgresql-docs - HTML Documentation for PostgreSQL
postgresql-plperl - The PL/Tcl, PL/Perl, and PL/Python procedural languages for PostgreSQL
postgresql-plpython - The PL/Python Procedural Languages for PostgreSQL
postgresql-pltcl - PL/Tcl Procedural Language for PostgreSQL
psqlODBC - ODBC Driver for PostgreSQL
PyGreSQL - Python Client Library for PostgreSQ
qt3-postgresql - A PostgreSQL Plug-In for Qt

Once the implementation of all these are completed, then we proceed to Fine Tunning and Client Connection adjustments:
The files for postgreSQL are installed in


This is the base directory for PostgreSQL:


The config files are found in


For now let’s keep in mind these two important parameter files

pg_hba.conf & postgresql.conf

Once installation is complete, start postgreSQl for the first time
Start postgresql for the first time:

opsujira:~ # rcpostgresql start

Then verify the status:

postgres@opsujira:~> pg_ctl -D /var/lib/pgsql/data status
pg_ctl: server is running (PID: 22435)
/usr/bin/postgres "-D" "/var/lib/pgsql/data"

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

opsujira:~ # su postgres -c psql postgres
abaez@opsujira:~> su postgres -c psql postgres
psql (9.1.3)
Type "help" for help.


From the postgresql prompt

postgres=# alter user postgres with password 'xxxxxx';

Quit from postgreSQL prompt

postgres=# q

Next on Part2 we’ll go through some more tuning and client connection adjustments stay tune…