#!/bin/bash if (( $# != 3 )); then echo "Usage: $(basename $0) <username> <db-host> <db-name>" exit 1 fi PSQL_USER=${1} PSQL_HOST=${2} PSQL_DB=${3} PSQL="psql --no-align --tuples-only --log-file $(hostname).log --no-psqlrc -U ${PSQL_USER} -h ${PSQL_HOST} -d ${PSQL_DB}" #Check the connection and permissions pg_version=$(${PSQL} -c "select version();" 2>/dev/null) if (( $? != 0 )); then echo "Error connecting to database: ${PSQL_USER}@${PSQL_HOST} (${PSQL_DB})" exit 1 fi echo ${pg_version} pg_class_permissions=$(${PSQL} -c "select has_table_privilege('pg_catalog.pg_class', 'update');") if [[ $pg_class_permissions != t ]]; then echo "Error, user ${PSQL_USER} cannot update pg_class in order to live update primary keys. Exiting..." exit 1 fi #Create procedures that generate the sql to rebuild indexes ${PSQL} 1>&2 <<\EOSQL -- Following two functions are used to re-index a primary key. -- Use as follows: -- select rebuild_pkey_index('public','test'); -- Then execute the SQL it generates. CREATE OR REPLACE FUNCTION swap_for_pkey(text,text,text) returns integer AS $$ DECLARE cmd text; oid1 integer; oid2 integer; filenode1 integer; filenode2 integer; relation text; BEGIN select oid::integer into oid1 from pg_class where relname=$2 and relnamespace = (select oid from pg_namespace where nspname=$1); RAISE NOTICE 'PKEY OID: %',oid1; select relfilenode::integer into filenode1 from pg_class where oid=oid1; select oid::integer into oid2 from pg_class where relname=$3 and relnamespace = (select oid from pg_namespace where nspname=$1); RAISE NOTICE 'PKEY OID: %',oid2; select relfilenode::integer into filenode2 from pg_class where oid=oid2; select (indrelid::regclass)::text into relation from pg_index where indexrelid=oid1; RAISE NOTICE 'RELATION NAME: %',relation; cmd:='LOCK '||relation||';'; RAISE NOTICE 'Executing :- %',cmd; Execute cmd; cmd:='UPDATE pg_class SET relfilenode='||filenode2|| ' WHERE oid='||oid1||';'; RAISE NOTICE 'Executing :- %',cmd; Execute cmd; cmd:='UPDATE pg_class SET relfilenode='||filenode1|| ' WHERE oid='||oid2||';'; RAISE NOTICE 'Executing :- %',cmd; Execute cmd; cmd:='DROP INDEX '||$1||'.'||$3||';'; RAISE NOTICE 'Executing :- %',cmd; Execute cmd; return 0; END; $$language plpgsql; CREATE OR REPLACE Function rebuild_pkey_index(text,text) returns setof text AS $$ DECLARE reloid integer; cmd text; rec record; oid1 integer; oid2 integer; filenode1 integer; filenode2 integer; relname1 text; BEGIN select oid into reloid from pg_class where relname=$2 and relnamespace=(select oid from pg_namespace where nspname=$1); for rec in SELECT c2.relname as indexname, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),0, position( 'INDEX'in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true))+6) ||'CONCURRENTLY '|| c2.relname||'_new '|| substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),position( 'ON 'in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)))||';' as command, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) from ' ON (.*) USING') as table FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c2.oid=reloid AND c.oid = i.indrelid AND i.indexrelid = c2.oid and i.indisprimary=true ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname LOOP return next rec.command; cmd:= 'ANALYZE VERBOSE '||rec.table||';'; return next cmd; cmd:='SELECT swap_for_pkey('||''''||$1||''''||','||''''||rec.indexname||''''||','||''''||rec.indexname||'_new'||''''||');'; return next cmd; END LOOP; END; $$language plpgsql; -- Following function will re-index a non-primary key. -- Use as follows: -- select rebuild_nonpkey_index('public','test'); -- Then execute the SQL it generates. CREATE OR REPLACE Function rebuild_nonpkey_index(text,text) returns setof text AS $$ DECLARE reloid integer; cmd text; rec record; BEGIN select oid into reloid from pg_class where relname=$2 and relnamespace=(select oid from pg_namespace where nspname=$1); for rec in SELECT c2.relname as indexname, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),0, position( 'INDEX'in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true))+6) ||'CONCURRENTLY '|| c2.relname||'_new '|| substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),position( 'ON 'in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)))||';' as command, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) from ' ON (.*) USING') as table FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c2.oid=reloid AND c.oid = i.indrelid AND i.indexrelid = c2.oid and i.indisprimary=false ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname LOOP return next rec.command; cmd:= 'ANALYZE VERBOSE '||rec.table||';'; return next cmd; cmd:= 'DROP INDEX '||rec.indexname||';'; return next cmd; cmd:='ALTER INDEX '||rec.indexname||'_new'||' RENAME TO '||rec.indexname||';'; return next cmd; END LOOP; END; $$language plpgsql; --Create stored procs to iterate over all indexes and run procedures above. CREATE OR REPLACE Function generate_rebuild_pkey_index(text) returns setof text AS $$ DECLARE cmd text; rec record; BEGIN for rec in SELECT s.indexrelname as index_name from pg_stat_user_indexes s join pg_index i on i.indexrelid=s.indexrelid where i.indisprimary=true and s.schemaname='public' order by s.relname, s.indexrelname LOOP cmd:= 'SELECT rebuild_pkey_index(''public'', ''' || rec.index_name || ''');'; return next cmd; END LOOP; END; $$language plpgsql; CREATE OR REPLACE Function generate_rebuild_unique_nonpkey_index(text) returns setof text AS $$ DECLARE cmd text; rec record; BEGIN for rec in SELECT s.indexrelname as index_name from pg_stat_user_indexes s join pg_index i on i.indexrelid=s.indexrelid where i.indisunique=true and i.indisprimary=false and s.schemaname='public' order by s.relname, s.indexrelname LOOP cmd:= 'SELECT rebuild_nonpkey_index(''public'', ''' || rec.index_name || ''');'; return next cmd; END LOOP; END; $$language plpgsql; CREATE OR REPLACE Function generate_rebuild_nonunique_nonpkey_index(text) returns setof text AS $$ DECLARE cmd text; rec record; BEGIN for rec in SELECT s.indexrelname as index_name from pg_stat_user_indexes s join pg_index i on i.indexrelid=s.indexrelid where i.indisunique=false and i.indisprimary=false and s.schemaname='public' order by s.relname, s.indexrelname LOOP cmd:= 'SELECT rebuild_nonpkey_index(''public'', ''' || rec.index_name || ''');'; return next cmd; END LOOP; END; $$language plpgsql; EOSQL if (( $? != 0 )); then echo "Error creating stored procedures. Exiting..." exit 1 fi #Execute the stored procs that generate the sql to run generate_reindex_sql=$(${PSQL} <<\EOSQL select generate_rebuild_pkey_index('public'); -- Do not run rebuild on unique non-primary key indexes (some may be constraints which -- this code does not cope with yet) --select generate_rebuild_unique_nonpkey_index('public'); select generate_rebuild_nonunique_nonpkey_index('public'); EOSQL ) reindex_sql=$(echo $generate_reindex_sql | ${PSQL}) if (( $? != 0 )); then echo "Error generating reindex sql. Exiting..." exit 1 fi #Execute the sql that will reindex everything echo $reindex_sql | ${PSQL} if (( $? != 0 )); then echo "Error executing stored procedures. Exiting..." exit 1 fi #Cleanup all the functions ${PSQL} 1>&2 <<\EOSQL drop function swap_for_pkey(text,text,text); drop function rebuild_pkey_index(text,text); drop function rebuild_nonpkey_index(text,text); drop function generate_rebuild_pkey_index(text); drop function generate_rebuild_unique_nonpkey_index(text); drop function generate_rebuild_nonunique_nonpkey_index(text); EOSQL if (( $? != 0 )); then echo "Error dropping stored procedures. Exiting..." exit 1 fi