Running multiple PostgreSQL 9.2

Running multiple PostgreSQL 9.2 Instances on one server in CentOS 6/RHEL 6/Fedora

This post describes how to run multiple PostgreSQL 9.2 Instances on one server in CentOS 6/RHEL 6/Fedora flavors. I am assuming that you have a running postgresql engine on the server already. If not check this post on how to install postgresql server that I wrote earlier and install the first/main instance. Now that you have a running database engine(I will call it first instance) you can start slicing second instance on the same server. As most of my posts I will divide this post into multiple steps for easier understanding and implementation.

1. Create new data directories for this second instance and let postgres user own them

mkdir /var/lib/pgsql/9.2/data2
chown postgres.postgres /var/lib/pgsql/9.2/data2

2. Create new init script for this instance and edit it

Copy the init script from first instance and rename it to postgresql2-9.2 and edit it for making changes that reflect the new data directory that we created in the above step.

cp /etc/init.d/postgresql-9.2 /etc/init.d/postgresql2-9.2
vi /etc/init.d/postgresql2-9.2

modify the following lines as shown below:

     73 # Set defaults for configuration variables
     74 PGENGINE=/usr/pgsql-9.2/bin
     75 PGPORT=5433
     76 PGDATA=/var/lib/pgsql/9.2/data2
     77 PGLOG=/var/lib/pgsql/9.2/pgstartup2.log
     78 # Log file for pg_upgrade
     79 PGUPLOG=/var/lib/pgsql/$PGMAJORVERSION/pgupgrade2.log
     80 
     81 lockfile="/var/lock/subsys/${NAME}"
     82 pidfile="/var/run/postmaster2-9.2.pid"     

I have updated PGPORT, PGDATA, PGLOG, PGUPLOG, pidfile variables specific to this new instance. You can subscript to what ever you like. I am currently sub-scripting all file names with 2. Also you can use what ever open port you want this new server instance to run on.

3. Initialize and start this new postgres instance

service postgresql2-9.2 initdb
service postgresql2-9.2 start 

4. Update configs : pg_hba.conf and postgresql.conf

Update the listen address and port variable values to reflect to what they actually should be.

vi /var/lib/pgsql/9.2/data2/postgresql.conf

     57 # - Connection Settings -
     58 
     59 listen_addresses = '*'                  # what IP address(es) to listen on;
     60                                         # comma-separated list of addresses;
     61                                         # defaults to 'localhost'; use '*' for all
     62                                         # (change requires restart)
     63 port = 5433                             # (change requires restart)

Update pg_hba.conf to your needs. Please check my other post that I have link up on how to install postgresql 9.2 which have a bit more description on ph_hba conf file.

5. Test connect and add this new instance service to server startup

Connect to this new instance, and see if you can connect, create a user and do what ever you want there.

su postgres
psql -p5433 -dpostgres

And here is how you add it to server startup list :

chkconfig --add postgresql2-9.2 
chkconfig postgresql2-9.2 --level 2345 on

Thats all folks you have a new shiny postgres instance running on the same server as main instance. You can slice as many instances as you want as long as you have enough memory and processors on this server. Open up this port in iptables to access it from outside the server if needed.