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:
Press “ENTER” to all of the prompts that follow.
Transfer the keys to the other server by typing:
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:
We will modify the access file with the user we just created:
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:
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:
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.
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:
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.
Here, we need to create a recovery file called 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:
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.
gem 'makara', github: 'taskrabbit/makara', tag: 'v0.3.x'
# 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
production: adapter: 'mysql2_makara' database: 'MyAppProduction' # any other standard AR configurations