Recent release of new versions of PostgreSQL suggests that you do reindex of all indexes. But this will take a while, and since we don't actually have ‘REINDEX CONCURRENTLY' command – it's a bit tricky.
So, since I will be doing this on several databases, decided to write a script that will handle the work for me.
The script is written to be ran using psql, and it generates file (/tmp/upgrade_reindex.sql) that will do actuall reindexing.
It is not perfect, there are two known issues with it:
- It doesn't handle indexes used by exclusion constraints – reason is very simple – you can do “ALTER TABLE ADD PRIMARY KEY USING INDEX", or “… ADD UNIQUE USING INDEX", but you can't do so with exclusion constraints
- While index generation is not locking (using CONCURRENTLY), there are ALTER commands that can lock – they take just a short time, but getting index might take a while in environment with lots of activity
First thing – I can't do much about. Seconds – well, it's possible to handle using trick that pg_reorg is using (short statement_timeout, lock, long statement_timeout, alter – all in loop to retry on error with statement_timeout), but I haven't got yet to do it.
Lastly – it doesn't touch indexes in pg_catalog or information_schema – why? I don't like touching catalog. And if I'd have to (which looks like I might need to), I would just do it using plain old “REINDEX" – these indexes are usually very small, so it shouldn't be a problem.
SET search_path = pg_catalog; \set QUIET \pset format unaligned \pset tuples_only on \set output_file /tmp/upgrade_reindex.sql \o :output_file SELECT E'\\set ON_ERROR_STOP 1'; WITH base_info AS ( SELECT pg_get_indexdef(i.indexrelid) as index_create, format( 'DROP INDEX %I.%I', n.nspname, c.relname) as index_drop, pg_get_constraintdef(con.oid) as constraint_create, case when con.conname IS NOT NULL THEN format( 'ALTER TABLE %I.%I DROP CONSTRAINT %I', tn.nspname, t.relname, con.conname ) ELSE NULL::TEXT END as constraint_drop, c.relname as index_name, n.nspname as index_namespace, t.relname as table_name, con.contype as constraint_type FROM pg_index i join pg_class c on i.indexrelid = c.oid join pg_namespace n on c.relnamespace = n.oid left outer join pg_constraint con on i.indexrelid = con.conindid AND con.contype in ('p', 'u', 'x') left outer join pg_class t on con.conrelid = t.oid left outer join pg_namespace tn on t.relnamespace = tn.oid WHERE n.nspname !~ '^(pg_|information_schema)' ), cmds as ( SELECT constraint_type, index_create || coalesce( ' [ constraint: ' || constraint_create || ']', '' ) as cmd_comment, case when constraint_type = 'x' THEN E'\\echo ' ELSE '' END as prefix, regexp_replace( index_create, ' INDEX ' || quote_ident( index_name ) || ' ', ' INDEX CONCURRENTLY __upgrade_reindex ' ) as cmd_create, coalesce( constraint_drop, index_drop) as cmd_drop, format( 'ALTER INDEX %I.%I RENAME TO %I', index_namespace, '__upgrade_reindex', index_name ) as cmd_rename, case when constraint_type = 'p' THEN format( 'ALTER TABLE %I.%I ADD PRIMARY KEY USING INDEX %I', index_namespace, table_name, index_name ) when constraint_type = 'u' THEN format( 'ALTER TABLE %I.%I ADD UNIQUE USING INDEX %I', index_namespace, table_name, index_name ) ELSE NULL::TEXT END as cmd_conadd FROM base_info ) SELECT case when prefix = '' THEN '' ELSE E'\\echo This cannot be reindexed concurrently, because it is used by exclusion constraint\n' END || prefix || '-- ' || cmd_comment || E'\n' || prefix || cmd_create || E';\n' || prefix || cmd_drop || E';\n' || prefix || cmd_rename || E';\n' || coalesce( prefix || cmd_conadd || E';\n', '' ) FROM cmds ; \o \echo 'Just type: \\i' :output_file