#replication

Replication and Load Balance in PosgreSQL 9.4 with PgPool2

When you design a scale, high availability, high performance system, I sure that you have to apply Replication, Sharding and Load Balance technologies for your database layer. In this post, I will show you the way how to setup a load balance with PgPool2 for your PostgreSQL Master-Slave Replication.

My scenario as image below:

pgpool (1)

Step 1: Install PostgreSQL Replication for your servers

At first, Please refer to 2 posts:

 

After that, install Pgpool Lib:

Step 2, Install PgPool2 for server 192.168.1.3

Edit /etc/pgpool2/pgpool.conf as below:

Make sure you set enable_pool_hba = on

And change /etc/pgpool2/pool_hba.conf to enable md5 for authentication:

Add account to /etc/pgpool2/pool_passwd.conf (just a example)

Now, run pgpool with log:

And stop Pgpool if you need:

Finally, test your installation. Good Luck!

Replication Master-Slave with PostgreSQL 9.4 in Ubuntu 14.04

My scenario is I have two servers at addresses 192.168.1.191 and 192.168.1.171, and installed Ubuntu 14.04 and PostgreSQL 9.4.

To know how to install PostgreSQL, please read Install PostgreSQL 9.4 in Ubuntu 14.04

Step 1: Make sure that the two servers are able to SSH freely between them

Switch to Postgres account:

Generate ssh key:

Copy SSH key to opposite server:

Note: you have to do this step for the both servers 

Step 2: Setup Master Node

Create an account for replication

Modify postgres access config file:

Add content below:

And modify postgres config file:

The config value should be:

And now, restart Master server:

Step 3: Setup Slave Node

Stop PostgreSQL:

Modify postgres access config file:

Add content below:

And modify postgres config file:

The config value should be:

Step 4: Start the replication

In Master Server, run command below to start the replication

In Slave Server, create recovery.conf file:

And add the content below to that file:

And restart Slave Server to complete the setup:

It is time for testing, you can try to create and insert data to Master Server. It will be synchronized to Slave Server