Postgresql – Confused about postgresql backup/restore and schema changes/restoration

postgresql

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.

Best Answer

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...

Related Topic