Thread: Deleting old WAL-files

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
thansay 1 year. 

Is there a good way of finding the oldest WAL-segment needed for a given basebackup? (If not, what is the typical
methodfor 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
themethod used) 


Regards,
Gustav Karlsson

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

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

> 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>

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>

Hi,

What is the proper way start a new master from a basebackup when you do not have the WAL-files?

We are using postgresql 9.4.

Regards,
Gustav Karlsson

On Tue, Dec 15, 2015 at 11:47 AM, John Scalia <jayknowsunix@gmail.com> wrote:

> If you absolutely must use the pg_basebackup as your source and I'm assuming
> you have a tarball without any of WAL files, you could expand the tarball
> into you data directory, and use pg_resetxlog to "fool" the master into
> starting up. I only say "fool" as it just builds you a blank WAL file which
> this system can use as its integrity check and properly start.

Please note that this will generally leave you with a corrupted
cluster; you would be well advised to follow the advice in the
documentation:

http://www.postgresql.org/docs/9.4/interactive/app-pgresetxlog.html

| After running this command, it should be possible to start the
| server, but bear in mind that the database might contain
| inconsistent data due to partially-committed transactions. You
| should immediately dump your data, run initdb, and reload. After
| reload, check for inconsistencies and repair as needed.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company