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@xxxxxxxxxx] Sent: Wednesday, December 18, 2013 9:04 AM To: Doom.zhou Cc: Campbell, Lance; pgsql-admin@xxxxxxxxxxxxxx Subject: Re: 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@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). ^^ > > -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
