search

reorg / pg_repack

Reorganize tables in PostgreSQL databases with minimal locks
BSD 3-Clause "New" or "Revised" License
1.85k stars 170 forks source link

How to know pg_repack succeeded or failed in both table mode and index mode #302

Open yinan8128 opened 2 years ago

yinan8128 commented 2 years ago

Hello,

We did some test in pg_repack index mode, found that the exit code are all 0 even failed

  1. Succeeded in index mode -bash-4.2$ /usr/pgsql-11/bin/pg_repack --dbname=testdb1 --table=test --only-indexes --wait-timeout=5 --no-kill-backend INFO: repacking indexes of "test" INFO: repacking index "public.test_x_idx" -bash-4.2$ echo $? 0

  2. Failed in index mode as lock timeout -bash-4.2$ /usr/pgsql-11/bin/pg_repack --dbname=testdb1 --table=test --only-indexes --wait-timeout=5 --no-kill-backend INFO: repacking indexes of "test" INFO: repacking index "public.test_x_idx" WARNING: Error creating index "public"."index_21141": ERROR: canceling statement due to lock timeout WARNING: Skipping index swapping for "test", since no new indexes built INFO: Skipping drop of index_21141 WARNING: repack failed for "test" -bash-4.2$ echo $? 0

  3. Failed in index mode as invalid index exists -bash-4.2$ /usr/pgsql-11/bin/pg_repack --dbname=testdb1 --table=test --only-indexes --wait-timeout=5 --no-kill-backend INFO: repacking indexes of "test" INFO: repacking index "public.test_x_idx" WARNING: Cannot create index "public"."index_22832", already exists DETAIL: An invalid index may have been left behind by a previous pg_repack on the table which was interrupted. Please use DROP INDEX "public"."index_22832" to remove this index and try again. WARNING: skipping invalid index: public.public.index_22832 WARNING: Skipping index swapping for "test", since no new indexes built INFO: Skipping drop of index_22832 INFO: Skipping drop of index_23747 WARNING: repack failed for "test" -bash-4.2$ echo $? 0

We need to use shell to trigger pg_repack, how to know pg_repack succeeded or failed in both table mode and index mode please? Thanks

MichaelDBA commented 1 year ago

What version of pg_repack were you using, @yinan8128 ? and was it OnPrem or cloud PG?

andreasscherbaum commented 1 year ago

It skipped the table in your example, and issued a warning. But overall it succeeded.

What you likely want to do it also catch the entire output and grep for WARNING and ERROR lines.