i wanted to give a few thoughts regarding your script. I don't know your intended use, but generally indexes are not something that should be rebuilt over an entire database. I can not think of a scenario where I would need to do this. Building indexes takes system resources and should never be done blindly.
If this is a production environment, dropping the indexes before rebuilding them would be horrible for system performance. A better approach would be to identify only those indexes that actually require a rebuild.
When rebuilding indexes, I create the new index first so there is always an index for queries needing them, then I drop and rename the index in a transaction as the last step.
reiner
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
Software Architect
Web Services at Public Affairs
217-333-0382
<image002.png>
