Re: WAL configuration and REINDEX

I apologise if I am uninformed, but it is my understanding that REINDEX is
still required even with the most regular of vacuums?

On 9/22/06, Jim Nasby <jimn(at)enterprisedb(dot)com> wrote:
>
> On Sep 21, 2006, at 6:40 AM, Jose Manuel Garci a Valladolid wrote:
> > I have a PostgreSQL 8.1.4 server under Linux Red Hat with several
> > databases. The server is configured with WAL archiving turned on.
> > As a maintenance process, every night one cron job launches a
> > backup process to the server with
> > pg_dump, then the server shuts down and starts up and do a REINDEX
> > DATABASE to all databases to keep all tables reindexed.
> >
> > After 30 days of no activity to the server (this is a DBA test
> > server) the amount of WAL segments is increasing at very high
> > speed. Every REINDEX process generates between 25 and 30 WAL
> > segments. With this behavior (and no activity!!) I can not maintain
> > this amount of data to performs future WAL backups and recovers.
> >
> > Any idea to decrease the number of WAL segments generated?
> > Is possible to know when a table would be reindexed and to avoid
> > dayly REINDEX process?
>
> I think you're running off some pretty old information.
>
> First, you should only enable WAL archiving if you intend to use
> PITR. pg_dump and PITR are completely un-related. In fact, if you've
> never taken a base filesystem backup, all those old WAL files are
> completely useless.
>
> Second, as long as you are vacuuming frequently enough, there's no
> reason you should ever have to REINDEX. You certainly shouldn't be
> doing it on a regular basis.
> --
> Jim Nasby jimn(at)enterprisedb(dot)com
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>
> --
> Jim Nasby jimn(at)enterprisedb(dot)com
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>