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(dot)klaver(at)aklaver(dot)com