I have a local postgres database (9.2) running on Ubuntu that is constantly being written to by an application capturing sub-second data. (99.9% of the transactions are inserts). I also have a postgres database with the same data structure sitting in the cloud. I need a way to get some of the data (not all of the data) up the cloud in near real-time. (Oh, I might have several local postgres databases pushing to the same cloud database.) I would of course need to ensure all the data I want gets delivered if the connection went down. Ideally I would just like to specify what tables/views to push. Anyone have any ideas? I would prefer not to have to write my own solution but I am not taking any option off the table.
I have looked at postgres streaming replication, however, from what I have read it's a full database replication not just a subset of the data.
Thanks in advance for the ideas.
Best Answer
Londiste, Bucardo or Slony-I can do this.
See Replication, Clustering and Connection Pooling on the PostgreSQL wiki for details.
Londiste would probably be my first choice for this use case, mainly for simplicity.