Обсуждение: 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

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

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.

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

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