Content-Type: text/plain; charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\))
Subject: Re: Disk filled-up issue after a lot of inserts and drop schema
From: Pietro Pugni <pietro.pugni@gmail.com>
In-Reply-To: <28460.1473864279@sss.pgh.pa.us>
Date: Wed, 14 Sep 2016 19:45:34 +0200
Cc: Rick Otten <rottenwindfish@gmail.com>,
 pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <FE960B29-7786-4A97-9288-4346F48A21A7@gmail.com>
References: <AEA46136-0A29-4F3C-8966-90178EE44B4B@gmail.com>
 <CAMAYy4K6nwkqqdRcWR4cNeYvCXAH7h4WAo6vq1AkX3+MYOezsw@mail.gmail.com>
 <28460.1473864279@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Thank you guys.
I=E2=80=99ve jsut discovered the issue.. I set "logging_collector=3Doff=E2=
=80=9D in the previous email but didn=E2=80=99t comment the other log* para=
meters, so Postgres was logging every single INSERT! This was caused the di=
sk to fill up.

The strange issue is that the log file didn=E2=80=99t exists when the disk =
filled up. I personally looked for it but it wasn=E2=80=99t where it should=
 have been ( /var/log/postgesql/ ), so I can=E2=80=99t exactly confirm that=
 the issue was the log file getting bigger and bigger.

Now, after writing the previous mail and rebooting postgres, I run several =
ETL jobs and the disk space was filling up. The log file reached 110GB in s=
ize.
After disabling *ALL* the log options in postgresql.conf, the log file does=
 just the essential and default information.


I=E2=80=99m sorry to have launched a false alarm, but we can consider the i=
ssue solved.

Thank you again

Best regards,
 Pietro Pugni


> Il giorno 14 set 2016, alle ore 16:44, Tom Lane <tgl@sss.pgh.pa.us> ha sc=
ritto:
>=20
> Rick Otten <rottenwindfish@gmail.com> writes:
>> I don't know why something still has an open file descriptor on something
>> you believe has been removed, but at least that explains why you are
>> experiencing the discrepancy between "du" and the real available space on
>> the disk.
>=20
> Yeah, the reported behavior clearly indicates that some PG process is
> holding open files that should have been dropped (and were unlinked).
> That's a bug, but there's not enough info here to find and fix it.
>=20
> If we're really lucky, this is the same bug that Andres found and fixed
> last week:
> https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dcommitdiff;h=3D=
26ce63ce76f91eac7570fcb893321ed0233d62ff
>=20
> but that guess is probably too optimistic, especially if it's a background
> process (such as the checkpointer process) that is holding the open files.
>=20
> If you can reproduce this, which I'm guessing you can, please use
> "lsof" or similar tool to see which Postgres process is holding open
> references to lots of no-longer-there files.
>=20
> 			regards, tom lane


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance