pg_dump ordering problem

From: Sue Fitt <sue(at)inf(dot)ed(dot)ac(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: pg_dump ordering problem
Date: 2005-10-25 10:46:16
Message-ID: 435E0CF8.60201@inf.ed.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Williams, Rodger 2005-10-25 12:52:04 First Install
Previous Message malini bu 2005-10-25 06:58:46 Unsubscribe