Re: Postgressql backup/restore question

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Wed, 2009-03-04 at 16:27 -0500, Tom Lane wrote:
>> Simon Riggs <simon(at)2ndQuadrant(dot)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