conf/88692: Amend /etc/periodic/daily/502.pgsql to restart

[Danny Howard]

>Number:         88692
>Category:       conf
>Synopsis:       Amend /etc/periodic/daily/502.pgsql to restart
>Confidential:   no
>Severity:       non-critical
>Priority:       low
>Responsible:    freebsd-bugs
>State:          open
>Quarter:        
>Keywords:       
>Date-Required:
>Class:          change-request
>Submitter-Id:   current-users
>Arrival-Date:   Tue Nov 08 21:40:16 GMT 2005
>Closed-Date:
>Last-Modified:
>Originator:     Danny Howard
>Release:        FreeBSD 4.10-RELEASE-p3 i386
>Organization:
>Environment:
System: FreeBSD ratchet.nebcorp.com 4.10-RELEASE-p3 FreeBSD 4.10-RELEASE-p3 #1: Thu Nov 4 22:42:42 PST 2004 root at ratchet.nebcorp.com:/usr/obj/usr/src/sys/RATCHET i386

postgresql-client-8.0.2 PostgreSQL database (client)
postgresql-server-8.0.2 The most advanced open-source database available anywhere

	
>Description:

Hello,

I have several times run in to this and hear its a common problem.
During regular operation, the Postgres DB server slowly leaks
connections until it runs out.  The simple "fix" is to up the number of
connections, but this means lowering the per-connection resources
available, reducing performance.

Another fix is to occasionally restart the database engine, freeing up
any open connections, and restoring the engine to a good state.  I have
accomplished this by adding the functionality to
/etc/periodic/daily/502.pgsql.  The attached patch should be
self-explanatory.

	
>How-To-Repeat:
	
>Fix:

*** /tmp/502.pgsql.ORIG	Tue Nov  8 13:18:34 2005
--- /etc/periodic/daily/502.pgsql	Tue Nov  8 13:21:22 2005
***************
*** 19,24 ****
--- 19,25 ----
  # daily_pgsql_vacuum_enable="YES" # do vacuum
  
  daily_pgsql_vacuum_enable="YES"
+ daily_pgsql_restart_enable="YES"
  daily_pgsql_backup_enable="NO"
  
  daily_pgsql_vacuum_args="-z"
***************
*** 89,94 ****
--- 90,110 ----
  	then
  	    echo
  	    echo "Errors were reported during vacuum."
+ 	    rc=3
+ 	fi
+ 	;;
+ esac
+ 
+ case "$daily_pgsql_restart_enable" in
+     [Yy][Ee][Ss])
+ 
+ 	echo
+ 	echo "restarting..."
+ 	/usr/local/etc/rc.d/010.pgsql.sh restart
+ 	if [ $? -gt 0 ]
+ 	then
+ 	    echo
+ 	    echo "Errors were reported during restart."
  	    rc=3
  	fi
  	;;
	


>Release-Note:
>Audit-Trail:
>Unformatted: