Thanks Francisco for these inputs. I hadn't considered log shipping as I knew I didn't want to track changes to all tables (and databases). Setting up a local partial mirror is an interesting thought which hadn't crossed my mind .. I'll giver that some consideration.
Though currently I am thinking to address the problem of generating deltas at the application level rather than to use postgresql features which are largely optimized for a slightly different set of circumstances and requirements. Impressive what can be done witha 2400 baud modem when you set your mind to it. Fortunately this days are mostly behind us :-) On 30 June 2014 13:05, Francisco Olarte <fola...@peoplecall.com> wrote: > Hi Bob. > > On Mon, Jun 30, 2014 at 10:05 AM, Bob Jolliffe <bobjolli...@gmail.com> > wrote: > > What are people's thoughts about a more optimal solution? I would like > to > > use a more incremental approach to replication. This does not have to > be a > > "live" replication .. asynchronously triggering once every 24 hours is > > sufficient. Also there are only a subset of tables which are required > (the > > rest consist of data which is generated). > > > If you only need to replicate once every 24 hours, which means you can > tolerate lags, you could try log shipping. Instead of sending the wal > records from master to standby directly just spool them, compress them > as much as you can ( I would try pglesslog plus an XZ on it's output > ), and send it once a day. This for the 'incremental part'. For the > only a subset of tables, you could try to set up a local partial > mirror using any of the trigger based replication products and then do > log-shipping of that. > > Also, the logical replication slot stuff added to the latest version > seems really promissing for this kind of thing, but I'm not familiar > enough with it to recommend anything. > > Also, depending on your data updating patterns, database sizes and > other stuff, a trigger based replication approach can save a lot of > traffic. I mean, if you have records which are heavily updated, but > only replicate once a day, you can collapse all the day stuff in a > single update. I once did a similar thing to transmit deltas over a > 2400bps modem by making daily sorted dumps and sending daily deltas > with previous day ( it needed a bit of coding, about a couple hundred > lines, but produced ridiculously small deltas, and with a bit of care > their application was idempotent, which simplified the recovery on > errors ). > > Francisco Olarte. >
