Re: Problem loading pg_dump file - Mailing list pgsql-general

I've done a bit more digging into this, here's what I've found --

The text db dump file is much too big to edit by hand (~37GB), so I ran the import in single-step mode:

psql -U bdu -s bdu_01_21_07 < bduprod_2-01-21-07

Here's the first error I run across:

***(Single step mode: verify command)*******************************************
COPY blocked_info (id, created_at, reason_code, note, do_count_links) FROM stdin;
***(press return to proceed or enter x and return to cancel)********************
ERROR:  invalid input syntax for integer: "2006-10-09 22:55:58"
CONTEXT:  COPY blocked_info, line 1, column id: "2006-10-09 22:55:58"

The part of the script that is responsible for this error is:

COPY blocked_info (id, created_at, reason_code, note, do_count_links) FROM stdin;
1       2006-10-09 22:55:58     0       \N      \N
2       2006-10-09 22:55:58     0       \N      \N
3       2006-10-09 22:55:58     0       \N      \N
4       2006-10-09 22:55:58     0       \N      \N
5       2006-10-09 22:55:58     0       \N      \N
6       2006-10-10 13:03:27     \N      \N      \N
7       2006-10-10 13:06:28     \N      \N      \N
8       2006-10-10 13:09:37     \N      \N      \N
9       2006-10-27 22:39:49     \N      \N      \N
10      2006-10-27 22:39:58     \N      \N      \N
11      2006-11-09 04:48:18     \N      \N      \N
12      2006-11-30 16:03:58     \N      \N      f
13      2006-12-02 15:11:42     \N      \N      f
14      2006-12-04 12:31:20     \N      \N      f
15      2006-12-05 00:11:30     \N      \N      f
16      2006-12-05 00:15:45     \N      \N      f
17      2006-12-05 03:02:29     \N      \N      f
18      2006-12-05 12:03:10     \N      \N      f
20      2006-12-05 16:20:15     \N      \N      f
19      2006-12-05 16:20:15     \N      \N      f
21      2006-12-16 16:13:24     \N      \N      f
22      2006-12-19 16:06:43     \N      \N      f
23      2006-12-20 01:33:51     \N      \N      f
24      2006-12-21 18:38:56     \N      \N      f
25      2006-12-22 15:06:15     \N      \N      f
26      2006-12-23 09:43:17     \N      \N      f
27      2007-01-04 12:37:50     \N      \N      f
28      2007-01-08 17:33:26     \N      \N      f
29      2007-01-08 17:38:47     \N      \N      f
30      2007-01-13 15:32:34     \N      \N      f
\.

Now, I'm not too familiar with the copy command, but the above looks correct to me.

When I let subsequent statements run, I get similar errors such as:

ERROR:  invalid input syntax for integer: "0.261191951289869"
ERROR:  invalid input syntax for type real: "2006-08-23 22:54: 11.24"
ERROR:  invalid input syntax for integer: "0.99655325708605502"
ERROR:  invalid input syntax for type boolean: "1401353"

Any ideas what is causing this, or how to track this issue down?
------------------

In addition to the above, I did a new pg_dump using the -Fc option to generate an archive in binary/compressed format.

I also verified that the versions of pg_dump and pg_restore on both machines where the same ( 8.1.5).

I then copy that file over to our dev server an load it doing a pg_restore -v (verbose mode). This time, I get an error when loading data into a different table in the database (not the blocked_info table mentioned above).

The error message is:
pg_restore: restoring data for table "rawfeed"
pg_restore: [custom archiver] could not read data block -- expected 4096, got 3448
pg_restore: *** aborted because of error

I've tried running both with and without the -e (stop on errors) option, and either way the pg_restore stops when it hits this error.

When I list the database contents using pg_restore -l  -- the table where error occurs is table listed as #14 out of 23. This particular table includes a bytea column that contains gzipped data.

If I try to selectively restore any of the individual tables 1-13 (as listed by pg_restore -l), using the -t option, I do not encounter any errors. But if I try to restore any individual tables 14-23, I get the same error as above (but with a different table name).

In addition, if I try to generate a sql script from pg_restore using the -f option, for any of the individual tables 14-23, I get the same error: "could not read data block -- expected 4096, got 3448".

> pg_restore -t rawfeed -f rawfeed.sql bduprod_2-01-25-07
pg_restore: [custom archiver] could not read data block -- expected 4096, got 3448

In the case of table 14 (rawfeed), an output file is generated, it is mid-way through a copy command. I can run that partially generated script against the db without error. I can also verify that the last record in the script is successfully added to the db.

Note: this generated file to restore this one table is huge: 9.2G by itself. Is there an upper limit to the amount of data copy can load at one time?

If I generate a sql script for any of tables 15-23, it takes a long time for the command to finally return the same error (about the same amount of time to run the 'pg_restore -t rawfeed ...' variation above), and when it does an output file is generated, but the file only contains the schema creation commands and the first line of the copy command. It does not contain any of the table data. This is true even for a table that contains only 1 row of data.

----

At this point I'm not sure how to proceed.

My suspicion is still that this has something to do with the encoding of data in the database. So I'm trying to narrow down the location of the problem so that I can try to clean it up.

From trying to load the data from the pg_dump text format export, it looks like something is wrong with the copy command when loading the blocked_info table. However when trying to load the pg_dump -Fc binary format export, it appears there's some problem with the rawfeed table.

Any ideas on what to try next will be greatly appreciated.

thanks in advance,
Mason