I'm running amcheck on a set of indices (test machine, not prod) and want to
track the progress. Is there a SELECT clause that makes rows display as
they are created, or do I have to explicitly call bt_index_check() from a
shell script or SQL function in order to see the output as each index is
checked?
postgres=# select version();
version
------------------------------------------------------
PostgreSQL 9.6.18 on [snip] (Red Hat 4.4.7-23), 64-bit
(1 row)
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------+---------+------------+--------------------------------------------
amcheck_next | 2 | public | functions for verifying relation integrity
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
#!/bin/bash
echo `date +"%F %T, %a"` Starting
psql CDSLBXW -c \
"SELECT clock_timestamp(),
bt_index_check(c.oid, i.indisunique),
n.nspname,
c.relname ,
c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree'
AND c.relpersistence != 't'
AND c.relkind = 'i'
AND i.indisready
AND i.indisvalid
ORDER BY c.relpages desc
;"
echo `date +"%F %T, %a"` Finished
--
Angular momentum makes the world go 'round.
