I'm a developer who mostly gets away with knowing next-to-nothing about database administration. Just now I've been trying to double-check a database update script (something I can, mostly, manage in general), and because it does a few things I wanted to do a backup so that if it's broken I can restore, fix the script, and try again, without having to worry about manually un-doing partial schema changes.

Well the thing that confuses me is that after my update script did indeed fail after getting through a couple of ALTER TABLE steps, when I do the restore I get various errors (from unrelated tables) about duplicate keys, null constraint violations, and so on. Also, the schema changes my script made are apparently not un-done by the restore.

So it seems to me that I must be misunderstanding some basic aspect of how the dump/restore process worked. All I did was create a backup file of the database with "pg_dump", which I then fed back to "psql". It mostly worked, except for a few errors and, as I said, the failure to restore the schema itself to its former state. Does it just not attempt to do that at all? Is there a process to follow that would do what I was expecting (that is, complete restoration to exact former state)?

I'm the only person using this database; it's a local server on my workstation, so it's not like other developers are messing with it while I'm doing my thing.

This is postgresql 8.4 on an Ubuntu Linux (natty) machine, if it matters.


The gist of what you missed is that unless your pg_dump command included the -c option (clean - Drop objects before creating them) you need to drop all your tables before you restore.
pg_dump produces SQL to re-create tables and load the data back in to them, but if the table already exists the CREATE TABLE command will fail (which is why your schema didn't revert back to what you expected), and if there's already data in the tables you'll wind up with duplicate rows, or lots of errors about duplicate keys (depending on if keys are defined or not).

You should take some time to sit down, read and understand the Postgres documentation on backup/restore and the pg_dump man page. In fact I would suggest reading the Postgres documentation for any areas where you don't feel 100% confident, and/or asking on the mailing lists. The Postgres documentation is of remarkable quality, and I hold it up alongside the FreeBSD Handbook as an example of the kind of documentation all open source projects should strive to produce...

  • Well yes that would be a fairly important thing for me to have missed :-) Thanks, this is helpful. And I do read the documentation - if you look at the 8.4 page about the SQL Dump method, it does not mention that option anywhere. (I'm sure it's in the man page of course, but I didn't think to look there this morning, mostly because - as you say - the docs are so good I normally expect them to be comprehensive :-) – Pointy Jun 13 '11 at 15:34
  • The backup/restore docs are a little lacking - mostly because backup/restore is a fairly complex issue and it's hard to be comprehensive (for example in my environment we don't use pg_dump - We shut down a slave server and copy the contents of the $PGDATA directory) – voretaq7 Jun 13 '11 at 15:51

An alternative to the pg_dump -c approach is to use pg_restore --clean.

We use pg_dump -Fc which precludes using -c.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.