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

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

From
"Campbell, Lance"
Date:

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

 

 

Attachment



On Wed, Dec 18, 2013 at 4:24 AM, Campbell, Lance <lance@illinois.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).  ^^
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@gmail.com> wrote:



On Wed, Dec 18, 2013 at 4:24 AM, Campbell, Lance <lance@illinois.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).  ^^

Why don't you simply use a bash script that:
1. gets list of all indexes in a file with prefix 'DROP INDEX...<index_name'
2. Since now each line is a command, input this file to psql.

On Wed, Dec 18, 2013 at 03:43:01PM +0800, Doom.zhou wrote:
> 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@gmail.com> wrote:
>
> >
> >
> >
> > On Wed, Dec 18, 2013 at 4:24 AM, Campbell, Lance <lance@illinois.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).  ^^
> >


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

From
reiner peterke
Date:
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@illinois.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>

 

 

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

From
"Campbell, Lance"
Date:
Comment:
Why don't you simply use a bash script that:
1. gets list of all indexes in a file with prefix 'DROP INDEX...<index_name'
2. Since now each line is a command, input this file to psql.

I create all of my objects inside "schemas".  As a result that won't quite work because you need to set the search_path
priorto doing the drop and create.  The script works for both those people that just use the public "schema" and those
thatuse many "schema".  By "schema" I am referring to the name space that the database objects are built in.
 

Thanks,

Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382 



-----Original Message-----
From: Payal Singh [mailto:payal@omniti.com] 
Sent: Wednesday, December 18, 2013 9:04 AM
To: Doom.zhou
Cc: Campbell, Lance; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] script to drop and create all indexes in a database with parameter concurrently

Why don't you simply use a bash script that:
1. gets list of all indexes in a file with prefix 'DROP INDEX...<index_name'
2. Since now each line is a command, input this file to psql.

On Wed, Dec 18, 2013 at 03:43:01PM +0800, Doom.zhou wrote:
> 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@gmail.com> wrote:
> 
> >
> >
> >
> > On Wed, Dec 18, 2013 at 4:24 AM, Campbell, Lance <lance@illinois.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).  ^^
> >

not true. you can use something like --- select 'drop index '||schemaname||'.'||indexname from pg_indexes;

It will return all <schema.index>

Payal Singh,
OmniTi Computer Consulting Inc.
Junior Database Architect,
Phone: 240.646.0770 x 253


On Wed, Dec 18, 2013 at 3:02 PM, Campbell, Lance <lance@illinois.edu> wrote:
Comment:
Why don't you simply use a bash script that:
1. gets list of all indexes in a file with prefix 'DROP INDEX...<index_name'
2. Since now each line is a command, input this file to psql.

I create all of my objects inside "schemas".  As a result that won't quite work because you need to set the search_path prior to doing the drop and create.  The script works for both those people that just use the public "schema" and those that use many "schema".  By "schema" I am referring to the name space that the database objects are built in.

Thanks,

Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382



-----Original Message-----
From: Payal Singh [mailto:payal@omniti.com]
Sent: Wednesday, December 18, 2013 9:04 AM
To: Doom.zhou
Cc: Campbell, Lance; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] script to drop and create all indexes in a database with parameter concurrently

Why don't you simply use a bash script that:
1. gets list of all indexes in a file with prefix 'DROP INDEX...<index_name'
2. Since now each line is a command, input this file to psql.

On Wed, Dec 18, 2013 at 03:43:01PM +0800, Doom.zhou wrote:
> 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@gmail.com> wrote:
>
> >
> >
> >
> > On Wed, Dec 18, 2013 at 4:24 AM, Campbell, Lance <lance@illinois.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).  ^^
> >