Обсуждение: PQputline error with pg_restore

Hello,

I am running PostgreSQL 7.3.2 on Mandrake 9.1  I created a database
archive using pg_dump (pg_dump -Ft -v -R oral > dbarchive200106.tar).
When I try to restore this database with pg_restore on a new system
(pg_restore -d oral -R -v dbarchive200106.tar), I get the following error:

pg_restore: [archiver(db)] error returned by PQputline
pg_restore: *** aborted because of error

The size of the uncompressed tar file is 1.34 GB.  Based on other
informtion I've found here, I tried checking the log for the postmaster
(I assumed to be in /var/log), but no such file existed.  The memory
settings on the machine are:

sort_mem = 10000
vacuum_mem = 10000

Are these memory settings still too high?  Is there another
configuration I should check?  I would appreciate any suggestions on how
to fix this problem.

Thanks,
Jennifer

Jennifer Drake <drakeji@vcu.edu> writes:
> I am running PostgreSQL 7.3.2 on Mandrake 9.1

You should really update to something more current than 7.3.2 :-(

> pg_restore: [archiver(db)] error returned by PQputline
> pg_restore: *** aborted because of error

The first thing to do is get more info about the error, but
unfortunately that release of pg_restore isn't going to tell you what
the error message from PQputline is.  So you'll have to try to find
out from the postmaster's log.

> I tried checking the log for the postmaster
> (I assumed to be in /var/log), but no such file existed.

You'll need to look at the startup script Mandrake uses for postgres
to see where it sends the postmaster's stderr, but I wouldn't be
surprised to find that it sends to /dev/null :-(.  You can change the
script to redirect to some real file and then restart the postmaster and
then try the restore again.  Or reconfigure things so that the
postmaster sends its log messages to syslog --- though this may take
some fooling with syslog's configuration as well as with postgresql.conf.
(Syslog is probably a better choice for production purposes --- if you
redirect to a file, that file will continue to grow as long as the
postmaster runs.)

Once you've managed to see the underlying error message, if it doesn't
make things clear then pass the info along and we'll try to help.

BTW, another thing you could try is having pg_restore just generate
a SQL script, and then feed the SQL script to psql.  psql will probably
be more cooperative about showing the underlying message.

            regards, tom lane

<div>The postmaster stderr was being sent to /dev/null, so I changed that and was able to generate a log file. 
Unfortunately,I'm not sure what the output in the log file means - I don't have a lot of experience with this sort of
thingand would greatly appreciate any help.  Here's the info from the log:<br /><br /> postmaster successfully
started<br/> LOG:  database system was shut down at 2006-02-09 15:37:30 EST<br /> LOG:  checkpoint record is at
1/2C118B80<br/> LOG:  redo record is at 1/2C118B80; undo record is at 0/0; shutdown TRUE<br /> LOG:  next transaction
id:6659; next oid: 19331162<br /> LOG:  database system is ready<br /> WARNING:  Attribute "piece" has an unknown
type<br/>     Proceeding with relation creation anyway<br /> WARNING:  Attribute "piece" has an unknown type<br />    
Proceedingwith relation creation anyway<br /> LOG:  recycled transaction log file 000000010000002B<br /> LOG:  recycled
transactionlog file 000000010000002C<br /> LOG:  recycled transaction log file 000000010000002D<br /> LOG:  recycled
transactionlog file 000000010000002E<br /> LOG:  recycled transaction log file 000000010000002F<br /> LOG:  recycled
transactionlog file 0000000100000030<br /> LOG:  recycled transaction log file 0000000100000031<br /> LOG:  recycled
transactionlog file 0000000100000032<br /> LOG:  recycled transaction log file 0000000100000033<br /> LOG:  recycled
transactionlog file 0000000100000034<br /> ERROR:  copy: line 178286, overflow on numeric ABS(value) >= 10^3 for
fieldwith precision 5 scale 3<br /> FATAL:  Socket command type 8 unknown<br /> LOG:  recycled transaction log file
0000000100000036<br/> LOG:  recycled transaction log file 0000000100000037<br /> LOG:  recycled transaction log file
0000000100000035<br/><br /> Thanks,<br /> Jennifer<br /><br /></div><font
color="#990099">-----pgsql-general-owner@postgresql.orgwrote: -----<br /></font><blockquote style="border-left: 2px
solidrgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"><font face="monospace"
size="3"><br/>You should really update to something more current than 7.3.2 :-(<br /><br />The first thing to do is get
moreinfo about the error, but<br />unfortunately that release of pg_restore isn't going to tell you what<br />the error
messagefrom PQputline is.  So you'll have to try to find<br />out from the postmaster's log.<br /><br />You'll need to
lookat the startup script Mandrake uses for postgres<br />to see where it sends the postmaster's stderr, but I wouldn't
be<br/>surprised to find that it sends to /dev/null :-(.  You can change the<br />script to redirect to some real file
andthen restart the postmaster and<br />then try the restore again.  Or reconfigure things so that the<br />postmaster
sendsits log messages to syslog --- though this may take<br />some fooling with syslog's configuration as well as with
postgresql.conf.<br/>(Syslog is probably a better choice for production purposes --- if you<br />redirect to a file,
thatfile will continue to grow as long as the<br />postmaster runs.)<br /><br />Once you've managed to see the
underlyingerror message, if it doesn't<br />make things clear then pass the info along and we'll try to help.<br /><br
/>BTW,another thing you could try is having pg_restore just generate<br />a SQL script, and then feed the SQL script to
psql. psql will probably<br />be more cooperative about showing the underlying message.<br /><br /><span
style="visibility:hidden;">    </span><span style="visibility: hidden;">    </span><span style="visibility:
hidden;">    </span>regards, tom lane<br /><br /></font></blockquote><br /> 

Jennifer I Drake/O/VCU <drakeji@vcu.edu> writes:
> ERROR: copy: line 178286, overflow on numeric ABS(value) >= 10^3 for field with precision 5 scale 3

It would seem that you've got an incorrect (too large) value in a
numeric field in the dumped data.

It's not clear how you got into this state.  If you haven't mucked
with the dump then the value was presumably too large in the source
database, which would imply some bug in Postgres that had let it
escape range checking when it was stored into the table originally.
7.3.2 is so far back that this wouldn't surprise me a whole lot.
If you can reproduce such a problem in a more current version, we'd
like to see the details.

The easiest way to fix things is probably to make pg_restore generate
a SQL script file, edit the script, then load it.  You can either change
the data value if you think it's wrong, or widen the field precision if
you want to keep the data as-is.

            regards, tom lane