Pg Reindex
Console utility for gracefully rebuild indexes/pkeys for PostgreSQL, with minimal locking in semi-auto mode.
Install:
$ gem install pg_reindex
Using:
export PGRE_CFG=/some/path/database.yml
pgre --help
User in connection should be an owner of relations. And for rebuild pkey, should be superuser.
Tasks:
pgre dbs # Show list of databases from database.yml
pgre install ENV # Install function swap_for_pkey to database
pgre rebuild ENV (table|index)[,(table|index)] # rebuild tables,indexes,pkeys
pgre tables ENV # Show tables of database
Examples:
# Show tables with indexes, which full size more than 1Gb
pgre tables production -s 1000
# Show process without really rebuild
pgre rebuild production users,some_index1,some_index2
# Rebuild indexes
pgre rebuild production users --write
# Rebuild indexes and no ask confirmation (not recommended)
pgre rebuild production users --write --no-ask
Explanation/Warning:
Rebuild index produces sqls:
1. CREATE INDEX CONCURRENTLY bla2 on some_table USING btree (some_field);
2. ANALYZE some_table;
3. DROP INDEX bla;
4. ALTER INDEX bla2 RENAME TO bla;
2 can be blocked by long running query(LRQ), or autovacuum (in this case just kill autovacuum or wait LRQ). By careful, if between 2 and (3,4) started LRQ or autovacuum, (3,4) can blocks all queries on this table. If this happens, and (3,4) not quit after < 30s, should stop (3,4) by cancel query in PostgreSQL. And execute (3,4) manually.
Rebuild pkey produces sqls:
1. CREATE UNIQUE INDEX CONCURRENTLY some_table_pkey2 on some_table USING btree (ID);
2. ANALYZE some_table;
3. SELECT swap_for_pkey('public', 'some_table_pkey', 'some_table_pkey2');
Same issue with 2 and 3.
MIT-LICENCE