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@xxxxxxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
