An extension that provides checking of the integrity of database files, detecting missing or corrupted relfilenode files to ensure storage consistency.
Build and install the extension to the current PostgreSQL installation directory.
$ make
$ make installIf you prefer to install it in a different location, such as a local PostgreSQL installation directory, try:
PG_CONFIG=<postgres_install_dir>/bin/pg_config make
PG_CONFIG=<postgres_install_dir>/bin/pg_config make installpg_fsck_list_relfilenodes- list all relfilenode files in current database, include the tables in the non-default table spaces.pg_fsck_find_missing_relfilenodes- list all relfilenode files, which exist inpg_classcatalog but are missing in current database.pg_fsck_find_extra_relfilenodes- list all relfilenode files, which don't exist inpg_classcatalog but exist in the database directory.pg_fsck_identify_file(path)- reverse map: given an on-disk relation file path (e.g.base/5/16394,base/5/16394_fsm,base/5/16394.3,global/1260), resolve it back to the owning relation in the current database, splitting out the fork (main/fsm/vm/init) and segment number. Returns a NULLrelnamewhen the path cannot be attributed to a relation in the current database.pg_fsck_find_invalid_databases()- list databases stuck in the INVALID state left behind by an interruptedDROP DATABASE(pg_database.datconnlimit = -2). Such a database is unconnectable and un-autovacuumable and leaks itsbase/<oid>directory untilDROP DATABASEis re-run.
pg_fsck_file_summary()- a "what is eating my disk" rollup for the current database: aggregates the relfilenode files by fork type, schema, and object type, reporting file count and total size of each, plus a grand total and schema count.pg_fsck_list_temp_files()- inventory the temporary (query-spill) files underpgsql_tmpfor every tablespace. These are distinct from thebase/<db>/rewrite orphans surfaced bypg_fsck_find_extra_relfilenodes; on a healthy, idle cluster this should be empty.pg_fsck_check_segments()- check the physical layout of each relation's main-fork segment files: every non-final segment must be exactly one segment size, every file size must be a whole multiple of the block size, and there must be no gap in the.1 .2 .Nsequence. The segment size is taken at runtime (never hard-coded), so a server built with a non-default--with-segsizeis handled correctly.pg_fsck_check_forks()- filesystem-layer checks on the auxiliary fork files (_fsm/_vm/_init): a fork file whose size is not a block-size multiple, an oversized_vmfork (larger than its main fork, i.e. stale/orphaned), or an_initfork on a non-unlogged relation. It does not check fork existence (forks are optional) or vm/fsm content semantics (usepg_visibilityfor that).pg_fsck_check_tablespace_links()- audit thepg_tblspc/<oid>symlinks: flags a missing link, a target that does not exist, a target nested inside the data directory (which breaks base backups), or a missingPG_<major>_<catversion>subdirectory. A broken tablespace symlink can stop the whole server from starting.pg_fsck_find_orphan_database_dirs()- reconcile thebase/<oid>directories againstpg_databaseat database granularity: abase/<oid>with no catalog row (hard_orphan_dir, leftover from an interruptedDROP DATABASE), one whose database is INVALID (invalid_db_dir), or a live database whose directory is gone (missing_dir).
pg_fsck_findings()- the union of every check above in one uniform shape (severity,check_name,object,filepath,detail): missing/extra files, invalid databases, segment/fork anomalies, permission/ownership problems, tablespace-symlink problems, and orphan database directories. Inventory/helper functions are not included -- they report information, not problems.pg_fsck_findings_filtered(min_severity)- the same findings, filtered to a minimum severity (error<warning<info), e.g.pg_fsck_findings_filtered('error')for errors only.pg_fsck_check(format)- run the whole-database health check and render a multi-section report informat, one ofjson(default),markdown,html, orxml. This is the single entry point most users want. The report has six sections: environment (engine version, block/segment size, checksums, data directory, report time), summary (total files/size, schema count, error/warning counts), findings (the problems), invalid_databases, file_composition (files grouped by fork/schema/kind), and temp_files. See examples/ for sample output in every format.
pg_fsck_stat(path)- likepg_stat_filebut exposes what it omits: filemode, owneruid/gid, and (for symlinks, vialstat)is_symlink+symlink_target. Same read-path privilege model aspg_stat_file(superuser orpg_read_server_files).modeis the rawst_mode; format the permission bits withto_oct(mode & 511).pg_fsck_check_permissions()- audit on-disk permissions and ownership: flags directories/relation files that are more permissive than the cluster's mode allows (0700/0600, or 0750/0640 in group-access mode) or not owned by the server user. Catches the drift that makes a server refuse to start after a restore or container volume remount.
pg_fsck_get_my_database_id- get the database ID of current connection.pg_fsck_get_database_path(db_oid, spc_oid)- render the on-disk directory path for a database in a given tablespace (symbolicpg_tblspc/...path for non-default tablespaces).
By default, pg_fsck_find_missing_relfilenodes and pg_fsck_find_extra_relfilenodes are restricted to superusers, but other users can be granted the EXECUTE permission to run these functions.
For example of pg_fsck_find_missing_relfilenodes,
postgres=# CREATE EXTENSION pg_fsck;
CREATE EXTENSION
postgres=# SELECT pg_fsck_get_my_database_id();
pg_fsck_get_my_database_id
----------------------------
5
(1 row)
postgres=# SELECT * FROM pg_fsck_list_relfilenodes();
relname | table_oid | relfilenode | filepath
------------------------------------------------+-----------+-------------+-----------------------------------------
t1 | 24576 | 24576 | base/5/24576
test_table_in_ts_id_seq | 32770 | 32770 | base/5/32770
pg_toast_32771 | 32776 | 32776 | pg_tblspc/32769/PG_18_202502212/5/32776
pg_toast_32771_index | 32777 | 32777 | pg_tblspc/32769/PG_18_202502212/5/32777
test_table_in_ts | 32771 | 32771 | pg_tblspc/32769/PG_18_202502212/5/32771
test_table_in_ts_pkey | 32778 | 32778 | base/5/32778
large_table_id_seq | 32781 | 32781 | base/5/32781
pg_toast_32782 | 32787 | 32787 | base/5/32787
pg_toast_32782_index | 32788 | 32788 | base/5/32788
large_table | 32782 | 32782 | base/5/32782
large_table | 32782 | 32782 | base/5/32782.1
large_table | 32782 | 32782 | base/5/32782.2
large_table | 32782 | 32782 | base/5/32782.3
large_table_pkey | 32789 | 32789 | base/5/32789
pg_statistic | 2619 | 2619 | base/5/2619
...
-- The first scan, no file is missing.
postgres=# SELECT * from pg_fsck_find_missing_relfilenodes();
relname | reloid | relfilenode | filepath
---------+--------+-------------+----------
(0 rows)
-- We removed 32771 and 32782.3, scan again.
postgres=# SELECT * from pg_fsck_find_missing_relfilenodes();
relname | reloid | relfilenode | filepath
------------------+--------+-------------+-----------------------------------------
test_table_in_ts | 32771 | 32771 | pg_tblspc/32769/PG_18_202502212/5/32771
large_table | 32782 | 32782 | base/5/32782.3
(2 rows)For example of pg_fsck_find_extra_relfilenodes,
postgres=# CREATE EXTENSION pg_fsck;
CREATE EXTENSION
-- The first scan, no extra file.
postgres=# select * from pg_fsck_find_extra_relfilenodes();
relname | reloid | relfilenode | spcname | filepath
---------+--------+-------------+---------+----------
(0 rows)
-- After creating extra files in the default tblspace, global tblspace and a new tblspace.
postgres=# select * from pg_fsck_find_extra_relfilenodes();
filepath
----------------------------------------
pg_tblspc/16389/PG_18_202503071/5/5555
base/5/100001
global/1111_fsm
(3 rows)For example of pg_fsck_identify_file,
postgres=# SELECT * FROM pg_fsck_identify_file(pg_relation_filepath('t1'));
relname | reloid | relfilenode | fork | segment
---------+--------+-------------+------+---------
t1 | 16582 | 16582 | main | 0
(1 row)
-- Fork suffixes and segment numbers are parsed out:
postgres=# SELECT * FROM pg_fsck_identify_file('base/5/16582_fsm');
relname | reloid | relfilenode | fork | segment
---------+--------+-------------+------+---------
t1 | 16582 | 16582 | fsm | 0
(1 row)For example of pg_fsck_file_summary,
postgres=# SELECT * FROM pg_fsck_file_summary() WHERE dimension IN ('fork','total') ORDER BY dimension, label;
dimension | label | file_count | total_bytes
-----------+--------------+------------+-------------
fork | main | 294 | 7282688
total | ALL | 294 | 7282688
total | schema_count | 4 |
(3 rows)
-- Use dimension = 'schema' to see which schema is consuming the most space.For example of pg_fsck_check (the flagship multi-section report), in Markdown
on a database with a missing file, a stray file, and an invalid database:
# pg_fsck report
## Environment
- server_version: 20devel
- database: postgres
- block_size: 8192
- segment_size_bytes: 1073741824
- data_checksums: on
- data_directory: /var/lib/pgsql/data
- report_time: 2026-07-29T03:07:07Z
## Summary
- total_files: 294
- total_size: 7048 kB (7217152 bytes)
- schema_count: 4
- errors: 1
- warnings: 3
## Findings
| severity | check | object | filepath | detail |
|---|---|---|---|---|
| error | missing_relfilenode | orders | base/5/16653 | catalog relation orders has no file on disk |
| warning | extra_file | | base/5/8888888 | file on disk is not registered in pg_class |
| warning | extra_file | orders | base/5/16653_fsm | file on disk is not registered in pg_class |
## Invalid databases
| datoid | datname |
|---|---|
| 16661 | half_dropped_db |
## File composition, Temp files ...
Other formats render the same sections: json (default), html, xml. Full
samples of every format are in examples/.
This software is provided under the BSD license. See LICENSE for details.
Report issue on https://github.com/xnervwang/SeafileClientBuildTools, or send email to Xnerv Wang xnervwang@gmail.com.