Running multiple PostgreSQL 9.2 Instances on CentOS 6/RHEL 6/Fedora

Standard

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

multiple_pg_1

    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

multiple_pg_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"     

multiple_pg_3

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 

multiple_pg_4

    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.

multiple_pg_5

    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

multiple_pg_6

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

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

multiple_pg_7

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.

If you happen to have any issues or struck in the process, please feel free to email me or post in comments.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

About Prashanth Goriparthi

Sr. Database Administrator / Architect

6 Comments

  1. Prashanth,
    Instead of ‘modifiying’ your init script (at line 73) you can take the data you inserted @ 73 and place it into a file in /etc/sysconfig/pgsql/postgresql2-9.2

    Now your init script is unmodified and anything placed in /etc/sysconfig/pgsql/postgresql2-9.2 will be read by the init script.

    Certainly makes it easy to build additional instances as well.

    Enjoy!
    Wayne

  2. Hi Prashant,

    Nice tutorial indeed. By the way, do you think you could write the same for PostGreSQL 9.3 in Windows? It would be great help. I could not find init.d folder as there is no etc folder in windows version of PostGres.

    Cheers

  3. I like this tutorial.
    Between steps 4 and 5 you could add another restart of servres
    to pick up the updates from configuration files.

  4. Pingback: Rename Centos Server | Kuplux's

  5. I used your script to create and manage multiple snapshots (off of a logical volume) and static instances on one server which my development and qa team use on a daily basis. I automated the creation, deletion and refresh of my instances using bash scripts. Thanks for making this available to the community!

Leave a Reply

* Captcha [Please fill/solve below] : * Time limit is exhausted. Please reload the CAPTCHA.