Re: pg_restore out of memory

On 07/15/2016 12:37 AM, Miguel Ramos wrote:
>
> A Qui, 14-07-2016 às 10:52 +0100, Miguel Ramos escreveu:
>>
>> A Qua, 13-07-2016 às 18:42 -0400, Tom Lane escreveu:
>>>
>>> I wrote:
>>>>
>>>> I'm still suspicious that this might be some sort of NOTICE-
>>>> processing-
>>>> related buffer bloat.  Could you try loading the data with the
>>>> server's
>>>> log_min_messages level cranked down to NOTICE, so you can see
>>>> from
>>>> the
>>>> postmaster log whether any NOTICEs are being issued to the
>>>> pg_restore
>>>> session?
>>>
>>> BTW, I experimented with that theory by creating a table with a
>>> BEFORE
>>> INSERT trigger function that emits a NOTICE, and then making
>>> pg_restore
>>> restore a lot of data into it.  I could not see any memory growth
>>> in
>>> the pg_restore process.  However, I was testing 9.1.22, not 9.1.8.
>>> Also, some of the misbehaviors we've discovered along these lines
>>> have
>>> been timing-sensitive, meaning that the problem might or might not
>>> reproduce for another person even with the same software version.
>>> Are you running pg_restore locally on the same machine as the
>>> server,
>>> or across a network --- and if the latter, how fast is the network?
>>>
>>>             regards, tom lane
>>>
>>
>> I was running pg_restore locally.
>> The disk containing the backup, however, is on NAS.
>> The NAS is mounted on the server using SMB and the FreeBSD kernel
>> implementation of smbfs (mount_smbfs -I ... /mnt).
>> The kernel smbfs is notoriously outdated and sometimes we get
>> timeouts.
>>
>> However, those timeouts happen randomly and this "out of memory"
>> happens consistently.
>> This time, the server was no longer under heavy load, the log lines
>> are
>> consecutive, there was no activity during the start of the COPY
>> statement and the error.
>>
>> The network is 1Gbps with a single unmanaged 24-port switch.
>> The server
>> has two aggregated links to the switch.
>>
>>
>> I ran pg_restore locally because the server is in another office,
>> connected to mine through a VPN.
>>
>> Now I have arranjed for a PC to be there for me and my next test will
>> be to do the restore using the latest pgadmin.
>>
>>
>> Thanks,
>>
>> -- Miguel Ramos
>>
>
> I tried the restore using pgAdmin III 1.22.1.
> This time from a Windows PC connected to the server through a 1Gbps
> switch.
>
> Unfortunately the result was the same, and this was my best bet.
>
>
> I see (transcribed by hand from screenshot):
> ...
> pg_restore: processing data for table "inspection.positioned_scan"
> out of memory
>
> Process returned exit code 1.
>
>
> I hadn't yet set log_min_messages to 'notice'. But as
> client_min_messages is at 'notice', aren't this displayed on a verbose
> pg_restore?
> Maybe during the weekend I can have more verbose logging.
>
> Now I'm repeating the backup (maybe the file is bad) and then I will
> repeat the restore with log_min_messages to 'notice'.
>
> I suppose log_statement to 'all' is no longer necessary?
>
> What else?

The pg_dump file you are restoring from is a custom format.

Do you have room to do something like?:

1) pg_restore -d some_db -U some_user -t inspection.positioned_scan
/mnt/paysdeloire2013_convertida2.1.dump


>
>
> --
> Miguel Ramos
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com