PostgreSQL Installation and Configuration on Single Node and Multi Node Clustering

Installation of Postgresql through Postgres Enterprise Manager tool:

1. Download Postgres Enterprise Manager tool from the link https://www.enterprisedb.com/software-downloads-postgres

2. And Install Postgresql first with Graphical wizard as below,

3. Click Next to continue. The Installation Directory window opens.

4. The Data Directory window opens

5. Now, single node cluster is installed and configured. So you can log on to pg Admin for UI based transaction or terminal based transaction.

$ sudo -i -u postgres

$ psql

#postgres:+ SELECT * FROM pg_databases;

Multi DB clustering:

1. Repeat the same procedure in another system for multi node creation.

2. From now on, I assume that database clusters are located at /home/postgres/data and are owned by postgres user:

$ initdb -D /home/postgres/data

3. Next add followings to /home/postgres/data/postgresql.conf. “logging_collector” and below are not really relevant to Streaming replication but they are my favorites to make my life easier. You might want to remove “log_statement = ‘all'” in production environment however.

listen_address = '*'
hot_standby = on
wal_level = hot_standby
max_wal_senders = 1
logging_collector = on
log_filename = '%A.log'
log_line_prefix = '%p %t '
log_truncate_on_rotation = on
log_statement = 'all'

4. Put pg_hba.conf to /home/postgres/data. Of course you need to replace “/some/where/” with actual directory where you downloaded the scripts. Caution: settings here allows to access from any IP address. Please apply appropreate setting for your real world systems.

$ cp /etc/postgresql/pg_hba.conf" /home/postgres/data"

5. Start PostgreSQL server on server2 and server3. At this point, those PostgreSQL servers will run as primary server, thus no streaming replication is working.

$ pg_ctl -D /home/postgres/data start

6. Next you need to allow postgres user on server2 and server3 can access each other without password. Execute ssh-keygen command as postgres and append the contents of /home/postgres/.ssh/id_rsa.pub to /home/postgres/.ssh/authorized_keys of other server. After this we recommend to test the setting by executing ls command via ssh, for example.

$ ssh server2 ls

Installing pgPool II

1. Run the below commands in the server1 postgres server,

$ tar xfz /etc/postgresql/pgpool-II-3.3.3.tar.gz
$ cd pgpool-II-3.3.3
$ ./configure
$ make
$ sudo make install
$ cp /etc/postgresql//pgpool-II-3.3.3/install-functions.sh
$ sh install-functions.sh

2. Next you need to install pgpool-II configuration files onto server1. The main configuration file is pgpool.conf. The other one is the pcp.conf. You will need to execute followings as root.

$ cp /etc/postgresql/pgpool-II-3.3.3/pgpool.conf /usr/local/etc
$ chown apache /usr/local/etc/pgpool.conf
$ cp /etc/postgresql/pgpool-II-3.3.3/pcp.conf /usr/local/etc
$ chown apache /usr/local/etc/pcp.conf

3. Install basebackup.sh and pgpool_remote_start, neccessary for online recovery onto server2 and server3. Note that in pgpool_remote_start the path to pg_ctl command is specified. You might want to change it to an appropreate path according to your PostgreSQL installation.

$ cp /etc/postgresql/pgpool-II-3.3.3/baseback.sh /home/postgres/data
$ chmod 755 basebackup.sh
$ cp /etc/postgresql/pgpool-II-3.3.3/pgpool_remote_start /home/postgres/data
$ chmod 755 pgpool_remote_start

4. Install failover.sh for automatic failover onto server1.

$ sudo cp /etc/postgresql/pgpool-II-3.3.3/failover.sh /usr/local/etc
$ chmod 755 failover.sh

5. Create neccessary directories on server2 and server3. Execute followings as root.

$ mkdir /var/run/pgpool
$ chown apache /var/run/pgpool
$ mkdir /var/log/pgpool
$ chown apache /var/log/pgpool
$ mkdir /var/log/pgpool/trigger
$ chown postgres /var/log/pgpool/trigger

6. Add /var/www/.ssh/id_rsa.pub to /home/postggres/.ssh/authorized_keys on server2 and server3. After this we recommend to test the setting by executing ls command via ssh, for example do followings as apache user.

$ ssh postgres@server2 ls
$ ssh postgres@server3 ls

Installing pgpoolAdmin

1. Login to pgpoolAdmin and start pgpool-II from “pgpool status” menu. You see server2 port 5432 PostgreSQL is running as a primary server. You should be able to connect to server3 port 5432 by using psql. Let’s try to create a table.

$ cd /var/www/html/pgpoolAdmin-3.3.1
$ chmod 777 templates_c
$ chown apache conf/pgmgt.conf.php
$ chmod 644 conf/pgmgt.conf.php

$ createdb -h server1 test
$ psql -h server1 test
test=# create table t1(i int);
CREATE TABLE
test=#

2. Starting standby server.To start standby server, just click “Recovery” button. “basebackup.sh” will be executed and the standby server will be automatically started.

$ insert into t1 via pgpool-II.
-- it will be executed on primary server

psql -h server1 test
test=# insert into t1 values(1);
test=# \q

psql -h server3 test
-- now connected to standby server
test=# select * from t1;
i
---
1
(1 row)

3. If standby goes down, it is disconnected from pgpool-II. Users can issue SQL via pgpool-II as usual. Just streaming replication is stopped. To recover standby server, click “Recovery” button.