1. Forum
  2. Usenet
  3. COMP.DATABASES.POSTGRESQL

  • Pg_dump makes me nut !

    From DeepThought@21:1/5 to All on Fri Dec 18 13:00:15 2015
    Hi guys,

    well.. this is a strange problem. I've deeply searched over all forums
    but I've found nothing at all. Thanks in advance 4 answers.

    I have a database composed by 3 different schemas: one of them (called "notsobigschema" :P ) has a table of about 30Gigs, and with a single
    table of 19Gigs inside. This table is full of LOBs and I don't know why
    part of it seem in pg_toast_$tableoid and part of it in pg_largeobject.
    But this is not the problem.

    When I use pg_dump to make a db backup, it takes all memory and swap
    available and kernel kills pg_dump session caused a out of memory
    exception . I have a RAM of about 8Gb and a pair of swap.
    When I use a *pg_dump -n notsobigschema * , it succeded and finish
    with success.
    Obiuvsly, u can say, "schema flag exclude blob from dump". True. But in
    -n schema mode, pg_dump takes all RAM+SWAP too.

    So.. HOw can I explain ? DO the biggest table (without LOB ) fit
    perfectly in sum of RAM+SWAP , considering total consumption of memory ?
    Have pg_dump a different behaviour in memory managment when manages LOB ?


    How may can I limit pg_dump memory consumption to fit my resources?

    Thanks a lot

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Dimitri Fontaine@21:1/5 to DeepThought on Fri Dec 18 13:12:23 2015
    Hi,

    Please consider asking on the pgsql-general mailing list instead:

    http://www.postgresql.org/list/pgsql-general/

    Regards,

    DeepThought <[email protected]> writes:
    well.. this is a strange problem. I've deeply searched over all forums but I've found nothing at all. Thanks in advance 4 answers.

    I have a database composed by 3 different schemas: one of them (called "notsobigschema" :P ) has a table of about 30Gigs, and with a single table
    of 19Gigs inside. This table is full of LOBs and I don't know why part of it seem in pg_toast_$tableoid and part of it in pg_largeobject. But this is not the problem.

    When I use pg_dump to make a db backup, it takes all memory and swap available and kernel kills pg_dump session caused a out of memory exception
    . I have a RAM of about 8Gb and a pair of swap.
    When I use a *pg_dump -n notsobigschema * , it succeded and finish with success.
    Obiuvsly, u can say, "schema flag exclude blob from dump". True. But in -n schema mode, pg_dump takes all RAM+SWAP too.

    So.. HOw can I explain ? DO the biggest table (without LOB ) fit perfectly
    in sum of RAM+SWAP , considering total consumption of memory ?
    Have pg_dump a different behaviour in memory managment when manages LOB ?


    How may can I limit pg_dump memory consumption to fit my resources?

    --
    Dimitri Fontaine
    PostgreSQL DBA, Architecte

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Robert Klemme@21:1/5 to DeepThought on Sat Dec 19 21:38:39 2015
    On 18.12.2015 13:00, DeepThought wrote:

    How may can I limit pg_dump memory consumption to fit my resources?

    Just a wild guess: maybe you memory settings are generally way off, i.e.
    you have configured the DB to use too much mem as max and the max is not reached during regular operations.

    Kind regards

    robert


    --
    remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From DFS@21:1/5 to DeepThought on Thu May 11 07:44:25 2017
    On 12/18/2015 7:00 AM, DeepThought wrote:
    Hi guys,

    well.. this is a strange problem. I've deeply searched over all forums
    but I've found nothing at all. Thanks in advance 4 answers.

    http://www.urbandictionary.com/define.php?term=Nut

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)