Re: script to drop and create all indexes in a database with parameter concurrently - Mailing list pgsql-admin

#set variables

export PATH=$HOME/bin:$PATH

#main

 

dbhost=192.168.0.214

database=haier

dbport=5432

schema=public

dbschema=~/tbctemp/dbschema.txt

filtered=~/tbctemp/dbschema_filtered.txt

sql=~/tbctemp/rebuild_indexes.sql

 

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

 

pg_dump -U postgres -s -h "$dbhost" -p $dbport -n $schema  "$database" > "$dbschema"

 

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

#parallel 

tmp_fifofile="/tmp/$.fifo"

mkfifo $tmp_fifofile      

exec 6<>$tmp_fifofile     

rm $tmp_fifofile

thread=2

for ((i=0;i<$thread;i++));do 

echo

done >&6 # 

while read p

do

read -u6 

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

  #  echo $p >> "$sql"

  #else

  {

    name=$(cut -d\  -f4 <<<"${p}")

    drop="DROP INDEX $name;"

    echo $drop >> "$sql"

    echo $p >> "$sql"

        psql -U postgres -h "$dbhost" -p $dbport -d "$database" -c "$drop"

        psql -U postgres -h "$dbhost" -p $dbport -d "$database" -c "$p"

  #fi

    echo >&6

  } &

done < "$filtered"

wait

exec 6>&-

#psql -U postgres -h "$dbhost" -p $dbport -d "$database" -f "$sql"

 

#rm "$dbschema"

#rm "$filtered"

#rm "$sql"

 

 

#end