+2 votes
I have an ERP Web application which uses postgreSQL as a database. ERP application deployed on amazon server with public ip hence this application is accessible over internet globally.

Our company have two locations. Location 1 is main headquarter and Location 2 is production department situated in different region.

Internet bandwidth is very good at location 1 and employee using ERP web application like a charm.

But at location 2 we have internet problems due to which users find difficult to use our ERP web application.

I would like to deploye same application with same postgres schema on different server running locally on location 2.

With this approach employee at location 2 can use ERP system smoothly because it is deployed locally.

At midnight i would like to synchronise data from location 2 postgres server to Location 1 postgres server. With this approach all transaction did by employee at location 2 will be visible to employee at location 1.

Please note there are employee like accountant who doing transaction at same time at location 1 system.

How can we achieve this? What should we read?
in Server by (10.5k points) | 67 views

2 Answers

+4 votes
Best answer
you have loads of different ways of doing this. Here are a few:

External logical replication: projects like Slony, Londiste, Bucardo can replicate the needed objects through triggers;

Extensions like pglogical;

In-core logical replication which is available since Postgres 10

Foreign Data Wrappers: you could use postgres_fdw to make your table readable from a remote server. This means connecting to the other table, not replicating it.

Backup and restore: you could cron a pg_dump and restore of your table from server A to B.

Physical Replication: you could replicate the whole A cluster on cluster B using streaming replication. This could give you a nice disaster recovery solution. On the B server, you could then issue one of the solutions above to a second instance on the server B.

and probably several other ways :-).:
by (5k points)
selected by
+3 votes

If you want to sync a single database or table, then you can use Bucardo. This will help you to replicate a single table, single database, etc.

by (5k points)
205 questions
180 answers
223,023 users