Re: pg_restore out of memory

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?


-- 
Miguel Ramos