pg_restore error with out of memory

From: AI Rumman <rummandba(at)gmail(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: pg_restore error with out of memory
Date: 2012-12-13 19:32:15
Message-ID: CAGoODpe8dJ-qsQAis42jW8vt3ANRcAbi3rNqH5oo4Bj2hF6x9Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)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.

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2012-12-13 19:32:30 Re: initdb error
Previous Message Adrian Klaver 2012-12-13 19:16:32 Re: initdb error