Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42014=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1VtIfq-0001MW-Dz
 for pgsql-admin@arkaria.postgresql.org; Wed, 18 Dec 2013 15:04:34 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42014=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1VtIfp-0006ip-4G
 for pgsql-admin@arkaria.postgresql.org; Wed, 18 Dec 2013 15:04:33 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <payal@omniti.com>) id 1VtIfe-0006Tb-TI
 for pgsql-admin@postgresql.org; Wed, 18 Dec 2013 15:04:22 +0000
Received: from mail-qc0-f174.google.com ([209.85.216.174])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <payal@omniti.com>) id 1VtIfa-0001ad-2U
 for pgsql-admin@postgresql.org; Wed, 18 Dec 2013 15:04:22 +0000
Received: by mail-qc0-f174.google.com with SMTP id n7so6093012qcx.5
 for <pgsql-admin@postgresql.org>; Wed, 18 Dec 2013 07:04:15 -0800 (PST)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20130820;
 h=x-gm-message-state:date:from:to:cc:subject:message-id:references
 :mime-version:content-type:content-disposition
 :content-transfer-encoding:in-reply-to:user-agent;
 bh=DVJ/CUJbCxiLJk71iuiyuvMh53qCkNlXzxJnIBofum0=;
 b=Ux0PgflXOa224ZOMyrWpBzokNe77fScZu59WHrLdHg2mx9V3MhgB5vQfZSOy3io1el
 opmZ4K3rUb3hJsCXbzGFYZgC1upo9eJ9DaigwJ5Ur2zGDwH+/R120/kKtk2lFYdj15UM
 9dTXGIoirYDrxiWMzVLxyNVxQJlxRJGZ2nRqOhL8YzTidW5XJDFuf1EAa/OHGZvSBkOz
 zwTQsZ3PoeeTizFkGwaI5hcgvr4pm/HQtMJ7AnRnylhT7jbo2tDkYVj+VubFj7DYZNfH
 3Qf0x5ZGJbPHKcOP8MVL/2ictL7pWhZar2qbOb/9TcaEu45P88M+/ooFi15gh1QmDg/+
 h3KQ==
X-Gm-Message-State: 
 ALoCoQmmkTg+e+qsIhxak3yT/YwPxJBrA7gpPhiMv3vAKf0IpyTqWhR2z6f4z5idiJT2Edc3t7iY
X-Received: by 10.224.55.69 with SMTP id t5mr54329476qag.82.1387379055796;
 Wed, 18 Dec 2013 07:04:15 -0800 (PST)
Received: from payal-ThinkPad-T520
 (static-108-48-124-82.washdc.fios.verizon.net. [108.48.124.82])
 by mx.google.com with ESMTPSA id lc1sm877943qeb.5.2013.12.18.07.04.14
 for <multiple recipients>
 (version=TLSv1.2 cipher=RC4-SHA bits=128/128);
 Wed, 18 Dec 2013 07:04:15 -0800 (PST)
Date: Wed, 18 Dec 2013 10:04:13 -0500
From: Payal Singh <payal@omniti.com>
To: "Doom.zhou" <zzepaigh@gmail.com>
Cc: "Campbell, Lance" <lance@illinois.edu>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: script to drop and create all indexes in a database with
 parameter concurrently
Message-ID: <20131218150413.GA19712@payal-ThinkPad-T520>
References: 
 <B75CD08C73BD3543B97E4EF3964B7D701F65D459@CITESMBX1.ad.uillinois.edu>
 <CAK=muZv5yG3t=Gr1sBNiXvXNyoboVvUW_n1b9Xfg4axniobS2g@mail.gmail.com>
 <CAK=muZvbJDD_GNfrWt0ZRFko0DEC6PmOXKcpA8muT54cT3pchQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAK=muZvbJDD_GNfrWt0ZRFko0DEC6PmOXKcpA8muT54cT3pchQ@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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).  ^^
> >


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin