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.