Re: Recommend dba maintenance tasks on a regular bases

On Fri, 12 Sep 2008, Barbara Stephenson wrote:

> 1- pg_dump - binary dump every midday and nightly
> 2 - auto vacuum
> 3- rotate data logs

You should also consider running a script which does a VACUUM VERBOSE weekly
or twice monthly and emails you the last 8 lines of output.  This will allow
you to keep your FSM settings up to date.

Jim Nasby's article here: http://decibel.org/~decibel/pervasive/fsm.html has
some good info about the Free Space Map if you're not familiar with it.

In addition, it's probably worth setting log_min_duration_statement to
something like 500 or 1000 (500ms or 1s) so that you log slow queries.  Note
that 500ms is just an example, set it to a value you consider slow so that it
will log your slow queries.  Then, after you've gathered some
data, run it through pgfouine.  http://pgfouine.projects.postgresql.org/

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 916-647-6411    FAX: 916-405-4032