Thread: script to drop and build all indexes in database

PostgreSQL:9.2

OS: Linux

Does someone have a script that they would be willing to share that would:

1)      Create a list of all indexes within database X

2)      Loop for each index in list

3)      Inside loop drop index then create index with concurrently

4)      End loop

5)      Exit script

I have been using the command, “REINDEX DATABASE somedatabename” ,  to rebuild all of my indexes on a production database used for web applications.  But I believe while this is running late at night users are experiencing issues when they submit web requests that would write to tables that are being reindexed.  I discovered that reindex does not allow for the use of concurrently as a parameter. 

Suggestion:

It seems like it would be a good idea to add the parameter of concurrently to reindex.   

Thanks so much,

Lance Campbell

Software Architect

Web Services at Public Affairs

217-333-0382