Friday, December 14, 2012

pg_restore error with invalid input syntax for integer: "U" and out of memory

0 comments
I was trying to restore a 6 Gb database in my development machine which is running on Centos 5.6 with memory 1 GB.
The postgresql.conf was like :

shared_memory = 128 MB
maintenance_work_mem = 300 MB
checkpoint_segment = 10  # as the disk space is limited
fsync=off
autocommit=off

During restoration I got error  as follows:

LOG:  checkpoints are occurring too frequently (22 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".
pg_restore: out of memory
pg_restore: finished item 8570 TABLE DATA entity
pg_restore: [archiver] worker process failed: exit code 1
ERROR:  invalid input syntax for integer: "U"
CONTEXT:  COPY entity, line 2120568, column version: "U"
STATEMENT:  COPY entity (crmid, smcreatorid, smownerid, modifiedby, setype, description, createdtime, modifiedtime, viewedtime, status, version, presence, deleted, owner_type) FROM stdin;

LOG:  could not send data to client: Broken pipe
STATEMENT:  COPY entity (crmid, smcreatorid, smownerid, modifiedby, setype, description, createdtime, modifiedtime, viewedtime, status, version, presence, deleted, owner_type) FROM stdin;

During error my OS status:
free -m
             total       used       free     shared    buffers     cached
Mem:          1024        975         48          0          3        857
-/+ buffers/cache:        114        909
Swap:         1027          0       1027


I posted in forums, discussed with experts and modified the shared_buffers and maintenance_work_mem to lower. But still I was getting the error.

Then I removed this backup copy and made a new one and used it. Now I was able to restore database successfully.

Then I came to knew that this development server had an io error a few days ago during the restoration of the database from this backup and I think that caused the backup copy damaged.

Leave a Reply

Labels