pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPITR Recovery Question
15+ messages / 4 participants
[nested] [flat]
* PITR Recovery Question
@ 2010-06-03 13:37 Gnanakumar <gnanam@zoniac.com>
2010-06-03 15:19 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
2010-06-04 11:54 ` PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
0 siblings, 2 replies; 15+ messages in thread
From: Gnanakumar @ 2010-06-03 13:37 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
Hi,
My production server is running PostgreSQL v8.2.3 on CentOS release 5.2
(Final).
I've setup PITR in my production server. For some reason, after setting up
PITR, we're not able to manage and maintain it. Because of this our WAL
archive drive become full (100% use) approximately after 1 month.
PITR SETUP DETAILS
We've 2 drives. Primary drive (pgsql/data/ directory resides) is 400 GB and
secondary drive (WAL archive) is 30 GB. All WAL archives are written to
secondary drive.
Base backup taken on: Aug03, 2009
WAL archive drive become full (100% use) on: Sep05, 2009
Because this WAL archive drive has become full, all WAL archive segments to
be archived are accumulated into pg_xlog/ directory itself. Eventually, 9
months (as of today from Sep05, 2009) of WAL archives are residing in
pg_xlog/ directory.
My question is, in case if I would like to perform recovery process as it is
in this situation, will this work out? That is, I'm seeing/finding out
whether recovery process would perform successfully anywhere between the
date range Aug03, 2009 (my base backup date) and as of today - Jun03, 2009.
Reason I'm asking this is still all my WAL archives are residing in pg_xlog/
directory.
Experts advice/idea/suggestion on this appreciated.
Regards,
Gnanam
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PITR Recovery Question
2010-06-03 13:37 PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
@ 2010-06-03 15:19 ` Florian Pflug <fgp@phlo.org>
2010-06-04 05:05 ` Re: PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
1 sibling, 1 reply; 15+ messages in thread
From: Florian Pflug @ 2010-06-03 15:19 UTC (permalink / raw)
To: gnanam@zoniac.com; +Cc: pgsql-hackers@postgresql.org
Hi,
I'll try to answer your questions below, but in the future please post questions concerning the usage and administration of postgres to pgsql-general or pgsql-admin. This list focus is the development of new features and bugfixes.
On Jun 3, 2010, at 15:37 , Gnanakumar wrote:
> PITR SETUP DETAILS
> We've 2 drives. Primary drive (pgsql/data/ directory resides) is 400 GB and
> secondary drive (WAL archive) is 30 GB. All WAL archives are written to
> secondary drive.
>
> Base backup taken on: Aug03, 2009
> WAL archive drive become full (100% use) on: Sep05, 2009
>
> Because this WAL archive drive has become full, all WAL archive segments to
> be archived are accumulated into pg_xlog/ directory itself. Eventually, 9
> months (as of today from Sep05, 2009) of WAL archives are residing in
> pg_xlog/ directory.
This is by design. WAL logs are only removed from pg_xlog once they have been archived successfully. Since your archive_command fails due to the disk being full, they remain in pg_xlog. Once you enlarge the filesystem holding the WAL archive they should be copied and subsequently removed from pg_xlog.
Note that you'd usually take a new base backup once in a while to limit the number of WAL segments you need to retain. You can take a base backup while postgres is running by issuing pg_start_backup() before you start the copy and pg_stop_backup() after it finished. Apart from creating additional IO load, doing so won't interfere with normal query execution in any way.
> My question is, in case if I would like to perform recovery process as it is
> in this situation, will this work out? That is, I'm seeing/finding out
> whether recovery process would perform successfully anywhere between the
> date range Aug03, 2009 (my base backup date) and as of today - Jun03, 2009.
> Reason I'm asking this is still all my WAL archives are residing in pg_xlog/
> directory.
For PITR, you'll obviously need the WAL segment starting from the time your base backup started up until the point you want to recover to. If some of those WAL segments still reside in pg_xlog, you'll either need to teach your restore_command to fetch them from there. Note that you cannot recover "in reverse". To recover up to a certain point in time you always need to start from a base backup taken *before* that time.
best regards,
Florian Pflug
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PITR Recovery Question
2010-06-03 13:37 PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-03 15:19 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
@ 2010-06-04 05:05 ` Gnanakumar <gnanam@zoniac.com>
2010-06-04 10:59 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
0 siblings, 1 reply; 15+ messages in thread
From: Gnanakumar @ 2010-06-04 05:05 UTC (permalink / raw)
To: 'Florian Pflug' <fgp@phlo.org>; +Cc: pgsql-hackers@postgresql.org
Hi Florian,
Sure. I'll post my future questions either in pgsql-general or pgsql-admin.
> If some of those WAL segments still reside in pg_xlog, you'll either need
to teach your restore_command to fetch them from there. Note that you cannot
recover "in reverse".
My pg_xlog/ and walarchive/ directory locations are
"/usr/local/pgsql/data/pg_xlog" and "/mnt/pitr/walarchive" respectively.
If my normal restore command is: restore_command='cp
"/mnt/pitr/walarchive/%f "%p"', how should I instruct restore command to
fetch? Should I just replace this with something like restore_command='cp
/usr/local/pgsql/data/pg_xlog/%f "%p"'. Also you have mentioned that we
cannot recover "in reverse", what I understand from this is that even though
if I replace the restore command pointing to pg_xlog/ directory, this will
not work out in this situation? Is my understanding right?
-----Original Message-----
From: Florian Pflug [mailto:fgp@phlo.org]
Sent: Thursday, June 03, 2010 8:50 PM
To: gnanam@zoniac.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] PITR Recovery Question
Hi,
I'll try to answer your questions below, but in the future please post
questions concerning the usage and administration of postgres to
pgsql-general or pgsql-admin. This list focus is the development of new
features and bugfixes.
On Jun 3, 2010, at 15:37 , Gnanakumar wrote:
> PITR SETUP DETAILS
> We've 2 drives. Primary drive (pgsql/data/ directory resides) is 400 GB
and
> secondary drive (WAL archive) is 30 GB. All WAL archives are written to
> secondary drive.
>
> Base backup taken on: Aug03, 2009
> WAL archive drive become full (100% use) on: Sep05, 2009
>
> Because this WAL archive drive has become full, all WAL archive segments
to
> be archived are accumulated into pg_xlog/ directory itself. Eventually, 9
> months (as of today from Sep05, 2009) of WAL archives are residing in
> pg_xlog/ directory.
This is by design. WAL logs are only removed from pg_xlog once they have
been archived successfully. Since your archive_command fails due to the disk
being full, they remain in pg_xlog. Once you enlarge the filesystem holding
the WAL archive they should be copied and subsequently removed from pg_xlog.
Note that you'd usually take a new base backup once in a while to limit the
number of WAL segments you need to retain. You can take a base backup while
postgres is running by issuing pg_start_backup() before you start the copy
and pg_stop_backup() after it finished. Apart from creating additional IO
load, doing so won't interfere with normal query execution in any way.
> My question is, in case if I would like to perform recovery process as it
is
> in this situation, will this work out? That is, I'm seeing/finding out
> whether recovery process would perform successfully anywhere between the
> date range Aug03, 2009 (my base backup date) and as of today - Jun03,
2009.
> Reason I'm asking this is still all my WAL archives are residing in
pg_xlog/
> directory.
For PITR, you'll obviously need the WAL segment starting from the time your
base backup started up until the point you want to recover to. If some of
those WAL segments still reside in pg_xlog, you'll either need to teach your
restore_command to fetch them from there. Note that you cannot recover "in
reverse". To recover up to a certain point in time you always need to start
from a base backup taken *before* that time.
best regards,
Florian Pflug
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PITR Recovery Question
2010-06-03 13:37 PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-03 15:19 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
2010-06-04 05:05 ` Re: PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
@ 2010-06-04 10:59 ` Florian Pflug <fgp@phlo.org>
0 siblings, 0 replies; 15+ messages in thread
From: Florian Pflug @ 2010-06-04 10:59 UTC (permalink / raw)
To: gnanam@zoniac.com; +Cc: PostgreSQL-development hackers <pgsql-hackers@postgresql.org>
On Jun 4, 2010, at 7:05 , Gnanakumar wrote:
>> If some of those WAL segments still reside in pg_xlog, you'll either need
> to teach your restore_command to fetch them from there. Note that you cannot
> recover "in reverse".
>
> My pg_xlog/ and walarchive/ directory locations are
> "/usr/local/pgsql/data/pg_xlog" and "/mnt/pitr/walarchive" respectively.
>
> If my normal restore command is: restore_command='cp
> "/mnt/pitr/walarchive/%f "%p"', how should I instruct restore command to
> fetch? Should I just replace this with something like restore_command='cp
> /usr/local/pgsql/data/pg_xlog/%f "%p"'. Also you have mentioned that we
> cannot recover "in reverse", what I understand from this is that even though
> if I replace the restore command pointing to pg_xlog/ directory, this will
> not work out in this situation? Is my understanding right?
If you point it at a cluster's own pg_xlog directory, it won't work.
You might want to re-ead the section on the recovery process in the PTITR documentation, at
http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html#BACKUP-PITR-RECOVERY
If you have further questions, please take this discussion to pgsql-general.
best regards,
Florian Pflug
^ permalink raw reply [nested|flat] 15+ messages in thread
* PITR Recovery Question
2010-06-03 13:37 PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
@ 2010-06-04 11:54 ` Gnanakumar <gnanam@zoniac.com>
2010-06-04 15:02 ` Re: PITR Recovery Question Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-05 00:00 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
1 sibling, 2 replies; 15+ messages in thread
From: Gnanakumar @ 2010-06-04 11:54 UTC (permalink / raw)
To: pgsql-admin; +Cc: fgp@phlo.org
Hi Florian,
I'm moving this discussion to pgsql-admin. To give a picture of my original
question, it is given below, so that other users in this mailing list will
understand my original problem statement.
> If you point it at a cluster's own pg_xlog directory, it won't work.
> You might want to re-ead the section on the recovery process in the PTITR
documentation, at
http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html#BAC
KUP-PITR-RECOVERY
OK.
My pg_xlog/ and walarchive/ directory locations are
"/usr/local/pgsql/data/pg_xlog" and "/mnt/pitr/walarchive" respectively.
In case, if I decide to clean the old WAL archives and set right PITR from
today onwards by taking base backup, so that I can start managing and
maintaining atleast from now onwards, what is the correct way/method of
removing files from pg_xlog/, pg_xlog/archive_status/ and
/mnt/pitr/walarchive/ directories?
1. How do I clean up WAL archives that are accumulated in pg_xlog/
directory? Does it harm the database in anyway if I remove WAL segments
manually?
2. Few important command outputs:
[root@dbserver data]# pwd
/usr/local/pgsql/data
[root@dbserver data]# ls -l pg_xlog |wc -l
14438
[root@dbserver data]# ls -l pg_xlog/archive_status |wc -l
14436
[root@dbserver data]# ls -l /mnt/zoniacpitr/walarchive | wc -l
1783
NOTE: I can see all the files under pg_xlog/archive_status/ directory having
suffixed with "00*.ready".
-----Original Message-----
From: Gnanakumar [mailto:gnanam@zoniac.com]
Sent: Thursday, June 03, 2010 7:07 PM
To: pgsql-hackers@postgresql.org
Subject: PITR Recovery Question
Hi,
My production server is running PostgreSQL v8.2.3 on CentOS release 5.2
(Final).
I've setup PITR in my production server. For some reason, after setting up
PITR, we're not able to manage and maintain it. Because of this our WAL
archive drive become full (100% use) approximately after 1 month.
PITR SETUP DETAILS
We've 2 drives. Primary drive (pgsql/data/ directory resides) is 400 GB and
secondary drive (WAL archive) is 30 GB. All WAL archives are written to
secondary drive.
Base backup taken on: Aug03, 2009
WAL archive drive become full (100% use) on: Sep05, 2009
Because this WAL archive drive has become full, all WAL archive segments to
be archived are accumulated into pg_xlog/ directory itself. Eventually, 9
months (as of today from Sep05, 2009) of WAL archives are residing in
pg_xlog/ directory.
My question is, in case if I would like to perform recovery process as it is
in this situation, will this work out? That is, I'm seeing/finding out
whether recovery process would perform successfully anywhere between the
date range Aug03, 2009 (my base backup date) and as of today - Jun03, 2009.
Reason I'm asking this is still all my WAL archives are residing in pg_xlog/
directory.
Experts advice/idea/suggestion on this appreciated.
Regards,
Gnanam
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PITR Recovery Question
2010-06-03 13:37 PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-04 11:54 ` PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
@ 2010-06-04 15:02 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-05 05:21 ` Re: PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
1 sibling, 1 reply; 15+ messages in thread
From: Kevin Grittner @ 2010-06-04 15:02 UTC (permalink / raw)
To: pgsql-admin; gnanam@zoniac.com; +Cc: fgp@phlo.org
"Gnanakumar" <gnanam@zoniac.com> wrote:
> My pg_xlog/ and walarchive/ directory locations are
> "/usr/local/pgsql/data/pg_xlog" and "/mnt/pitr/walarchive"
> respectively.
>
> In case, if I decide to clean the old WAL archives and set right
> PITR from today onwards by taking base backup, so that I can start
> managing and maintaining atleast from now onwards, what is the
> correct way/method of removing files from pg_xlog/,
> pg_xlog/archive_status/ and /mnt/pitr/walarchive/ directories?
It is generally unsafe to delete any WAL files from pg_xlog. If
they are there because your archive command has been failing, you
need to turn off archiving or (probably more convenient) allow the
archive script to return success until things clear. One trick
would be to temporarily change your archive_command to 'true',
delete all files from your archive, and then change the command
back. Doing that without exposing yourself to a period where you
have no backup might be tricky, though.
If the only problem with the archive command is that the archive fs
is full, I would copy the contents of the archive directory to tape
or whatever medium you have for long-term storage, delete the
contents, and let archive succeed. The pg_xlog directory will
eventually clear, and then I would get a fresh PITR base backup
(following all the documented steps for doing so). You really want
to see WAL files flowing to your archive location before you start
the process of getting a new base backup.
If there's some other reason that the archive command has been
failing, what is it?
-Kevin
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PITR Recovery Question
2010-06-03 13:37 PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-04 11:54 ` PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-04 15:02 ` Re: PITR Recovery Question Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-06-05 05:21 ` Gnanakumar <gnanam@zoniac.com>
0 siblings, 0 replies; 15+ messages in thread
From: Gnanakumar @ 2010-06-05 05:21 UTC (permalink / raw)
To: 'Kevin Grittner' <Kevin.Grittner@wicourts.gov>; pgsql-admin; +Cc: fgp@phlo.org
Hi Kevin,
> It is generally unsafe to delete any WAL files from pg_xlog. If
> they are there because your archive command has been failing, you
> need to turn off archiving or (probably more convenient) allow the
> archive script to return success until things clear. One trick
> would be to temporarily change your archive_command to 'true',
> delete all files from your archive, and then change the command
> back. Doing that without exposing yourself to a period where you
> have no backup might be tricky, though.
I'm trying to see and understand your view point, but I couldn't able to get
this particular step clearly: "One trick would be to temporarily change
your archive_command to 'true', delete all files from your archive, and then
change the command back ". Can you please clarify and explain on this?
When you say *temporarily changing archive_command to 'true' *, do you mean
enabling/disabling of WAL archiving here? Per documentation, "If this is an
empty string (the default), WAL archiving is disabled.". And when you say
"change the command back", I understood it as *disabling*. Is my
understanding correct?
> If the only problem with the archive command is that the archive fs
> is full, I would copy the contents of the archive directory to tape
> or whatever medium you have for long-term storage, delete the
> contents, and let archive succeed. The pg_xlog directory will
> eventually clear, and then I would get a fresh PITR base backup
> (following all the documented steps for doing so). You really want
> to see WAL files flowing to your archive location before you start
> the process of getting a new base backup.
Yes, probably I should go ahead and proceed as you suggested above, that is
allowing archive script to run successfully until things are completely
clear.
> If there's some other reason that the archive command has been
> failing, what is it?
No other reason. It was failing only because my WAL archive drive was full.
Regards,
Gnanam
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PITR Recovery Question
2010-06-03 13:37 PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-04 11:54 ` PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
@ 2010-06-05 00:00 ` Florian Pflug <fgp@phlo.org>
2010-06-05 07:05 ` Re: PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
1 sibling, 1 reply; 15+ messages in thread
From: Florian Pflug @ 2010-06-05 00:00 UTC (permalink / raw)
To: gnanam@zoniac.com; +Cc: pgsql-admin
On Jun 4, 2010, at 13:54 , Gnanakumar wrote:
> In case, if I decide to clean the old WAL archives and set right PITR from
> today onwards by taking base backup, so that I can start managing and
> maintaining atleast from now onwards, what is the correct way/method of
> removing files from pg_xlog/, pg_xlog/archive_status/ and
> /mnt/pitr/walarchive/ directories?
You should *never*, I repeat, *never* remove files from pg_xlog manually unless you know *exactly* what you're doing!
If you remove those, and postgres crashes, you *will* lose your whole database!
About the same holds for pg_xlog/archive_status. Removing files from there won't prevent postgres from starting, but it *will* interfere with the WAL archiving process, and may lead to unusable base backups!
Postgres will recycle old WAL segments automatically, once they're no longer needed for crash recovery *and* after they've been archived successfully (if archiving is enabled). Once a WAL segment is recycled, it's corresponding archive_status/ file(s) will be removed too.
The correct way to clean out pg_xlog therefore is to either disable WAL archiving, or to make sure your archive_command succeeds eventually.
A common way to use PITR is the following.
1) You setup an archive_command.
2) You take a base backup. That also triggers the creation of a backup history file (<number1>.<number2>.backup) in the archive directory.
3) You remove old base backups. You probably want to keep more than one, though, just in case. How long you retain your base backups determines
how far back in time you'll be able to go in the event of a disaster. The furthest point you can go back to is the time pg_stop_backup() was called while taking the oldest base backup you retained.
4) You remove all WAL segments that predate the remaining base backups. For that, you find the backup history file in the archive directory that corresponds to the oldest remaining base backup and then remove all WAL segments whose name is numerically smaller than the <number1> from that backup history file. Keeping older WAL segments buys you nothing - WAL files without a base backup that *predates* them are worthless.
5) Your archive directory will now slowly grow. At some point the cost of storing all those segments will out-weight the cost of taking a new base backup. Whether that happens after an hour, a day, a week or a year depends on the size of your database vs. the amount of modifications it receives. However, at some point or another you will reach that cutoff point, and that is when you continue with (2).
http://www.postgresql.org/docs/8.2/static/continuous-archiving.html explains all of this pretty exhaustively.
best regards,
Florian Pflug
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PITR Recovery Question
2010-06-03 13:37 PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-04 11:54 ` PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-05 00:00 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
@ 2010-06-05 07:05 ` Gnanakumar <gnanam@zoniac.com>
2010-06-06 12:41 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
0 siblings, 1 reply; 15+ messages in thread
From: Gnanakumar @ 2010-06-05 07:05 UTC (permalink / raw)
To: 'Florian Pflug' <fgp@phlo.org>; +Cc: pgsql-admin
Hi Florian,
Thanks for your valuable suggestion and a detailed step on common way to use
PITR. Things are very clear now except that I've some other question in
connection to this.
> The correct way to clean out pg_xlog therefore is to either disable WAL
archiving, or to make sure your archive_command succeeds eventually.
Probably I would go with the 2nd option, that is allowing archive command to
run successfully until things are completely clear.
But this question is for my understanding: In case if I decide to go with
1st option, that is disable WAL archiving for a while, will it completely
clean out files from pg_xlog/ and pg_xlog/archive_status/ directories, so
that I can start the PITR by taking base backup by enabling WAL archiving
later?
> A common way to use PITR is the following.
> 4) You remove all WAL segments that predate the remaining base backups.
For that, you find the backup history file in the archive directory that
corresponds to the oldest remaining base backup and then remove all WAL
segments whose name is numerically smaller than the <number1> from that
backup history file. Keeping older WAL segments buys you nothing - WAL files
without a base backup that *predates* them are worthless.
Can you share with me any automated shell script that takes care of this
removal automatically? Or can you share any systematic way (steps) of doing
things if I want to do this manually?
Regards,
Gnanam
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PITR Recovery Question
2010-06-03 13:37 PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-04 11:54 ` PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-05 00:00 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
2010-06-05 07:05 ` Re: PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
@ 2010-06-06 12:41 ` Florian Pflug <fgp@phlo.org>
2010-06-06 23:23 ` Re: PITR Recovery Question Samuel Stearns <SStearns@internode.com.au>
2010-06-08 05:29 ` Re: PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
0 siblings, 2 replies; 15+ messages in thread
From: Florian Pflug @ 2010-06-06 12:41 UTC (permalink / raw)
To: gnanam@zoniac.com; +Cc: pgsql-admin
On Jun 5, 2010, at 9:05 , Gnanakumar wrote:
> Thanks for your valuable suggestion and a detailed step on common way to use
> PITR. Things are very clear now except that I've some other question in
> connection to this.
>
>> The correct way to clean out pg_xlog therefore is to either disable WAL archiving, or to make sure your archive_command succeeds eventually.
>
> Probably I would go with the 2nd option, that is allowing archive command to
> run successfully until things are completely clear.
>
> But this question is for my understanding: In case if I decide to go with
> 1st option, that is disable WAL archiving for a while, will it completely
> clean out files from pg_xlog/ and pg_xlog/archive_status/ directories, so
> that I can start the PITR by taking base backup by enabling WAL archiving
> later?
If you disable WAL archiving by setting archive_command to 'true', it'll surely clean out the files, since postgresql will actually believe it archived them successfully. I not sure what happens if you set archive_command to '' - that might disable the archiving process completely, and hence prevent the cleanup.
>> A common way to use PITR is the following.
>
>> 4) You remove all WAL segments that predate the remaining base backups. For that, you find the backup history file in the archive directory that corresponds to the oldest remaining base backup and then remove all WAL segments whose name is numerically smaller than the <number1> from that backup history file. Keeping older WAL segments buys you nothing - WAL files without a base backup that *predates* them are worthless.
>
> Can you share with me any automated shell script that takes care of this
> removal automatically? Or can you share any systematic way (steps) of doing
> things if I want to do this manually?
Sorry, I don't have a script for this at hand. But a quick search through the pgsql-admin archive brings up this post, which contains such a script.
http://archives.postgresql.org/pgsql-admin/2006-03/msg00337.php
best regards,
Florian Pflug
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PITR Recovery Question
2010-06-03 13:37 PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-04 11:54 ` PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-05 00:00 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
2010-06-05 07:05 ` Re: PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-06 12:41 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
@ 2010-06-06 23:23 ` Samuel Stearns <SStearns@internode.com.au>
2010-06-08 05:29 ` Re: PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
1 sibling, 1 reply; 15+ messages in thread
From: Samuel Stearns @ 2010-06-06 23:23 UTC (permalink / raw)
To: gnanam@zoniac.com <gnanam@zoniac.com>; +Cc: pgsql-admin
Here's a script to make your backup and rsync it to a remote destination:
#!/bin/bash
echo "checkpoint"
echo "CHECKPOINT;" | /local/pkg/bin/psql template1
echo "start backup"
echo "SELECT pg_start_backup('cisoradr:/cis/pgsql/katana7/backup');" | /local/pkg/bin/psql template1
echo "rsync"
/local/pkg/bin/rsync --delete -azxH /local/app/postgres/data pgsql@cisoradr-ext:/cis/pgsql/katana7/backup/.
echo "Stop backup"
echo "SELECT pg_stop_backup();" | /local/pkg/bin/psql template1
Sam
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Florian Pflug
Sent: Sunday, 6 June 2010 10:11 PM
To: gnanam@zoniac.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR Recovery Question
On Jun 5, 2010, at 9:05 , Gnanakumar wrote:
> Thanks for your valuable suggestion and a detailed step on common way to use
> PITR. Things are very clear now except that I've some other question in
> connection to this.
>
>> The correct way to clean out pg_xlog therefore is to either disable WAL archiving, or to make sure your archive_command succeeds eventually.
>
> Probably I would go with the 2nd option, that is allowing archive command to
> run successfully until things are completely clear.
>
> But this question is for my understanding: In case if I decide to go with
> 1st option, that is disable WAL archiving for a while, will it completely
> clean out files from pg_xlog/ and pg_xlog/archive_status/ directories, so
> that I can start the PITR by taking base backup by enabling WAL archiving
> later?
If you disable WAL archiving by setting archive_command to 'true', it'll surely clean out the files, since postgresql will actually believe it archived them successfully. I not sure what happens if you set archive_command to '' - that might disable the archiving process completely, and hence prevent the cleanup.
>> A common way to use PITR is the following.
>
>> 4) You remove all WAL segments that predate the remaining base backups. For that, you find the backup history file in the archive directory that corresponds to the oldest remaining base backup and then remove all WAL segments whose name is numerically smaller than the <number1> from that backup history file. Keeping older WAL segments buys you nothing - WAL files without a base backup that *predates* them are worthless.
>
> Can you share with me any automated shell script that takes care of this
> removal automatically? Or can you share any systematic way (steps) of doing
> things if I want to do this manually?
Sorry, I don't have a script for this at hand. But a quick search through the pgsql-admin archive brings up this post, which contains such a script.
http://archives.postgresql.org/pgsql-admin/2006-03/msg00337.php
best regards,
Florian Pflug
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PITR Recovery Question
2010-06-03 13:37 PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-04 11:54 ` PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-05 00:00 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
2010-06-05 07:05 ` Re: PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-06 12:41 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
2010-06-06 23:23 ` Re: PITR Recovery Question Samuel Stearns <SStearns@internode.com.au>
@ 2010-06-08 05:29 ` Gnanakumar <gnanam@zoniac.com>
0 siblings, 0 replies; 15+ messages in thread
From: Gnanakumar @ 2010-06-08 05:29 UTC (permalink / raw)
To: 'Samuel Stearns' <SStearns@internode.com.au>; +Cc: pgsql-admin
Hi Sam,
Thank your sharing this script.
> Here's a script to make your backup and rsync it to a remote destination:
> #!/bin/bash
> echo "checkpoint"
> echo "CHECKPOINT;" | /local/pkg/bin/psql template1
> echo "start backup"
> echo "SELECT pg_start_backup('cisoradr:/cis/pgsql/katana7/backup');" |
/local/pkg/bin/psql template1
> echo "rsync"
> /local/pkg/bin/rsync --delete -azxH /local/app/postgres/data
pgsql@cisoradr-ext:/cis/pgsql/katana7/backup/.
> echo "Stop backup"
> echo "SELECT pg_stop_backup();" | /local/pkg/bin/psql template1
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PITR Recovery Question
2010-06-03 13:37 PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-04 11:54 ` PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-05 00:00 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
2010-06-05 07:05 ` Re: PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-06 12:41 ` Re: PITR Recovery Question Florian Pflug <fgp@phlo.org>
@ 2010-06-08 05:29 ` Gnanakumar <gnanam@zoniac.com>
1 sibling, 0 replies; 15+ messages in thread
From: Gnanakumar @ 2010-06-08 05:29 UTC (permalink / raw)
To: 'Florian Pflug' <fgp@phlo.org>; +Cc: pgsql-admin
Hi Florian,
Thanks for the clarification and a link to a post on automated script.
On Jun 5, 2010, at 9:05 , Gnanakumar wrote:
> Thanks for your valuable suggestion and a detailed step on common way to
use
> PITR. Things are very clear now except that I've some other question in
> connection to this.
>
>> The correct way to clean out pg_xlog therefore is to either disable WAL
archiving, or to make sure your archive_command succeeds eventually.
>
> Probably I would go with the 2nd option, that is allowing archive command
to
> run successfully until things are completely clear.
>
> But this question is for my understanding: In case if I decide to go with
> 1st option, that is disable WAL archiving for a while, will it completely
> clean out files from pg_xlog/ and pg_xlog/archive_status/ directories, so
> that I can start the PITR by taking base backup by enabling WAL archiving
> later?
If you disable WAL archiving by setting archive_command to 'true', it'll
surely clean out the files, since postgresql will actually believe it
archived them successfully. I not sure what happens if you set
archive_command to '' - that might disable the archiving process completely,
and hence prevent the cleanup.
>> A common way to use PITR is the following.
>
>> 4) You remove all WAL segments that predate the remaining base backups.
For that, you find the backup history file in the archive directory that
corresponds to the oldest remaining base backup and then remove all WAL
segments whose name is numerically smaller than the <number1> from that
backup history file. Keeping older WAL segments buys you nothing - WAL files
without a base backup that *predates* them are worthless.
>
> Can you share with me any automated shell script that takes care of this
> removal automatically? Or can you share any systematic way (steps) of
doing
> things if I want to do this manually?
Sorry, I don't have a script for this at hand. But a quick search through
the pgsql-admin archive brings up this post, which contains such a script.
http://archives.postgresql.org/pgsql-admin/2006-03/msg00337.php
best regards,
Florian Pflug
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PITR Recovery Question
@ 2010-06-05 14:08 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-08 05:22 ` Re: PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
0 siblings, 1 reply; 15+ messages in thread
From: Kevin Grittner @ 2010-06-05 14:08 UTC (permalink / raw)
To: pgsql-admin; gnanam@zoniac.com; +Cc: fgp@phlo.org
"Gnanakumar" wrote:
> I couldn't able to get this particular step clearly: "One trick
> would be to temporarily change your archive_command to 'true',
> delete all files from your archive, and then change the command
> back ". Can you please clarify and explain on this?
Based on other statements you've made, this isn't a trick you want to
use; just make space in the archive directory, let archiving catch
up, and then take a fresh base backup.
That said, this trick is a way to tell PostgreSQL the archive was
successful, even though it wasn't actually copied. This is
occassionally a useful trick to clear out a backlog of WAL files very
quickly, at the cost of creating a gap in your WAL archive. Your OS
likely has an executable and/or a shell builtin named "true" which
does nothing except return the "success" exit code of zero. If you
have such a command on your OS and you set your archive command to
that, PostgreSQL will blast through cleaning up old WAL files.
kevin@kevin-desktop:~$ true
kevin@kevin-desktop:~$ echo $?
0
But since you said you can copy off the contents of your archive
directory and delete to make room, that's clearly the way to go.
-Kevin
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PITR Recovery Question
2010-06-05 14:08 Re: PITR Recovery Question Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-06-08 05:22 ` Gnanakumar <gnanam@zoniac.com>
0 siblings, 0 replies; 15+ messages in thread
From: Gnanakumar @ 2010-06-08 05:22 UTC (permalink / raw)
To: 'Kevin Grittner' <Kevin.Grittner@wicourts.gov>; pgsql-admin; +Cc: fgp@phlo.org
Hi Kevin,
Thanks for the clarification.
> kevin@kevin-desktop:~$ true
> kevin@kevin-desktop:~$ echo $?
> 0
Yes, my OS also has got this executable and is working.
Regards,
Gnanam
-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Saturday, June 05, 2010 7:39 PM
To: pgsql-admin@postgresql.org; gnanam@zoniac.com
Cc: fgp@phlo.org
Subject: RE: [ADMIN] PITR Recovery Question
"Gnanakumar" wrote:
> I couldn't able to get this particular step clearly: "One trick
> would be to temporarily change your archive_command to 'true',
> delete all files from your archive, and then change the command
> back ". Can you please clarify and explain on this?
Based on other statements you've made, this isn't a trick you want to
use; just make space in the archive directory, let archiving catch
up, and then take a fresh base backup.
That said, this trick is a way to tell PostgreSQL the archive was
successful, even though it wasn't actually copied. This is
occassionally a useful trick to clear out a backlog of WAL files very
quickly, at the cost of creating a gap in your WAL archive. Your OS
likely has an executable and/or a shell builtin named "true" which
does nothing except return the "success" exit code of zero. If you
have such a command on your OS and you set your archive command to
that, PostgreSQL will blast through cleaning up old WAL files.
kevin@kevin-desktop:~$ true
kevin@kevin-desktop:~$ echo $?
0
But since you said you can copy off the contents of your archive
directory and delete to make room, that's clearly the way to go.
-Kevin
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2010-06-08 05:29 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-03 13:37 PITR Recovery Question Gnanakumar <gnanam@zoniac.com>
2010-06-03 15:19 ` Florian Pflug <fgp@phlo.org>
2010-06-04 05:05 ` Gnanakumar <gnanam@zoniac.com>
2010-06-04 10:59 ` Florian Pflug <fgp@phlo.org>
2010-06-04 11:54 ` Gnanakumar <gnanam@zoniac.com>
2010-06-04 15:02 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-05 05:21 ` Gnanakumar <gnanam@zoniac.com>
2010-06-05 00:00 ` Florian Pflug <fgp@phlo.org>
2010-06-05 07:05 ` Gnanakumar <gnanam@zoniac.com>
2010-06-06 12:41 ` Florian Pflug <fgp@phlo.org>
2010-06-06 23:23 ` Samuel Stearns <SStearns@internode.com.au>
2010-06-08 05:29 ` Gnanakumar <gnanam@zoniac.com>
2010-06-08 05:29 ` Gnanakumar <gnanam@zoniac.com>
2010-06-05 14:08 Re: PITR Recovery Question Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-08 05:22 ` Gnanakumar <gnanam@zoniac.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox