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

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

On 17 dec 2013, at 21:24, "Campbell, Lance" <lance(at)illinois(dot)edu> wrote:

> 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>
>
>