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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
