Re: pg_dump out of memory

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