Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9F0322E002F for ; Sun, 24 Feb 2008 21:02:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 92467-03 for ; Sun, 24 Feb 2008 21:02:20 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.187]) by postgresql.org (Postfix) with ESMTP id 96E772E0033 for ; Sun, 24 Feb 2008 21:02:19 -0400 (AST) Received: by fk-out-0910.google.com with SMTP id b27so1761906fka.3 for ; Sun, 24 Feb 2008 17:02:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=jnk+DfZaeaz2nJoMgPaAEHIH9FtTi5R5dLCcfSP5lNQ=; b=b5Zm1z/ZyaFhecGzPEFA8lJHxXj20RZErz7swFatwf14STSGsJrD3ekdb8bfQbY6TEILAHVXt//tAEV8aShcA20nDgZ6C5/izIL7bZA/aqFPX8KlxEO1lq1M/gc2GrarZGi40wkl/jP34Tt++p7G4yiuY+z1DJILJVZYvf9+KLA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Ww6c+70ZJbCXDYmX9MWOxtKY+BArW1n23iXj9Ul2NbGgW/UHp8rpj9DH8gWc/4142KqwgTNyzNzmvVEK8fVoviwPxbmIi3EZz96CPZeiFU62B6ynQf6QL9a7J7YV2l/a7Csdplkk5qlMLmolOxSTmKvdMsxKufenENCck5zRiME= Received: by 10.82.180.17 with SMTP id c17mr4480519buf.4.1203901338001; Sun, 24 Feb 2008 17:02:18 -0800 (PST) Received: by 10.82.186.16 with HTTP; Sun, 24 Feb 2008 17:02:17 -0800 (PST) Message-ID: <758d5e7f0802241702y48df55dejf0410e87d76fc598@mail.gmail.com> Date: Mon, 25 Feb 2008 02:02:17 +0100 From: "Dawid Kuroczko" To: "pgsql-admin@postgresql.org" Subject: Re: WAL archiving In-Reply-To: <758d5e7f0802241701h221ddebbka852adc11f343ba6@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <47BE07ED.1070802@internode.com.au> <758d5e7f0802241701h221ddebbka852adc11f343ba6@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/303 X-Sequence-Number: 28377 On Fri, Feb 22, 2008 at 12:23 AM, Shilpa Sudhakar wrote: > Since the wal logs keep increasing, do we take the base backup every now > and then so that we can delete the old log files? > How often do we take a base filesystem backup keeping in mind that our > systems are 24 x 7. Depends on your workload and needs. Say, if you have a policy on having ability to "go back in time" for at least 30 days, then you must keep last base backup that was done before 30 days and all the log files since then. But your primary concern should be probably recovery time after failure (your whole machine just fried ;)). What you need to do is restore last backup and reply all archival WAL logs since then. And assume worst possible situation (the machine fried just before full backup), for a weekly backups it means 7 days worth of WAL-log reply. Now, if your database is DML-heavy recovery won't be very fast, so you might want to take base backups more frequently. Or better -- set up a standby server which will constantly reply the backups. Regards, Dawid Kuroczko PS: And before setting a policy, do try test recovery and note down how much time it took.