PostgreSQL Master-Slave Replication on Heroku with Ruby on Rails
PostgreSQL master-slave replication on Heroku with Ruby on Rails
In that article I would like to explain what a master-slave replication for PostgreSQL (postgres) database means, how it could be useful for your Ruby on Rails application and finally how to start using it in your app if you host it on Heroku.Master-slave replicationReplication is copying database information to a second system in order to create high availability and redundancy.Second system doesn’t mean that there are only two systems (for instance database servers). There can be many of them.To simplify things let’s consider only two postgres servers. First will be a master server and second — slave. That configuration means that we gonna perform queries that writes something (INSERT, UPDATE, DELETE) on a master instance. Further, these changes will be propagated on a slave server. So after, rather short time, slave server will have same data as master. We can decide if we want to run read queries (SELECT) only on a slave or both master and slave (using some algorithm to decide which to choose, like Round robin). It depends on your configuration and load on each database.There can be many situations when that solution can be useful for your application. For example, you have a web app that has some front end for your clients but also, you need to perform a lot of back end tasks in background to process data. It may happen that massive number of operations that come from these background tasks can block your front end by making all web queries taking a lot of time to finish.In that meaning, you can use that feature as a load balancer. That obviously requires some research to find out where your bottlenecks are.Octopus gemThere is a gem that can help us with dealing with master-slave replication in our Ruby on Rails application. It’s called Octopus (https://github.com/thiagopradi/octopus). That gem have a few more features that we’re not gonna cover in that article.Let’s start with adding it to the Gemfile:gem 'ar-octopus'Next, let’s create a config file for it at config/shards.yml :octopus:  replicated: true  fully_replicated: true  production:    slave:      adapter: postgresql      host: localhost      database: slave_dbBecause of replicated: true command we don’t need to specify the master instance, only the slave. A configuration for the master comes from the config/database.ymlfully_replicated: true means that all write queries will be send to master and all read queries to slave. If you skip that, you can decide about that for each ActiveRecord model, or even for a query (User.using(:slave).first).How to configure it on HerokuYou will need two PostgreSQL on databases with minimum Standard 0 pricing plan to be to go with master-slave replication. That will cost you at least $50/month. It’s not for free but that’s a more advanced solution so most probably there is a good business decision behind this if you’re considering this.Beauty of choosing Heroku for this comes from the fact that you don’t need to have any devops knowledge how to set up two postgres servers and configure a replication between them.Let’s say that you have your first Postgres database added as add-on to your Heroku application. Let’s say that its name is CHARCOAL. Now, let’s add a follower of that database:$ heroku addons:create heroku-postgresql:standard-0 --follow HEROKU_POSTGRESQL_CHARCOAL_URLAdding heroku-postgresql:standard-0 to sushi... done, v71 ($50/mo)Attached as HEROKU_POSTGRESQL_WHITEFollower will become available for read-only queries when up-to-dateUse `heroku pg:wait` to track statusNow let’s wait a moment after everything is completed:$ heroku pg:waitWaiting for database HEROKU_POSTGRESQL_WHITE_URL... availableSo, we have a new database added WHITE and that database is a follower of CHARCOAL. And that’s it! From an infrastructure point of view everything is done.Now, when we have all credentials of the slave database let’s adjust config/shards.yml :<%   def slave_db_uri     @cached_slave_db_uri ||=       begin         slave_db_env_key = 'HEROKU_POSTGRESQL_WHITE_URL'         if slave_db_env_key.present? && ENV[slave_db_env_key]           URI.parse(ENV[slave_db_env_key])         end      end  end%>octopus:  replicated: true  fully_replicated: true  production:    slave:      adapter: postgresql      database: <%= (slave_db_uri.path || "").split("/")[1] %>      username: <%= slave_db_uri.user %>      password: "<%= slave_db_uri.password %>"      host: <%= slave_db_uri.host %>      port: <%= slave_db_uri.port %>So we’ve set up slave database and all credentials come from an environment variable that is automatically added when you add new database (HEROKU_POSTGRESQL_WHITE_URL in our case). We are parsing that variable and splitting into values that we need to configure a connection with a slave database.SummaryIn that article, I’ve briefly explain what master-slave replication is and when it can be useful.We’ve seen how to configure Octopus gem that we gonna use as a library for our Rails application and how to set up everything on Heroku.As a result:read query will be send to a slave database (WHITE): User.where(name: 'John')write query will be send to a master database (CHARCOAL): User.create(name: 'Molly')copying data between databases has been set up by Heroku and you don’t have to worry about that :)