PostgreSQL Tutorial: Check orphaned data files

Summary: in this tutorial, you will learn how to check orphaned data files in PostgreSQL.

Table of Contents

Introduction

PostgreSQL, as all other relational database systems, needs to persist data on disk, either by writing the write ahead log or by synchronizing the data files on disk whenever there is a checkpoint. When it comes to the data files PostgreSQL creates a new one once a relation reaches segment_size, which is 1GB by default. Because of this a relation can consist of many files if the relation is growing. The question I want to look at in this tutorial is, if there can be orphaned files. A file would be orphaned if it is not part/referenced by any relation but the file is still there on disk. That would be a waste of disk space on the one hand, but it would also be inconsistent with what is stored in PostgreSQL catalog.

Example

Can it happen that files on disk do not belong to any relation? Consider this example: In one session we start a new transaction and create a table t without committing the transaction.

BEGIN;

CREATE TABLE t (id integer, name text);

INSERT INTO t (id, name)
  SELECT i, repeat('Pg', 32)
    FROM generate_series(1, 1000000) AS s(i);

Additionally we get the PID of the session and the location of the relation file on disk:

select * from pg_backend_pid();
 pg_backend_pid
----------------
           7170
(1 row)

select pg_relation_filepath('t');
 pg_relation_filepath
----------------------
 base/12974/24601
(1 row)

Without committing we can check the file on disk, as PostgreSQL already created it:

$ ls -lh $PGDATA/base/12974/24601
-rw------- 1 postgres postgres 97M Mar 18 21:19 /db/pgsql/data/base/12974/24601

If the server is crashing now, or the out of memory killer kicks in and kills the session: what happens then? We can simulate that quite easy by killing the session without giving it any chance to cleanup (which is signal 9):

$ kill -9 7170

To summarize: The session was killed before the transaction was able to complete and the transaction already created a table. What must happen is, that the transaction is rolled back and the table must not be there:

postgres-# select 1;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# select * from t;
ERROR:  relation "t" does not exist
LINE 1: select * from t;

This is fine and what is expected. But do we still see the file on disk?

$ ls -lh $PGDATA/base/12974/24601
-rw------- 1 postgres postgres 97M Mar 18 21:19 /db/pgsql/data/base/12974/24601

Yes, now we really have an orphaned file which does not belong to any relation PostgreSQL knows of:

select relname from pg_class where oid = 24601;
 relname
---------
(0 rows)

Find orphaned data files

In the worst case that could be gigabytes or terabytes of wasted space. Is there at least a way to detect such orphaned files? You would need to compare what PostgreSQL has recorded in the catalog against the file system, and then delete everything PostgreSQL is not aware of and this needs to be done very, very carefully.

All of those data files under the data directory should have entry in pg_class (otherwise PostgreSQL is not aware of them). We can use the following query to list the files seems orphaned in the current database:

SELECT
  unnest(
    string_to_array(
      string_agg(
        (SELECT string_agg(spc.path || '/' || file, ',')
          FROM pg_ls_dir(spc.path) AS file
          WHERE file ~ E'^[0-9]*$'),
        ',')
    , ',')
  )	AS filepath
FROM (
  SELECT DISTINCT
    regexp_replace(pg_relation_filepath(c.oid), E'/[0-9]*$', '') AS path
  FROM pg_class c
  WHERE c.relkind IN ('r', 'i', 't', 'S') AND
        c.relpersistence = 'p'
) AS spc
EXCEPT
SELECT
  pg_relation_filepath(c.oid) AS relpath
FROM pg_class c
WHERE c.relkind IN ('r', 'i', 't', 'S', 'm') AND
      c.relpersistence IN ('p', 'u');

Finally, getting the list of orphaned files:

     filepath
------------------
 base/12974/24605
 base/12974/24604
 base/12974/24601
(3 rows)

While you could get a list of the orphaned files with above query, you could get false positive due to in progress transactions (means started and not committed or rolled back yet) that are creating files (like create table, relation rewrites…). We can check these creating objects using the following query:

SELECT DISTINCT pid, relation
  FROM pg_locks AS l
  JOIN pg_database AS d ON l.database = d.oid
  WHERE d.datname = current_database() AND
        locktype = 'relation' AND
        granted AND
        NOT EXISTS (
          SELECT oid FROM pg_class AS c
            WHERE c.oid = relation
        );

Once you’ve excluded the creating files, the rest would be the files you need to check carefully. If you are sure it is an orphaned file you may delete it (after you make a backup, of course).

See more

PostgreSQL Monitoring