initialise multiple postgres instance on same RHEL server

It is pretty easy to install multiple instances of PostgreSQL servers on same server and have its benefits.

The initialise file for PostgreSQL server is located here: /etc/init.d/postgresql

where /var/lib/pgsql/data   refers to PostgreSQL data directory (PGDATA) by default.

We will make a copy and modify it by changing the environment variable in our init script here /etc/sysconfig/pgsql/postgresql.

I'm creating a dev server named as  "postgresql-9.6_dev_2" hence making a symbolic link to the main postgresql init script.

[root@oracleasm1 init.d]# pwd
/etc/init.d
 
[root@oracleasm1 init.d]# ln -s postgresql postgresql-9.6_dev_2

Now feed the values in new init script. Changing the port to 5433

my 2 cents here :) 





" PGLOG  is startup-log (a first place to look while troubleshooting)"

[root@oracleasm1 init.d]# vi postgresql-9.6_dev_2

PGDATA=/var/lib/pgsql/dev-data_new
PGPORT=5433
PGLOG=/var/lib/pgsql/dev-data_new/pgstartup.\${PGPORT}.log

Once this is done, you can initialise the data directory as below :

[root@oracleasm1 init.d]# /etc/init.d/postgresql-9.6_dev_2 initdb

Oops, it failed  I cheked the pgstartup log and got to know its the directory permission issue.

<= the log looks clean :)

Let’s start the service now..

This time, it again failed. I tried to validated the log and it failed with error as below :

> WARNING:  could not create listen socket for "localhost"

> FATAL:  could not create any TCP/IP sockets

I simply tried to edit postgresql.conf file new port.  Don't forget to reload the config.

-bash-4.1$ vi postgresql.conf

#port = 5432
port = 5433

--------------------------------------------

-bash-4.1$ psql -c "select pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

Output from Log :

[root@oracleasm1 init.d]# ps -ef|grep 12793
postgres 12793     1  0 19:26 ?        00:00:00 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/dev-data_new
postgres 12795 12793  0 19:26 ?        00:00:00 postgres: logger process             
postgres 12797 12793  0 19:26 ?        00:00:00 postgres: checkpointer process       
postgres 12798 12793  0 19:26 ?        00:00:00 postgres: writer process             
postgres 12799 12793  0 19:26 ?        00:00:00 postgres: wal writer process         
postgres 12800 12793  0 19:26 ?        00:00:00 postgres: autovacuum launcher process
postgres 12801 12793  0 19:26 ?        00:00:00 postgres: stats collector process    
root     15028  3575  0 21:09 pts/0    00:00:00 grep 12793
[root@oracleasm1 init.d]#


------------------------------------------

-bash-4.1$ psql postgres -p 5433

postgres=# show port ;
 port
------
 5433
(1 row)

Follow us on Facebook ..!!