Check PostgreSQL databases for corruption with pg_amcheck

You will run a read-only corruption check against a PostgreSQL database, narrow the scope when needed, and recognise the options that can block writes. Allow 10 to 20 minutes for a first targeted check, plus longer for a large database. The examples use 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 then check its version. 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. The normal command 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 values with real connection details. --password forces a prompt before connecting; without it, the client prompts automatically when authentication requires a password. Do not put a real password in a shell command or article script. 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 that 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. This 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 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 changes that case into a warning.

5. Add useful 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; 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, 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.

For table checks, --exclude-toast-pointers skips validation of pointers into TOAST data. That can save time, but it removes a corruption check. --skip=all-visible or --skip=all-frozen skips marked pages; no pages are skipped by default. Use --startblock and --endblock only for a deliberately targeted table investigation.

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 command, client version, database, relation name and server logs. Avoid writes to the affected object until the database owner has assessed the evidence. --on-error-stop limits table processing after corruption is found on the first affected page, while index checking already stops after its first corrupt page.

Do not repair an index, rewrite a table or restore a backup as an automatic response to this command. Those actions change state and can destroy evidence. Use the PostgreSQL incident process, take an approved backup or snapshot if required, and test any repair on a copy first.