5

I have several PostgreSQL DBs in different geographical locations (local sites).

  • Each local site DB have the same schema, but unique data. For example, Take a table with columns: Site_ID, Department_ID, Department_Name. Site_ID is unique for each site.

  • I want to collect all the data from the local site DBs into a centralised DB (PostgreSQL again) which acts as a data warehouse.

  • The corresponding example table on the centralised DB will have the same columns as above. All local site data will go into this table. Each site data designated by the Site_ID, of course.

Question: How to achieve this with PostgreSQL replication methods (streaming/multi-master UDR/BDR/etc.) I see this can be done with SQLServer using Transactional Replication. What is the best way to achieve this functionality with PostgreSQL?

Restriction: The local sites can make only outgoing network connections (i.e. no inbound connections due to firewall restrictions)

  • So, to be clear, you've defined multiple databases, each with a single defined schema? And you want to duplicate data into another database, itself with a single schema? If so, it's not clear what advantage you have by using separated databases. If you merge your data into a single database with multiple schemas, you can manipulate your data (including creating views or materialized views) with standard commands; but using separate databases limits this, as you can only be connected to a single database. – Chris Harrington Jan 18 '16 at 1:28
  • 1
    @ChrisHarrington The multiple databases should not have access to each others data (they can belong to different clients), but the report database is allowed to do so. Having the same schema enables me to adopt the same codebase. For this requirement, the SQLServer solution, 'transactional replication' works (dba.stackexchange.com/questions/43931/…). But how to achieve same with postgre. – 911 Jan 18 '16 at 2:11
  • Do you need to do near-real-time replication, or would periodic (once a day, every hour...) replication be enough? Is the central database indeed read-only (to clients)? – jcaron Jan 20 '16 at 10:08
  • Also, how much data are we talking about (total as well as changes)? – jcaron Jan 20 '16 at 10:11
  • @jcaron there's both high volume data (can be pushed at regular intervals e.g. 15min) as well as low volume frequent (realtime) data. – 911 Jan 27 '16 at 1:16
3
+50

I see at least two options:

  • if you don't need near-real-time replication, but just once a day (or maybe every hour) is enough for you, and the amount of data is small, you can quite simply create a script that will run in each remote location:

    1. connect to the local database
    2. do a dump of the local data (pg_dump --data-only should be fine)
    3. connect to the remote database
    4. remove all data for that location from it
    5. push the data from the dump.
  • if you need near-real-time replication, or the volume of data is large (but the changes are small in comparison), you could look into Slony. Haven't used it in a while, and it's not necessarily its typical use scenario, but I believe it should work as the datasets are distinct.

    Slony uses triggers on the tables to store changes in a log, and then daemons that transfer the logs from one server to the other, and apply the changes. It would be worth checking if this would work in the "can't connect from central to remote" scenario, though. If it doesn't, you'll need to consider some form of VPN (which you should anyway).

0

From looking at SQLServer Transactional Replication, it sounds like Londiste from Skytools3 would work well for this. As I understand it, one of the uses that Skype had for Londiste was to push data from their PL/Proxy OLTP setup into their data warehouse. This was set up to push data from multiple databases that have tables with the same table structure to a central database with the same shared table structure.

There is a simple tutorial titled: How To Set Up "Merge" Replication of the Same Table from Multiple Partitions that you could reference to try and set this up.

And here's further information from a PGCon presentation: Londiste Replication system for PostgreSQL

  • Does Londiste works when the central database cannot connect to the local databases (due to firewall restrictions) ? (only local databases can connect to the central database) – 911 Jan 27 '16 at 1:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.