Orphaned relation files after PostgreSQL crash

Summary: In this article, we will explore the issue of orphaned relation files after a PostgreSQL crash, as well as the related solutions.

Table of Contents

Crash recovery scenario: Use case

Below, we create a table in a PostgreSQL database, insert some data.

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);

Next, we start a transaction block, create an empty table t1, copy table t to create a new table t_copy, and check the relation file path in the system.

BEGIN;

CREATE TABLE t1 ();

CREATE TABLE t_copy AS TABLE t;

SELECT pg_relation_filepath(name) AS path
  FROM (VALUES ('t1'), ('t_copy')) AS t (name);
     path
--------------
 base/5/16389
 base/5/16392

Let’s stop the database immediately, simulate the database failure with abnormal downtime, and restart the database service:

$ pg_ctl stop --mode=immediate
$ pg_ctl start

After the database restarts, manually performs a checkpoint:

CHECKPOINT;

Check the newly created relation files in the transaction block before the restart:

$ ls -lh base/5/{16389,16392}
-rw------- 1 postgres postgres   0 Aug 18 17:43 base/5/16389
-rw------- 1 postgres postgres 97M Aug 18 17:44 base/5/16392

Crash recovery scenario: How it works

The following are the internal reasons for the orphaned relation files issue after crash recovery when creating table files in a PostgreSQL transaction:

1. During the execution of a transaction, PostgreSQL records the created relation files in the session memory.
2. When a transaction aborts abnormally or a rollback operation is performed, these relation files are cleaned up immediately.
3. When a transaction is interrupted unexpectedly, perhaps due to a PANIC error caused by insufficient disk space, an OOM (Out-of-Memory) signal from the system, buggy extensions with software errors, or a server crash due to power outage, the records for newly created relation files in the session memory are lost, leading to orphaned relation files.

The solution by Redrock Postgres

Redrock Postgres records undo logs when creating relation files. If the connected session is terminated due to certain reasons, the database checks the aborted transactions after restarted. The database rolls back the aborted transactions and clears the remaining relation files based on the recorded undo logs.

Below, we create a same table and insert the same data as above, in a Redrock Postgres database.

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);

Next, we start a transaction block, create an empty table t1, copy table t to create a new table t_copy, and check the relation file path in the system.

BEGIN;

CREATE TABLE t1 ();

CREATE TABLE t_copy AS TABLE t;

SELECT pg_relation_filepath(name) AS path
  FROM (VALUES ('t1'), ('t_copy')) AS t (name);
     path
--------------
 base/5/16389
 base/5/16392

Let’s stop the database immediately, simulate the database failure with abnormal downtime, and restart the database service:

$ pg_ctl stop --mode=immediate
$ pg_ctl start

After the database restarts, manually performs a checkpoint:

CHECKPOINT;

The remaining relation files are not immediately cleaned up when the transaction is rolled back, and the actual cleanup of the relation files is done during the checkpoint process.

Check the newly created relation files in the transaction block before the restart:

$ ls -lh base/5/{16389,16392}
ls: cannot access 'base/5/16389': No such file or directory
ls: cannot access 'base/5/16392': No such file or directory

After the database restarts, the abnormally aborted transaction is rolled back, and the remaining relation files are cleaned up according to the undo log recorded by the transaction.

See more

Redrock Postgres Documentation: Undo Segments