Thread: pg_restore error with out of memory

pg_restore error with out of memory

From
AI Rumman
Date:
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.

Re: pg_restore error with out of memory

From
"Kevin Grittner"
Date:
AI Rumman wrote:

I am going to restore a 6 Gb database in my development machine
which is running on Centos 5.6 with memory 1 GB.

> pg_restore: out of memory
> pg_restore: finished item 8570 TABLE DATA entity
> pg_restore: [archiver] worker process failed: exit code 1

> I set postgresql.conf as -
> shared_memory = 128 MB
> maintenance_work_mem = 300 MB

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

You have 1024 MB total RAM.
You seem to be using 114 MB of that before starting PostgreSQL.
You have PostgreSQL configured to use 128 MB of shared buffers,
which is only part of its shared memory.
You have configured 300 MB per maintenance_work_mem allocation.
There can be several of these at one time.
You are running pg_restore, which needs to use memory to interpret
the map of the dump and dependencies among objects.

You are using more memory than you have.

If you really need to run PostgreSQL on a machine with 1GB of
memory, you need to use a configuration much closer to the default.

Don't expect performance to be the same as on a larger server.

-Kevin


Re: pg_restore error with out of memory

From
AI Rumman
Date:
I modified the 
shared_buffer=50 MB
and
maintenance_work_mem = 50 MB

But still getting the same error.

On Thu, Dec 13, 2012 at 7:36 PM, Kevin Grittner <kgrittn@mail.com> wrote:
AI Rumman wrote:

I am going to restore a 6 Gb database in my development machine
which is running on Centos 5.6 with memory 1 GB.

> pg_restore: out of memory
> pg_restore: finished item 8570 TABLE DATA entity
> pg_restore: [archiver] worker process failed: exit code 1

> I set postgresql.conf as -
> shared_memory = 128 MB
> maintenance_work_mem = 300 MB

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

You have 1024 MB total RAM.
You seem to be using 114 MB of that before starting PostgreSQL.
You have PostgreSQL configured to use 128 MB of shared buffers,
which is only part of its shared memory.
You have configured 300 MB per maintenance_work_mem allocation.
There can be several of these at one time.
You are running pg_restore, which needs to use memory to interpret
the map of the dump and dependencies among objects.

You are using more memory than you have.

If you really need to run PostgreSQL on a machine with 1GB of
memory, you need to use a configuration much closer to the default.

Don't expect performance to be the same as on a larger server.

-Kevin

Re: pg_restore error with out of memory

From
"Kevin Grittner"
Date:
AI Rumman wrote:

> I modified the
> shared_buffer=50 MB
> and
> maintenance_work_mem = 50 MB
>
> But still getting the same error.

I hope you restarted the PostgreSQL server? If so, pg_restore might
just need more RAM than that machine has to deal with that
particular database. You might try a text dump, loaded by psql
instead.

-Kevin