Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 2EF2D632349 for ; Thu, 15 Apr 2010 12:56:58 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 87351-10 for ; Thu, 15 Apr 2010 15:56:47 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from exprod7og115.obsmtp.com (exprod7og115.obsmtp.com [64.18.2.217]) by mail.postgresql.org (Postfix) with SMTP id B4781632414 for ; Thu, 15 Apr 2010 12:56:46 -0300 (ADT) Received: from source ([209.85.160.50]) by exprod7ob115.postini.com ([64.18.6.12]) with SMTP ID DSNKS8c3PWHfz2LQ3vfCRk9TPNvMyZQNgag/@postini.com; Thu, 15 Apr 2010 08:56:46 PDT Received: by mail-pw0-f50.google.com with SMTP id 1so1052692pwj.9 for ; Thu, 15 Apr 2010 08:56:45 -0700 (PDT) MIME-Version: 1.0 Received: by 10.141.44.8 with HTTP; Thu, 15 Apr 2010 08:56:44 -0700 (PDT) In-Reply-To: <7965A9DCF12CC14984420BCC37B1608F25AC0B99AD@Elzar.grant.co.uk> References: <7965A9DCF12CC14984420BCC37B1608F25AC0B99AD@Elzar.grant.co.uk> Date: Thu, 15 Apr 2010 11:56:44 -0400 Received: by 10.141.108.1 with SMTP id k1mr488254rvm.120.1271347005137; Thu, 15 Apr 2010 08:56:45 -0700 (PDT) Message-ID: Subject: Re: archived WALL files question From: Scott Mead To: Renato Oliveira Cc: "pgsql-admin@postgresql.org" Content-Type: multipart/alternative; boundary=000e0cd18024a0a7720484488a65 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.801 tagged_above=-10 required=5 tests=BAYES_50=0.8, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/193 X-Sequence-Number: 34287 --000e0cd18024a0a7720484488a65 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable On Thu, Apr 15, 2010 at 11:31 AM, Renato Oliveira < renato.oliveira@grant.co.uk> wrote: > Dear all, > > > > I was reading again the documentation... =93The archive command should > generally de designed to refuse to overwrite any pre-existing archive fil= e.=94 > > This means it will keep writing logs to the folder specified forever, and > without an intervention, the media will run out of space. > > > > What do you guys do with regards to this situation, for example: > > How to you clean up the old archived logs? > > > > For example: > > you archive your log files from your main Postgres server to a folder > /mnt/pitr for example > > You set your standby to pick the logs from /mnt/pitr, then it archives ea= ch > log as it comes. > > /mnt/pitr will fill up very quickly and run out of space if we don=92t ha= ve a > process to DELETE/ARCHIEVE older logs. > > > > I guess the process which picks up the logs for the standby server, needs > to take care of the logs, by deleting the older ones or by archiving them > permanently? > Depends on what it is you're trying to accomplish: *) PITR slave server constantly applying logs If all you want is a server to constantly apply the logs and you don't care about them afterwards, look into the '%r' macro in pg_standby. It wil= l automatically archive files for you -- Of course, your standby instance needs to have write access to the /mnt/pitr folder to delete from. If you are using the archive_command to copy files in the /mnt/pitr directory, and then doing a cron based copy to a backup server, have your cronjob delete files from the primary after it is confirmed that the logs got shipped safely to the backup. *) Backup retention time If you're trying to keep logs around so that you can do a point in time recovery with old backups, you want to figure your retention times and determine your RTO (http://en.wikipedia.org/wiki/Recovery_time_objective). If you need to be able to recovery to any point in time for the past 1 week with a low RTO, then you want to keep that week's worth of logs uncompressed and available. Anything beyond that, use a cron job to compress the logs (they usually compress pretty well based on your data). Basically, you need to keep all the low-RTO required logs around so that you can quickly get at them. If you don't have any low RTO requirements and you just want to keep a few weeks worth of data around, I would recommend that you add a few lines of code to the end of your backup job to compress (or you could delete if you don't want them) all the logs prior to the backup that you are taking. Hope this helps --Scott > > > How do you guys deal with this problem? > > > > Thank you very much in advance > > > > Best regards > > > > Renato > > > > > > Renato Oliveira > Systems Administrator > e-mail: renato.oliveira@grant.co.uk > > Tel: +44 (0)1763 260811 > Fax: +44 (0)1763 262410 > www.grant.co.uk > > Grant Instruments (Cambridge) Ltd > > Company registered in England, registration number 658133 > > Registered office address: > 29 Station Road, > Shepreth, > CAMBS SG8 6GB > UK > > > > > > ** > > *P** **Please consider the environment before printing this email* > *CONFIDENTIALITY*: The information in this e-mail and any attachments is > confidential. It is intended only for the named recipients(s). If you are > not the named recipient please notify the sender immediately and do not > disclose the contents to another person or take copies. > > ** > *VIRUSES:* The contents of this e-mail or attachment(s) may contain > viruses which could damage your own computer system. Whilst Grant > Instruments (Cambridge) Ltd has taken every reasonable precaution to > minimise this risk, we cannot accept liability for any damage which you > sustain as a result of software viruses. You should therefore carry out y= our > own virus checks before opening the attachment(s). > > *OpenXML*: For information about the OpenXML file format in use within > Grant Instruments please visit our website > --000e0cd18024a0a7720484488a65 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable
On Thu, Apr 15, 2010 at 11:31 AM, Renato Oli= veira <= renato.oliveira@grant.co.uk> wrote:

Dear all,

=A0

I was reading again the documentation... =93The arch= ive command should generally de designed to refuse to overwrite any pre-existin= g archive file.=94

This means it will keep writing logs to the folder s= pecified forever, and without an intervention, the media will run out of space.

=A0

What do you guys do with regards to this situation, = for example:

How to you clean up the old archived logs?

=A0

For example:

you archive your log files from your main Postgres s= erver to a folder /mnt/pitr for example

You set your standby to pick the logs from /mnt/pitr= , then it archives each log as it comes.

/mnt/pitr will fill up very quickly and run out of s= pace if we don=92t have a process to DELETE/ARCHIEVE older logs.

=A0

I guess the process which picks up the logs for the = standby server, needs to take care of the logs, by deleting the older ones or by archiving them permanently?

Depends on what it is you're trying to accomplish:


*) PITR slave server constantly applying log= s

=A0=A0 If all you want is a server to constantly apply = the logs and you don't care about them afterwards, look into the '%= r' macro in pg_standby. =A0It will automatically archive files for you = -- Of course, your standby instance needs to have write access to the /mnt/= pitr folder to delete from.

=A0=A0 If you are using the archive_command to copy fil= es in the /mnt/pitr directory, and then doing a cron based copy to a backup= server, have your cronjob delete files from the primary after it is confir= med that the logs got shipped safely to the backup.

*) Backup retention time

=A0= =A0 If you're trying to keep logs around so that you can do a point in = time recovery with old backups, you want to figure your retention times and= determine your RTO (http://en.wikipedia.org/wiki/Recovery_time_objective).

=A0=A0 =A0 If you need to be able to recovery to any po= int in time for the past 1 week with a low RTO, then you want to keep that = week's worth of logs uncompressed and available. =A0Anything beyond tha= t, use a cron job to compress the logs (they usually compress pretty well b= ased on your data). =A0

=A0=A0 =A0 Basically, you need to keep all the low-RTO = required logs around so that you can quickly get at them. =A0If you don'= ;t have any low RTO requirements and you just want to keep a few weeks wort= h of data around, I would recommend that you add a few lines of code to the= end of your backup job to compress (or you could delete if you don't w= ant them) all the logs prior to the backup that you are taking.

Hope this helps

--Scott
<= div>=A0

=A0

How do you guys deal with this problem?

=A0

Thank you very much in advance

=A0

Best regards

=A0

Renato

=A0

=A0

=A0
Renato= Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk<= /font>
=A0
Tel: += 44 (0)1763 260811
Fax: +44 (0)1763 262410
www.grant.co.uk
=A0
Grant = Instruments (Cambridge) Ltd
=A0
Company registered in England, regis= tration number 658133
=A0
Registered office address:
29 Station Ro= ad,
Shepreth,
CAMBS SG8 6GB
UK
=A0
=A0
=A0
=A0

=A0

P Please consider the environment befor= e printing this email

CONFIDENTIALITY: The = information in this e-mail and any attachments is confidential. It is inten= ded only for the named recipients(s). If you are not the named recipient pl= ease notify the sender immediately and do not disclose the contents to anot= her person or take copies.
=A0
VIRUSES: The contents= of this e-mail or attachment(s) may contain viruses which could damage you= r own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken e= very reasonable precaution to minimise this risk, we cannot accept liabilit= y for any damage which you sustain as a result of software viruses. You sho= uld therefore carry out your own virus checks before opening the attachment= (s).
=A0
OpenXML: For informat= ion about the OpenXML file format in use within Grant Instruments please vi= sit our website

--000e0cd18024a0a7720484488a65--