Re: Forced quiesce

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