BUG #17449: Disk space not released

On Sat, Mar 26, 2022 at 7:13 AM PG Bug reporting form
<noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 17449
Logged by: Giorgio Saviane
Email address: gsaviane@gmail.com
PostgreSQL version: 11.13
Operating system: Linux 5.8
Description:

Hello, I noticed an uncontrolled disk occupation growth caused by a Postgres
database in one of my deployments.
The actual database size took more than 500Gb (checked with select
pg_size_pretty(pg_database_size('dbname')) although tables accounted for a
total of ~ 50Gb (checked with pg_total_relation_size()).

So, the output of pg_database_size is 500GB and pg_total_relation_size
for all the tables is 50GB right? pg_database_size includes table
space size as well, were there any table spaces and files under it?
BTW, pg_total_relation_size also includes all the files of a relation
irrespective of whether the vacuum happened or not.

Despite any attempt
of full vacuum the discrepancy remained the same. I suspect that Postgres
started leaking disk space. I could see many 1Gb files with a timestamp of
two months back in time in the postgres data folder.
Restarting the server did not have any effect, so I decided to pg_dump the
database and pg_restore the backup in a new instance. That worked, the new
database is now ~ 50 Gb and dropping the old one released that 500Gb of disk
space.
The database was under streaming replication and I noticed the postgres log
reporting many of these messages

requested WAL segment 0000000100000000000000E3 has already been removed

Could be that the leak started in correspondence of that error?
If so, is there anything we can do to prevent it? I already set
wal_keep_segments = 100, but I'm not sure it is enough and how to tune it.
Is there any possible workaround to release the leaked space without going
through a backup? (It took two hours)

Which all other directories in the data directory took more space? Is
it pg_wal or base or some other? Were there any inactive replication
slots? Was the archiving happening properly?

Regards,
Bharath Rupireddy.

On Sat, Mar 26, 2022 at 7:13 AM PG Bug reporting form
<noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 17449
Logged by: Giorgio Saviane
Email address: gsaviane@gmail.com
PostgreSQL version: 11.13
Operating system: Linux 5.8
Description:

Hello, I noticed an uncontrolled disk occupation growth caused by a

Postgres

database in one of my deployments.
The actual database size took more than 500Gb (checked with select
pg_size_pretty(pg_database_size('dbname')) although tables accounted for

a

total of ~ 50Gb (checked with pg_total_relation_size()).

So, the output of pg_database_size is 500GB and pg_total_relation_size
for all the tables is 50GB right? pg_database_size includes table
space size as well, were there any table spaces and files under it?
BTW, pg_total_relation_size also includes all the files of a relation
irrespective of whether the vacuum happened or not.

The database was created in a single tablespace. Tables did not hold any
other
additional files than those inherently used for storing their data and
indexes.

Despite any attempt
of full vacuum the discrepancy remained the same. I suspect that Postgres
started leaking disk space. I could see many 1Gb files with a timestamp

of

two months back in time in the postgres data folder.
Restarting the server did not have any effect, so I decided to pg_dump

the

database and pg_restore the backup in a new instance. That worked, the

new

database is now ~ 50 Gb and dropping the old one released that 500Gb of

disk

space.
The database was under streaming replication and I noticed the postgres

log

reporting many of these messages

requested WAL segment 0000000100000000000000E3 has already been removed

Could be that the leak started in correspondence of that error?
If so, is there anything we can do to prevent it? I already set
wal_keep_segments = 100, but I'm not sure it is enough and how to tune

it.

Is there any possible workaround to release the leaked space without

going

through a backup? (It took two hours)

Which all other directories in the data directory took more space? Is
it pg_wal or base or some other? Were there any inactive replication
slots? Was the archiving happening properly?

The content of /var/lib/postoresql/11/main/base dir was as following:
$ du -hs
7.6M 1
78M 1194171
7.6M 1236634587
7.6M 13052
7.6M 13053
561G 16385
20M pgsql_tmp

And that 16385 dir was full of 1Gb files whose name had a <number>.<digit>
pattern. I cannot say for sure if there were other subdirectories in it.
I didn't check the replication status unfortunately. I just removed it and
recreated
it over the new instance.

Kind regards

On Thu, May 19, 2022 at 4:15 PM Giorgio Saviane <gsaviane@gmail.com> wrote:

On Sat, Mar 26, 2022 at 7:13 AM PG Bug reporting form

<noreply@postgresql.org> wrote:

Despite any attempt

of full vacuum the discrepancy remained the same. I suspect that

Postgres

started leaking disk space. I could see many 1Gb files with a timestamp

of

two months back in time in the postgres data folder.

If the database suffers a crash (or immediate shutdown) in the middle of
something like VACUUM FULL or CLUSTER, it might leave orphaned in-process
files such as the ones you describe behind and have no way to know to clean
them up. The knowledge about what it was working on just before the crash
was lost in the crash.

Files not touched in 2 months and also not referenced in
pg_class.relfilenode are almost certainly such orphaned files and could,
with extreme nervousness, be cleaned up by hand. Especially if the
human-readable log files support a crash having happened at that time.

Restarting the server did not have any effect, so I decided to pg_dump
the

database and pg_restore the backup in a new instance. That worked, the

new

database is now ~ 50 Gb and dropping the old one released that 500Gb of

disk

space.
The database was under streaming replication and I noticed the postgres

log

reporting many of these messages

requested WAL segment 0000000100000000000000E3 has already been

removed

When did those start? Before you rebuilt the master? Was your replica
using, or attempting to use, replication slots?

Cheers,

Jeff

Despite any attempt
of full vacuum the discrepancy remained the same. I suspect that Postgres
started leaking disk space. I could see many 1Gb files with a timestamp of
two months back in time in the postgres data folder.

If the database suffers a crash (or immediate shutdown) in the middle of something like VACUUM FULL or CLUSTER, it might leave orphaned in-process files such as the ones you describe behind and have no way to know to clean them up. The knowledge about what it was working on just before the crash was lost in the crash.

Files not touched in 2 months and also not referenced in pg_class.relfilenode are almost certainly such orphaned files and could, with extreme nervousness, be cleaned up by hand. Especially if the human-readable log files support a crash having happened at that time.

That was not the case. The server has been running seamlessly since I
rebuilt the master.

Restarting the server did not have any effect, so I decided to pg_dump the
database and pg_restore the backup in a new instance. That worked, the new
database is now ~ 50 Gb and dropping the old one released that 500Gb of disk
space.
The database was under streaming replication and I noticed the postgres log
reporting many of these messages

requested WAL segment 0000000100000000000000E3 has already been removed

When did those start? Before you rebuilt the master? Was your replica using, or attempting to use, replication slots?

They show up after I rebuild the master and re-enable the replica. No,
the replica is not using any slot, but I got that it would help in
case of unstable networking between slave and master.

Kind regards

Giorgio

On Mon, May 30, 2022, 7:40 AM Giorgio Saviane <gsaviane@gmail.com> wrote:

Despite any attempt
of full vacuum the discrepancy remained the same. I suspect that

Postgres

started leaking disk space. I could see many 1Gb files with a

timestamp of

two months back in time in the postgres data folder.

If the database suffers a crash (or immediate shutdown) in the middle of

something like VACUUM FULL or CLUSTER, it might leave orphaned in-process
files such as the ones you describe behind and have no way to know to clean
them up. The knowledge about what it was working on just before the crash
was lost in the crash.

Files not touched in 2 months and also not referenced in

pg_class.relfilenode are almost certainly such orphaned files and could,
with extreme nervousness, be cleaned up by hand. Especially if the
human-readable log files support a crash having happened at that time.

That was not the case. The server has been running seamlessly since I
rebuilt the master.

But as i understand it, you rebuilt the master as a response to the
problem, so the time period in question is before the rebuild, not after.

Cheers,

Jeff

Despite any attempt
of full vacuum the discrepancy remained the same. I suspect that Postgres
started leaking disk space. I could see many 1Gb files with a timestamp of
two months back in time in the postgres data folder.

If the database suffers a crash (or immediate shutdown) in the middle of something like VACUUM FULL or CLUSTER, it might leave orphaned in-process files such as the ones you describe behind and have no way to know to clean them up. The knowledge about what it was working on just before the crash was lost in the crash.

Files not touched in 2 months and also not referenced in pg_class.relfilenode are almost certainly such orphaned files and could, with extreme nervousness, be cleaned up by hand. Especially if the human-readable log files support a crash having happened at that time.

That was not the case. The server has been running seamlessly since I
rebuilt the master.

But as i understand it, you rebuilt the master as a response to the problem, so the time period in question is before the rebuild, not after.

Yes, but then after the master was rebuilt it happened that the master
started logging "requested WAL segment XXX has already been removed"
again.

Kind regards

Giorgio

On Tue, May 31, 2022 at 5:26 AM Giorgio Saviane <gsaviane@gmail.com> wrote:

Despite any attempt
of full vacuum the discrepancy remained the same. I suspect that

Postgres

started leaking disk space. I could see many 1Gb files with a

timestamp of

two months back in time in the postgres data folder.

If the database suffers a crash (or immediate shutdown) in the middle

of something like VACUUM FULL or CLUSTER, it might leave orphaned
in-process files such as the ones you describe behind and have no way to
know to clean them up. The knowledge about what it was working on just
before the crash was lost in the crash.

Files not touched in 2 months and also not referenced in

pg_class.relfilenode are almost certainly such orphaned files and could,
with extreme nervousness, be cleaned up by hand. Especially if the
human-readable log files support a crash having happened at that time.

That was not the case. The server has been running seamlessly since I
rebuilt the master.

But as i understand it, you rebuilt the master as a response to the

problem, so the time period in question is before the rebuild, not after.

Yes, but then after the master was rebuilt it happened that the master
started logging "requested WAL segment XXX has already been removed"
again.

It is expected that the replica will be out of sync after the master is
rebuilt, and will need to be recreated. If it had happened before the
rebuild, that might indicate something interesting. We don't know what
caused the problem to start with, but there is no indication it is a bug,
rather than just an unfortunately timed crash/immediate shutdown/power
failure.

Cheers,

Jeff