Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 8EEE81337B84 for ; Thu, 28 Oct 2010 09:46:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 66745-06 for ; Thu, 28 Oct 2010 12:46:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mh5.mail.rice.edu (mh5.mail.rice.edu [128.42.199.32]) by mail.postgresql.org (Postfix) with ESMTP id 1A3691336DB6 for ; Thu, 28 Oct 2010 09:46:48 -0300 (ADT) Received: from mh5.mail.rice.edu (localhost.localdomain [127.0.0.1]) by mh5.mail.rice.edu (Postfix) with ESMTP id A20A228F754; Thu, 28 Oct 2010 07:46:48 -0500 (CDT) X-Virus-Scanned: by amavis-2.6.4 at mh5.mail.rice.edu, auth channel Received: from mh5.mail.rice.edu ([127.0.0.1]) by mh5.mail.rice.edu (mh5.mail.rice.edu [127.0.0.1]) (amavis, port 10026) with ESMTP id 61ek2p8M6DiG; Thu, 28 Oct 2010 07:46:48 -0500 (CDT) X-SMTP-Auth: no X-SMTP-Auth: no Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48]) by mh5.mail.rice.edu (Postfix) with ESMTP id 7542228F703; Thu, 28 Oct 2010 07:46:48 -0500 (CDT) Received: by aart.is.rice.edu (Postfix, from userid 18612) id CF3142D5A6; Thu, 28 Oct 2010 07:46:47 -0500 (CDT) Date: Thu, 28 Oct 2010 07:46:47 -0500 From: Kenneth Marshall To: "Little, Douglas" Cc: "pgsql-admin@postgresql.org" Subject: Re: Forced quiesce Message-ID: <20101028124647.GD27429@aart.is.rice.edu> References: <8585BA53443004458E0BAA6134C5A7FB4773E160@EGEXCMB01.oww.root.lcl> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <8585BA53443004458E0BAA6134C5A7FB4773E160@EGEXCMB01.oww.root.lcl> User-Agent: Mutt/1.5.16 (2007-06-09) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/200 X-Sequence-Number: 35941 On Thu, Oct 28, 2010 at 07:39:19AM -0500, Little, Douglas wrote: > Hi, > We're having to vacuum full the system tables everyday due to the number of temp table drop/creates. > We're finding that if anyone tries to execute a job during the maintenance period, it often hangs the db. > > Is there a way that we can force the db to quiesce? Or lock out users. > Luckly, most users are coming in thru a network VIP, so all client IP's are the same. > > Thanks in advance. > > Doug Little > Hi Doug, You could use pgbouncer to connect to your database and then issue a "PAUSE" command before the VACUUM FULL: PAUSE; PgBouncer tries to disconnect from all servers, first waiting for all queries to complete. The command will not return before all queries are finished. To be used at the time of database restart. There is also a SUSPEND option, but the PAUSE looks like it will do what you need. Regards, Ken