How to find and safely remove orphaned files in order to return some amount of disk space to the system. This task must be performed with extreme care.
Under certain circumstances PostgreSQL operations can leave "orphaned" files in its data directory of which the server retains no knowledge. These files consume unnecessary disk space which can sometimes be significant.
For example, a VACUUM FULL operation completely rebuilds a table by writing new data files for the table that contains just the live data from the original. It then deletes the data files for the old table. If the VACUUM FULL crashes (usually due to out of space) before completion, the original data files will remain in use but any new data files that it created before crashing may be left behind, with the server being left unaware of them. If the VACUUM FULL operation was 100GB into a 200GB table build then you may have 100GB of orphaned data files left in your server's data directory.
The following query will present a list of files in the current database's data directory that are not listed in the PostgreSQL catalog.
These orphaned files should be moved to a separate location and retained until the database is known to be functioning correctly.
with tablespaces as (
select oid as tsoid
from pg_tablespace, pg_tablespace_databases(oid)
where spcname <> 'pg_global'
group by oid
having array[(select oid from pg_database where datname = current_catalog)] @>
array_agg(pg_tablespace_databases)
),
dboid as (
select oid::text as datoid from pg_database where datname = current_catalog
),
datadirs (dir) as (
select format('%s/PG_%s_%s/%s', pg_tablespace_location(tsoid),
(select floor(setting::int / 10000)
from pg_catalog.pg_settings where name = 'server_version_num'),
(select catalog_version_no from pg_control_system()),
(select datoid from dboid))
from tablespaces
union all
select format('base/%s', datoid) from dboid
where (pg_stat_file(format('base/%s', datoid), true)).isdir is not null
),
allfiles as (
select format('%s/%s', dir, pg_ls_dir) as fullname,
pg_ls_dir as basename,
greatest(position('.' in pg_ls_dir), position('_' in pg_ls_dir)) as position
from datadirs, pg_ls_dir(dir)
where pg_ls_dir not in ('pg_filenode.map', 'pg_internal.init', 'PG_VERSION')
), filenodes as (
select pg_relation_filenode(oid) as filenode
from pg_class where pg_relation_filenode(oid)::text <> '0'
), orphaned as (
select basename, fullname
from allfiles
where case when position <> 0 then left(basename, position - 1) else basename end
not in (select filenode::text from filenodes)
)
select * from orphaned, pg_stat_file(fullname);
These orphaned files should be moved to a separate location and retained until the database is known to be functioning correctly.
The surest method of determining that the data is complete is to perform a logical dump of the database, redirecting that output to /dev/null. If that completes with no errors then the database and its data are fully functional and stable.
We also understand that performing a full dump of a large database can be time consuming. An alternative method would be to select a few rows from each table in the database. This will ensure the data files are still present. This can be accomplished with the following:
DO
$$
DECLARE command TEXT;
BEGIN
FOR command IN SELECT 'SELECT * FROM ' || pgn.nspname || '.' || pgs.relname || ' LIMIT 10;'
FROM pg_catalog.pg_class pgs
JOIN pg_catalog.pg_namespace pgn
ON pgs.relnamespace = pgn.oid
WHERE pgs.relkind = 'r'
AND pgn.nspname NOT IN ('information_schema', 'pg_catalog', 'sys')
LOOP
RAISE NOTICE 'Performing %', command;
EXECUTE command;
RAISE NOTICE 'Done';
END LOOP;
END
$$;
The above command will not emit the selected tuples to the screen, but if an error is encountered it will be shown.
Once proper database operations have been confirmed, the moved files may be safely deleted.