X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 48D0F9FB302 for ; Thu, 21 Sep 2006 08:09:10 -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 79514-01 for ; Thu, 21 Sep 2006 08:08:55 -0300 (ADT) X-Greylist: delayed 00:19:59.931349 by SQLgrey- Received: from nicochan.gva.es (nicochan.gva.es [193.144.127.143]) by postgresql.org (Postfix) with ESMTP id 32CE69FB31F for ; Thu, 21 Sep 2006 08:08:54 -0300 (ADT) Received: (qmail 19510 invoked by uid 7797); 21 Sep 2006 10:42:12 -0000 Received: from 172.19.20.176 by nicochan (envelope-from , uid 7791) with qmail-scanner-1.24-st-qms (clamdscan: 0.88.4/1812. spamassassin: 3.1.4. perlscan: 1.24-st-qms. Clear:RC:1(172.19.20.176):SA:0(-1.8/5.0):. Processed in 0.204197 secs); 21 Sep 2006 10:42:12 -0000 X-Antivirus-Nicochan-Mail-From: garcia_josval@gva.es via nicochan X-Antivirus-Nicochan: 1.24-st-qms (Clear:RC:1(172.19.20.176):SA:0(-1.8/5.0):. Processed in 0.204197 secs Process 19499) Received: from unknown (HELO [172.19.20.176]) ([172.19.20.176]) (envelope-sender ) by nicochan.gva.es (qmail-ldap-1.03) with SMTP for ; 21 Sep 2006 10:42:11 -0000 Message-ID: <45126C37.6020106@gva.es> Date: Thu, 21 Sep 2006 12:40:55 +0200 From: =?ISO-8859-1?Q?Jose_Manuel_Garci=ADa_Valladolid?= Reply-To: garcia_josval@gva.es User-Agent: Thunderbird 1.5.0.4 (X11/20060516) MIME-Version: 1.0 To: pgsql-admin@postgresql.org CC: Jose Alarcon Subject: WAL configuration and REINDEX Content-Type: multipart/mixed; boundary="------------040303050908000204050006" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/225 X-Sequence-Number: 23139 This is a multi-part message in MIME format. --------------040303050908000204050006 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Hello admins, 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? Thank you. Jose Manuel García V. --------------040303050908000204050006 Content-Type: text/x-vcard; charset=utf-8; name="garcia_josval.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="garcia_josval.vcf" begin:vcard fn;quoted-printable:Jose Manuel Garc=C3=ADa Valladolid n;quoted-printable:Garc=C3=ADa Valladolid;Jose Manuel org;quoted-printable;quoted-printable:Conseller=C3=ADa de Infraestructuras y Transportes;Servicio de Organizaci=C3=B3n e Inform=C3=A1tica adr;quoted-printable;dom:;;Av. Blasco Iba=C3=B1ez 50;Valencia;VALENCIA email;internet:garcia_josval@gva.es title:Analista x-mozilla-html:FALSE url:http://www.cit.gva.es version:2.1 end:vcard --------------040303050908000204050006--