3

I currently have a few dozen sites pushing data to a central database over VPN with rsyslog. All these records are going into one table (there are roughly 20 - 30 per second being inserted per site). Thus, there are roughly 30 million records in ONE TABLE and growing fast. The database design is a separate issue to be dealt with later.

My goal is to setup a local database at each site with this one table that is gathering the syslogs and replicate it to our central database (hopefully synchronously) to a new table named after the facility (rather that one big table gathering all 17 facility data).

I have looked at Bucardo, Slony, and SymmetricDS but they all seem like redundancy / backup solutions, but what I really need is seemingly basic single table replication from each site to the central database.

My initial impression with Bucardo is that I would need to install it at each one of the sites to accomplish this. I'm looking for a little guidance if anyone can offer some advice. I believe I have Google'd to death, but I'm hoping to be proven wrong.

whobbs
  • 31
  • 2

2 Answers2

1

Each one of the well-known logical replication systems for PostgreSQL is suitable for this: Bucardo, Londiste, Slony. They are not necessarily only redundancy solutions. (Arguably, they are pretty bad redundancy solutions.) Pick the one you like best. Perhaps Londiste is easiest to get started with, but YMMV. (I'm not familiar with SymmetricDS.)

Peter Eisentraut
  • 3,665
  • 1
  • 24
  • 21
  • Thanks @peter-eisentraut. Have you used Bucardo for multiple sites? I am going to give Londiste a try, it seems simple which is what I'm looking for. I don't know why I had skipped over it when looking at the Postgres wiki page. I will update my question with my solution when it's working. – whobbs Nov 14 '13 at 15:19
0

As of Postgres 9.3 you should be able to do this using Foreign Data Wrappers - a brief explanation is available here.

Basically create the appropriate table(s) on your central server, then create the tables(s) as a foreign table (using CREATE FOREIGN TABLE) on the remote sites' Postgres instance. You can then treat the foreign table like any other table, except all your changes get sent back to the "real" server.

Note that this doesn't work if you need asynchronous capabilities (write stuff to the remote sites' tables while the central server is offline and send it over when the central server comes back). For that, do as Peter suggested and use one of the transaction-based replication tools.

Community
  • 1
voretaq7
  • 79,879
  • 17
  • 130
  • 214
  • Thanks @voretaq7. I had looked at foreign data wrappers made available in the latest release however our VPN connections go down for 10-30 seconds (sometimes more) so we need asynchronous capability to guarantee data delivery. – whobbs Nov 14 '13 at 15:23
  • @whobbs Yeah if you're in that sort of situation definitely go with one of the trigger/transactional replication solutions (they can do async & queueing which is not something you want to implement yourself). – voretaq7 Nov 14 '13 at 17:37