Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 0520E9FA4D7 for ; Sun, 24 Sep 2006 19:23:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 80400-08 for ; Sun, 24 Sep 2006 19:23:11 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.237]) by postgresql.org (Postfix) with ESMTP id E9BBF9F9F0C for ; Sun, 24 Sep 2006 19:23:10 -0300 (ADT) Received: by qb-out-0506.google.com with SMTP id e11so255527qba for ; Sun, 24 Sep 2006 15:23:09 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=uCZcL/lDEFzq3PO6unc1t/gisfm916WPNAugtlpIsWSCRunxCsz56eEeWCVcDMiFGw2HHOehk46+tjMMvXJILOqsKj+RfdzLJ6RaapWevecccAYDLHV8W11xOxd03Bgl/5oLdNGEIxKT7+p1Rb81D0pmIR2+DH+i0a1VrPu7+0E= Received: by 10.90.118.10 with SMTP id q10mr1120801agc; Sun, 24 Sep 2006 15:23:09 -0700 (PDT) Received: by 10.90.93.9 with HTTP; Sun, 24 Sep 2006 15:23:09 -0700 (PDT) Message-ID: <1c66bda80609241523p2c111042p96bb2f935da08f51@mail.gmail.com> Date: Mon, 25 Sep 2006 10:23:09 +1200 From: adey To: "Jim Nasby" Subject: Re: WAL configuration and REINDEX Cc: garcia_josval@gva.es, pgsql-admin@postgresql.org, "Jose Alarcon" In-Reply-To: <8CC6FC7A-C737-4473-99E1-9860FA61D9C5@enterprisedb.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_15319_27775571.1159136589475" References: <45126C37.6020106@gva.es> <8CC6FC7A-C737-4473-99E1-9860FA61D9C5@enterprisedb.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.574 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/256 X-Sequence-Number: 23170 ------=_Part_15319_27775571.1159136589475 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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@enterprisedb.com > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > > -- > Jim Nasby jimn@enterprisedb.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@postgresql.org so that your > message can get through to the mailing list cleanly > ------=_Part_15319_27775571.1159136589475 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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@enterprisedb.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@enterprisedb.com
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


--
Jim Nasby                                    jimn@enterprisedb.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@postgresql.org so that your
      message can get through to the mailing list cleanly

------=_Part_15319_27775571.1159136589475--