Re: Restoring an existing database

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