Re: Large Database Restore

Richard Huxton escribió:
> Alvaro Herrera wrote:
> >Richard Huxton escribió:
> >>Alvaro Herrera wrote:
> >>>Lee Keel escribió:
> >>>>So then the best way to do this kind of backup\restore is to use
> >>>>pg_dump?
> >>>>Is there any plan in the future to be able to do some sort of file-level
> >>>>backup like SqlServer?
> >>>Actually you can do single databases, but you must also include some
> >>>other directories besides the database directory. You would need to
> >>>include everything, excluding things in the "base" directory, but not
> >>>exclude your databases directory in "base".
> >>Will that not cause trouble if you've added users though?
> >
> >Huh, maybe it will, but then I don't see how. Can you be more specific?
>
> Well, user info is stored in "global" (as is the list of databases, and
> it looks like language handler definitions too). Run strings over the
> files and you'll see.

Oh, the fear is that you would overwrite the new user files with the
data from the backup. Yeah, that's correct, you would. Also on restore
you would overwrite the pg_xlog and pg_clog areas and the control file,
which while good for your newly restored database, would render the
other databases corrupted.

Absolutely true.

The only way I see to make this work would be to freeze the involved
database (with vacuum freeze), then stop the postmaster cleanly, then
make the tarball of just the DB directory. But then, if shared catalog
state changed between the backup and the restore (say, because you
create a table, which gets registered in the shared catalog
pg_shdepend), said changes would not be rolled back either leading to
possible problems later on.

I hadn't noticed this was so difficult to do! (You can solve the
pg_shdepend problem by copying that database's entries elsewhere and
then restoring them along the tarball, but I'm not sure it's workable
for the other shared catalogs).

A lot more involved that you probably want anyway.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support