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@xxxxxxxxxxxxxxxx
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
--
Jim Nasby jimn@xxxxxxxxxxxxxxxx
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
