Thread: Barman versus pgBackRest

Hi Thomas,

On 3/6/18 2:53 PM, Thomas Poty wrote:
> Hello Community,
> 
> I hesitate to use barman or pgBackRest.  I have found a lot of common
> points between them and a few differences:

To be clear, I'm the original author and primary maintainer of
pgBackRest.  I'll let the Barman folks speak to their strengths, but I'm
happy to address your points below.

> About pgBarman, I like :
> - be able restore on a remote server from the backup server

This a good feature, and one that has been requested for pgBackRest. You
can do this fairly trivially with ssh, however, so it generally hasn't
been a big deal for people.  Is there a particular reason you need this
feature?

> - use replication slots for backingup wal on the backup server.

Another good feature.  We have not added it yet because pgBackRest was
originally written for very high-volume clusters (100K+ WAL per day) and
our parallel async feature answers that need much better.  We recommend
a replicated standby for more update-to-date data.

Even so, we are looking at adding support for replication slots to
pgBackRest.  We are considering a hybrid scheme that will use
replication to keep the WAL archive as up to date as possible, while
doing bulk transfer with archive_command.

> About pgBackRest, I like :
> 
> - real differential backup.
> - lots of options
> - option for backingup if PostgreSQL is already in backup mode
> 
> 
> I would like to have :
> - advices or feedbach about using pgBackrest or barman.
> - pros and cons of these solutions

I'll stick with some of the major pgBackRest pros:

- Parallel backup including compression and checksums
- Encryption
- S3 support
- Parallel archive
- Delta restore
- Page checksum validation
- Backup resume

More about features here: https://pgbackrest.org

> - differences that I would not have seen.

pgBackRest is used in some very demanding environments and we are
constantly answering the needs of our users with features and
performance improvements, e.g. the enormous improvements to archive-push
speed in the 2.0 release.

I'd be happy to answer any specific questions you have about pgBackRest.

Regards,
-- 
-David
david@pgmasters.net

Hi Thomas,

On 4/11/18 3:14 AM, Thomas Poty wrote:
> 
> Sorry for answering only now but I just saw you answer only now.
> 
>  > To be clear, I'm the original author and primary maintainer of
> pgBackRest.
> 
> I am very happy to see guys like you to take time to answer me. Thank you

You are welcome.  Users are the reason I work on this project.

>  >This a good feature, and one that has been requested for pgBackRest. You
>  >can do this fairly trivially with ssh, however, so it generally hasn't
>  >been a big deal for people.  Is there a particular reason you need this
>  >feature?
> 
> The reason is probably a psychologic matter but I like the idea of a 
> unique connecting point to restore DBs of different location.
> 
> I am very impatient to see "replication slot" support and "remote 
> restore" feature added.

Remote restore is likely to land first, though neither feature is 
currently at the top of the list.  Unfortunately, we have limited 
resources and must prioritize.

Regards,
-- 
-David
david@pgmasters.net

On 03/09/2018 08:56 AM, David Steele wrote:
[snip]
>> About pgBarman, I like :
>> - be able restore on a remote server from the backup server
> This a good feature, and one that has been requested for pgBackRest. You
> can do this fairly trivially with ssh, however, so it generally hasn't
> been a big deal for people.  Is there a particular reason you need this
> feature?

(Sorry to dredge up this old thread.)

Do you just change the IP address of the "restore target"?

>> - use replication slots for backingup wal on the backup server.
> Another good feature.  We have not added it yet because pgBackRest was
> originally written for very high-volume clusters (100K+ WAL per day) and
> our parallel async feature answers that need much better.  We recommend
> a replicated standby for more update-to-date data.

Every N minutes you copy the WAL files to the backup server?


-- 
Angular momentum makes the world go 'round.

On 09/04/2018 07:14 AM, Thomas Poty wrote:

> Do you just change the IP address of the "restore target"?

Do you expect a typical restore command?

I'm investigating barman and pgBackRest to replace our exitsing NetBackup system, so don't know what you mean by "typical restore command".

Here are our typical use cases:

1. If my barman backup server has full backups, diffs and WALs for database server MAIN_PG_SERVER, which hosts databases D1, D2 and D3, how much work is it to do a PITR restore of D2 to a different Pg server?

2. Can I restore an older copy of database D2 to MAIN_PG_SERVER, giving it a new name (so that now there would be databases D1, D2, D3 and D2_OLD)?  That's pretty trivial on SQL Server, and something I've had to do before so the operations staff can research a problem.)

Thanks

That was about barman, in the barman group.  This is asking about pgbackrest...  :)

So: does pgbackrest have this ability which barman does not have?  The "--db-include" option seems to indicate that you can restore a single db, but does indicate whether or not you can rename it.

Your problem looks like this one ;-)

https://groups.google.com/forum/#!topic/pgbarman/kXcEpSLhw8w

answer may help

Physical backup/restore operates on a whole cluster...

Le mar. 4 sept. 2018 à 14:47, Ron <ronljohnsonjr@gmail.com> a écrit :

On 09/04/2018 07:14 AM, Thomas Poty wrote:

> Do you just change the IP address of the "restore target"?

Do you expect a typical restore command?

I'm investigating barman and pgBackRest to replace our exitsing NetBackup system, so don't know what you mean by "typical restore command".

Here are our typical use cases:

1. If my barman backup server has full backups, diffs and WALs for database server MAIN_PG_SERVER, which hosts databases D1, D2 and D3, how much work is it to do a PITR restore of D2 to a different Pg server?

2. Can I restore an older copy of database D2 to MAIN_PG_SERVER, giving it a new name (so that now there would be databases D1, D2, D3 and D2_OLD)?  That's pretty trivial on SQL Server, and something I've had to do before so the operations staff can research a problem.)

Thanks

--
Angular momentum makes the world go 'round.

On 09/04/2018 07:14 AM, Ron wrote:
>
> That was about barman, in the barman group.  This is asking about 
> pgbackrest...  :)
>
> So: does pgbackrest have this ability which barman does not have? The 
> "--db-include" option seems to indicate that you can restore a single 
> db, but does indicate whether or not you can rename it.

https://pgbackrest.org/configuration.html#section-restore/option-db-include

JD


-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
*****     Unless otherwise stated, opinions are my own.   *****

On 09/04/2018 09:24 AM, Joshua D. Drake wrote:
> On 09/04/2018 07:14 AM, Ron wrote:
>>
>> That was about barman, in the barman group.  This is asking about 
>> pgbackrest...  :)
>>
>> So: does pgbackrest have this ability which barman does not have? The 
>> "--db-include" option seems to indicate that you can restore a single db, 
>> but does indicate whether or not you can rename it.
>
> https://pgbackrest.org/configuration.html#section-restore/option-db-include
>

Which implies that you can't do it?

(Postgres backup/restore capabilities are quite limited, which is disapointing.)

-- 
Angular momentum makes the world go 'round.

On 09/04/2018 07:52 AM, Ron wrote:
> On 09/04/2018 09:24 AM, Joshua D. Drake wrote:
>> On 09/04/2018 07:14 AM, Ron wrote:
>>>
>>> That was about barman, in the barman group.  This is asking about 
>>> pgbackrest...  :)
>>>
>>> So: does pgbackrest have this ability which barman does not have? 
>>> The "--db-include" option seems to indicate that you can restore a 
>>> single db, but does indicate whether or not you can rename it.
>>
>> https://pgbackrest.org/configuration.html#section-restore/option-db-include 
>>
>>
>
> Which implies that you can't do it?

You can restore a single database and then issue a simple ALTER DATABASE 
command to change the DB name.

>
> (Postgres backup/restore capabilities are quite limited, which is 
> disapointing.)

Not sure I agree with that. If you want to restore and then rename a DB, 
rename it.

ALTER DATABASE foo RENAME TO bar;

JD



-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
*****     Unless otherwise stated, opinions are my own.   *****

On 09/04/2018 10:24 AM, Joshua D. Drake wrote:
> On 09/04/2018 07:52 AM, Ron wrote:
>> On 09/04/2018 09:24 AM, Joshua D. Drake wrote:
>>> On 09/04/2018 07:14 AM, Ron wrote:
>>>>
>>>> That was about barman, in the barman group.  This is asking about 
>>>> pgbackrest...  :)
>>>>
>>>> So: does pgbackrest have this ability which barman does not have? The 
>>>> "--db-include" option seems to indicate that you can restore a single 
>>>> db, but does indicate whether or not you can rename it.
>>>
>>> https://pgbackrest.org/configuration.html#section-restore/option-db-include
>>>
>>
>> Which implies that you can't do it?
>
> You can restore a single database and then issue a simple ALTER DATABASE 
> command to change the DB name.
>
>>
>> (Postgres backup/restore capabilities are quite limited, which is 
>> disapointing.)
>
> Not sure I agree with that. If you want to restore and then rename a DB, 
> rename it.
>
> ALTER DATABASE foo RENAME TO bar;

But restoring an old "foo" overwrites the existing "foo".  On SQL Server 
databases, we occasionally need to restore an old foo backup "foo_old" along 
side production foo.


-- 
Angular momentum makes the world go 'round.

On 9/4/18 11:24 AM, Joshua D. Drake wrote:
> On 09/04/2018 07:52 AM, Ron wrote:
>> On 09/04/2018 09:24 AM, Joshua D. Drake wrote:
>>> On 09/04/2018 07:14 AM, Ron wrote:
>>>>
>>>> That was about barman, in the barman group.  This is asking about
>>>> pgbackrest...  :)
>>>>
>>>> So: does pgbackrest have this ability which barman does not have?
>>>> The "--db-include" option seems to indicate that you can restore a
>>>> single db, but does indicate whether or not you can rename it.
>>>
>>> https://pgbackrest.org/configuration.html#section-restore/option-db-include
>>>
>>>
>>
>> Which implies that you can't do it?
> 
> You can restore a single database and then issue a simple ALTER DATABASE
> command to change the DB name.

This will work, but I don't think it's what Ron is getting at.

To be clear, it is not possible to restore a database into an *existing*
cluster using pgBackRest selective restore.  This is a limitation of
PostgreSQL file-level backups.

To do what Ron wants you would need to restore it to a new cluster, then
use pg_dump to logically dump and restore it to whatever cluster you
want it in.  This still saves time since there is less to restore but is
obviously not ideal.

Regards,
-- 
-David
david@pgmasters.net

On 09/04/2018 10:51 AM, David Steele wrote:
[snip]
> This will work, but I don't think it's what Ron is getting at.
>
> To be clear, it is not possible to restore a database into an *existing*
> cluster using pgBackRest selective restore.  This is a limitation of
> PostgreSQL file-level backups.
>
> To do what Ron wants you would need to restore it to a new cluster, then
> use pg_dump to logically dump and restore it to whatever cluster you
> want it in.  This still saves time since there is less to restore but is
> obviously not ideal.

That's exactly what I'm referring to.

Presumably I could restore it to a new cluster on the same VM via initdb on 
a different port and PGDATA directory?

-- 
Angular momentum makes the world go 'round.

On 9/4/18 11:55 AM, Ron wrote:
> On 09/04/2018 10:51 AM, David Steele wrote:
> [snip]
>> This will work, but I don't think it's what Ron is getting at.
>>
>> To be clear, it is not possible to restore a database into an *existing*
>> cluster using pgBackRest selective restore.  This is a limitation of
>> PostgreSQL file-level backups.
>>
>> To do what Ron wants you would need to restore it to a new cluster, then
>> use pg_dump to logically dump and restore it to whatever cluster you
>> want it in.  This still saves time since there is less to restore but is
>> obviously not ideal.
> 
> That's exactly what I'm referring to.
> 
> Presumably I could restore it to a new cluster on the same VM via initdb
> on a different port and PGDATA directory?

Definitely.  No need to initdb since all the required files will be
restored by pgBackRest.  You'll just need to create an empty directory
to restore into.

Regards,
-- 
-David
david@pgmasters.net

On 09/04/2018 08:55 AM, Ron wrote:
> On 09/04/2018 10:51 AM, David Steele wrote:
> [snip]
>> This will work, but I don't think it's what Ron is getting at.
>>
>> To be clear, it is not possible to restore a database into an *existing*
>> cluster using pgBackRest selective restore.  This is a limitation of
>> PostgreSQL file-level backups.
>>
>> To do what Ron wants you would need to restore it to a new cluster, then
>> use pg_dump to logically dump and restore it to whatever cluster you
>> want it in.  This still saves time since there is less to restore but is
>> obviously not ideal.
>
> That's exactly what I'm referring to.
>
> Presumably I could restore it to a new cluster on the same VM via 
> initdb on a different port and PGDATA directory?

Yes.

jD


-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
*****     Unless otherwise stated, opinions are my own.   *****

Hi Ron,

On 9/4/18 7:41 AM, Ron wrote:
> On 03/09/2018 08:56 AM, David Steele wrote:
> [snip]
>>> About pgBarman, I like :
>>> - be able restore on a remote server from the backup server
>> This a good feature, and one that has been requested for pgBackRest. You
>> can do this fairly trivially with ssh, however, so it generally hasn't
>> been a big deal for people.  Is there a particular reason you need this
>> feature?
> 
> (Sorry to dredge up this old thread.)
> 
> Do you just change the IP address of the "restore target"?

[I'll assume you wanted to hear about pgBackRest here since we discussed
it down thread.]

Generally restores are done from the database server, but if you want to
run a restore from the backup server you can run it via ssh:

ssh user@pg-server pgbackrest [...]

> 
>>> - use replication slots for backingup wal on the backup server.
>> Another good feature.  We have not added it yet because pgBackRest was
>> originally written for very high-volume clusters (100K+ WAL per day) and
>> our parallel async feature answers that need much better.  We recommend
>> a replicated standby for more update-to-date data.
> 
> Every N minutes you copy the WAL files to the backup server?
> 
> 

-- 
-David
david@pgmasters.net

On 9/4/18 7:41 AM, Ron wrote:
> On 03/09/2018 08:56 AM, David Steele wrote:
> 
>>> - use replication slots for backingup wal on the backup server.
>> Another good feature.  We have not added it yet because pgBackRest was
>> originally written for very high-volume clusters (100K+ WAL per day) and
>> our parallel async feature answers that need much better.  We recommend
>> a replicated standby for more update-to-date data.
> 
> Every N minutes you copy the WAL files to the backup server?

[Accidentally hit send on the previous post, here's the rest...]

WAL segments are transferred whenever Postgres indicates that a segment
is finished via the archive_command.  Async archiving "looks ahead" to
find WAL segments that are ready to archive.

You can use archive_timeout to force Postgres to push a WAL segment
every N seconds for clusters that have idle time.


Regards,
-- 
-David
david@pgmasters.net

Hello,

One strong point of barman IMHO is transparently converting an incremental backup to a full backup for retention purposes, so retention specification is far more liberal than with pgbackrest, and configuring for incremental backup does not pose any limitations to the schedule of backups.
In our environment our net connection to the remote site (repo) is extremely lousy, (although within Switzerland if that makes any difference), so with pgbackrest a full backup of our 1.3TB db, would take about 2 days ,lets say set in cron weekly on sunday, (Sunday 1:00->Tuesday),  then I would have to take incr backups from Wednesday->Saturday. And we would have to also take a full backup next Sunday.
With pgbarman we had to set :
reuse_backup = link
retention_policy = RECOVERY WINDOW OF 14 DAYS
and just perform regular backups. So for barman every backup can be used as a base for the next backup, which achieves fast backups and reduced disk space.
In pgbackrest one has to be explicit about the retention of both full backups and diff backups.
it would be nice in pgbackrest to only have incremental backups and let the system do the necessary management (converting incremental to full) transparently and asynchronously, e.g.via cron.
I have read about the --repo-hardlink option.
"
This gives the appearance that each backup is a full backup at the file-system level
"
So could we just take a first full backup and then switch permanently to incr backups?

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On Thu, 17 Jan 2019 13:09:18 +0200
Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

> Hello,
>
> One strong point of barman IMHO is transparently converting an incremental
> backup to a full backup for retention purposes, so retention specification is
> far more liberal than with pgbackrest, and configuring for incremental backup
> does not pose any limitations to the schedule of backups. In our environment
> our net connection to the remote site (repo) is extremely lousy, (although
> within Switzerland if that makes any difference), so with pgbackrest a full
> backup of our 1.3TB db, would take about 2 days ,lets say set in cron weekly
> on sunday, (Sunday 1:00->Tuesday),  then I would have to take incr backups
> from Wednesday->Saturday. And we would have to also take a full backup next
> Sunday. With pgbarman we had to set : reuse_backup = link retention_policy =
> RECOVERY WINDOW OF 14 DAYS and just perform regular backups. So for barman
> every backup can be used as a base for the next backup, which achieves fast
> backups and reduced disk space. In pgbackrest one has to be explicit about
> the retention of both full backups and diff backups. it would be nice in
> pgbackrest to only have incremental backups and let the system do the
> necessary management (converting incremental to full) transparently and
> asynchronously, e.g.via cron. I have read about the --repo-hardlink option. "
> This gives the appearance that each backup is a full backup at the
> file-system level " So could we just take a first full backup and then switch
> permanently to incr backups?

Funnily enough, I opened an issue a month ago about this feature. If you want
to join the brainstorming, discuss, add some opinions and thoughts, see
https://github.com/pgbackrest/pgbackrest/issues/644

On 17/1/19 4:12 μ.μ., Jehan-Guillaume (ioguix) de Rorthais wrote:
> On Thu, 17 Jan 2019 13:09:18 +0200
> Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
>
>> Hello,
>>
>> One strong point of barman IMHO is transparently converting an incremental
>> backup to a full backup for retention purposes, so retention specification is
>> far more liberal than with pgbackrest, and configuring for incremental backup
>> does not pose any limitations to the schedule of backups. In our environment
>> our net connection to the remote site (repo) is extremely lousy, (although
>> within Switzerland if that makes any difference), so with pgbackrest a full
>> backup of our 1.3TB db, would take about 2 days ,lets say set in cron weekly
>> on sunday, (Sunday 1:00->Tuesday),  then I would have to take incr backups
>> from Wednesday->Saturday. And we would have to also take a full backup next
>> Sunday. With pgbarman we had to set : reuse_backup = link retention_policy =
>> RECOVERY WINDOW OF 14 DAYS and just perform regular backups. So for barman
>> every backup can be used as a base for the next backup, which achieves fast
>> backups and reduced disk space. In pgbackrest one has to be explicit about
>> the retention of both full backups and diff backups. it would be nice in
>> pgbackrest to only have incremental backups and let the system do the
>> necessary management (converting incremental to full) transparently and
>> asynchronously, e.g.via cron. I have read about the --repo-hardlink option. "
>> This gives the appearance that each backup is a full backup at the
>> file-system level " So could we just take a first full backup and then switch
>> permanently to incr backups?
> Funnily enough, I opened an issue a month ago about this feature. If you want
> to join the brainstorming, discuss, add some opinions and thoughts, see
> https://github.com/pgbackrest/pgbackrest/issues/644
Surely I enjoyed your convo there!
>


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt