Thread: 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
orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com
Attachment
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
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
orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com
Attachment
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
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