Introduction

Postgres is a popular database management system, There are many way to set up replication on a postgres system. Here I will cover How to configure replication using a hot standby, and then with the help of makara gem Redirecting Rails Read Queries to Slave server.

For this We need two ubuntu 14.04 VSP servers. Out of which one will be our master server and other will be slave server.

Install Postgres Server:

Following steps need to be performed on both the servers.

For installation of postgres run the following commands.

 sudo apt-get update
 sudo apt-get install postgresql postgresql-contrib postgresql-client
 

By Default PostgresSQL creates a user called “postgres” in order to handle its initial databases. We will need to set a password for the postgres user so that we can transfer the key files initially.

 sudo passwd postgres
 

Switch over to the postgres user:

 sudo su - postgres
 

Generate an ssh key for the postgres user:

 ssh-keygen
 

Press “ENTER” to all of the prompts that follow.

Transfer the keys to the other server by typing:

 ssh-copy-id IP_address_of_opposite_server
 

You should now be able to ssh freely between your two servers as the postgres user.

Configure the Master Server:

All of this commanded should be executed with the postgres user.

 psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'password';"
 

Create user called “rep” that can be used solely for replication:

Next, we will move to the postgres configuration directory:

 cd /etc/postgresql/9.3/main
 

We will modify the access file with the user we just created:

 nano pg_hba.conf
 

At any place not at the bottom of the file, add a line to let the new user get access to this server:

 host replication rep IP_address_of_slave/32 md5
 

Save and close the file.

Then open postgres configuration file:

 nano postgresql.conf
 

Find these parameters. Uncomment them if they are commented, and modify the values according to what we have listed below:

 listen_addresses = 'localhost,IP_address_of_THIS_host'
 wal_level = 'hot_standby'
 archive_mode = on
 archive_command = 'cd .'
 max_wal_senders = 1
 hot_standby = on
 

Save and close the file.

Restart the master server to implement your changes:

 service postgresql restart
 

Configure the Slave Server

Firstly you need to shut down the slave server

 service postgresql stop
 

We will be making some similar configuration changes to postgres files, so change to the configuration directory:

 cd /etc/postgresql/9.3/main
 

Allow the other server to connect to this. This is in case we need to turn the slave into the master later on down the road.

 nano pg_hba.conf
 

Again, add this line somewhere not at the end of the file:

 host replication rep IP_address_of_master/32 md5
 

Next, open the postgres configuration file:

 nano postgresql.conf
 

You can use the same configuration options you set for the master server, modifying only the IP address to reflect the slave server’s address:

 listen_addresses = 'localhost,IP_address_of_THIS_host'
 wal_level = 'hot_standby'
 archive_mode = on
 archive_command = 'cd .'
 max_wal_senders = 1
 hot_standby = on
 Save and close the file.
 

Replicating the database:

Before we start slave replicate the master we need to give initial database to build. This is because it reads the log of master server and applies the changes to its own database. we need database to match the master database.

On the master server, we can use an internal postgres backup start command to create a backup label command. We then will transfer the database data to our slave and then issue an internal backup stop command to clean up:

 psql -c "select pg_start_backup('initial_backup');"
 rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.3/main/ slave_IP_address:/var/lib/postgresql/9.3/main/
 psql -c "select pg_stop_backup();"
 

Now we have to configure a recovery file on our slave server. on slave navigate to data directory.

 cd /var/lib/postgresql/9.3/main
 

Here, we need to create a recovery file called recovery.conf:

 nano recovery.conf
 

fill the following information.

 standby_mode = 'on'
 primary_conninfo = 'host=master_IP_address port=5432 user=rep password=yourpassword'
 trigger_file = '/tmp/postgresql.trigger.5432'
 

Make sure to change IP address and password for rep user.

The last line in the file, trigger_file, is one of the most interesting parts of the entire configuration. If you create a file at that location on your slave machine, your slave will reconfigure itself to act as a master.

This will break your current replication, especially if the master server is still running, but is what you would need to do if your master server goes down. This will allow the slave to begin accepting writes. You can then fix the master server and turn that into the slave.

You should now have the pieces in place to start your slave server. Type:

service postgresql start
You’ll want to check the logs to see if there are any problems. They are located on both machines here:

 less /var/log/postgresql/postgresql-9.3-main.log
 

You should see that it is successfully connecting to the master server.

Use makara gem

Makara is generic master/slave proxy. It handles the heavy lifting of managing, choosing, blacklisting, and cycling through connections. It comes with an ActiveRecord database adapter implementation.

Installation

 gem 'makara', github: 'taskrabbit/makara', tag: 'v0.3.x'
 

Database.yml

 production:
 adapter: 'mysql2_makara'
 database: 'MyAppProduction'
 # any other standard AR configurations# add a makara subconfig

 makara:
   # the following are default values
   blacklist_duration: 5
   master_ttl: 5
   sticky: true

   # list your connections with the override values (they're merged into the top-level config)
   # be sure to provide the role if master, role is assumed to be a slave if not provided
   connections:
     - role: master
       host: master.sql.host
     - role: slave
       host: slave1.sql.host
     - role: slave
       host: slave2.sql.host