© Laurenz Albe 2025
Table of Contents
PostgreSQL is famous for its stability, and rightly so. But that does not mean that it can never crash. And while PostgreSQL usually cleans up after itself, it has no good way to do so after a crash (after all, it has lost its memory). As a consequence, you can end up with orphaned files in your data directory. If these files are small, they probably won't worry you. But sometimes you can end up with a lot of junk in your data directory. It is notoriously difficult to deal with that problem, so I decided to write about it.
In case the title of this article gives you bad feelings about PostgreSQL, I'll try to dispel them. PostgreSQL is not in the habit of crashing. But sometimes a crash is not PostgreSQL's fault. Let me enumerate a few possible causes for a crash:
pg_wal ran full, and PostgreSQL couldn't create a new WAL segmentPANICIn fact, the most frequent cause of a crash that leaves large orphaned files in its wake is people running VACUUM (FULL). This command creates a copy of the table and all its indexes. If you run out of disk space and PostgreSQL cannot create a new WAL segment, the database server will crash. Such a crash will leave the partly created copy of the table behind. To avoid that kind of problem, I recommend that you keep pg_wal and the data directory on different file systems (and while you are at it, put the log files on a separate file system as well).
You will probably understand that PostgreSQL cannot clean up when it crashes, but why can't it clean up when it restarts? I have two answers for that:
pg_filenode.map could take a long time, during which the server is not yet availableI see particularly the second point as a very good reason to forgo any automatic clean-up activity.
That's the crucial question. I'll offer you two approaches:
The simple, safe and supported way to deal with the problem is:
pg_dump the database to diskDROP DATABASE — that will delete all files, including the orphansIncidentally, that is also the correct way to clean a database that has experienced any form of data corruption.
While safe and simple, cleaning a large database that way can cause egregious down time. If you feel that you cannot afford that, and you don't want to take the risky path described below, you should simply live with the orphaned files. They won't do any harm, they only eat up some of your disk space, and these days disk space is often much cheaper than an extended down time.
There are a couple of PostgreSQL functions that allow a superuser to examine the file system:
| function | description |
|---|---|
pg_ls_dir(dirname) |
lists a directory on the database server, returns a table of text |
pg_stat_file(filename) |
returns a composite record with all kinds of information about a database server file, including the size and the modification timestamp |
pg_filenode_relation(tablespace, filenode) |
returns the object ID of the relation (table, index, sequence, ...) that belongs to a certain file in the database directory (NULL if there is no such relation) |
With some knowledge of the internals of PostgreSQL, that allows us to compose a query for all orphaned files in a database directory:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
WITH d AS ( /* default tablespace directory for the database */ SELECT current_setting('data_directory') || '/base/' || (SELECT oid FROM pg_database WHERE datname = current_database()) AS path ) SELECT split_part(dir.f, '.', 1) AS filenode, /* relation size is the sum of all segments */ pg_size_pretty(sum(f.size)) AS size, max(f.modification) AS last_modified FROM d /* all the files in the database directory */ CROSS JOIN LATERAL pg_ls_dir(d.path) AS dir(f) /* file data like size and modification timestamp */ CROSS JOIN LATERAL pg_stat_file(d.path || '/' || dir.f) AS f WHERE /* file name is digits only, optionally followed by a dot and more digits */ /* that means: a segment of the "main fork" of a relation */ f ~ '^\d+(\.\d+)*$' /* PostgreSQL doesn't know a relation that belongs to the file */ AND pg_filenode_relation(0::oid, split_part(dir.f, '.', 1)::bigint::oid) IS NULL GROUP BY filenode; |
To delete a filenode returned by the query (let's assume we get 12345), go to the database directory:
|
1 2 3 4 |
SELECT current_setting('data_directory') || '/base/' || (SELECT oid FROM pg_database WHERE datname = current_database()); |
and delete the files that belong to it:
|
1 |
rm 12345* |
Be careful when deleting files:
Before you delete any files in the data directory, make sure you have a good backup.
The safe way to get rid of orphaned files after a crash is to dump and restore the database. For the more daring people out there, I gave you a query that helps you identify those orphaned files.
Forgive the ignorance, but "people running VACUUM (FULL). This command creates a copy of the table and all its indexes. If you run out of disk space..."
Wouldn't it be possible, to teach vacuum to first calculate at least a rough estimate of the needed space, and after a look at the filesystem's available space emit a warning, instead of simply crashing the server?
It would be possible. But there are some considerations:
For one, that would mean that we have to teach PostgreSQL how to figure out the amount of free space on a file system on all the many operating systems that it supports. I don't think that's so simple.
Furthermore, the “rough estimate” could be off in both directions: you could get warned, even if there is enough space, or
VACUUM (FULL)could still fill the disk even though no warning was deemed necessary.Also, would a warning help? A warning doesn't keep the statement from running.
AFAIK any files created before WAL-logging can became orphan in case of crash. E.g. CREATE INDEX (at least for for b-tree and gist) writes files to disk, then logs them.
Correct!