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

Question

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:

1. Create REST service which will post data each row from each table to master database.
2. 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.

• Marco: What is the size of database on each machine? Can you afford downtime? Are the schema's on each machine the same?
• Gopal00005: Yes schema is same on all machine and each schema will have 200 records per day and yes we can afford downtime too.
• Marco: Do you want to merge them into 1 schema or do you want all 5 to remain separate? If you want to merge them then do you use sequences to 'generate' primary keys or can you have duplicate keys?
• Gopal00005: Yes I want to merge them all in single DB as I've explained in question. Yes I've used sequences to generate primary keys.

Answer 1

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.

• Gopal00005: I can not follow this methodology because I want to sync each database(Local Database) with master(Amazon RDS) at every 3 hour interval.