Обсуждение: pg_dump out of memory
Hi All, I moved a physical box to a VM, and set its memory to 1Gig. Everything runs fine except one backup: /pub/backup# pg_dump -Fc -U postgres -f wildfire.backup wildfirep g_dump: Dumping the contents of table "ofrrds" failed: PQgetResult() failed. pg_dump: Error message from server: ERROR: out of memory DETAIL: Failed on request of size 1073741823. pg_dump: The command was: COPY public.ofrrds (id, updateddate, bytes) TO stdout; I've been reducing my memory settings: maintenance_work_mem = 80MB work_mem = 5MB shared_buffers = 200MB But it doesnt seem to make a difference. The tables looks like: wildfire=# \dt+ ofrrds List of relations Schema | Name | Type | Owner | Size | Description --------+--------+-------+-------+-------+------------- public | ofrrds | table | andy | 15 MB | ildfire=# \d ofrrds Table "public.ofrrds" Column | Type | Modifiers -------------+------------------------+----------- id | character varying(100) | not null updateddate | bigint | not null bytes | bytea | Indexes: "ofrrds_pk" PRIMARY KEY, btree (id) wildfire=# select id, length(bytes) from ofrrds; id | length -------------------+----------- muc_rooms | 152330241 packet_count | 76165121 muc_users | 76165121 sessions | 76165121 muc_occupants | 9520641 muc_traffic | server_bytes | 301850625 proxyTransferRate | 76165121 server_sessions | 152330241 conversations | 304660481 (10 rows) I'm not sure how to get this backup to run. Any hints would be appreciated. -Andy
On 07/03/2018 07:43 PM, Andy Colson wrote: > Hi All, > > I moved a physical box to a VM, and set its memory to 1Gig. Everything > runs fine except one backup: > > > /pub/backup# pg_dump -Fc -U postgres -f wildfire.backup wildfirep > > g_dump: Dumping the contents of table "ofrrds" failed: PQgetResult() > failed. > pg_dump: Error message from server: ERROR: out of memory > DETAIL: Failed on request of size 1073741823. > pg_dump: The command was: COPY public.ofrrds (id, updateddate, bytes) TO > stdout; > > I've been reducing my memory settings: > > maintenance_work_mem = 80MB > work_mem = 5MB > shared_buffers = 200MB > > But it doesnt seem to make a difference. > The tables looks like: > > wildfire=# \dt+ ofrrds > List of relations > Schema | Name | Type | Owner | Size | Description > --------+--------+-------+-------+-------+------------- > public | ofrrds | table | andy | 15 MB | > > > ildfire=# \d ofrrds > Table "public.ofrrds" > Column | Type | Modifiers > -------------+------------------------+----------- > id | character varying(100) | not null > updateddate | bigint | not null > bytes | bytea | > Indexes: > "ofrrds_pk" PRIMARY KEY, btree (id) > > > wildfire=# select id, length(bytes) from ofrrds; > id | length > -------------------+----------- > muc_rooms | 152330241 > packet_count | 76165121 > muc_users | 76165121 > sessions | 76165121 > muc_occupants | 9520641 > muc_traffic | > server_bytes | 301850625 > proxyTransferRate | 76165121 > server_sessions | 152330241 > conversations | 304660481 > (10 rows) > > > I'm not sure how to get this backup to run. Any hints would be > appreciated. Maybe: 1) Try: pg_dump -t ofrrds to dump only that table. 2) If that works then: pg_dump -T ofrrds to dump everything but that table. > > -Andy > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 07/03/2018 10:21 PM, Adrian Klaver wrote: > On 07/03/2018 07:43 PM, Andy Colson wrote: >> Hi All, >> >> I moved a physical box to a VM, and set its memory to 1Gig. Everything >> runs fine except one backup: >> >> >> /pub/backup# pg_dump -Fc -U postgres -f wildfire.backup wildfirep >> >> g_dump: Dumping the contents of table "ofrrds" failed: PQgetResult() failed. >> pg_dump: Error message from server: ERROR: out of memory >> DETAIL: Failed on request of size 1073741823. >> pg_dump: The command was: COPY public.ofrrds (id, updateddate, bytes) TO >> stdout; >> >> >> >> I'm not sure how to get this backup to run. Any hints would be appreciated. > > Maybe: > > 1) Try: > pg_dump -t ofrrds > to dump only that table. > It didnt work. I get the same error. Also, I'm running Slackware 14.2, and PG 9.5.11 -Andy
On 07/03/2018 08:28 PM, Andy Colson wrote: > On 07/03/2018 10:21 PM, Adrian Klaver wrote: >> On 07/03/2018 07:43 PM, Andy Colson wrote: >>> Hi All, >>> >>> I moved a physical box to a VM, and set its memory to 1Gig. Everything >>> runs fine except one backup: >>> >>> >>> /pub/backup# pg_dump -Fc -U postgres -f wildfire.backup wildfirep >>> >>> g_dump: Dumping the contents of table "ofrrds" failed: PQgetResult() >>> failed. >>> pg_dump: Error message from server: ERROR: out of memory >>> DETAIL: Failed on request of size 1073741823. >>> pg_dump: The command was: COPY public.ofrrds (id, updateddate, bytes) TO >>> stdout; >>> >>> >>> >>> I'm not sure how to get this backup to run. Any hints would be >>> appreciated. >> >> Maybe: >> >> 1) Try: >> pg_dump -t ofrrds >> to dump only that table. >> > > It didnt work. I get the same error. Well all I can think of is to give the VM more memory. > > Also, I'm running Slackware 14.2, and PG 9.5.11 > > -Andy > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, 3 Jul 2018 21:43:38 -0500, Andy Colson <andy@squeakycode.net> wrote: >Hi All, > >I moved a physical box to a VM, and set its memory to 1Gig. Everything >runs fine except one backup: > > >/pub/backup# pg_dump -Fc -U postgres -f wildfire.backup wildfirep > >g_dump: Dumping the contents of table "ofrrds" failed: PQgetResult() failed. >pg_dump: Error message from server: ERROR: out of memory >DETAIL: Failed on request of size 1073741823. ^^^^^^^^^^ pg_dump is trying to allocate 1GB. Obviously it can't if 1GB is all you have. >pg_dump: The command was: COPY public.ofrrds (id, updateddate, bytes) TO >stdout; > >wildfire=# \dt+ ofrrds > List of relations > Schema | Name | Type | Owner | Size | Description >--------+--------+-------+-------+-------+------------- > public | ofrrds | table | andy | 15 MB | > > >wildfire=# \d ofrrds > Table "public.ofrrds" > Column | Type | Modifiers >-------------+------------------------+----------- > id | character varying(100) | not null > updateddate | bigint | not null > bytes | bytea | >Indexes: > "ofrrds_pk" PRIMARY KEY, btree (id) > There must be a heck of a lot of data in that bytea column. >I'm not sure how to get this backup to run. Any hints would be appreciated. As Adrian mentioned already, you're going to have to give it more memory somehow. Either more RAM or a big swap file. George
On 4 July 2018 at 14:43, Andy Colson <andy@squeakycode.net> wrote: > I moved a physical box to a VM, and set its memory to 1Gig. Everything > runs fine except one backup: > > > /pub/backup# pg_dump -Fc -U postgres -f wildfire.backup wildfirep > > g_dump: Dumping the contents of table "ofrrds" failed: PQgetResult() failed. > pg_dump: Error message from server: ERROR: out of memory > DETAIL: Failed on request of size 1073741823.> pg_dump: The command was: COPY public.ofrrds (id, updateddate, bytes) TO > stdout; There will be less memory pressure on the server if the pg_dump was performed from another host. When running pg_dump locally the 290MB bytea value will be allocated in both the backend process pg_dump is using and pg_dump itself. Running the backup remotely won't require the latter to be allocated on the server. > I've been reducing my memory settings: > > maintenance_work_mem = 80MB > work_mem = 5MB > shared_buffers = 200MB You may also get it to work by reducing shared_buffers further. work_mem won't have any affect, neither will maintenance_work_mem. Failing that, the suggestions of more RAM and/or swap look good. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 07/04/2018 12:31 AM, David Rowley wrote: > On 4 July 2018 at 14:43, Andy Colson <andy@squeakycode.net> wrote: >> I moved a physical box to a VM, and set its memory to 1Gig. Everything >> runs fine except one backup: >> >> >> /pub/backup# pg_dump -Fc -U postgres -f wildfire.backup wildfirep >> >> g_dump: Dumping the contents of table "ofrrds" failed: PQgetResult() failed. >> pg_dump: Error message from server: ERROR: out of memory >> DETAIL: Failed on request of size 1073741823.> pg_dump: The command was: COPY public.ofrrds (id, updateddate, bytes)TO >> stdout; > > There will be less memory pressure on the server if the pg_dump was > performed from another host. When running pg_dump locally the 290MB > bytea value will be allocated in both the backend process pg_dump is > using and pg_dump itself. Running the backup remotely won't require > the latter to be allocated on the server. > >> I've been reducing my memory settings: >> >> maintenance_work_mem = 80MB >> work_mem = 5MB >> shared_buffers = 200MB > > You may also get it to work by reducing shared_buffers further. > work_mem won't have any affect, neither will maintenance_work_mem. > > Failing that, the suggestions of more RAM and/or swap look good. > Adding more ram to the vm is the simplest option. I just seems a waste cuz of one backup. Thanks all. -Andy