Обсуждение: 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

Excerpts from Little, Douglas's message of jue oct 28 09:39:19 -0300 2010:
> Hi,
> We're having to vacuum full the system tables everyday due to the number of  temp table drop/creates.

Seems to me that you're solving the wrong problem.  I think you should
be doing very frequent lazy vacuum (not full) of certain system catalogs
to prevent bloat.  That way you don't need the maintenance to be done
with exclusive locks.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support