Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Williams, RodgerDate: 2005-10-25 12:52:04
Subject: First Install
Previous:From: malini buDate: 2005-10-25 06:58:46
Subject: Unsubscribe

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group