Re: pg_restore out of memory

Às 15:40 de 12-07-2016, Tom Lane escreveu:
> Miguel Ramos <org(dot)postgresql(at)miguel(dot)ramos(dot)name> writes:
>> We have backed up a database and now when trying to restore it to the
>> same server we get this:
>
>>>> # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump
>>>> pg_restore: [custom archiver] out of memory
>
> It looks to me like this error is pg_restore itself running out of memory,
> not reporting a server-side OOM condition. You could verify that by
> looking in the server log to see whether any out-of-memory error appeared
> there. But assuming that I'm right, the other responses suggesting
> tweaking server configurations are not on-point at all.

The logs of the last attempt are already gone.
Obviously, I'll keep tonight's logs.

> Unless you're running pg_restore under a really small ulimit, this would
> seem to suggest some kind of memory leak in pg_restore itself. I wonder
> how many objects in your dump (how long is "pg_restore -l" output)?

pg_restore -l | wc gives me:
1055 7984 70675

It looks small to me.
We don't have a lot of tables, instead we have really huge tables.
We try to keep the schema normalized when possible, and we only don't do
that when the resulting tuples become too small for PostgreSQL, when the
row overhead becomes prohibitive.

>
>> - PostgreSQL 9.1.8 custom compiled to get 32kB blocks
>
> 9.1.8 is pretty old ...

Oh, I'll forward your email to those who were older than I, the many far
wiser than I, but whose love of database servers is certainly not
stronger than mine, by far.

Thanks,

--
Miguel Ramos