Postgresql – Best way to move different desktop local PostgreSQL DB to single amazon RDS

amazon-rdsjavapostgresql

I have system which is having java Desktop application with PostgreSQL which is installed on 5 machine each is having individual database and having databases into same database structure.

Now I want to move that all slave data to one master database periodically. Which way will be convenient ?

I found two approach:

Create REST service which will post data each row from each table to master database.
Create slave-master replication.

If there is any other technique then please update me.

Please explain how to apply best approach.

EDITED

Used same schema for all the desktop application and used sequences to generate primary keys.

I want to merge them all into single AMAZON RDS postgreSQL database.

Best Answer

Based on the replies you gave to my comments you must figure out how to resolve the duplicate keys issue. Also I do not think that master-slave replication is appropriate in this case.

I would, after making sure that there are no duplicate keys, use pg_dump to transfer your data into AmazonRDS one by one. Perform checks after every database that you do. If you have time and space then first do a test. During this test the 5 machines can remain active.

For master-slave you should check the Replication page of PostgreSQL. The problem remains the duplicate keys as mentioned there as:

Your users take a local copy of the database with them on laptops when they leave the office, make changes while they are away, and need to merge those with the main database when they return. Here you'd want an asynchronous, lazy replication approach, and will be forced to consider how to handle conflicts in cases where the same record has been modified both on the master server and on a local copy.

I do not think however that you can have 5 different 'versions' on the local desktops.

Related Solutions

MySQL: Does Amazon’s RDS Multi-AZ feature use binary logs

Yes it does uses mysql binary logs for Multi-AZ replication.

Here this FAQ at amazon site confirms this:

Q: Can I directly access the binary logs for my Database Instance to manage my own replication?
A. Amazon RDS does not currently provide access to the binary logs for your Database Instance.

http://aws.amazon.com/rds/faqs/#103

And this one too.

You may find in some cases that your Read Replica(s) aren’t able to receive or apply updates from their source Multi-AZ DB Instance after a Mulit-AZ failover. This is because some MySQL binlog events were not flushed to disk at the time of the failover. After the failover, the Read Replica may ask for binlogs from the source that it doesn’t have. This loss of MySQL binlogs during a crash is described in the MySQL document here.

http://aws.amazon.com/rds/faqs/#107

This simply confirms that it does uses binary logs.

Postgresql – How to schedule PostgreSQL replication

You can use pg_basebackup to take a copy of the PostgreSQL database over the replication protocol. It copies the whole DB though, it isn't incremental. You'll want to use the --xlog-method=stream argument unless you have a shared WAL archive set up.

Streaming replication isn't really well suited to the purpose you describe. You can set it to have replication lag, but it's more minutes or hours rather than days, as the amount of WAL you have to retain on the master or in an archive to get days of replication lag is huge. It's also fixed, not schedulable.

You could achieve what you describe with lots of WAL archiving plus a custom-created recovery.conf for each backup, but it'll be fiddly at best.

The thing you need to realise is that by (say) Friday, the master no longer has any record of the state it was in on Monday. It can't update last Friday's snapshot to this Friday because it no longer knows what state it was in on intermediate days like last Monday ... or last Friday. It doesn't know what state the old replica is in, nor how to get it from there to where it is now.

You can tell it to remember that much state by archiving WAL segments, but you need an immense amount of disk space for that and it's very inefficient to catch the backup up from WAL if your DB is busy.

I'd recommend just using pg_basebackup and/or pg_dump on a schedule.

Best Answer

Related Solutions

MySQL: Does Amazon’s RDS Multi-AZ feature use binary logs

Postgresql – How to schedule PostgreSQL replication

Related Question