Re: Recommend dba maintenance tasks on a regular bases

On Fri, Sep 12, 2008 at 9:49 AM, Barbara Stephenson
<barbara(at)turbocorp(dot)com> wrote:
> Hello,
>
> We are currently using Postgresql 8.3.3 on Red Hat 4 and our largest database
> is around 8454 MB.
>
> I have recommend the below to my group but not sure if reindexing should be
> involved since autovacuum is on?
>
> How can I be sure auto vacumming is working fine? We haven't had any problems
> plus I do a query and it does list all the tables and shows the last update
> of auto vacuum and auto analyze. Is that it?
>
>
> 1- pg_dump - binary dump every midday and nightly

If uptime is critical, also look at setting up a PITR server.

> 2 - auto vacuum
> autovacuum = on
> log_autovacuum_min_duration = 0
> autovacuum_max_workers = 3
> autovacuum_naptime = 1min
> autovacuum_vacuum_threshold = 50
> autovacuum_vacuum_scale_factor = 0.2
> autovacuum_analyze_scale_factor = 0.1

What's your autovacuum sleep? 10 to 20 is pretty reasonable. If you
set it much higher autovacuum may not run fast enough to keep up.

Run regular vacuum verbose on the database and examine the last 20 or
so lines (need to be superuser I think) to see how many free space map
slots you need. FSM slots are super cheap (6 bytes each) but they do
come from shared memory. A good setting for a database in your size
range with high updates will be between the default 200k or so to 1M.

> 3- rotate data logs

pgsql has this ability built in, as does your OS. I prefer letting
pgsql log as I like the options better than syslog, plus it seems to
be faster.

Also, don't forget to add long term periodic maintenance. Things like
taking the server down every 6 or 12 months to blow out dust, run
memory and drive tests, etc...