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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
