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

Поиск
Список
Период
Сортировка
От Campbell, Lance
Тема script to drop and create all indexes in a database with parameter concurrently
Дата
Msg-id B75CD08C73BD3543B97E4EF3964B7D701F65D459@CITESMBX1.ad.uillinois.edu
обсуждение исходный текст
Ответы Re: script to drop and create all indexes in a database with parameter concurrently  ("Doom.zhou" <zzepaigh@gmail.com>)
Re: script to drop and create all indexes in a database with parameter concurrently  (reiner peterke <zedaardv@gmail.com>)
Список pgsql-admin

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

University of Illinois at Urbana-Champaign logo

 

 

Вложения

В списке pgsql-admin по дате отправления:

Предыдущее
От: Matheus de Oliveira
Дата:
Сообщение: Re: shmmax 9.3
Следующее
От: "Doom.zhou"
Дата:
Сообщение: Re: Notice about psql !