Hi, On 2017-08-14 14:12:22 +0200, Chris Travers wrote: > Problem: > The system this came up on is PostgreSQL 9.6.3 and has had repeated trouble > with disk space. Querying pg_database_size, as well as du on the > subdirectory of base/ show total usage to be around 3.8TB. Summing up the > size of the relations in pg_class though shows around 2.1TB. > > Initial troubleshooting found around 150 GB of space in pg_temp which had > never been cleared and was at least several days old. Restarting the > server cleared these up. > > Poking around the base/[oid] directory, I found a large number of files > which did not correspond with a pg_class entry. One of the apparent > relations was nearly 1TB in size. > > What I think happened: > I think various pg_temp/* and orphaned relation files (In base/[oid]) were > created when PostgreSQL crashed due to running out of space in various > operations including creating materialised views. > > So my question is if there is a way we can safely clean these up on server > restart? If not does it make sense to try to create a utility that can > connect to PostgreSQL, seek out valid files, and delete the rest?
I think the fix here is to call RemovePgTempFiles() during crash-restarts, instead of just full starts. The previously stated need to be able to inspect temp files after a crash can be less impactfully fulfilled with restart_after_crash = false. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
