Обсуждение: Restoring an existing database

I'd like to back up my database. I've read through the pg_dump and
pg_restore sections in the Reference, and Chapter 9 of the
Administrator's Guide of the online docs that come with 7.3.2. I still
have a few questions that I still have. I guess I'd just like
reassurance that I'm not going to screw up and lose my data!:)

Question 1 (Encoding). My database is UTF-8 encoded. I'm assuming the
archive made by pg_dump will include this encoding and that pg_restore
will have no problem reading it back in. Is this assumption valid? I've
got a lot of Japanese in my database and would hate to lose it.

Question 2 (OIDs). The database includes lots of foreign keys, though
they all reference my own ID numbers, not OIDs. Chapter 9.1 (SQL Dump)
says

<quote>
Important:  When your database schema relies on OIDs (for instance as
foreign keys) you must instruct pg_dump to dump the OIDs as well. To do
this, use the -o command line option. "Large objects" are not dumped by
default, either. See pg_dump's command reference page if you use large
objects.
</quote>

Since my foreign keys don't rely on the OIDs, I don't need to include
OIDs with the dump, correct? (Am I reading that correctly?)

Question 3 (Restoring an existing database). To make everything clean
and pretty, suppose I should use the -c option with pg_restore to drop
the tables before they're recreated. What happens if I don't drop the
tables? I'm guessing it will pretty much trash the database. The reason
I ask is that it would be nice to know that it's just replacing the
data (deleting a row, inserting a row), rather than having to drop the
tables wholesale.

But then again, I guess it has to delete the old, insert the new
anyway. And it's probably a lot faster dropping a whole table than
going through line by line.

Question 4 (Privileges). I undestand that the user who backs up the
data should have read privileges to everything that is being dumped,
and that ownership of the objects being restored is maintained. I'm
wondering about users who have restricted (i.e., not superuser or
ownership) privileges to the database objects. Will these be restored
as well, or will I have to regrant these privileges? It seems to me
that if the object is dropped and recreated, they're not really the
same object--they just share the same name, so the privileges would
need to be re-established.

This might be another reason dumping with OIDs would be important. If
the privileges are based on OID (as I suspect, though I haven't done
any research on this), then the privileges would automatically be
restored.

Question 5 (Transactions). Is the restore done inside one big
transaction block? If it was, then if it failed, the original database
wouldn't be affected.

Thanks a lot for any clarification on these points, or suggestions of
where I should look for further information. It will reassure me that I
won't lose what I've worked on so far. It's my first major database,
and I'm probably being a bit overcautious, but then again, the data is
important, isn't it.

Thanks!

Michael Glaesemann
grzm myrealbox com

Micheal,

> Since my foreign keys don't rely on the OIDs, I don't need to include
> OIDs with the dump, correct? (Am I reading that correctly?)

Correct.

> Question 3 (Restoring an existing database). To make everything clean
> and pretty, suppose I should use the -c option with pg_restore to drop
> the tables before they're recreated. What happens if I don't drop the
> tables? I'm guessing it will pretty much trash the database. The reason
> I ask is that it would be nice to know that it's just replacing the
> data (deleting a row, inserting a row), rather than having to drop the
> tables wholesale.

Usually, in my experience, you get a primary key violation and the restore
quits pretty early on.  But you could end up with a substantial number of
duplicate rows where keys didn't prevent it.  And your sequences could be set
wrong.

> But then again, I guess it has to delete the old, insert the new
> anyway. And it's probably a lot faster dropping a whole table than
> going through line by line.

Actually, I usually simply drop and re-create the target *database* before
restoring.

> This might be another reason dumping with OIDs would be important. If
> the privileges are based on OID (as I suspect, though I haven't done
> any research on this), then the privileges would automatically be
> restored.

Priveleges are not related to OID.  The one caveat is that postgres users are
not restored through a regular pg_dump; that is, pg_dump will restore the
priveleges if the system database is intact, but if it's not you need the
contents of a pg_dumpall.

> Question 5 (Transactions). Is the restore done inside one big
> transaction block? If it was, then if it failed, the original database
> wouldn't be affected.

No, it's not.

> won't lose what I've worked on so far. It's my first major database,
> and I'm probably being a bit overcautious, but then again, the data is
> important, isn't it.

Better safe than looking for a new job, I always say.


--
Josh Berkus
Aglio Database Solutions
San Francisco