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

["Doom.zhou" <zzepaigh@xxxxxxxxx>]

oh my fault !!it's horrible

ERROR:  deadlock detected

DETAIL:  Process 10504 waits for ShareUpdateExclusiveLock on relation 23298 of database 16407; blocked by process 10502.

        Process 10502 waits for ShareLock on virtual transaction 2/6981; blocked by process 10504.

        Process 10504: CREATE INDEX CONCURRENTLY t_ems_log_opt_time_idx ON t_ems_log USING btree (opt_time);

        Process 10502: CREATE INDEX CONCURRENTLY t_ems_log_create_by_idx ON t_ems_log USING btree (create_by);

HINT:  See server log for query details.

STATEMENT:  CREATE INDEX CONCURRENTLY t_ems_log_opt_time_idx ON t_ems_log USING btree (opt_time);

parallel feature script 

#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

On Wed, Dec 18, 2013 at 2:19 PM, Doom.zhou <zzepaigh@xxxxxxxxx> wrote:

On Wed, Dec 18, 2013 at 4:24 AM, Campbell, Lance <lance@xxxxxxxxxxxx> 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"

 Great script !  

i think you can add parallel perform each couple (drop & create index).  ^^