This is my first note in a series of posts about supporting PostgreSQL on OEL 6.4. Let me start with a fair warning, I’m a total newbie when it comes to PostgreSQL (or PG in short). Don’t have any PG database in production, but this might (and I’m sure it will!) change sooner or later — you know what they say that necessity is the mother of invention. Considering economic downturn that’ll likely last to the end of this decade (or even further)…and you find yourself happy that you have at least some alternative.
Actually, I was always fond to an idea to replace some production “big vendor” databases (in particular Oracle, MS SQL and MySQL) with the most sophisticated free & OSS database on the planet. Sure, PG has some “critical” shortcomings (such as complete lack of proper auditing and even worse, the lack of proper incremental binary backup/recovery tools similar to Oracle RMAN), but the robust, feature-rich, MVCC capable RDBMS is more than enough to handle all sorts of application workload without the need to worry about licenses. Let’s start with installation steps for installation of PG 9.2.4 on OEL 6.4:

Let’s check what we already have on OEL 6.4:

[root@acme ~]# uname -r
2.6.39-400.17.1.el6uek.x86_64

[root@acme ~]# yum info postgresql | grep Version
Version     : 8.4.13

Let’s get rid of PG 8.4.13:

[root@acme ~]# yum erase postgresql

// disable OEL 6.4 default PG repository by adding exclude=postgresql* to
// /etc/yum/pluginconf.d/rhnplugin.conf

[root@acme ~]# nano /etc/yum/pluginconf.d/rhnplugin.conf

[root@acme ~]# cat /etc/yum/pluginconf.d/rhnplugin.conf
[main]
enabled = 0
gpgcheck = 1
exclude=postgresql*

Now we need to install current (PG 9.2) repository:

# rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6.4-x86_64/pgdg-redhat92-9.2-7.noarch.rpm

Now, the installation is as easy as:

# yum install postgresql92-server postgresql92 postgresql92-contrib

....
Dependencies Resolved

===============================================...
 Package                               Arch    ...
===============================================...
Installing:
 postgresql92                          x86_64  
 postgresql92-contrib                  x86_64  
 postgresql92-server                   x86_64  
Installing for dependencies:
 postgresql92-libs                     x86_64  
 uuid                                  x86_64  

Transaction Summary
===============================================...
Install       5 Package(s)

Total download size: 5.4 M
Installed size: 23 M
Is this ok [y/N]:
...

[root@acme ~]# psql --version
psql (PostgreSQL) 9.2.4

Now, we need to initialize PG cluster and create our first superuser (me;):

// you can't run initdb as root, you must become user under which 
// postgres server will run! By default the OS user is postgres.

[root@acme ~]# su - postgres

// cluster initialization. Note that I told initdb my locale, which is
// specific to my country. It's important for further 
// creation of databases in the cluster to specify correct locale!!
// Also note, that I could tell initdb where to create data directory, let's // say on some dedicated mount point, such as:
// --pgdata=/u01/pg92/data
// as this is my test database, I don't mind having data files
// in default location (/var/lib/pgsql/9.2/data)

-bash-4.1$ /usr/pgsql-9.2/bin/initdb --locale=sl_SI.UTF8

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "sl_SI.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

fixing permissions on existing directory /var/lib/pgsql/9.2/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/9.2/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/pgsql-9.2/bin/postgres -D /var/lib/pgsql/9.2/data
or
    /usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/9.2/data -l logfile start

Now, we need to setup some basic parameters that’ll allow us to connect to the PG over the network:

//
// setup static IP for PG "listener" (postmaster)
// Obviously make sure that port 5432 is open on firewall!
//

-bash-4.1$ nano /var/lib/pgsql/9.2/data/postgresql.conf

listen_addresses='192.168.1.100'
port = 5432

//
// edit PG permissions in pg_hba.conf
// In my case I limited network access to my 2 workstations.
// md5 encrypted passwords are recommended method for login!
//

-bash-4.1$ nano /var/lib/pgsql/9.2/data/pg_hba.conf

host    all             all             192.168.1.110/24          md5
host    all             all             192.168.1.120/24          md5

Post installation configuration:

// start PG

# service postgresql-9.2 start
Starting postgresql-9.2 service:                           [  OK  ]

// if you want PG to start on boot...

[root@acme ~]# chkconfig | grep postgresql-9.2
postgresql-9.2  0:off   1:off   2:off   3:off   4:off   5:off   6:off

# chkconfig --levels 235 postgresql-9.2 on 

// create superuser...

root@acme ~]# su - postgres
-bash-4.1$ psql postgres
psql (9.2.4)
Type "help" for help.

postgres=# CREATE ROLE alesk WITH SUPERUSER LOGIN PASSWORD 'mysecret';
CREATE ROLE
postgres=#