Re: Basic Question on Point In Time Recovery

On Wed, Mar 11, 2015 at 10:54:56AM +0000, Robert Inder wrote:
> But, at least while the system is under rapid development, we also
> want to have a way to roll a particular client's database back to a
> (recent) "known good" state, but without affecting any other client.
>
> My understanding is that the WAL files mechanism is installation-wide
> -- it will affect all clients alike.

Right. It's the WAL, so everything in the relevant Postgres system is
involved.

> So before we go down that route, I'd like to check that we're not
> doing something dopey.

No, frequent pg_dumps are indeed hard on I/O.

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

Best regards,

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca