Thread: Pg_autovacuum script.
Since I could not locate anything similar on the net, I have written a script to automatically start and stop the autovacuum daemon. This should be added to the existing postgresql script.
Please let me know if you can see any shortcomings. I have taken into consideration that more than one postmaster may be running on the same machine, so I have stored the pg_autovacuum pid into a file, which is referenced when bringing down the postmaster instance. I have not gone to the effort of including all the pg_autovacuum parameters, you can add them at will.
One issue I can foresee is maintainability, where you will have to update the postgresql script file with the snippets below, every 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=/var/lib/pg_autovacuum.log
PGAUTO_SLEEP=60
Added to the end of the Start function.
# Start pg_autovacuum. Theo Galanakis 11/05/2005
PAUTO_START=$"Starting pg_autovacuum service: "
echo -n "$PAUTO_START"
pg_autovacuum -D -s ${PGAUTO_SLEEP} -p ${PGPORT} -U postgres -L ${PGAUTO_LOGFILE}
pg_autovacuum_pid=`pidof -s $PGENGINE/pg_autovacuum`
if [ $pg_autovacuum_pid ]
then
success "$PAUTO_START"
echo $pg_autovacuum_pid > /var/run/pg_autovacuum.${PGPORT}.pid
echo
else
failure "$PAUTO_START"
echo
fi
Added to the end of the Stop function.
# Stop pg_autovaccum. Theo Galanakis 11/04/2005
echo -n $"Stopping pg_autovacuum service: "
pg_autovacuum_pid=`head -n 1 /var/run/pg_autovacuum.${PGPORT}.pid`
kill -TERM $pg_autovacuum_pid
ret=`ps --no-heading ${pg_autovacuum_pid}`
if [ -z "$ret" ]
then
echo_success
else
echo_failure
fi
echo
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. |
A while back, I wrote this: http://www.sitening.com/auto_pg_autovacuum While not exactly an init script, it sanely avoids multiply starting 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 laying this issue to rest. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ 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 > script to automatically start and stop the autovacuum daemon. This > should be added to the existing postgresql script. > > Please let me know if you can see any shortcomings. I have taken into > consideration that more than one postmaster may be running on the same > machine, so I have stored the pg_autovacuum pid into a file, which is > referenced when bringing down the postmaster instance. I have not gone > to the effort of including all the pg_autovacuum parameters, you can > add them at will. > > One issue I can foresee is maintainability, where you will have to > update the postgresql script file with the snippets below, every > 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=/var/lib/pg_autovacuum.log > PGAUTO_SLEEP=60 > > Added to the end of the Start function. > # Start pg_autovacuum. Theo Galanakis 11/05/2005 > PAUTO_START=$"Starting pg_autovacuum service: " > echo -n "$PAUTO_START" > pg_autovacuum -D -s ${PGAUTO_SLEEP} -p ${PGPORT} -U postgres > -L ${PGAUTO_LOGFILE} > pg_autovacuum_pid=`pidof -s $PGENGINE/pg_autovacuum` > if [ $pg_autovacuum_pid ] > then > success "$PAUTO_START" > echo $pg_autovacuum_pid > > /var/run/pg_autovacuum.${PGPORT}.pid > echo > else > failure "$PAUTO_START" > echo > fi > > Added to the end of the Stop function. > # Stop pg_autovaccum. Theo Galanakis 11/04/2005 > echo -n $"Stopping pg_autovacuum service: " > pg_autovacuum_pid=`head -n 1 > /var/run/pg_autovacuum.${PGPORT}.pid` > kill -TERM $pg_autovacuum_pid > ret=`ps --no-heading ${pg_autovacuum_pid}` > if [ -z "$ret" ] > then > echo_success > else > echo_failure > fi > echo > 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. > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
A while back, I wrote this: http://www.sitening.com/auto_pg_autovacuum While not exactly an init script, it sanely avoids multiply starting 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 laying this issue to rest. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ 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 > script to automatically start and stop the autovacuum daemon. This > should be added to the existing postgresql script. > > Please let me know if you can see any shortcomings. I have taken into > consideration that more than one postmaster may be running on the same > machine, so I have stored the pg_autovacuum pid into a file, which is > referenced when bringing down the postmaster instance. I have not gone > to the effort of including all the pg_autovacuum parameters, you can > add them at will. > > One issue I can foresee is maintainability, where you will have to > update the postgresql script file with the snippets below, every > 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=/var/lib/pg_autovacuum.log > PGAUTO_SLEEP=60 > > Added to the end of the Start function. > # Start pg_autovacuum. Theo Galanakis 11/05/2005 > PAUTO_START=$"Starting pg_autovacuum service: " > echo -n "$PAUTO_START" > pg_autovacuum -D -s ${PGAUTO_SLEEP} -p ${PGPORT} -U postgres > -L ${PGAUTO_LOGFILE} > pg_autovacuum_pid=`pidof -s $PGENGINE/pg_autovacuum` > if [ $pg_autovacuum_pid ] > then > success "$PAUTO_START" > echo $pg_autovacuum_pid > > /var/run/pg_autovacuum.${PGPORT}.pid > echo > else > failure "$PAUTO_START" > echo > fi > > Added to the end of the Stop function. > # Stop pg_autovaccum. Theo Galanakis 11/04/2005 > echo -n $"Stopping pg_autovacuum service: " > pg_autovacuum_pid=`head -n 1 > /var/run/pg_autovacuum.${PGPORT}.pid` > kill -TERM $pg_autovacuum_pid > ret=`ps --no-heading ${pg_autovacuum_pid}` > if [ -z "$ret" ] > then > echo_success > else > echo_failure > fi > echo > 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. > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)