Thread: PIT Recovery of only certain database in the "cluster" ?

Hi,

We are contemplating some migrations to PostgreSQL, but I am having a hard time figuring out how to use the Continuous Archiving feature on the instance for our databases.

We would like to have, say a database for each application , in our cluster/instance.
And use continuous archiving to be able to maximize our chance of not loosing any data... And do Point In Tiem recovery of any of the databases.

So : one instance/cluster with many databases, and the ability to restore any of them to any point in time, just like "some other RDBMS" ;)



However - when reading about the WAL, it seems they are covering *all* databases in our cluster/instance ?

And then it is only possible to do PIT recovery/restore on all databases - so all applications will be nuked back to whatever time we restore to ?


Any suggestions on how to accommodate the wish that each database should be able to do PIT recovery ?

Do we need to create only one single database in it's own instance/cluster and then do WAL backup/continous archiving on that - or ?
Or should we use pg_dump every 15 minutes and say they will loose max 15minutes data if a single database must be restored... ?


Or have I completely mixed up Oracle/ PostgreSQL terminology :) ?


Regards,
Brian

Dem Fra Hedehusene i Danmark <post_overalt@hotmail.com> writes:
> We would like to have, say a database for each application , in our cluster/instance.
> And use continuous archiving to be able to maximize our chance of not loosing any data... And do Point In Tiem
recoveryof any of the databases. 

Doesn't work that way: PITR operates across a whole cluster.

            regards, tom lane

Thanks Tom,

Pardon me this one additional Q since I'm used to SQLsrv/Oracle where this single db point in time restore would be quite easily done :
Is the thought behind this, that databases in a cluster will always be "dependent" on each other and therefore likely to require a recovery of all cluster databases to the same point in time, if requested by one of the "application database owners"  ?

So, one is forced to create "single database cluster" if we want to offer single database recovery, isolated from other running databases ?


/Brian


> CC: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] PIT Recovery of only certain database in the "cluster" ?
> Date: Fri, 11 Mar 2011 09:29:15 -0500
> From: tgl@sss.pgh.pa.us
>
>
> > We would like to have, say a database for each application , in our cluster/instance.
> > And use continuous archiving to be able to maximize our chance of not loosing any data... And do Point In Tiem recovery of any of the databases.
>
> Doesn't work that way: PITR operates across a whole cluster.
>
> regards, tom lane
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice