Thread: archived WALL files question

Renato Oliveira <renato.oliveira@grant.co.uk> wrote:

> I was reading again the documentation... "The archive command
> should generally de designed to refuse to overwrite any
> pre-existing archive file."  This means it will keep writing logs
> to the folder specified forever, and without an intervention, the
> media will run out of space.

Overwriting an existing file wouldn't help with that, since the
filenames keep changing.  It might, for example, prevent
accidentally wiping out the WAL files from one database cluster with
WAL files from another by copying the postgresql.conf file and
neglecting to change the archive script.

> What do you guys do with regards to this situation, for example:
> How to you clean up the old archived logs?

We keep two weekly base backups and all the WAL files needed to
recover from the earlier of the two to present.  We also keep an
archival copy of the first base backup of each month with just the
WAL files needed to start it.  We delete WAL files when no longer
needed to support this retention policy.  It's all pretty automatic
based on bash scripts run from cron jobs.

Of course, you'll want to tailor your strategy to your business
needs.

-Kevin

We manage the WAL files via skytools WALMGR

As far as log files we run a backup every 3 house and keep 12 housers worth on the server, everything else is sent to amazon S3 via s3sync

---

From: pgsql-admin-owner@postgresql.org <pgsql-admin-owner@postgresql.org>
To: Renato Oliveira <renato.oliveira@grant.co.uk>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Sent: Thu Apr 15 11:56:44 2010
Subject: Re: [ADMIN] archived WALL files question

I am sorry Kevin, I really appreciate your experience and your knowledge, and that's why I am asking; I thought the
basebackup was only necessary once. For example once you have done your first base backup, that is it, all you need is
toreplay the logs and backup the logs. 

What would be the reason(s) for you to do weekly base backups?

Thank you very much

Best regards

Renato



Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk

Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
http://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

-----Original Message-----


From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: 15 April 2010 17:02
To: Renato Oliveira; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] archived WALL files question

Renato Oliveira <renato.oliveira@grant.co.uk> wrote:

> I was reading again the documentation... "The archive command
> should generally de designed to refuse to overwrite any
> pre-existing archive file."  This means it will keep writing logs
> to the folder specified forever, and without an intervention, the
> media will run out of space.

Overwriting an existing file wouldn't help with that, since the
filenames keep changing.  It might, for example, prevent
accidentally wiping out the WAL files from one database cluster with
WAL files from another by copying the postgresql.conf file and
neglecting to change the archive script.

> What do you guys do with regards to this situation, for example:
> How to you clean up the old archived logs?

We keep two weekly base backups and all the WAL files needed to
recover from the earlier of the two to present.  We also keep an
archival copy of the first base backup of each month with just the
WAL files needed to start it.  We delete WAL files when no longer
needed to support this retention policy.  It's all pretty automatic
based on bash scripts run from cron jobs.

Of course, you'll want to tailor your strategy to your business
needs.

-Kevin



-----Original Message-----


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
toanother person or take copies. 

VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system.
WhilstGrant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept
liabilityfor any damage which you sustain as a result of software viruses. You should therefore carry out your own
viruschecks before opening the attachment(s). 

OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our
http://www.grant.co.uk/Support/openxml.html



Hi Renato,

I had the same question. I think, as far as I understood, the point is that if you have a few base backups, not only logs replay would be faster for a recovery but also you don't need to archive WAL segments before the base backup.

**
I also have a question regarding the frequency of log shipping  from the primary server to a directory exported to the standby server. The standby server is stopped ready to be launched in recovery mode. The point is in the primary server. I noticed the new logs files don't get copied to the directory specified on the archive_command. It's only copied when I do the pg_start_backup()/pg_stop_backup() base backup. Is this behaviour only achieved if I set archive_timeout? I did not want to do that, because I thought as soon as the 16MB WAL segment file got created it would be copied to the exported directory. Besides, I don't think I would need to perform base backups frequently.

Any advice?

Thanks

On 04/16/2010 12:00 AM, Renato Oliveira wrote:

I am sorry Kevin, I really appreciate your experience and your knowledge, and that's why I am asking; I thought the base backup was only necessary once. For example once you have done your first base backup, that is it, all you need is to replay the logs and backup the logs.

What would be the reason(s) for you to do weekly base backups?

Thank you very much

Best regards

Renato



Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk

Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
http://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

-----Original Message-----


From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: 15 April 2010 17:02
To: Renato Oliveira; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] archived WALL files question

Renato Oliveira <renato.oliveira@grant.co.uk> wrote:
 

I was reading again the documentation... "The archive command
should generally de designed to refuse to overwrite any
pre-existing archive file."  This means it will keep writing logs
to the folder specified forever, and without an intervention, the
media will run out of space.   

Overwriting an existing file wouldn't help with that, since the
filenames keep changing.  It might, for example, prevent
accidentally wiping out the WAL files from one database cluster with
WAL files from another by copying the postgresql.conf file and
neglecting to change the archive script.
 

What do you guys do with regards to this situation, for example:
How to you clean up the old archived logs?   

We keep two weekly base backups and all the WAL files needed to
recover from the earlier of the two to present.  We also keep an
archival copy of the first base backup of each month with just the
WAL files needed to start it.  We delete WAL files when no longer
needed to support this retention policy.  It's all pretty automatic
based on bash scripts run from cron jobs.

Of course, you'll want to tailor your strategy to your business
needs.

-Kevin



-----Original Message-----


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 your own virus checks before opening the attachment(s).

OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our http://www.grant.co.uk/Support/openxml.html

 

Renato Oliveira wrote:

> I thought the base backup was only necessary once. For example
> once you have done your first base backup, that is it, all you
> need is to replay the logs and backup the logs.  What would be
> the reason(s) for you to do weekly base backups?

There are a few reasons, many of which are probably not applicable
to most environments.

(1)  Most of our source databases are spread around the state --
some over 300 miles away (500 km for those with sane units of
measure).  Management has mandated that we must have backups in our
central location which have been restored to confirm usability, as
well as a copy of the backup files in the source locations.  We
don't have equipment at the source locations to keep a warm standby
running, so a recovery there would need to replay all WAL files
since the base backup.  A weekly cycle for base backups keeps the
set of WAL files we need to keep relatively small and allows
relatively rapid recovery.

(2)  Because of the large number of database clusters being backup
up (about 100), it is not feasible to actually have warm standbys
for all of them which we can just switch to in case of emergency.
We use one rather largish machine to host a all the "warm standby"
instances, just to make sure that the WAL files are applying without
error to the base backups.  We keep three machines prepped and ready
to go for emergencies, but we have to do the whole PITR recovery
from the base backup to get a production-ready copy.  Again, the
shorter the time from the base backup, the fewer WAL files to apply,
and the sooner we're ready to go.

(3)  We're mandated to keep monthly archival "snapshot" backups, so
we just grab the first weekly base backup of each month, with the
minimum set of WAL files needed to get it running (determined by the
.backup file).

(4)  A fair amount of paranoia.  If any subtle bugs affecting corner
cases were ever to creep into the software, the affects would be
minimized by using a fresher base backup and fewer WAL files.

I have heard of people running warm standbys for months without
getting a fresh base, and for many users that may be the best
option.  Our situation is pretty unique -- although when I say that,
people are usually quick to point out that so is everyone else's.
;-)

-Kevin

Kevin,

Thank you very much. I understand the multiple base backups are only to reduce the amount of time it could take to
actuallyrestore or reply all the LOGS. 
Because let's suppose; I create a base backup today 19/04/2010 then one year later, I need to use that base backup, I
willhave to play one year worth of logs. 

It is an interesting setup you have there, but you must have loads of space for all of those backups.
How long does it take for base backup of one of your busy DBs?

Thank you Very much

Renato



Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk

Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
http://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

-----Original Message-----


From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: 16 April 2010 18:16
To: Frederiko Costa; Renato Oliveira; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] archived WALL files question

Renato Oliveira wrote:

> I thought the base backup was only necessary once. For example
> once you have done your first base backup, that is it, all you
> need is to replay the logs and backup the logs.  What would be
> the reason(s) for you to do weekly base backups?

There are a few reasons, many of which are probably not applicable
to most environments.

(1)  Most of our source databases are spread around the state --
some over 300 miles away (500 km for those with sane units of
measure).  Management has mandated that we must have backups in our
central location which have been restored to confirm usability, as
well as a copy of the backup files in the source locations.  We
don't have equipment at the source locations to keep a warm standby
running, so a recovery there would need to replay all WAL files
since the base backup.  A weekly cycle for base backups keeps the
set of WAL files we need to keep relatively small and allows
relatively rapid recovery.

(2)  Because of the large number of database clusters being backup
up (about 100), it is not feasible to actually have warm standbys
for all of them which we can just switch to in case of emergency.
We use one rather largish machine to host a all the "warm standby"
instances, just to make sure that the WAL files are applying without
error to the base backups.  We keep three machines prepped and ready
to go for emergencies, but we have to do the whole PITR recovery
from the base backup to get a production-ready copy.  Again, the
shorter the time from the base backup, the fewer WAL files to apply,
and the sooner we're ready to go.

(3)  We're mandated to keep monthly archival "snapshot" backups, so
we just grab the first weekly base backup of each month, with the
minimum set of WAL files needed to get it running (determined by the
.backup file).

(4)  A fair amount of paranoia.  If any subtle bugs affecting corner
cases were ever to creep into the software, the affects would be
minimized by using a fresher base backup and fewer WAL files.

I have heard of people running warm standbys for months without
getting a fresh base, and for many users that may be the best
option.  Our situation is pretty unique -- although when I say that,
people are usually quick to point out that so is everyone else's.
;-)

-Kevin



-----Original Message-----


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
toanother person or take copies. 

VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system.
WhilstGrant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept
liabilityfor any damage which you sustain as a result of software viruses. You should therefore carry out your own
viruschecks before opening the attachment(s). 

OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our
http://www.grant.co.uk/Support/openxml.html

Hi Renato,

I had the same question. I think, as far as I understood, the point is that if you have a few base backups, not only logs replay would be faster for a recovery but also you don't need to archive WAL segments before the base backup.

**
I also have a question regarding the frequency of log shipping  from the primary server to a directory exported to the standby server. The standby server is stopped ready to be launched in recovery mode. The point is in the primary server. I noticed the new logs files don't get copied to the directory specified on the archive_command. It's only copied when I do the pg_start_backup()/pg_stop_backup() base backup. Is this behaviour only achieved if I set archive_timeout? I did not want to do that, because I thought as soon as the 16MB WAL segment file got created it would be copied to the exported directory. Besides, I don't think I would need to perform base backups frequently.

Any advice?

Thanks

On 04/16/2010 12:00 AM, Renato Oliveira wrote:

I am sorry Kevin, I really appreciate your experience and your knowledge, and that's why I am asking; I thought the base backup was only necessary once. For example once you have done your first base backup, that is it, all you need is to replay the logs and backup the logs.

What would be the reason(s) for you to do weekly base backups?

Thank you very much

Best regards

Renato



Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk

Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
http://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

-----Original Message-----


From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: 15 April 2010 17:02
To: Renato Oliveira; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] archived WALL files question

Renato Oliveira <renato.oliveira@grant.co.uk> wrote:
 

I was reading again the documentation... "The archive command
should generally de designed to refuse to overwrite any
pre-existing archive file."  This means it will keep writing logs
to the folder specified forever, and without an intervention, the
media will run out of space.   

Overwriting an existing file wouldn't help with that, since the
filenames keep changing.  It might, for example, prevent
accidentally wiping out the WAL files from one database cluster with
WAL files from another by copying the postgresql.conf file and
neglecting to change the archive script.
 

What do you guys do with regards to this situation, for example:
How to you clean up the old archived logs?   

We keep two weekly base backups and all the WAL files needed to
recover from the earlier of the two to present.  We also keep an
archival copy of the first base backup of each month with just the
WAL files needed to start it.  We delete WAL files when no longer
needed to support this retention policy.  It's all pretty automatic
based on bash scripts run from cron jobs.

Of course, you'll want to tailor your strategy to your business
needs.

-Kevin



-----Original Message-----


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 your own virus checks before opening the attachment(s).

OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our http://www.grant.co.uk/Support/openxml.html

 

Frederiko Costa <frederiko@gmail.com> wrote:

> I noticed the new logs files don't get copied to the directory
> specified on the archive_command. It's only copied when I do the
> pg_start_backup()/pg_stop_backup() base backup. Is this behaviour
> only achieved if I set archive_timeout? I did not want to do that,
> because I thought as soon as the 16MB WAL segment file got created
> it would be copied to the exported directory.

Have you actually been filling up any 16MB WAL file segments?  How
have you determined that?  What happens when you run (as a database
superuser) this command?:

SELECT pg_switch_xlog();

If the reason you don't want to set archive_timeout is that you
don't want 16MB files piling up when there is little or no activity,
you might want to consider using pglesslog or pg_clearxlogtail.
These can eliminate nearly all the cost of the more frequent logs.

-Kevin

I have seen new 16 MB segments files created in pg_xlog directory as time goes on. Honestly, I did not understand why it got created, because I was running it on a VM and there was no activity. I got several new segments.

If I run the command you asked, this is the output. Neither segment is copied, nor a new segment file gets created:

 select pg_switch_xlog();
 pg_switch_xlog
----------------
 0/81000088
(1 row)

Wasn't this command supposed to create a new segment file and copy the last non-copied segments - using command specified at archive_command - to the destination you defined?

I wanted to understand why the newly created wal segment files (with 16 MB each) did not copied automatically to the new place. What's the criteria? They only get copied when I carry out a base backup.

Thanks for the help!

On 04/19/2010 09:41 AM, Kevin Grittner wrote:

Frederiko Costa <frederiko@gmail.com> wrote: 

I noticed the new logs files don't get copied to the directory
specified on the archive_command. It's only copied when I do the
pg_start_backup()/pg_stop_backup() base backup. Is this behaviour
only achieved if I set archive_timeout? I did not want to do that,
because I thought as soon as the 16MB WAL segment file got created
it would be copied to the exported directory.   

 
Have you actually been filling up any 16MB WAL file segments?  How
have you determined that?  What happens when you run (as a database
superuser) this command?:
SELECT pg_switch_xlog();
If the reason you don't want to set archive_timeout is that you
don't want 16MB files piling up when there is little or no activity,
you might want to consider using pglesslog or pg_clearxlogtail. 
These can eliminate nearly all the cost of the more frequent logs.
-Kevin 

Frederiko Costa <frederiko@gmail.com> wrote:
> I have seen new 16 MB segments files created in pg_xlog directory
> as time goes on. Honestly, I did not understand why it got
> created, because I was running it on a VM and there was no
> activity. I got several new segments.
>
> If I run the command you asked, this is the output. Neither
> segment is copied, nor a new segment file gets created:
>
>   select pg_switch_xlog();
>   pg_switch_xlog
> ----------------
>   0/81000088
> (1 row)

What version of PostgreSQL is this?

What do you see if you run?:

show archive_command;
show archive_mode;
show archive_timeout;

Check your log file from the time you ran pg_switch_xlog to look for
any messages which might give a clue to what's happening.

-Kevin

On 04/19/2010 10:26 AM, Kevin Grittner wrote:
> Frederiko Costa<frederiko@gmail.com>  wrote:
>
>> I have seen new 16 MB segments files created in pg_xlog directory
>> as time goes on. Honestly, I did not understand why it got
>> created, because I was running it on a VM and there was no
>> activity. I got several new segments.
>>
>> If I run the command you asked, this is the output. Neither
>> segment is copied, nor a new segment file gets created:
>>
>>    select pg_switch_xlog();
>>    pg_switch_xlog
>> ----------------
>>    0/81000088
>> (1 row)
>>
>
> What version of PostgreSQL is this?
>
8.4.3
>
> What do you see if you run?:
>
> show archive_command;
>
cp -p %p /mnt/data/%f
> show archive_mode;
>
on
> show archive_timeout;
>
1min
I have just enabled that. Now, log files are being copied directly to
the /mnt/data dir. However, the same segments are not in the pg_xlog
dir. Is this a default behaviour?

Must I set archive_timeout? I don't think I want that, because,
specially for the next few months, where the activity would be very
limited and I would get several zero written segments just because
timeout has been reached. Is this approach recommended anyway? Or is it
better to use this approach even having limited approach?

> Check your log file from the time you ran pg_switch_xlog to look for
> any messages which might give a clue to what's happening.
>
>
The log files did not write anything about pg_switch_xlog.
> -Kevin
>

Frederiko Costa <frederiko@gmail.com> wrote:

> log files are being copied directly to the /mnt/data dir.
> However, the same segments are not in the pg_xlog
> dir. Is this a default behaviour?

Yes, the pg_xlog directory tries to keep a set of files ready to
receive WAL, and to hold it until the next checkpoint completes,
while the archive directory holds WAL files which have filled or
reached the archive_timeout limit.  There can be some overlap, but
basically you can view the archive as "past" and pg_xlog as
"future", with some potential overlap in "the present".

> Must I set archive_timeout?

No, but once a database write occurs, you don't have it backed up
until the WAL file is written to the archive directory.
archive_timeout is for those who want some wall-clock bounds on how
much they can lose in, say, a drive failure.  If the database can be
reloaded from some other source, or the data has no real value, it
may not pay to use this feature.

-Kevin