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.
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.
