Thread: Postgressql backup/restore question

Can we do a point-in-time restore of a single database out of n databases??.
------------------------------------------------------------------------------------------------------------------------------------------
I have 5 databases in the postgresql server.

I have taken full-backup of the entire data directory (/opt/postgresql/data) and individual dumps also (for safety).
then i have taken all the log files till now.
Now, the server is crashed.

Is there anyway to restore only 1 database out of total 5 database to particular point-in-time. (similar to Mysql)

I have individual dumps of each database and all corresponding log files from that time.
Can i selectively restore the databases.

Can I use dump files + log files to restore the databases.
---------------------------------------------------------------------------------------------------------------------------------------------

Please help me in this regards

samana srikanth wrote:
>     Can we do a point-in-time restore of a single database out of n
>     databases??.

In principle no.  But you could invent workarounds such as recovering to
the point where you are happy with your restored one database, and then
restore the other n-1 databases from an SQL dump.

On Wed, 2009-03-04 at 15:59 +0200, Peter Eisentraut wrote:
> samana srikanth wrote:
> >     Can we do a point-in-time restore of a single database out of n
> >     databases??.
>
> In principle no.  But you could invent workarounds such as recovering to
> the point where you are happy with your restored one database, and then
> restore the other n-1 databases from an SQL dump.

It is possible, but we just don't currently support it.

My submission on rmgr plugins would have provided this feature though it
was rejected as "not wanted".

I have code hooks required to do this, if people want to contact me
off-list.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support

Simon Riggs <simon@2ndQuadrant.com> writes:
> On Wed, 2009-03-04 at 15:59 +0200, Peter Eisentraut wrote:
>> samana srikanth wrote:
>>> Can we do a point-in-time restore of a single database out of n
>>> databases??.
>>
>> In principle no.  But you could invent workarounds such as recovering to
>> the point where you are happy with your restored one database, and then
>> restore the other n-1 databases from an SQL dump.

> It is possible, but we just don't currently support it.

It's not as easy as all that.  What will you do with updates to shared
catalogs?

            regards, tom lane

On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
> > On Wed, 2009-03-04 at 15:59 +0200, Peter Eisentraut wrote:
> >> samana srikanth wrote:
> >>> Can we do a point-in-time restore of a single database out of n
> >>> databases??.
> >>
> >> In principle no.  But you could invent workarounds such as recovering to
> >> the point where you are happy with your restored one database, and then
> >> restore the other n-1 databases from an SQL dump.
>
> > It is possible, but we just don't currently support it.
>
> It's not as easy as all that.  What will you do with updates to shared
> catalogs?

Apply them.

So: its possible to do shared catalogs plus a subset of other databases.
I was assuming that updates to shared catalogs were small overall.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support

Simon Riggs <simon@2ndQuadrant.com> writes:
> On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote:
>> It's not as easy as all that.  What will you do with updates to shared
>> catalogs?

> Apply them.

... which leaves your other databases in inconsistent states.

            regards, tom lane

On Wed, 2009-03-04 at 16:27 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
> > On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote:
> >> It's not as easy as all that.  What will you do with updates to shared
> >> catalogs?
>
> > Apply them.
>
> ... which leaves your other databases in inconsistent states.

Which is not a problem if you didn't want to restore them in the first
place. You might complain that we would need safeguards to protect
people from trying to access non-restored databases and then failing to
understand why they aren't there.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support

Simon Riggs <simon@2ndQuadrant.com> writes:
> On Wed, 2009-03-04 at 16:27 -0500, Tom Lane wrote:
>> Simon Riggs <simon@2ndQuadrant.com> writes:
> On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote:
>>>> It's not as easy as all that.  What will you do with updates to shared
>>>> catalogs?
>>
>>> Apply them.
>>
>> ... which leaves your other databases in inconsistent states.

> Which is not a problem if you didn't want to restore them in the first
> place.

Only for small values of "not a problem".  For example, you might have
pg_shdepend entries saying that various objects in some other database
depend on some role.  If you then want to drop the role, you can't;
and you can't attach to the other database to get rid of the objects,
since it's not there.  You'd also still have pg_database entries
pointing at the not-there databases.

This behavior might be all right for an emergency recovery kind of tool,
but I can't see us considering it a supported feature.

The larger point though is that I suspect what the OP really is looking
for is "restore just this one database into my existing cluster, without
breaking the other databases that are already in it".  There is zero
chance of ever doing that with a WAL-based backup --- transaction ID
inconsistencies would break it, even without considering the contents
of shared catalogs.

            regards, tom lane

On Wed, 2009-03-04 at 17:19 -0500, Tom Lane wrote:

> This behavior might be all right for an emergency recovery kind of tool,
> but I can't see us considering it a supported feature.

I agree post-recovery cleanup would be required to bring up a fully safe
read-write database. That's one of the reasons my longer term thoughts
are towards running transactions immediately after recovery completes,
for other uses also.

> The larger point though is that I suspect what the OP really is looking
> for is "restore just this one database into my existing cluster, without
> breaking the other databases that are already in it".  There is zero
> chance of ever doing that with a WAL-based backup --- transaction ID
> inconsistencies would break it, even without considering the contents
> of shared catalogs.

Agreed.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support