An extension that provides checking of the integrity of database files, detecting missing or corrupted relfilenode files to ensure storage consistency.
Every push and pull request is built and tested against PostgreSQL 14–17 (PGDG packages, SQL regression tests) and the development branch built from source (regression + TAP tests). See .github/workflows/ci.yml.
pg_fsck targets PostgreSQL 14 and newer. The C functions use server APIs
that stabilized in PG14, and are version-gated with PG_VERSION_NUM where they
differ across majors (for example, the data-checksum predicate is
DataChecksumsEnabled() on PG14–18 and DataChecksumsNeedVerify() on PG19+).
Building against an older major fails fast with a clear #error.
Development and testing are done on PostgreSQL 20devel; older majors are
supported on a best-effort basis (the version gates are in place, but only the
development branch is continuously tested here).
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_check_pgupgrade_leftovers()- detectpg_upgradeversion-directory debris: anyPG_<major>_<catversion>subdirectory under a tablespace location other than the running cluster's (from an aborted upgrade, or an old version dir a--linkupgrade left behind). For each leftover it reports whether its files are hard-linked (nlink > 1) to another directory -- i.e. a--linkupgrade sharing inodes with live data -- so you do not delete it blindly. Detection only.
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, hard-link countnlink, 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; the low 9 bitsmode & 511are the permission bits.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_check_page_headers(rel)- read every block of a relation's main fork and validate the page-header invariants only (boundspd_lower <= pd_upper <= pd_special <= BLCKSZ, page size, layout version, flag bits, and the stored data-page checksum). It does not interpret tuples or line pointers -- that is amcheck's job; this is the deepest into page content pg_fsck goes. Pages are read raw via smgr (bypassing shared buffers) so a corrupt header or checksum is reported rather than making the buffer manager error out first on a checksums-on cluster. Covers indexes too, which amcheck'sverify_heapamdoes not.
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.