Thread: Deleting old WAL-files

Deleting old WAL-files

From
Gustav Karlsson
Date:
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



Re: Deleting old WAL-files

From
Magnus Hagander
Date:
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. 


--

Re: Deleting old WAL-files

From
Jan Lentfer
Date:
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


Re: Deleting old WAL-files

From
Albe Laurenz
Date:
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

Re: Deleting old WAL-files

From
"Gilberto Castillo"
Date:

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

Re: Deleting old WAL-files

From
Gustav Karlsson
Date:
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>



Proper way to restore from a basebackup without the WAL-files?

From
Gustav Karlsson
Date:
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



Re: Proper way to restore from a basebackup without the WAL-files?

From
John Scalia
Date:
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.
--
Jay

On Tue, Dec 15, 2015 at 11:35 AM, Gustav Karlsson <gustav.karlsson@bekk.no> wrote:
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



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Proper way to restore from a basebackup without the WAL-files?

From
Gustav Karlsson
Date:
Ok, thanks. That was actually what I ended up doing, but I get some warnings in the log. For example:

< 2015-12-15 00:00:58.206 CET ><  ><  >ERROR:  failed to re-find parent key in index “myindex_idx" for deletion target page 101400
..

Is that to be expected? 

Also, will the server be in a state where I can start replication to a slave? (given a fresh backup from the new timeline)


Regards,
Gustav



On Dec 15, 2015, at 6:47 PM, 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.
--
Jay

On Tue, Dec 15, 2015 at 11:35 AM, Gustav Karlsson <gustav.karlsson@bekk.no> wrote:
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



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


Re: Proper way to restore from a basebackup without the WAL-files?

From
Kevin Grittner
Date:
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


Re: Proper way to restore from a basebackup without the WAL-files?

From
Keith
Date:


On Tue, Dec 15, 2015 at 1:19 PM, Kevin Grittner <kgrittn@gmail.com> wrote:
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


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


Hopefully this is just a one-off occurrence where you needed to do this. The best thing long term would be to ensure you're backing up the WAL files along with pg_basebackup. Use the -X option with pg_basebackup so you have a consistent backup that can be started on its own. Or make sure you're backing up your WAL files another way (archive_command on master, pg_receivexlogs, etc).

Re: Proper way to restore from a basebackup without the WAL-files?

From
Gustav Karlsson
Date:
Thanks everyone for your answers and pointing me to relevant documentation! I will attempt to do a dump and restore immediately after pg_resetxlog and see where that leaves me.

It is indeed a one off for us. Generally we have a complete WAL-history.


Regards,
Gustav



On Dec 15, 2015, at 7:58 PM, Keith <keith@keithf4.com> wrote:



On Tue, Dec 15, 2015 at 1:19 PM, Kevin Grittner <kgrittn@gmail.com> wrote:
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


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


Hopefully this is just a one-off occurrence where you needed to do this. The best thing long term would be to ensure you're backing up the WAL files along with pg_basebackup. Use the -X option with pg_basebackup so you have a consistent backup that can be started on its own. Or make sure you're backing up your WAL files another way (archive_command on master, pg_receivexlogs, etc).