CODEWITHSUNDEEP
ruby-on-railspostgresql
newBike
newBike

Reputation: 15002

how to synchronize databases among servers

If I have 3 servers, and run Rails app

Each server has their own database.

I want their database can be synchronized in a consistent.

I use the PostgreSQL.

And my OS environment is Ubuntu server 12.04

Is there any gem can sync databases ?

I just want to mimic if I have servers in Taiwan, Japan, American.

When user add a data into my database in Taiwan,

Then the data will sync to other servers as soon as possible to ensure the consistent.

I think its a big issue, But I have no idea , I need some better tutorial to read.

Upvotes: 0

Views: 191

Answers (2)

Richard Peck
Richard Peck

Reputation: 76774

Whilst I don't know of any gem to clone databases, I do have experience migrating & cloning db's

@Harmic posted a good answer! But there's something else you need to consider: PostgreSQL, MYSQL, SQLite3 etc are all SQL-driven db's. This means you can use SQL to transfer data between them all. This could be as manual or automated process as you want, but the reality is your db's literally just store a set of data, and you can use SQL to migrate it between any system

The way we use PGSQL migrations is the pg_dump & pg_restore commands

This is a favourite of Heroku - you can use pg_dump to save an SQL file (which contains all the SQL queries to build & populate a new PGSQL db):

pg_dump mydb > db.sql

You'll then be able to use pg_restore to port that data into another PGSQL db:

pg_restore -C -d postgres db.sql

Upvotes: 0

harmic
harmic

Reputation: 30587

There are a lot of different possible solutions for this, and choosing the best one will depend on what you require. Before trying to choose one you will need to analyse your application to determine its requirements.

The built in replication functionality in Postgresql allows for a single master, multiple slave configuration. In this configuration, one database is the master, and all updates must be made on the master, but read-only queries are allowed on the slaves.

Tools such as PgPool II can be used to hide the details of this from the application, automatically distributing read-only queries to the slaves while executing statements that would update the database on the master. PgPool can also handle automatic promotion of a slave to master in the event of node failure.

There are also a number of projects providing add-on replication functionality. A summary of these is available on the Postgresql Wiki.

Upvotes: 3

Related Questions