Re: Basic Question on Point In Time Recovery

On 3/11/15 6:46 AM, Andrew Sullivan wrote:
>> Is our current "frequent pg_dump" approach a sensible way to go about
>> >things. Or are we missing something? Is there some other way to
>> >restore one database without affecting the others?
> Slony-I, which is a PITA to administer, has a mode where you can ship
> logs off and restore them in pieces. The logs are not WAL, but Slony
> logs (which are produced by triggers and some explicit event writing
> for schema changes). So they work per-database. Schema changes are
> really quite involved for Slony, and there's overhead resulting from
> the triggrs, and as I said it's rather clunky to administer. But it's
> been around some time, it still is actively maintained, and it has
> this functionality. The PITR tools were, last I checked, pretty
> primitive. But the tool might work for your case. I don't know
> whether Bucardo or Londiste (two alternative systems that work on
> roughly the same principle) have this functionality, but I kind of
> doubt it since both were designed to get rid of several of the
> complexities that Slony presented. (Slony had all those complexities
> because it was trying to offer all this functionality at once.)

You could do something very similar with londiste by setting up a second
queue and delaying when you move data to it from the primary queue,
based on event_time.

Or now that I think about it... you might be able to do that right in
the replay process itself.

The big reason I prefer londiste over Slony is that it's extremely
modular, so it's easy to do stuff like this.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com