From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | Michael Glaesemann <grzm(at)myrealbox(dot)com>, pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Restoring an existing database |
Date: | 2003-06-26 06:12:08 |
Message-ID: | 200306252312.08209.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Heiko.Kehlenbrink | 2003-06-26 09:47:47 | plpgsql rowtype |
Previous Message | Michael Glaesemann | 2003-06-26 04:32:36 | Restoring an existing database |