How to send SQL queries to two databases simultaneously in Rails?

Question

I have a very high-traffic Rails app. We use an older version of PostgreSQL as the backend database which we need to upgrade. We cannot use either the data-directory copy method because the formats of data files have changed too much between our existing releases and the current PostgreSQL release (10.x at the time of writing). We also cannot use the dump-restore processes for migration because we would either incur downtime of several hours or lose important customer data. Replication would not be possible as the two DB versions are incompatible for that.

The strategy so far is to have two databases and copy all the data (and functions) from existing to a new installation. However, while the copy is happening, we need data arriving at the backend to reach both servers so that once the data migration is complete, the switch becomes a matter of redeploying the code.

I have figured out the other parts of the puzzle but am unable to determine how to send all writes happening on the Rails app to both DB servers.

I am not bothered if both installations get queried for displaying data to the user (I can discard the data coming out of the new installation); so, if it is possible on driver level, or adding a line somewhere in the ActiveRecord, I am fine with it.

PS: Rails version is 4.1 and the company is not planning to upgrade that.

sameera207 · Accepted Answer · 2018-03-23 05:19:14Z

1

you can have multiple database by adding an env for the database.yml file. After that you can have a seperate class Like ActiveRecordBase and connect that to the new env.

have a look at this post

However, as I can see, that will not solve your problem. Redirecting new data to the new DB while copying from the old one can lead to data inconsistencies.

For and example, ID of a record can be changed due to two data source feeds.

If you are upgrading the DB, I would recommend define a schedule downtime and let your users know in advance. I would say, having a small downtime is far better than fixing inconstant data down the line.

When you have a downtime,

Let the customers know well in advance
Keep the downtime minimal
Have a backup procedure, in an even the new site takes longer than you think, rollback to the old site.

answered Mar 23, 2018 at 5:19

sameera207

16.6k20 gold badges92 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Manoj Monga Over a year ago

To add to what you mentioned, You can also create a replica of current setup into a different production like environment and start your migration work there. Once you have it tested on a non-prod server, it will give you more confidence about what you're doing. And will also help you with practical issue which are unforeseen while thinking about the approach.

Vaibhav Kaushal Over a year ago

@ManojMonga I have tested the replica. The only key missing right now is having Rails send its SQL commands to two servers at once.

Vaibhav Kaushal Over a year ago

@sameera207 We already use multiple databases with the app, of which only one needs migration. We can discard the other one and recreate. Also, like I mentioned, I have figured out the other parts of this problem. The only thing I need now is Rails app querying two DBs for all ActiveRecord queries.

Collectives™ on Stack Overflow

How to send SQL queries to two databases simultaneously in Rails?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related