Fichiers orphelins

Il peut arriver que des fichiers inutiles polluent les répertoires de PostgreSQL, par exemple après un crash. Ils peuvent représenter une volumétrie importante. Les outils ci-dessous permettent de les détecter.

Danger

Attention aux faux positifs ! Détruire un fichier réellement utilisé par PostgreSQL peut mener à un résultat catastrophique ! Dans le doute, n’effacez pas ou adressez-vous au support.

Si possible, tester la suppression sur une instance de test, par exemple après une copie physique complète incluant tous les fichiers. Vérifiez vos sauvegardes au préalable.

Requête

Cette requête est à exécuter base par base et est dérivée de https://github.com/gleu/pgstats/blob/master/pgreport_queries.h#L136pgstat“

```sql
WITH ver AS (
    SELECT
        current_setting('server_version_num') pgversion,
        v::INTEGER / 10000 || '.' || MOD(v::INTEGER, 10000) / 100 AS version
    FROM
        current_setting('server_version_num') v
),
tbl_paths AS (
    SELECT
        tbs.oid AS tbs_oid,
        spcname,
        'pg_tblspc/' || tbs.oid || '/' || (
            SELECT
                dir
            FROM
                pg_ls_dir('pg_tblspc/' || tbs.oid || '/', TRUE, FALSE) dir
            WHERE
                dir LIKE E'PG\\_' || ver.version || E'\\_%') AS tbl_path
    FROM
        pg_tablespace tbs,
        ver
    WHERE
        tbs.spcname NOT IN ('pg_default', 'pg_global')
),
files AS (
    SELECT
        d.oid AS database_oid,
        0 AS tbs_oid,
        'base/' || d.oid AS path,
        file_name AS file_name,
        SUBSTRING(file_name FROM E'[0-9]+') AS base_name
    FROM
        pg_database d,
        pg_ls_dir('base/' || d.oid, TRUE, FALSE) AS file_name
    WHERE
        d.datname = current_database()
    UNION ALL
    SELECT
        d.oid,
        tbp.tbs_oid,
        tbl_path || '/' || d.oid,
        file_name,
        (SUBSTRING(file_name FROM E'[0-9]+')) AS base_name
    FROM
        pg_database d,
        tbl_paths tbp,
        pg_ls_dir(tbp.tbl_path || '/' || d.oid, TRUE, FALSE) AS file_name
    WHERE
        d.datname = current_database()
),
orphans AS (
    SELECT
        tbs_oid,
        base_name,
        file_name,
        current_setting('data_directory') || '/' || path || '/' || file_name AS orphaned_file,
        pg_filenode_relation(tbs_oid, base_name::oid) AS rel_without_pgclass
    FROM
        ver,
        files
        LEFT JOIN pg_class c ON (c.relfilenode::text = files.base_name
                OR (c.oid::text = files.base_name
                    AND c.relfilenode = 0
                    AND c.relname LIKE 'pg_%'))
    WHERE
        c.oid IS NULL
        AND LOWER(file_name)
        NOT LIKE 'pg_%'
)
SELECT
    orphaned_file,
    pg_size_pretty((pg_stat_file(orphaned_file)).size) AS file_size,
    (pg_stat_file(orphaned_file)).modification AS modification_date,
    current_database()
FROM
    orphans
WHERE
    rel_without_pgclass IS NULL
ORDER BY (pg_stat_file(orphaned_file)).size DESC
```

Exemple de sortie :

```text
                orphaned_file                   | file_size |   modification_date    | current_database 
---------------------------------------------------+-----------+------------------------+------------------
/var/lib/postgresql/9.6/infra/base/41987/66529626 | 0 bytes   | 2021-10-21 14:31:01+02 | opm
/var/lib/postgresql/9.6/infra/base/41987/66529627 | 0 bytes   | 2021-10-21 14:31:01+02 | opm
/var/lib/postgresql/9.6/infra/base/41987/66529628 | 0 bytes   | 2021-10-21 14:31:01+02 | opm
/var/lib/postgresql/9.6/infra/base/41987/66529629 | 0 bytes   | 2021-10-21 14:31:01+02 | opm
```

Extension pg_orphaned

Dépôt Github : https://github.com/bdrouvot/pg_orphaned.

Cette extension de Bertrand Drouvot en C évite les faux positifs. Cependant, comme toute extension en C, un bug peut mener au crash de l’instance.

Elle n’est hélas pas packagée au moment où ceci est écrit (juin 2024). Il faudra donc la compiler.