Thread: pg_dump ordering problem

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

Sue Fitt <sue@inf.ed.ac.uk> writes:
> (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.

I don't think you tried the 8.0 pg_dump.  (7.4 did not have any
dependency ordering logic and so was quite prone to this sort of thing,
but 8.0 ought to get it right.)  If you're certain you did use 8.0,
could we see those parts of the schema?

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

This is one of the techniques used to break circular dependencies.
Also, indexes and foreign key constraints are deliberately added after
loading data, for speed reasons.

            regards, tom lane

Quoting Tom Lane <tgl@sss.pgh.pa.us>:

> Sue Fitt <sue@inf.ed.ac.uk> writes:
> > (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.
>
> I don't think you tried the 8.0 pg_dump.  (7.4 did not have any
> dependency ordering logic and so was quite prone to this sort of thing,
> but 8.0 ought to get it right.)  If you're certain you did use 8.0,
> could we see those parts of the schema?
>
> > 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?
>
> This is one of the techniques used to break circular dependencies.
> Also, indexes and foreign key constraints are deliberately added after
> loading data, for speed reasons.
>
>             regards, tom lane
>


Thanks for that, I've gone back and done another data dump on the original
database with both 7.4.8 and 8.0.4 and you're right, the ordering problem goes
away with the 8.0.4 version of pg_dump. I guess I got confused as I had so many
errors...

However, I've now found a different issue....

I *did* have to edit the 8.0.4 file to reload it as it contained unicode
chars that were not read back in:

         ERROR: Unicode characters greater than or equal to 0x10000 are not
supported
         ERROR: invalid byte sequence for encoding "UNICODE": 0xeb7265
         etc.

(This was the same for the 7.4.8 pg_dump). This error broke some foreign keys as
well, so I took the unicode out of the file temporarily. Now, I've found
comments about unicode not being supported, and I assumed it had been fixed in
my local version of 7.4.8 and not my (new) installation of version 8.  However,
while I was checking the database dumps just now I noticed that pg_dumpall (both
versions) does not cause this problem; I can read the unicode back in again from
this output. The lines in the file which actually contain the unicode look
identical, though admittedly on my screen the char is just an empty box, but
maybe the problem is elsewhere in the file? Any thoughts? Is there an option to
pg_dump that would enable me to read the unicode back correctly? Or another way
of dumping out a single database?

Sue