Hi all,
This is my first post to this list so I hope I'm not overlooking
anything obvious.
I've been saving my databases using pg_dump, and loading them back in
where necessary. Recently I upgraded (7.4 to 8.0.4) and so needed to
save my database then reload it in version 8. So, I did
pg_dump mydb > db.out
Then, in 8.0.4
createdb newdatabase
psql -d newdatabase -f db.out
However, mydb contained amongst other things a domain "word" and a
constraint on it, "check_word". During the reload I get the errors
ERROR: function check_word(text) does not exist
HINT: No function matches the given name and argument types. You may need to add explicit type casts.
ERROR: type "word" does not exist
(I tried v7.4 and v8.0.4 pg_dump and got the same thing). Looking at the
output file db.out I can see that the domain and function are used near
the beginning of the file, but defined at the end. Editing this file by
hand to put the definitions at the beginning sorted the problem, and I
could then read the database in.
So, what am I doing wrong? There must be a better, more straightforward
and more robust way of dumping and reloading a database, one that does
not involve hacking the output file.
As an aside, why does pg_dump output altered tables as CREATE TABLE...
ALTER TABLE instead of just updating the CREATE TABLE statement to
assimilate the ALTER TABLE information? Would assimilation of the
information not create cleaner output?
Any help appreciated,
Sue