Thread: Hints about how to debug pg_dump problem?
Hi,
I'm trying to move a database from a development machine onto a production
machine. I seem to get good output from my pg_dump command, but when I read
the resulting files, I can't get them to import everything. (FWIW, I'm
moving from a Debian potato/woody combo onto a fresh Debian potato install
with just PostgreSQL updated to the 7.0.2 deb package.)
What I don't understand is the order in which a dump file is processed. I
thought it should be processed pretty much in the order it appears, right?
After all, if you do
$ psql -e my_database < my_dumpfile
you should just get a straight-ahead reading of the dumpfile, no? Watching
the output, however, things are not getting created even though there are no
complaints from psql (about that data).
For instance, if I have data from table1 which appears in the dump file
before data from table2, and I'm getting a parse error on table2, shouldn't
the data from table1 show up (assuming that nothing in it depends on the
data from table2 being there) after the error?
The read eventually dies with a parse error. I'm sure there must be
something wrong in my source database that I'm getting this, but I don't
even know where to begin tracking down the problem. I've dumped and re-read
this database before, but I've added the data that is causing the problem
since then. Obviously, that's what the problem is; I just can't see how the
file is getting read. Any hints as to where to start would be much
appreciated.
A
--
Andrew Sullivan Computer Services
<sullivana@bpl.on.ca> Burlington Public Library
+1 905 639 3611 x158 2331 New Street
Burlington, Ontario, Canada L7R 1J4
Andrew Sullivan wrote: >What I don't understand is the order in which a dump file is processed. I >thought it should be processed pretty much in the order it appears, right? >After all, if you do > > $ psql -e my_database < my_dumpfile > >you should just get a straight-ahead reading of the dumpfile, no? Watching >the output, however, things are not getting created even though there are no >complaints from psql (about that data). > >For instance, if I have data from table1 which appears in the dump file >before data from table2, and I'm getting a parse error on table2, shouldn't >the data from table1 show up (assuming that nothing in it depends on the >data from table2 being there) after the error? If things happen in a transaction, they are thrown away if the transaction aborts. Is this your case? >The read eventually dies with a parse error. I'm sure there must be >something wrong in my source database that I'm getting this, but I don't >even know where to begin tracking down the problem. I've dumped and re-read >this database before, but I've added the data that is causing the problem >since then. Obviously, that's what the problem is; I just can't see how the >file is getting read. Any hints as to where to start would be much >appreciated. What does the parse error say? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "I waited patiently for the LORD; and he inclined unto me, and heard my cry. He brought me up also out of an horrible pit, out of the miry clay, and set my feet upon a rock, and established my goings. And he hath put a new song in my mouth, even praise unto our God. Many shall see it, and fear, and shall trust in the LORD." Psalms 40:1-3
At 16:29 10/07/00 -0400, Andrew Sullivan wrote:
>
>For instance, if I have data from table1 which appears in the dump file
>before data from table2, and I'm getting a parse error on table2, shouldn't
>the data from table1 show up (assuming that nothing in it depends on the
>data from table2 being there) after the error?
>
I would have thought so. I have seen data lost on a reload when the backed
dies. Is this happening? Or is it really just a parse error?
>The read eventually dies with a parse error. I'm sure there must be
>something wrong in my source database that I'm getting this, but I don't
>even know where to begin tracking down the problem. I've dumped and re-read
>this database before, but I've added the data that is causing the problem
>since then. Obviously, that's what the problem is; I just can't see how the
>file is getting read. Any hints as to where to start would be much
>appreciated.
The simplest thing to do would be to copy the dump file and edit it; break
it into bits that you can run independantly, and see where the error occurs.
P.S. I assume you are using the pg_dump that came with 7.0.2 or earlier.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
Philip Warner writes: > >The read eventually dies with a parse error. I'm sure there must be > >something wrong in my source database that I'm getting this, but I don't > >even know where to begin tracking down the problem. I've dumped and re-read > >this database before, but I've added the data that is causing the problem > >since then. Obviously, that's what the problem is; I just can't see how the > >file is getting read. Any hints as to where to start would be much > >appreciated. > > The simplest thing to do would be to copy the dump file and edit it; break > it into bits that you can run independantly, and see where the error occurs. > > P.S. I assume you are using the pg_dump that came with 7.0.2 or earlier. If it's 7.0.2 you can run psql with -f (instead of <) and with `-V ON_ERROR_STOP=on' to get error messages with line numbers and immediate exit if an error occurs. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden