I am going to restore a 6 Gb database in my development machine which is running on Centos 5.6 with memory 1 GB.
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
[postgres@rumman data]$ 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;
The table entity has 2164182 rows.
And description as -
\d entity
Table "public.entity"
Column | Type | Modifiers
--------------+-----------------------------+------------------------------
crmid | integer | not null
smcreatorid | integer | not null default 0
smownerid | integer | not null default 0
modifiedby | integer | not null default 0
setype | character varying(30) | not null
description | text |
createdtime | timestamp without time zone | not null
modifiedtime | timestamp without time zone | not null
viewedtime | timestamp without time zone |
status | character varying(50) |
version | integer | not null default 0
presence | integer | default 1
deleted | integer | not null default 0
owner_type | character(1) | not null default 'U'::bpchar
Indexes:
"entity_pkey" PRIMARY KEY, btree (crmid)
"entity_createdtime_idx" btree (createdtime)
"entity_modifiedby_idx" btree (modifiedby)
"entity_modifiedtime_idx" btree (modifiedtime)
"entity_setype_idx" btree (setype) WHERE deleted = 0
"entity_smcreatorid_idx" btree (smcreatorid)
"entity_smownerid_idx" btree (smownerid)
"ftx_enentity_description" gin (to_tsvector('en'::regconfig, for_fts(description)))
"entity_deleted_idx" btree (deleted)
Referenced by:
TABLE "service" CONSTRAINT "fk_1_service" FOREIGN KEY (serviceid) REFERENCES entity(crmid) ON DELETE CASCADE
TABLE "servicecontracts" CONSTRAINT "fk_1_servicecontracts" FOREIGN KEY (servicecontractsid) REFERENCES entity(crmid) ON DELETE CASCADE
TABLE "cc2entity" CONSTRAINT "fk_cc2entityentity" FOREIGN KEY (crm_id) REFERENCES entity(crmid) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "emails_optout_history" CONSTRAINT "fk_emails_optout_historyid" FOREIGN KEY (crmid) REFERENCES entity(crmid) ON DELETE CASCADE
TABLE "emails_optout_history" CONSTRAINT "fk_emails_optout_history_emailid" FOREIGN KEY (emailid) REFERENCES entity(crmid) ON DELETE CASCADE
I set postgresql.conf as -
shared_memory = 128 MB
maintenance_work_mem = 300 MB
checkpoint_segment = 10 # as the disk space is limited
fsync=off
autocommit=off
The backup was takes at Postgresql 9.2.3 and I am going to restore at Postrgesql 9.2.1.
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
Please let me know what could be the actual cause of the error.
Thanks.
