Postgres has very useful utilities, and these utilities play a major role in Postgres to solve the main issues faced by users day by day, such as high availability, data corruption-related issues, etc. pg_amcheck is a Postgres utility to check the correctness of database objects like tables, indexes, toast tables, etc. Also, in each utility, we have used many more flags, and these flags provide different types of usage of this tool. In compiled Postgres, the utilities exist inside the binary folder like this.
cybrosys@cybrosys:~/postgres_19_rc1/postgres/bin$ ls
Result:
clusterdb dropdb initdb pg_archivecleanup pg_checksums pg_controldata pg_dump pg_receivewal pg_restore pg_test_timing pg_waldump psql vacuumlo
createdb dropuser oid2name pg_basebackup pg_combinebackup pg_createsubscriber pg_dumpall pg_recvlogical pg_rewind pg_upgrade pg_walsummary reindexdb
createuser ecpg pg_amcheck pgbench pg_config pg_ctl pg_isready pg_resetwal pg_test_fsync pg_verifybackup postgres vacuumdb
Checking the version of the pg_amcheck utility.
pg_amcheck --version
Result:
pg_amcheck (PostgreSQL) 18.6 (Ubuntu 18.6-1.pgdg22.04+2)
Use the –help flag to explore more about the available options we can use with the pg_am utility.
pg_amcheck --help
Result:
pg_amcheck checks objects in a PostgreSQL database for corruption.
Usage:
pg_amcheck [OPTION]... [DBNAME]
Target options:
-a, --all check all databases
-d, --database=PATTERN check matching database(s)
-D, --exclude-database=PATTERN do NOT check matching database(s)
-i, --index=PATTERN check matching index(es)
-I, --exclude-index=PATTERN do NOT check matching index(es)
-r, --relation=PATTERN check matching relation(s)
-R, --exclude-relation=PATTERN do NOT check matching relation(s)
-s, --schema=PATTERN check matching schema(s)
-S, --exclude-schema=PATTERN do NOT check matching schema(s)
-t, --table=PATTERN check matching table(s)
-T, --exclude-table=PATTERN do NOT check matching table(s)
--no-dependent-indexes do NOT expand list of relations to include indexes
--no-dependent-toast do NOT expand list of relations to include TOAST tables
--no-strict-names do NOT require patterns to match objects
Table checking options:
--exclude-toast-pointers do NOT follow relation TOAST pointers
--on-error-stop stop checking at end of first corrupt page
--skip=OPTION do NOT check "all-frozen" or "all-visible" blocks
--startblock=BLOCK begin checking table(s) at the given block number
--endblock=BLOCK check table(s) only up to the given block number
B-tree index checking options:
--checkunique check unique constraint if index is unique
--heapallindexed check that all heap tuples are found within indexes
--parent-check check index parent/child relationships
--rootdescend search from root page to refind tuples
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-w, --no-password never prompt for password
-W, --password force password prompt
--maintenance-db=DBNAME alternate maintenance database
Other options:
-e, --echo show the commands being sent to the server
-j, --jobs=NUM use this many concurrent connections to the server
-P, --progress show progress information
-v, --verbose write a lot of output
-V, --version output version information, then exit
--install-missing install missing extensions
-?, --help show this help, then exit
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
Usage of pg_amcheck utility examples
1. Run amcheck on a specific database
pg_amcheck -d odoo_17 -p 5432 -h localhost -v
Result :
pg_amcheck: including database "odoo_17"
pg_amcheck: in database "odoo_17": using amcheck version "1.5" in schema "public"
pg_amcheck: checking heap table "odoo_17.public.sale_order_old"
pg_amcheck: checking heap table "odoo_17.pg_catalog.pg_attribute"
pg_amcheck: checking heap table "odoo_17.public.ir_model_data"
pg_amcheck: checking btree index "odoo_17.public.ir_model_data_module_name_uniq_index"
pg_amcheck: checking heap table "odoo_17.pg_toast.pg_toast_40463"
pg_amcheck: checking heap table "odoo_17.pg_toast.pg_toast_40479"
pg_amcheck: checking heap table "odoo_17.public.sale_order_y2027"
pg_amcheck: checking heap table "odoo_17.pg_toast.pg_toast_40495"
pg_amcheck: checking heap table "odoo_17.public.sale_order_default"
pg_amcheck: checking heap table "odoo_17.pg_toast.pg_toast_40511"
Here we can specify the database name by using the -d flag. The -p flag indicates the port of the Postgres where the pg_amcheck will run.
The -h specifies the host type, and -v exeuctes the pg_amcheck utility in verbose mode.
2. Run amcheck on all databases
pg_amcheck --all -p 5432 -h localhost
Result :
Password:
pg_amcheck: warning: skipping database "template1": amcheck is not installed
Here the - - all flag will execute the pg_amcheck in all databases, and it will check whether the extension named amcheck is installed or not.
It checks whether the amcheck extension is installed in all databases and displays a warning if it is not available.
So ensure that the amcheck extension is installed correctly on the pg_amcheck running database.
3. amcheck on specific tables
pg_amcheck -t res_users odoo_17 -p 5432 -h localhost -v
Result :
Password:
pg_amcheck: including database "odoo_17"
pg_amcheck: in database "odoo_17": using amcheck version "1.5" in schema "public"
pg_amcheck: checking btree index "odoo_17.public.res_users__partner_id_index"
pg_amcheck: checking heap table "odoo_17.public.res_users"
pg_amcheck: checking btree index "odoo_17.pg_toast.pg_toast_16511_index"
pg_amcheck: checking btree index "odoo_17.public.res_users_pkey"
pg_amcheck: checking heap table "odoo_17.pg_toast.pg_toast_16511"
pg_amcheck: checking btree index "odoo_17.public.res_users_login_key"
We can also specify the specific table name by using the -t flag like this.
It will check the consistency of this table and indexes during the execution of pg_amcheck utility. It will also check the related toast tables.
4. Amcheck in multi tables
pg_amcheck \
-t res_users \
-t res_partner \
odoo_17 -p 5432 -h localhost -v
Result :
Password:
pg_amcheck: including database "odoo_17"
pg_amcheck: in database "odoo_17": using amcheck version "1.5" in schema "public"
pg_amcheck: checking heap table "odoo_17.public.res_partner"
pg_amcheck: checking btree index "odoo_17.public.res_partner_pkey"
pg_amcheck: checking btree index "odoo_17.public.res_partner__name_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__complete_name_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__date_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__parent_id_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__ref_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__vat_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__company_id_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__commercial_partner_id_index"
pg_amcheck: checking btree index "odoo_17.public.res_users__partner_id_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner_phone_partial_tgm"
pg_amcheck: checking heap table "odoo_17.public.res_users"
pg_amcheck: checking btree index "odoo_17.pg_toast.pg_toast_16511_index"
pg_amcheck: checking btree index "odoo_17.public.res_users_pkey"
pg_amcheck: checking btree index "odoo_17.pg_toast.pg_toast_16629_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner_mobile_partial_tgm"
pg_amcheck: checking heap table "odoo_17.pg_toast.pg_toast_16511"
pg_amcheck: checking heap table "odoo_17.pg_toast.pg_toast_16629"
pg_amcheck: checking btree index "odoo_17.public.res_users_login_key"
We can also check the amcheck on multiple tables like this.
5. amcheck on similar named tables
pg_amcheck -t "sale*" odoo_17 -p 5432 -h localhost -v
Result :
Password:
pg_amcheck: including database "odoo_17"
pg_amcheck: in database "odoo_17": using amcheck version "1.5" in schema "public"
pg_amcheck: checking heap table "odoo_17.public.sale_order_old"
pg_amcheck: checking heap table "odoo_17.public.sale_order_y2025"
pg_amcheck: checking btree index "odoo_17.public.sale_order_old_date_order_id_idx"
pg_amcheck: checking heap table "odoo_17.public.sale_order_y2021"
pg_amcheck: checking heap table "odoo_17.public.sale_order_y2022"
pg_amcheck: checking heap table "odoo_17.public.sale_order_y2020"
pg_amcheck: checking heap table "odoo_17.public.sale_order_y2024"
pg_amcheck: checking btree index "odoo_17.public.sale_order_old_pkey"
pg_amcheck: checking btree index "odoo_17.public.sale_order_y2025_pkey"
pg_amcheck: checking btree index "odoo_17.public.sale_order_y2025_date_order_id_idx"
pg_amcheck: checking btree index "odoo_17.public.sale_order_old__create_date_index"
pg_amcheck: checking btree index "odoo_17.public.sale_order_old__partner_id_index"
pg_amcheck: checking btree index "odoo_17.public.sale_order_old__partner_invoice_id_ind
In the same way, we can use the * operator to execute the amcheck on similarly named tables like the one above.
6. Exclude a specific schema
pg_amcheck -s public odoo_17 --exclude-schema=pg_catalog -p 5432 -h localhost -v
Result :
Password:
pg_amcheck: including database "odoo_17"
pg_amcheck: in database "odoo_17": using amcheck version "1.5" in schema "public"
pg_amcheck: checking heap table "odoo_17.public.sale_order_old"
pg_amcheck: checking heap table "odoo_17.public.ir_model_data"
pg_amcheck: checking btree index "odoo_17.public.ir_model_data_module_name_uniq_index"
pg_amcheck: checking heap table "odoo_17.public.ir_module_module"
pg_amcheck: checking heap table "odoo_17.public.ir_ui_view"
pg_amcheck: checking heap table "odoo_17.public.ir_model_fields"
pg_amcheck: checking heap table "odoo_17.public.sale_order_y2025"
pg_amcheck: checking btree index "odoo_17.public.ir_model_data_model_res_id_index"
pg_amcheck: checking btree index "odoo_17.public.sale_order_old_date_order_id_idx"
We can also use the –exclude-schema to exclude a specific schema when executing the amcheck functionality.
7. amcheck on specific relation
pg_amcheck \
-r sale_mv \
odoo_17 -p 5432 -h localhost -v
Result :
Password:
pg_amcheck: including database "odoo_17"
pg_amcheck: in database "odoo_17": using amcheck version "1.5" in schema "public"
pg_amcheck: checking btree index "odoo_17.pg_toast.pg_toast_41380_index"
pg_amcheck: checking heap table "odoo_17.public.sale_mv"
pg_amcheck: checking heap table "odoo_17.pg_toast.pg_toast_41380"
This will include tables, mat views, toast, and partitioned tables.
8. Exclude a specific relation
pg_amcheck --exclude-relation sale_mv odoo_17 -p 5432 -h localhost -v
Result :
Password:
pg_amcheck: including database "odoo_17"
pg_amcheck: in database "odoo_17": using amcheck version "1.5" in schema "public"
pg_amcheck: checking heap table "odoo_17.public.sale_order_old"
pg_amcheck: checking heap table "odoo_17.pg_catalog.pg_attribute"
pg_amcheck: checking heap table "odoo_17.public.ir_model_data"
By using this flag -exclude-relation, we can exclude a specific relation on the amcheck execution like this.
9. amcheck on specific index
pg_amcheck odoo_17 -i product_template_attribute_value__product_tmpl_id_index -p 5432 -h localhost -v -U cybrosys
Result :
Password:
pg_amcheck: including database "odoo_17"
pg_amcheck: in database "odoo_17": using amcheck version "1.5" in schema "public"
pg_amcheck: checking btree index "odoo_17.public.product_template_attribute_value__product_tmpl_id_index"
We can also use the -i flag to only check the correctness of a specific index like this.
10. amcheck on multi indexes
pg_amcheck -i product_template_attribute_value__product_tmpl_id_index -i crm_team_member__user_id_index odoo_17 -p 5432 -h localhost -v
Result:
Password:
pg_amcheck: including database "odoo_17"
pg_amcheck: in database "odoo_17": using amcheck version "1.5" in schema "public"
pg_amcheck: checking btree index "odoo_17.public.product_template_attribute_value__product_tmpl_id_index"
pg_amcheck: checking btree index "odoo_17.public.crm_team_member__user_id_index"
In the same way, we can specify multiple indexes by using the same -i flag like this.
11. Exclude a specific index
pg_amcheck odoo_17 -I product_template_attribute_value__product_tmpl_id_index -p 5432 -h localhost -v -U cybrosys
Result:
Password:
pg_amcheck: including database "odoo_17"
pg_amcheck: in database "odoo_17": using amcheck version "1.5" in schema "public"
pg_amcheck: checking heap table "odoo_17.public.sale_mv"
pg_amcheck: checking heap table "odoo_17.public.sale_order_old"
In the same way, we can also exclude a specific index by using the -I flag, and it will execute the amcheck on all objects in the database except the specified one.
12. Exclude a specific table
pg_amcheck odoo_17 -T res_partner -p 5432 -h localhost -v -U cybrosys
Result:
Password:
pg_amcheck: including database "odoo_17"
pg_amcheck: in database "odoo_17": using amcheck version "1.5" in schema "public"
pg_amcheck: checking heap table "odoo_17.public.sale_mv"
pg_amcheck: checking heap table "odoo_17.public.sale_order_old"
pg_amcheck: checking heap table "odoo_17.pg_catalog.pg_attribute"
pg_amcheck: checking heap table "odoo_17.public.ir_model_data"
pg_amcheck: checking btree index "odoo_17.public.ir_model_data_module_name_uniq_index"
pg_amcheck: checking heap table "odoo_17.public.ir_module_module"
13. Exclude the toast tables based on a specific table.
pg_amcheck odoo_17 -t res_partner --no-dependent-toast -p 5432 -h localhost -v -U cybrosys
Result:
Password:
pg_amcheck: including database "odoo_17"
pg_amcheck: in database "odoo_17": using amcheck version "1.5" in schema "public"
pg_amcheck: checking heap table "odoo_17.public.res_partner"
pg_amcheck: checking btree index "odoo_17.public.res_partner_pkey"
pg_amcheck: checking btree index "odoo_17.public.res_partner__name_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__complete_name_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__date_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__parent_id_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__ref_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__vat_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__company_id_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__commercial_partner_id_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner_phone_partial_tgm"
pg_amcheck: checking btree index "odoo_17.public.res_partner_mobile_partial_tgm"
We can completely avoid the toast tables for checking the correctness by using this flag --no-dependent-toast.
14. Check that all table entries have their related index entries.
pg_amcheck odoo_17 -t res_partner --heapallindexed -p 5432 -h localhost -v -U cybrosys
Result :
Password:
pg_amcheck: including database "odoo_17"
pg_amcheck: in database "odoo_17": using amcheck version "1.5" in schema "public"
pg_amcheck: checking heap table "odoo_17.public.res_partner"
pg_amcheck: checking btree index "odoo_17.public.res_partner_pkey"
pg_amcheck: checking btree index "odoo_17.public.res_partner__name_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__complete_name_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__date_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__parent_id_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__ref_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__vat_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__company_id_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner__commercial_partner_id_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner_phone_partial_tgm"
pg_amcheck: checking btree index "odoo_17.pg_toast.pg_toast_16629_index"
pg_amcheck: checking btree index "odoo_17.public.res_partner_mobile_partial_tgm"
pg_amcheck: checking heap table "odoo_17.pg_toast.pg_toast_16629"
We can also use this flag --heapallindexed to completely check that each table record has its related index entries exist or not.
By using this utility, we can completely ensure that the database object is corrupted or not. We can use this in situations like database migration, database recovery, etc. This only finds issues based on the normal tables, indexes, toast table, and also relations like materialized views, etc.
Also, we can use different types of options to execute the checking of amcheck based on the user's needs.
This utility was first developed in PostgreSQL version 14, and it still exists in the latest version of Postgres 19. It does not repair corruption, and its main purpose is to detect corruption early so corrective actions (such as REINDEX or restoring from backup) can be taken.