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

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 variablesexport
> PATH=$HOME/bin:$PATH#main dbhost=192.168.0.214database=haierdbport=5432schema=publicdbschema=~/tbctemp/dbschema.txtfiltered=~/tbctemp/dbschema_filtered.txtsql=~/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_fifofilethread=2for
> ((i=0;i<$thread;i++));do echodone >&6 # while read pdoread -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"waitexec 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(at)gmail(dot)com> wrote:

>
>
>
> On Wed, Dec 18, 2013 at 4:24 AM, 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"
>>
>>
>>
> Great script !
> i think you can add parallel perform each couple (drop & create index). ^^
>