Re: OOM killer while pg_restore

Le 03/03/2022 à 19:43, Tom Lane a écrit :
> =?UTF-8?Q?Marc_Recht=c3=a9?= <marc4@rechte.fr> writes:
>> Le 03/03/2022 à 16:31, Tom Lane a écrit :
>>> Does memory consumption hold steady if you drop the FK constraints?
>> Actually the number of rows is 232735712.
>> Accordingly the RAM consumption would be x12 x3 = 7.8 GiB.
>> This is close to the 8,1g I reported earlier (actually it was closer to
>> 7.8 GB, due to GiB vs. GB confusion).
>> So there is no memory leak.
>> It took 16 hours on my box to reach that RAM consumption, and then the
>> COPY failed when checking the first FK (as the referenced table was empty).
> I'm guessing it was swapping like mad :-(
>
> We've long recommended dropping FK constraints during bulk data loads,
> and then re-establishing them later.  That's a lot cheaper than retail
> validity checks, even without the memory-consumption angle.  Ideally
> that sort of behavior would be automated, but nobody's gotten that
> done yet.  (pg_restore does do it like that during a full restore,
> but not for a data-only restore, so I guess you were doing the latter.)
>
>             regards, tom lane
>
>
Did the test without the 3 FK, but with PK and index:

I took 9.5 hours and consumed 1GB of RAM (vs. 16 hours and 8 GB).

Thanks you for the explanations.

I  assume there is currently no GUC to limit RAM consumption of a backend ?

Marc