From: Kenneth Marshall <ktm@rice.edu>
To: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Forced quiesce
Date: Thu, 28 Oct 2010 07:46:47 -0500
Message-ID: <20101028124647.GD27429@aart.is.rice.edu> (raw)
In-Reply-To: <8585BA53443004458E0BAA6134C5A7FB4773E160@EGEXCMB01.oww.root.lcl>
References: <8585BA53443004458E0BAA6134C5A7FB4773E160@EGEXCMB01.oww.root.lcl>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ktm@rice.edu, DOUGLAS.LITTLE@orbitz.com
Subject: Re: Forced quiesce
In-Reply-To: <20101028124647.GD27429@aart.is.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox