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.
Best Answer
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.)