Excerpts from Gareth.Williams's message of mar jun 01 02:44:35 -0400 2010: > Hi, > > We want to reindex the database behind a production service without > interrupting the service. > > I had an idea for creating the index with a new name then dropping the > existing index and renaming the new one - and it seems to work and would > reduce the time without an index to be minimal. I tried: > psql -d ICAT -c 'create unique index concurrently tmp_idx_objt_access1 > on R_OBJT_ACCESS (object_id,user_id);' > # would check if that worked before proceeding # > psql -d ICAT -c 'drop index idx_objt_access1;' > psql -d ICAT -c 'alter index tmp_idx_objt_access1 rename to idx_objt_access1;'
Note that you should wait until the new index becomes usable before deleting the old one; otherwise you could have an intermediate period during which you have no index. IIRC a concurrently created index does not become usable until the oldest transaction that was current when index creation started has finished (IOW the transaction in pg_index.indcheckxmin has gone). -- Álvaro Herrera <alvhe...@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
