Обсуждение: Forced quiesce

Поиск
Список
Период
Сортировка

Forced quiesce

От
"Little, Douglas"
Дата:

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

 

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide

500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741

Douglas.Little@orbitz.com

 cid:image001.jpg@01CABEC8.D4980670  orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com

 

Вложения

Re: Forced quiesce

От
Kenneth Marshall
Дата:
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

Re: Forced quiesce

От
"Plugge, Joe R."
Дата:

Could you roll in a temporary pg_hba.conf, issue the pg_ctl reload command to reread that and then identify those ips using ps, and kill –TERM them?   After your maintenance, rotate back in your full pg_hba.conf and reload it.

 

 

 

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Little, Douglas
Sent: Thursday, October 28, 2010 7:39 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Forced quiesce

 

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

 

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide

500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741

Douglas.Little@orbitz.com

 cid:image001.jpg@01CABEC8.D4980670  orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com

 

Вложения

Re: Forced quiesce

От
Sergey Konoplev
Дата:
Hi,

David Fetter has an article about the issue like your. He describes the situation with DROP DATABASE but it could easily be adapted for VACUUM. Here is the link http://people.planetpostgresql.org/dfetter/index.php?/archives/63-Kick-em-out,-and-keep-em-out!.html

On 28 October 2010 16:39, Little, Douglas <DOUGLAS.LITTLE@orbitz.com> 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
>
>  
>
> Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
>
> 500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741
>
> Douglas.Little@orbitz.com
>
>    orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com
>
>  


--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp

Re: Forced quiesce

От
Alvaro Herrera
Дата:
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