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 …. postgres@opsujira:~>
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). or 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:
collectd-plugin-postgresql libecpg6 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
/usr/share/postgresql/
This is the base directory for PostgreSQL:
/var/lib/pgsql/
The config files are found in
/var/lib/pgsql/data
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" postgres@opsujira:~>
Set Username & password
Login as postgres user and set the password
opsujira:~ # su postgres -c psql postgres or abaez@opsujira:~> su postgres -c psql postgres Password: psql (9.1.3) Type "help" for help. postgres=#
From the postgresql prompt
postgres=# alter user postgres with password 'xxxxxx'; ALTER ROLE postgres=#
Quit from postgreSQL prompt
postgres=# q
Next on Part2 we’ll go through some more tuning and client connection adjustments stay tune…