X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 09D2552A66 for ; Mon, 11 Apr 2005 19:43:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 03778-02 for ; Mon, 11 Apr 2005 22:43:52 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id B0A045295D for ; Mon, 11 Apr 2005 19:43:50 -0300 (ADT) Received: (qmail 30441 invoked from network); 11 Apr 2005 22:43:52 -0000 Received: from w080.z064003242.bna-tn.dsl.cnc.net (HELO ?192.168.1.22?) (64.3.242.80) by 0 with SMTP; 11 Apr 2005 22:43:52 -0000 In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v619.2) Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Message-Id: <595622f28653c8e3910b8b1328166439@sitening.com> Content-Transfer-Encoding: quoted-printable Cc: PGSQL Admin From: Thomas F.O'Connell Subject: Re: Pg_autovacuum script. Date: Mon, 11 Apr 2005 17:43:52 -0500 To: Theo Galanakis X-Mailer: Apple Mail (2.619.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/144 X-Sequence-Number: 17157 A while back, I wrote this: http://www.sitening.com/auto_pg_autovacuum While not exactly an init script, it sanely avoids multiply starting=20 pg_autovacuum. Then I did this: http://www.sitening.com/pgautovacuum which is a proper init script. Thanks for prompting me to post it! Now there are some options! But I'm hopeful that autovacuum will be integrated into 8.1, thereby=20 laying this issue to rest. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i=99 http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 10, 2005, at 10:19 PM, Theo Galanakis wrote: > Since I could not locate anything similar on the net, I have written a=20= > script to automatically start and stop the autovacuum daemon. This=20 > should be added to the existing postgresql script. > > Please let me know if you can see any shortcomings. I have taken into=20= > consideration that more than one postmaster may be running on the same=20= > machine, so I have stored the pg_autovacuum pid into a file, which is=20= > referenced when bringing down the postmaster instance. I have not gone=20= > to the effort of including all the pg_autovacuum parameters, you can=20= > add them at will. > > One issue I can foresee is maintainability, where you will have to=20 > update the postgresql script file with the snippets below, every=20 > postgres upgrade. > > Here are the snippets : > > This goes where all the other init scripts are located. > # Initialize pg_autovacuum defaults. Theo Galanakis 11/04/2005 > PGAUTO_LOGFILE=3D/var/lib/pg_autovacuum.log > PGAUTO_SLEEP=3D60 > > Added to the end of the Start function. > =A0# Start pg_autovacuum. Theo Galanakis 11/05/2005 > =A0=A0=A0=A0=A0=A0=A0 PAUTO_START=3D$"Starting pg_autovacuum service: = " > =A0=A0=A0=A0=A0=A0=A0 echo -n "$PAUTO_START" > =A0=A0=A0=A0=A0=A0=A0 pg_autovacuum -D -s ${PGAUTO_SLEEP} -p ${PGPORT} = -U postgres=20 > -L ${PGAUTO_LOGFILE} > =A0=A0=A0=A0=A0=A0=A0 pg_autovacuum_pid=3D`pidof -s = $PGENGINE/pg_autovacuum` > =A0=A0=A0=A0=A0=A0=A0 if [ $pg_autovacuum_pid ] > =A0=A0=A0=A0=A0=A0=A0 then > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 success "$PAUTO_START" > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 echo $pg_autovacuum_pid >=20 > /var/run/pg_autovacuum.${PGPORT}.pid > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 echo > =A0=A0=A0=A0=A0=A0=A0 else > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 failure "$PAUTO_START" > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 echo > =A0=A0=A0=A0=A0=A0=A0 fi > > Added to the end of the Stop function. > # Stop pg_autovaccum. Theo Galanakis 11/04/2005 > =A0=A0=A0=A0=A0=A0=A0 echo -n $"Stopping pg_autovacuum service: " > =A0=A0=A0=A0=A0=A0=A0 pg_autovacuum_pid=3D`head -n 1=20 > /var/run/pg_autovacuum.${PGPORT}.pid` > =A0=A0=A0=A0=A0=A0=A0 kill -TERM $pg_autovacuum_pid > =A0=A0=A0=A0=A0=A0=A0 ret=3D`ps --no-heading ${pg_autovacuum_pid}` > =A0=A0=A0=A0=A0=A0=A0 if [ -z "$ret" ] > =A0=A0=A0=A0=A0=A0=A0 then > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 echo_success > =A0=A0=A0=A0=A0=A0=A0 else > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 echo_failure > =A0=A0=A0=A0=A0=A0=A0 fi > =A0=A0=A0=A0=A0=A0=A0 echo > =A0=A0=A0=A0=A0=A0=A0 rm -f /var/run/pg_autovacuum.${PGPORT}.pid > > ______________________________________________________________________ > This email, including attachments, is intended only for the addressee > and may be confidential, privileged and subject to copyright. If you > have received this email in error, please advise the sender and = delete > it. If you are not the intended recipient of this email, you must not > use, copy or disclose its content to anyone. You must not copy or > communicate to others content that is confidential or subject to > copyright, unless you have the consent of the content owner. >