script to drop and create all indexes in a database with parameter concurrently

Below is a Linux script I wrote that will drop and create all of the indexes for your database with the "concurrently" parameter.

#!/bin/sh

dbhost=localhost
database=somedatabasename
dbschema=/tempfile/dbschema.txt
filtered=/tempfile/dbschema_filtered.txt
sql=/tempfile/rebuild_indexes.sql

rm "$dbschema"
rm "$filtered"
rm "$sql"

pg_dump -s -h "$dbhost" "$database" > "$dbschema"

grep -e CREATE\ INDEX -e SET\ search_path "$dbschema" | sed 's/CREATE\ INDEX/CREATE\ INDEX\ CONCURRENTLY/g' > "$filtered"

while read p; do

if [[ "$p" == SET* ]]; then

echo $p >> "$sql"

else

name=$(cut -d\ -f4 <<<"${p}")
drop="DROP INDEX $name;"
echo $drop >> "$sql"
echo $p >> "$sql"

fi

done < "$filtered"

psql -U ows -h "$dbhost" -d "$database" -f "$sql"

rm "$dbschema"
rm "$filtered"
rm "$sql"

Thanks,

Lance Campbell<http://illinois.edu/person/lance>
Software Architect
Web Services at Public Affairs
217-333-0382
[University of Illinois at Urbana-Champaign logo]<http://illinois.edu/>