Search Postgresql Archives

pg_restore error with out of memory

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux