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()). 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)
Kind regards
Giorgio Saviane
