Обсуждение: pg_restore error with out of memory
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.
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
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:You have 1024 MB total RAM.
> 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 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
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