pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDeleting old WAL-files
6+ messages / 5 participants
[nested] [flat]
* Deleting old WAL-files
@ 2015-05-19 11:43 Gustav Karlsson <Gustav.Karlsson@BEKK.no>
0 siblings, 3 replies; 6+ messages in thread
From: Gustav Karlsson @ 2015-05-19 11:43 UTC (permalink / raw)
To: pgsql-admin
Hi,
I am looking for a good method of determining what WAL-segments can be deleted from our backup-archive.
We are using Postgresql 9.3 and do nightly basebackups along with archiving of WAL-segments.
We would like to prevent our backup-archive from growing forever, and thus delete basebackups and WAL-segments older than say 1 year.
Is there a good way of finding the oldest WAL-segment needed for a given basebackup? (If not, what is the typical method for deleting old WAL-segments?)
(We are aware that Barman is offering this functionality, and might migrate to using it, but would still like to know the method used)
Regards,
Gustav Karlsson
--
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] 6+ messages in thread
* Re: Deleting old WAL-files
@ 2015-05-19 11:51 Magnus Hagander <magnus@hagander.net>
parent: Gustav Karlsson <Gustav.Karlsson@BEKK.no>
2 siblings, 0 replies; 6+ messages in thread
From: Magnus Hagander @ 2015-05-19 11:51 UTC (permalink / raw)
To: Gustav Karlsson <Gustav.Karlsson@bekk.no>; +Cc: pgsql-admin
On Tue, May 19, 2015 at 1:43 PM, Gustav Karlsson <Gustav.Karlsson@bekk.no>
wrote:
> Hi,
>
> I am looking for a good method of determining what WAL-segments can be
> deleted from our backup-archive.
>
> We are using Postgresql 9.3 and do nightly basebackups along with
> archiving of WAL-segments.
>
> We would like to prevent our backup-archive from growing forever, and thus
> delete basebackups and WAL-segments older than say 1 year.
>
> Is there a good way of finding the oldest WAL-segment needed for a given
> basebackup? (If not, what is the typical method for deleting old
> WAL-segments?)
>
You can look inside the backup_label file in the base backup. It has the
starting WAL file required to recover from that backup. If you look at the
.backup file that's been archived to the log archive it also contains the
last segment required.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Deleting old WAL-files
@ 2015-05-19 11:52 Jan Lentfer <Jan.Lentfer@web.de>
parent: Gustav Karlsson <Gustav.Karlsson@BEKK.no>
2 siblings, 0 replies; 6+ messages in thread
From: Jan Lentfer @ 2015-05-19 11:52 UTC (permalink / raw)
To: pgsql-admin
Am 2015-05-19 13:43, schrieb Gustav Karlsson:
> Is there a good way of finding the oldest WAL-segment needed for a
> given basebackup? (If not, what is the typical method for deleting
> old
> WAL-segments?)
>
>
> (We are aware that Barman is offering this functionality, and might
> migrate to using it, but would still like to know the method used)
In short terms you need all the WAL segments created during and after
the base backup. It's also explained in the manual:
To make use of the backup, you will need to keep all the WAL segment
files generated during and after the file system backup. To aid you in
doing this, the base backup process creates a backup history file that
is immediately stored into the WAL archive area. This file is named
after the first WAL segment file that you need for the file system
backup. For example, if the starting WAL file is
0000000100001234000055CD the backup history file will be named something
like 0000000100001234000055CD.007C9330.backup
From:
http://www.postgresql.org/docs/9.4/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP
hth
Jan
--
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] 6+ messages in thread
* Re: Deleting old WAL-files
@ 2015-05-19 12:09 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Gustav Karlsson <Gustav.Karlsson@BEKK.no>
2 siblings, 1 reply; 6+ messages in thread
From: Albe Laurenz @ 2015-05-19 12:09 UTC (permalink / raw)
To: 'Gustav Karlsson *EXTERN*' <Gustav.Karlsson@BEKK.no>; pgsql-admin
Gustav Karlsson wrote:
> I am looking for a good method of determining what WAL-segments can be deleted from our backup-
> archive.
>
> We are using Postgresql 9.3 and do nightly basebackups along with archiving of WAL-segments.
>
> We would like to prevent our backup-archive from growing forever, and thus delete basebackups and WAL-
> segments older than say 1 year.
>
> Is there a good way of finding the oldest WAL-segment needed for a given basebackup? (If not, what is
> the typical method for deleting old WAL-segments?)
The backup contains a file "backup_label" which contains a line like
START WAL LOCATION: 0/83000028 (file 000000010000000000000083)
That is the oldest file needed for recovery.
But a simple solution would be to keep WAL archives for one day more than your oldest
base backup, that way you can never miss a WAL archive that you need.
Yours,
Laurenz Albe
--
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] 6+ messages in thread
* Re: Deleting old WAL-files
@ 2015-05-19 13:29 Gilberto Castillo <gilberto.castillo@etecsa.cu>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 6+ messages in thread
From: Gilberto Castillo @ 2015-05-19 13:29 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: 'Gustav Karlsson *EXTERN*' <gustav.karlsson@bekk.no>; pgsql-admin
> Gustav Karlsson wrote:
>> I am looking for a good method of determining what WAL-segments can be
>> deleted from our backup-
>> archive.
>>
>> We are using Postgresql 9.3 and do nightly basebackups along with
>> archiving of WAL-segments.
>>
>> We would like to prevent our backup-archive from growing forever, and
>> thus delete basebackups and WAL-
>> segments older than say 1 year.
>>
>> Is there a good way of finding the oldest WAL-segment needed for a given
>> basebackup? (If not, what is
>> the typical method for deleting old WAL-segments?)
>
> The backup contains a file "backup_label" which contains a line like
>
> START WAL LOCATION: 0/83000028 (file 000000010000000000000083)
>
> That is the oldest file needed for recovery.
>
> But a simple solution would be to keep WAL archives for one day more than
> your oldest
> base backup, that way you can never miss a WAL archive that you need.
See you:
http://www.postgresql.org/docs/current/static/pgarchivecleanup.html
Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba
---
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
Visit our web-site: <http://www.kaspersky.com;, <http://www.viruslist.com;
--
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] 6+ messages in thread
* Re: Deleting old WAL-files
@ 2015-05-20 05:29 Gustav Karlsson <Gustav.Karlsson@BEKK.no>
parent: Gilberto Castillo <gilberto.castillo@etecsa.cu>
0 siblings, 0 replies; 6+ messages in thread
From: Gustav Karlsson @ 2015-05-20 05:29 UTC (permalink / raw)
To: pgsql-admin
Thank you all for great answers and pointing me to the relevant documentation!
Regards,
Gustav
On May 19, 2015, at 3:29 PM, Gilberto Castillo <gilberto.castillo@etecsa.cu>
wrote:
>
>
>> Gustav Karlsson wrote:
>>> I am looking for a good method of determining what WAL-segments can be
>>> deleted from our backup-
>>> archive.
>>>
>>> We are using Postgresql 9.3 and do nightly basebackups along with
>>> archiving of WAL-segments.
>>>
>>> We would like to prevent our backup-archive from growing forever, and
>>> thus delete basebackups and WAL-
>>> segments older than say 1 year.
>>>
>>> Is there a good way of finding the oldest WAL-segment needed for a given
>>> basebackup? (If not, what is
>>> the typical method for deleting old WAL-segments?)
>>
>> The backup contains a file "backup_label" which contains a line like
>>
>> START WAL LOCATION: 0/83000028 (file 000000010000000000000083)
>>
>> That is the oldest file needed for recovery.
>>
>> But a simple solution would be to keep WAL archives for one day more than
>> your oldest
>> base backup, that way you can never miss a WAL archive that you need.
>
> See you:
>
> http://www.postgresql.org/docs/current/static/pgarchivecleanup.html
>
>
>
> Saludos,
> Gilberto Castillo
> ETECSA, La Habana, Cuba
> ---
> This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
> Visit our web-site: <http://www.kaspersky.com;, <http://www.viruslist.com;
--
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] 6+ messages in thread
end of thread, other threads:[~2015-05-20 05:29 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-19 11:43 Deleting old WAL-files Gustav Karlsson <Gustav.Karlsson@BEKK.no>
2015-05-19 11:51 ` Magnus Hagander <magnus@hagander.net>
2015-05-19 11:52 ` Jan Lentfer <Jan.Lentfer@web.de>
2015-05-19 12:09 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2015-05-19 13:29 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-05-20 05:29 ` Gustav Karlsson <Gustav.Karlsson@BEKK.no>
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