Automate PostgreSQL Database Instance Creation


At times there are needs to setup multiple PostgreSQL instances. I have explained you how to install PostgreSQL & how to automate its installation on Linux. This time I will show you how to automate PostgreSQL instances creation using a shell-script. Instances are PostgreSQL installations to different directories running on different ports.

Below is the script that I wrote to automate this task 🙂 It will prompt you to enter the location & port.

#set -x
#PostgreSQL instance creation script version 1.0
#Author : - Shashank Srivastava
echo "Welcome to PostgreSQL instance creation script. Below instances are already created. Use a unique path."
echo ""
ls -lf /opt | grep -i postgre | sort
echo ""
echo -n "Enter the absolute path for this instance. "
read ans
echo ""
echo "You chose $ans. Below are the Postgre ports currently in use. Please choose a unique port when prompted."
echo ""
netstat -tulnp | grep postgre
echo ""
echo -n "Enter port for this new instance to run on. "
read PORTNUM
echo ""
mkdir -p $ans
sleep 2s
mkdir -p $ans/data
sleep 2s
chown -R postgres $ans/data
cd /home/shashank/postgresql-9.3.5
./configure -prefix=$ans
make
make install
su - postgres -c "$ans/bin/initdb -D $ans/data"
su - postgres -c "cp -p $ans/data/postgresql.conf $ans/data/postgresql.conf.bkp"
sed -i "s/#port = 5432/port = $PORTNUM/g" $ans/data/postgresql.conf
su - postgres -c "$ans/bin/pg_ctl -D $ans/data -l logfile start"
echo ""
echo "Checking PostgreSQL verison..."
echo ""
$ans/bin/psql --version
echo ""
echo "Postgre instance is running on port $PORTNUM"
netstat -tulnp | grep $PORTNUM
echo ""
echo "Installation Successful."

Here’s the script in action 😉

[root@server shashank]# sh postgre_instance_setup.sh
Welcome to PostgreSQL instance creation script. Below instances are already created. Use a unique path.

PostgreSQL
PostgreSQL2
PostgreSQL3
PostgreSQL4
PostgreSQL5
PostgreSQL6
PostgreSQL7

Enter the absolute path for this instance. abc

You chose abc. Below are the Postgre ports currently in use. Please choose a unique port when prompted.

tcp        0      0 127.0.0.1:5437              0.0.0.0:*                   LISTEN      785/postgres
tcp        0      0 127.0.0.1:5438              0.0.0.0:*                   LISTEN      8863/postgres
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      6723/postgres
tcp        0      0 0.0.0.0:5433                0.0.0.0:*                   LISTEN      51663/postgres
tcp        0      0 0.0.0.0:5434                0.0.0.0:*                   LISTEN      35999/postgres
tcp        0      0 127.0.0.1:5435              0.0.0.0:*                   LISTEN      48190/postgres
tcp        0      0 127.0.0.1:5436              0.0.0.0:*                   LISTEN      57141/postgres
tcp        0      0 ::1:5437                    :::*                        LISTEN      785/postgres
tcp        0      0 ::1:5438                    :::*                        LISTEN      8863/postgres
tcp        0      0 :::5432                     :::*                        LISTEN      6723/postgres
tcp        0      0 :::5433                     :::*                        LISTEN      51663/postgres
tcp        0      0 :::5434                     :::*                        LISTEN      35999/postgres
tcp        0      0 ::1:5435                    :::*                        LISTEN      48190/postgres
tcp        0      0 ::1:5436                    :::*                        LISTEN      57141/postgres

Enter port for this new instance to run on.