Base backups and WAL files archiving

[Jonathan Leroy]

Hi all,

I need a clarification regarding base backups. In
http://blog.2ndquadrant.com/management-wal-archive-barman/ Gabriele
said :

"According to Barman design, every WAL file is automatically
associated to the closest previous base backup available in the
server’s backup history.
This definition implies that, in case no base backup has yet been
taken, the cron command discards the incoming WAL files from the
server’s archive."

So, if I understand correctly, I just need to make a base backup when
adding the remote server to Barman, and it's no longer required to
make base backups ?
At this time, I use a crontab that launch "barman backup all" every 8
hours : is it really needed ?

Thank you,

--
Jonathan Leroy

[Gabriele Bartolini]

Hi Jonathan,

Il 25/04/13 19:52, Jonathan Leroy ha scritto:

> So, if I understand correctly, I just need to make a base backup when
> adding the remote server to Barman, and it's no longer required to
> make base backups ?
> At this time, I use a crontab that launch "barman backup all" every 8
> hours : is it really needed ?

Under normal circumstances, a weekly backup is more than enough. 8 hours
is a very high frequency, which could be justified only by high write
workloads, in order to reduce the recovery time.

Cheers,
Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele....@2ndQuadrant.it | www.2ndQuadrant.it

[Marcelo Mella]

Hi Guys 

Few days ago I started to try barman, and still I'm trying it, but until now it seems to be a great solution to keep backups of all the databases that I need to manage

But, there is a concept that I still can't understand, and I need your help 

Barman manage the backups using separately the WAL files with the backups files, (I'm not sure what exactly are the backup files), so If I'm not wrong, the WAL files are transmitted from Postgres to Barman server when they are generated. Is correct? 

Initially, I was thinking to create backups every 30 mins o even 1 hour, to minimize the amount of data loss in case of disaster scenario, but after read other comments here,  you recommend use once a week or even every fortnight like a good choice. 

For example, if the backup was created a Sunday night and Thursday a disaster happened, the sunday backup + WAL files (more recent created files?) are enough to recreate the Database with the minimum loss of data?. 

I really appreciate your help to understand better how manage my backups 

Thanks

 gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

[Damon Snyder]

Hi Marcelo,

There are two components to a "point in time recovery" backup (also known as PITR). The first component is the base backup. The base backup captures the "raw" or physical data from the postgres installation. This is usually the /var/lib/pgsql/data directory but may vary depending on your installation. This base backup is usually taken using rsync or other file copy utility.

The second component of the PITR backup is the write-ahead log (WAL) archiving or shipping. You can think of the WALs as diffs or deltas between the current time and when the base backup was taken. In order to perform a recovery from a base backup + WALs, you need the base backup and all of the WALs up to the point in time that you want to recover to (hence PITR). Given this, you typically want to copy your base backup and WALs to a different machine (or geographic location) so that you have everything you need to perform a recovery in the event of a catastrophic failure of your database system.

The WAL archiving portion is typically configured with the checkpoint_* and archive_* configuration options in postgresql.conf. The mechanical process of moving WALs is specified in the archive_command configuration option. The archive_command is usually a script that will rsync or scp the WALs to a different system. You could also copy them to NFS or another networked filesystem.

The great think about barman is that it handles the base backup (barman backup my-db-host) and WAL processing (happens via barman cron) for you. It also automates the recovery down to one command.

The frequency of your PITR backups depends on your situation. It really depends on your situation. You might take the following into consideration when determining the backup scheme that fits your needs:

• Consider the impact of the backup on the running system and your users. A base backup is going to be IO intensive depending on the size of your database. In our case, we can only tolerate doing the backups during off-peak hours in the middle of the night.
  
• How much of an outage can you tolerate? Run some tests. Take a backup, wait 24 hours, then try a restore. How long does it take to restore? Using that as a guide you can then toggle the frequency to match your business requirements.
• Consider the cost. Keeping more backups requires more disk space. Do you need redundancy in your backups? How many do you need to keep? Are there legal requirements?

Addressing your specific example regarding taking a backup on Sunday and restoring on Thursday-- the base backup plus all of the WALs up to the point of disaster should be enough to restore the system up to the last complete WAL that was archived. Depending on your configuration this could mean a data loss of zero to some number of minutes determined by your checkpoint settings. Generally speaking the data loss should be minimal. Note that if your base backup is large and the number of data modifications is also large then the recovery could take some time (it also depends on your IO subsystem). In our case, a ~60GB database + 8 hours of WALs (90/10 read to write workload) takes about 30 minutes to restore on enterprise SSDs.

Hope this helps,

Damon

--
--
You received this message because you are subscribed to the "Barman for PostgreSQL" group.
To post to this group, send email to pgba...@googlegroups.com
To unsubscribe from this group, send email to
pgbarman+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/pgbarman?hl=en?hl=en-GB
 
---
You received this message because you are subscribed to the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgbarman+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
http://damonsnyder.net

[Marcelo Mella]

Hi Damon

Thank you very much, great answer.

Now, I need to estimate the amount of time that the backup generation will take in my case (until now, I'm only use small databases to test the solution). But with all the information that you give, I can get an idea how proceed ;)

Again, thank you

Bye

[Veit Guna]

Hi.

Thanks for the tips. I don't want to hijack this thread, but I have a question that goes in the same direction.
I am doing a nightly base backup with barman and syncing wals via rsync.
I would like to keep the last 5 base backups and one per month just for safety and historical reasons.
I made a script that uses the barman script accordingly using delete etc.
But it seems that the size of the last base backups wals is still increasing over time when new base backups are performed.
Is this expected behavior or do I see things the wrong way? I am using barman list command to verify the sizes.
I would expect that the size of an old backup is fix after a new base backup is created. Do I miss something?

Keep up the good work :-)!

Damon Snyder <drsn...@gmail.com> schrieb:

--
Von meinem Tablet gesendet.

[Damon Snyder]

Hi Veit,

If I understand your question regarding the size of the WALs with the first (oldest) base backup this is to be expected. The size of the WALs should reflect the WALs required to bring the base backup up-to the present. So, the older the base backup, the more WALs that will be required to restore.

That being said, I have only ever restored from the most recent valid base backup. I wouldn't expect there to be any difference in the outcome (other than how long it takes) if you restore using the newest or the oldest backup so long as you have all of the WALs.

Hope this helps,

Damon

[Gabriele Bartolini]

Spot on, Damon! Very good answer, I would not have been able to do
better.

On Tue, 12 Nov 2013 10:13:13 -0800, Damon Snyder <drsn...@gmail.com>
wrote:

> * Consider the impact of the backup on the running system and your

> users. A base backup is going to be IO intensive depending on the
> size
> of your database. In our case, we can only tolerate doing the backups
> during off-peak hours in the middle of the night.

> * How much of an outage can you tolerate? Run some tests. Take a

> backup, wait 24 hours, then try a restore. How long does it take to
> restore? Using that as a guide you can then toggle the frequency to
> match your business requirements.

> * Consider the cost. Keeping more backups requires more disk space.

> Do you need redundancy in your backups? How many do you need to keep?
> Are there legal requirements?
>
> Addressing your specific example regarding taking a backup on Sunday
> and restoring on Thursday-- the base backup plus all of the WALs up
> to
> the point of disaster should be enough to restore the system up to
> the
> last complete WAL that was archived. Depending on your configuration
> this could mean a data loss of zero to some number of minutes
> determined by your checkpoint settings. Generally speaking the data
> loss should be minimal. Note that if your base backup is large and
> the
> number of data modifications is also large then the recovery could
> take some time (it also depends on your IO subsystem). In our case, a
> ~60GB database + 8 hours of WALs (90/10 read to write workload) takes
> about 30 minutes to restore on enterprise SSDs.
>
> Hope this helps,
> Damon
>

>  gabriele....@2ndQuadrant.it | www.2ndQuadrant.it [2]

>
> --
> --
> You received this message because you are subscribed to the "Barman
> for PostgreSQL" group.

> To post to this group, send email to pgba...@googlegroups.com [3]

> To unsubscribe from this group, send email to

> pgbarman+u...@googlegroups.com [4]

> For more options, visit this group at

> http://groups.google.com/group/pgbarman?hl=en?hl=en-GB [5]

>  
> ---
> You received this message because you are subscribed to the Google
> Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
> To unsubscribe from this group and stop receiving emails from it,

> send an email to pgbarman+u...@googlegroups.com [6].

> For more options, visit https://groups.google.com/groups/opt_out

> [7].
>
> --
> http://damonsnyder.net [8]

>
> --
> --
> You received this message because you are subscribed to the "Barman
> for PostgreSQL" group.
> To post to this group, send email to pgba...@googlegroups.com
> To unsubscribe from this group, send email to
> pgbarman+u...@googlegroups.com
> For more options, visit this group at

> http://groups.google.com/group/pgbarman?hl=en?hl=en-GB [9]

>
> ---
> You received this message because you are subscribed to the Google
> Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to pgbarman+u...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out

> [10].
>
>
> Links:
> ------
> [1] mailto:mme...@gmail.com
> [2] http://www.2ndQuadrant.it
> [3] mailto:pgba...@googlegroups.com
> [4] mailto:pgbarman%2Bunsu...@googlegroups.com
> [5] http://groups.google.com/group/pgbarman?hl=en?hl=en-GB
> [6] mailto:pgbarman%2Bunsu...@googlegroups.com
> [7] https://groups.google.com/groups/opt_out
> [8] http://damonsnyder.net
> [9] http://groups.google.com/group/pgbarman?hl=en?hl=en-GB
> [10] https://groups.google.com/groups/opt_out

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support

Gabriele....@2ndQuadrant.it - www.2ndQuadrant.it

[Gabriele Bartolini]

Hi Veit,

thank you for your praises, they are really well accepted. I suggest
that you read this article, if you have not done so earlier:
http://blog.2ndquadrant.com/management-wal-archive-barman/

I tried to explain how the WAL archive works with Barman and I
believe it should answer your questions.

Cheers,
Gabriele

On Tue, 12 Nov 2013 20:01:41 +0100, Veit Guna <veit...@gmx.de> wrote:
> Hi.
>
> Thanks for the tips. I don't want to hijack this thread, but I have a
> question that goes in the same direction.
> I am doing a nightly base backup with barman and syncing wals via
> rsync.
> I would like to keep the last 5 base backups and one per month just
> for safety and historical reasons.
> I made a script that uses the barman script accordingly using delete
> etc.
> But it seems that the size of the last base backups wals is still
> increasing over time when new base backups are performed.
> Is this expected behavior or do I see things the wrong way? I am
> using barman list command to verify the sizes.
> I would expect that the size of an old backup is fix after a new
> base backup is created. Do I miss something?
>
> Keep up the good work :-)!
>

> Damon Snyder schrieb:

> * Consider the impact of the backup on the running system and your

> users. A base backup is going to be IO intensive depending on the
> size
> of your database. In our case, we can only tolerate doing the backups
> during off-peak hours in the middle of the night.

> * How much of an outage can you tolerate? Run some tests. Take a

> backup, wait 24 hours, then try a restore. How long does it take to
> restore? Using that as a guide you can then toggle the frequency to
> match your business requirements.

> * Consider the cost. Keeping more backups requires more disk space.

> Do you need redundancy in your backups? How many do you need to keep?
> Are there legal requirements?
>
> Addressing your specific example regarding taking a backup on Sunday
> and restoring on Thursday-- the base backup plus all of the WALs up
> to
> the point of disaster should be enough to restore the system up to
> the
> last complete WAL that was archived. Depending on your configuration
> this could mean a data loss of zero to some number of minutes
> determined by your checkpoint settings. Generally speaking the data
> loss should be minimal. Note that if your base backup is large and
> the
> number of data modifications is also large then the recovery could
> take some time (it also depends on your IO subsystem). In our case, a
> ~60GB database + 8 hours of WALs (90/10 read to write workload) takes
> about 30 minutes to restore on enterprise SSDs.
>
> Hope this helps,
> Damon
>

>  gabriele....@2ndQuadrant.it | www.2ndQuadrant.it [2]

>
> --
> --
> You received this message because you are subscribed to the "Barman
> for PostgreSQL" group.

> To post to this group, send email to pgba...@googlegroups.com [3]

> To unsubscribe from this group, send email to

> pgbarman+u...@googlegroups.com [4]

> For more options, visit this group at

> http://groups.google.com/group/pgbarman?hl=en?hl=en-GB [5]

>  
> ---
> You received this message because you are subscribed to the Google
> Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
> To unsubscribe from this group and stop receiving emails from it,

> send an email to pgbarman+u...@googlegroups.com [6].
> For more options, visit https://groups.google.com/groups/opt_out
> [7].
>
> --
> http://damonsnyder.net [8]
>

> --
> Von meinem Tablet gesendet.
>

> --
> --
> You received this message because you are subscribed to the "Barman
> for PostgreSQL" group.
> To post to this group, send email to pgba...@googlegroups.com
> To unsubscribe from this group, send email to
> pgbarman+u...@googlegroups.com
> For more options, visit this group at

> http://groups.google.com/group/pgbarman?hl=en?hl=en-GB [9]

>
> ---
> You received this message because you are subscribed to the Google
> Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to pgbarman+u...@googlegroups.com.

> For more options, visit https://groups.google.com/groups/opt_out
> [10].
>
>
> Links:
> ------
> [1] mailto:mme...@gmail.com
> [2] http://www.2ndQuadrant.it
> [3] mailto:pgba...@googlegroups.com
> [4] mailto:pgbarman%2Bunsu...@googlegroups.com
> [5] http://groups.google.com/group/pgbarman?hl=en?hl=en-GB
> [6] mailto:pgbarman%2Bunsu...@googlegroups.com
> [7] https://groups.google.com/groups/opt_out
> [8] http://damonsnyder.net
> [9] http://groups.google.com/group/pgbarman?hl=en?hl=en-GB
> [10] https://groups.google.com/groups/opt_out

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support

Gabriele....@2ndQuadrant.it - www.2ndQuadrant.it

[Veit Guna]

Hi Gabriele.

Thanks for the link. I see clearer now :).

So does it make sense to have an option that allows me to "disable"
further WAL archiving for a specific base backup?
In a sense of a virtual "delete". So I could keep that base backup
(as-is, without associated WALs) for historical reasons.

Maybe also a function to "extract/recover" a specific base backup
(without WALs) from the archive list?

Does it make sense at all :)?

Regards
Veit

[Gabriele Bartolini]

Hi Veit,

On Wed, 13 Nov 2013 10:25:02 +0100, Veit Guna <veit...@gmx.de> wrote:
> So does it make sense to have an option that allows me to "disable"
> further WAL archiving for a specific base backup?
> In a sense of a virtual "delete". So I could keep that base backup
> (as-is, without associated WALs) for historical reasons.

I am really glad you are asking this! When I designed retention
policies I had thought about this issue and therefore prepared the
'wal_retention_policy' option, currently only fixed to the main
retention policy setting.

We are looking for sponsors that want to implement this in Barman,
allowing users to specify, for example:

retention_policy = RECOVERY WINDOW OF 6 MONTHS
wal_retention_policy = RECOVERY WINDOW OF 1 MONTH

With this setting, you can have full PITR for the last month and
standalone backups in the preceeding 5 months (so just base backups and
only WAL files necessary for their consistency).

I suggest you read:

* http://blog.2ndquadrant.com/retention-of-backups-with-barman/
* http://blog.2ndquadrant.com/configuring-retention-policies-in-barman/

> Maybe also a function to "extract/recover" a specific base backup
> (without WALs) from the archive list?

Import/Export of backups is in the TODO list. At the moment though you
can look at the list-files command.

> Does it make sense at all :)?

It does. ;)

Ciao,

Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support

Gabriele....@2ndQuadrant.it - www.2ndQuadrant.it

[Veit Guna]

Ah, I see. Thanks for the pointers!

Regards
Veit