Check PostgreSQL Databases for Corruption with pg_amcheck

pg_amcheck runs a read-only corruption check against a PostgreSQL database, using the amcheck extension without touching a single row. You will narrow the scope when needed and learn which options can block writes. Allow 10 to 20 minutes for a first targeted check, plus longer for a large database, using the installed PostgreSQL 16.15 client on this machine.

1. Confirm the installed client

pg_amcheck is a client utility that runs the amcheck extension's checks against one or more databases. It checks ordinary and TOAST tables, materialised views, sequences and B-tree indexes; other relation types are silently skipped. This installation provides the binary in PostgreSQL's versioned directory, so it is not on the default PATH here.

$ PG_AMCHECK=/usr/lib/postgresql/16/bin/pg_amcheck
$ "$PG_AMCHECK" --version
pg_amcheck (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

Keep PG_AMCHECK set for the rest of this guide. On another host, use command -v pg_amcheck and check its version instead. The local manual says this utility is designed for PostgreSQL 14.0 and later.

2. Check one database first

Use a database account that can connect and read the objects you intend to check. This does not need sudo, and it does not change table data:

$ "$PG_AMCHECK" --host=127.0.0.1 --port=5432 --username=CHECK_USER --password CHECK_DATABASE
Password:
$ printf 'exit status: %s\n' "$?"
exit status: 0

• Replace the uppercase placeholders with real connection details.
• Do not put a real password in a shell command or script. --password forces a prompt before connecting; without it, the client prompts automatically when authentication requires one. Use a suitably protected .pgpass file for unattended work, or omit --password when another approved authentication method is already configured.

A successful exit status means the checks completed without reporting a corruption error. It does not mean every relation type was checked, or that a future check cannot find a problem.

3. Make the scope explicit

A bare database name selects one database. Alternatively, --all checks every database except those excluded, while --database=PATTERN selects matching databases; the pattern options can be repeated.

$ "$PG_AMCHECK" --all --exclude-database='template*' --maintenance-db=postgres \
    --jobs=2 --progress --verbose \
    --host=127.0.0.1 --port=5432 --username=CHECK_USER --password
Password:

When database selection is needed, --maintenance-db supplies the database used to discover the list; if omitted, the client tries postgres and then template1. A database name on the command line must not be combined with other database selection options.

Patterns are shell-sensitive: quote them so the shell does not expand wildcard characters before pg_amcheck sees them. A table pattern such as app.* selects tables in a matching schema. A database-qualified, three-part pattern such as sales.*.orders* can also add matching databases to the check list.

4. Check a table without losing its related checks

By default, checking a table also checks its B-tree indexes and TOAST table. That catches useful dependent structures, but it can make a focused check broader than the command appears. Suppress those dependent checks only when you have a reason:

$ "$PG_AMCHECK" --table='public.orders' \
    --no-dependent-indexes --no-dependent-toast \
    --host=127.0.0.1 --port=5432 --username=CHECK_USER CHECK_DATABASE

Use --index='public.orders_*' for indexes only, or --schema='public' for tables and indexes in a schema. --relation is broader than --table, covering all supported relation types. If a table, index or relation pattern matches nothing, that is normally a fatal error; add --no-strict-names only when an unmatched pattern is expected, because it turns that case into a warning instead.

5. Add diagnostics and parallelism

The default is one server connection. --jobs=4 allows up to four concurrent connections, limited by the number of objects to check. Start with a small value and watch the server, especially on a busy system. --progress reports completed and total relation counts and sizes; --verbose prints each relation and more detail for server errors.

$ "$PG_AMCHECK" --database='app_*' --exclude-database='app_test' \
    --jobs=4 --progress --verbose \
    --host=127.0.0.1 --port=5432 --username=CHECK_USER \
    --maintenance-db=postgres CHECK_DATABASE

Do not combine a positional database name with --database or --all. If you need to see the SQL sent to the server, add --echo, and treat that output as potentially revealing database object names and query details.

6. Treat stronger index checks as a maintenance operation

• --rootdescend also enables --parent-check. It can use substantially more resources and may be of limited value for corruption seen in practice. The lower-impact default uses the ordinary B-tree check.
• --heapallindexed is another additional index check and may take longer on large indexes.
• --exclude-toast-pointers skips validation of pointers into TOAST data for table checks. That saves time, but it removes a corruption check.
• --skip=all-visible or --skip=all-frozen skips marked pages; no pages are skipped by default.
• --startblock and --endblock are for a deliberately targeted table investigation only.

7. Handle missing amcheck carefully

The check requires the amcheck extension. If it is missing, --install-missing can install the required extension objects, placing them in pg_catalog by default or in a schema supplied as --install-missing=SCHEMA.

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'amcheck';

If an administrator approves the installation, run the same connection command with --install-missing, then repeat the check. To undo an extension installation, the database owner can use DROP EXTENSION amcheck; only after confirming that no other process depends on it. That command removes extension-owned objects and is not a routine recovery step.

8. Record and investigate a failure

A non-zero exit status or a corruption message is a finding to preserve, not a reason to rerun repeatedly with stronger flags.

• Save the evidence. Record the command, client version, database, relation name and server logs.
• Avoid writes to the affected object until the database owner has assessed the evidence.
• Know where checks stop. --on-error-stop limits table processing after corruption is found on the first affected page; index checking already stops after its first corrupt page.