Barman disaster recovery solution

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!

Barman will fit most requirements. PgBackRest excels when WAL traffic goes on 100000 files/day or more. I have written an article, not yet publised, on a comparison on the 3 most known solutions. Will
post a link as soon as it gets published.

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

Am 21.02.19 um 08:17 schrieb Julie Nishimura:

Does anyone use this solution? any recommenations?

Thanks!

sure, many of our customers. why not?

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Em 21/02/2019 04:17, Julie Nishimura escreveu:

Does anyone use this solution? any recommenations?

Thanks!

We do use it.

IMHO, those are minimum recommendations:

1) start using it! It's easy and robust.

2) for minimal impact over production servers, setup replicated servers
and create your backup from slave servers.

3) *_test your backups_*. This is a MUST HAVE - no option here.

4) have your backup server in different cities, or states, or even
countries. Never, ever create a backup on the server at the side of your
production server.

5) only communicate with your servers using SSH and private key
certificates. Establish a PKI infrastructure in a way that production
and backup servers only communicate using SSH and certificates.

6) your backup servers shall never ever be connected directly to the
internet. Hackers love low attention backup servers running with minimal
security.

No backup solution (no matter which one you choose) is 100% guaranteed:
your disks may fail, your network mail fail, your memory may fail, files
get corrupted - so, setup a regular "restore" to separate "test backup
server" on daily basis. Having a virtual server for this purpose has
minimal budget impact if any at all, and you save your sanity in case of
a disaster.

Regards,

Edson

Greetings,

* Edson Carlos Ericksson Richter (richter@simkorp.com.br) wrote:

No backup solution (no matter which one you choose) is 100% guaranteed: your
disks may fail, your network mail fail, your memory may fail, files get
corrupted - so, setup a regular "restore" to separate "test backup server"
on daily basis. Having a virtual server for this purpose has minimal budget
impact if any at all, and you save your sanity in case of a disaster.

While performing a straight restore is definitely good, to deal with the
in-place corruption risk of whatever your backup repository is, you really
need to do more than that. If the middle of some index gets corrupted in
the backup, you may not notice it on the restore and even with casual use
of the restored server, which is why robust backup software really should
have a manifest of all the files in the backup and their checksums and
that should be checked on a restore.

One alternative, if your backup solution doesn't handle this for you,
and if you have page-level checksums enabled for your PG cluster (which I
strongly recommend...) would be to perform the complete restore and then
run pg_verify_checksums (or pg_checksums, depending on version) on the
restored cluster (note that you should bring the cluster up and let WAL
replay go through to at least reach consistency too...), which will
hopefully pick up on and report any latent corruption.

Note that doing something like a simple 'pg_dump' on the restored
cluster won't check the page-level checksums in indexes (or check indexes
at all), though that would provide you with a logical export of the data
that should be able to be imported into a new cluster (assuming you keep
the results of the pg_dump, that is..).

Thanks!

Stephen

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!

Barman will fit most requirements. PgBackRest excels when WAL traffic goes on 100000 files/day or more. I have written an article, not yet publised, on a comparison on the 3 most known solutions.
Will post a link as soon as it gets published.

Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql

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

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

On 27/2/19 1:58 μ.μ., richter@simkorp.com.br wrote:

Just to notice, I d o use backup from standby and WAL archive from standby. It is possible. But you have to configure standby with option of wal archive "always".

I guess there are issues with it. If this was so easy then pgbarman and pgbackrest would support it out of the box.

Just my 2c,

Edson Richter

/Enviado do meu Telefone LG/

------ Mensagem original------
*De: *Achilleas Mantzios
*Data: *qua, 27 de fev de 2019 06:40
*Para: *pgsql-general@lists.postgresql.org <mailto:pgsql-general@lists.postgresql.org>;
*Cc:*
*As! sunto:*Re: Barman disaster recovery solution

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!

Barman will fit most requirements. PgBackRest excels when WAL traffic goes on 100000 files/day or more. I have written an article, not yet publised, on a comparison on the 3 most known solutions.
Will post a link as soon as it gets published.

Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql

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

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

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

On 2/27/19 2:31 PM, Achilleas Mantzios wrote:

On 27/2/19 1:58 μ.μ., richter@simkorp.com.br wrote:

Just to notice, I d o use backup from standby and WAL archive from
standby. It is possible. But you have to configure standby with option
of wal archive "always".

I guess there are issues with it. If this was so easy then pgbarman and
pgbackrest would support it out of the box.

There are a few issues with it:

1) If you allow the primary and standby to archive to the same
repository then there needs to be some conflict resolution if they write
at the same time. If they write to different repositories then you need
to decided which one to use for a restore, or have some kind of conflict
resolution between them. It gets complicated.

2) Writing only from the standby reduces load on the primary but if the
connection to the primary is down then you can get behind on archiving.
If something then happens to the primary then your recovery point will
be limited.

Regards,
--
-David
david@pgmasters.net

On 27/2/19 4:16 μ.μ., David Steele wrote:

On 2/27/19 2:31 PM, Achilleas Mantzios wrote:

On 27/2/19 1:58 μ.μ., richter@simkorp.com.br wrote:

Just to notice, I d o use backup from standby and WAL archive from standby. It is possible. But you have to configure standby with option of wal archive "always".

I guess there are issues with it. If this was so easy then pgbarman and pgbackrest would support it out of the box.

There are a few issues with it:

1) If you allow the primary and standby to archive to the same repository then there needs to be some conflict resolution if they write at the same time.  If they write to different repositories
then you need to decided which one to use for a restore, or have some kind of conflict resolution between them.  It gets complicated.

2) Writing only from the standby reduces load on the primary but if the connection to the primary is down then you can get behind on archiving. If something then happens to the primary then your
recovery point will be limited.

David to quote an older email from you:
"pgBackRest currently requires some files and all WAL to be sent from the primary even when doing backup from standby.  We may improve this in the future but it's not on the road map right now. "
So, I had the impression that receiving WALs from the standby was a greater technical problem.

Regards,

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

Em 27/02/2019 09:31, Achilleas Mantzios escreveu:

On 27/2/19 1:58 μ.μ., richter@simkorp.com.br wrote:

Just to notice, I d o use backup from standby and WAL archive from
standby. It is possible. But you have to configure standby with
option of wal archive "always".

I guess there are issues with it. If this was so easy then pgbarman
and pgbackrest would support it out of the box.

Well,

This setup it is working really well for past two years; prior to that,
we used backup from primary server.

We have about 50 databases, half terabyte in total, primary and standby
separated geographically.

We had to write special app to monitor if standby is behind primary (it
compares the transaction id between primary and standby).

For eight years, we had no single failure from PgSQL databases (using
since 9.0 and today on 9.6), replication is for "just in case" data
center unavailability, and backup is for disaster recovery (in case two
data centers in different states from different vendors get out of work
at same time).

But we give no chance to bad luck: we monitor replication status every 2
minutes, we make full backup every 2 days with incremental backup in
between, and test all backups on a recover server every day. As pointed,
we have no single database failure that required to use the replication
server or the backup server, but we will not lower the attention.

Regards,

Edson

On 27/2/19 5:04 μ.μ., Edson Carlos Ericksson Richter wrote:

Em 27/02/2019 09:31, Achilleas Mantzios escreveu:

On 27/2/19 1:58 μ.μ., richter@simkorp.com.br wrote:

Just to notice, I d o use backup from standby and WAL archive from standby. It is possible. But you have to configure standby with option of wal archive "always".

I guess there are issues with it. If this was so easy then pgbarman and pgbackrest would support it out of the box.

Well,

This setup it is working really well for past two years; prior to that, we used backup from primary server.

Using which tool?

We have about 50 databases, half terabyte in total, primary and standby separated geographically.

We had to write special app to monitor if standby is behind primary (it compares the transaction id between primary and standby).

For eight years, we had no single failure from PgSQL databases (using since 9.0 and today on 9.6), replication is for "just in case" data center unavailability, and backup is for disaster recovery
(in case two data centers in different states from different vendors get out of work at same time).

But we give no chance to bad luck: we monitor replication status every 2 minutes, we make full backup every 2 days with incremental backup in between, and test all backups on a recover server every
day. As pointed, we have no single database failure that required to use the replication server or the backup server, but we will not lower the attention.

Which means you tested your backups?

Regards,

Edson

Just my 2c,

Edson Richter

/Enviado do meu Telefone LG/

------ Mensagem original------
*De: *Achilleas Mantzios
*Data: *qua, 27 de fev de 2019 06:40
*Para: *pgsql-general@lists.postgresql.org <mailto:pgsql-general@lists.postgresql.org>;
*Cc:*
*As! sunto:*Re: Barman disaster recovery solution

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!

Barman will fit most requirements. PgBackRest excels when WAL traffic goes on 100000 files/day or more. I have written an article, not yet publised, on a comparison on the 3 most known solutions.
Will post a link as soon as it gets published.

Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql

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

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

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

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

Em 27/02/2019 12:12, Achilleas Mantzios escreveu:

On 27/2/19 5:04 μ.μ., Edson Carlos Ericksson Richter wrote:

Em 27/02/2019 09:31, Achilleas Mantzios escreveu:

On 27/2/19 1:58 μ.μ., richter@simkorp.com.br wrote:

Just to notice, I d o use backup from standby and WAL archive from
standby. It is possible. But you have to configure standby with
option of wal archive "always".

I guess there are issues with it. If this was so easy then pgbarman
and pgbackrest would support it out of the box.

Well,

This setup it is working really well for past two years; prior to
that, we used backup from primary server.

Using which tool?

Barman.

We have about 50 databases, half terabyte in total, primary and
standby separated geographically.

We had to write special app to monitor if standby is behind primary
(it compares the transaction id between primary and standby).

For eight years, we had no single failure from PgSQL databases (using
since 9.0 and today on 9.6), replication is for "just in case" data
center unavailability, and backup is for disaster recovery (in case
two data centers in different states from different vendors get out
of work at same time).

But we give no chance to bad luck: we monitor replication status
every 2 minutes, we make full backup every 2 days with incremental
backup in between, and test all backups on a recover server every
day. As pointed, we have no single database failure that required to
use the replication server or the backup server, but we will not
lower the attention.

Which means you tested your backups?

Recover and run our main app on it.

Regards,

Edson

On Wed, Feb 27, 2019 at 1:39 AM Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:

Hello, as promised here is my blog :

https://severalnines.com/blog/current-state-open-source-backup-management-postgresql

Nice blog post. If you're aiming for a comprehensive run down of tools, I
suggest including wal-g. We've been using it since it was released (and its
predecessor wal-e for years before that) and it's been great. We currently
use it to back up a replica to S3, and it has no issues doing that. In more
recent versions, it supports delta backups, which decrease the time/load
required for a backup immensely in our case.

Cheers,
Mark

On 2/27/19 4:48 PM, Achilleas Mantzios wrote:

On 27/2/19 4:16 μ.μ., David Steele wrote:

On 2/27/19 2:31 PM, Achilleas Mantzios wrote:

On 27/2/19 1:58 μ.μ., richter@simkorp.com.br wrote:

Just to notice, I d o use backup from standby and WAL archive from
standby. It is possible. But you have to configure standby with
option of wal archive "always".

I guess there are issues with it. If this was so easy then pgbarman
and pgbackrest would support it out of the box.

There are a few issues with it:

1) If you allow the primary and standby to archive to the same
repository then there needs to be some conflict resolution if they
write at the same time.  If they write to different repositories then
you need to decided which one to use for a restore, or have some kind
of conflict resolution between them.  It gets complicated.

2) Writing only from the standby reduces load on the primary but if
the connection to the primary is down then you can get behind on
archiving. If something then happens to the primary then your recovery
point will be limited.

David to quote an older email from you:
"pgBackRest currently requires some files and all WAL to be sent from
the primary even when doing backup from standby.  We may improve this in
the future but it's not on the road map right now. "
So, I had the impression that receiving WALs from the standby was a
greater technical problem.

No, it just increases the risk of being behind on archiving.

One of the things pgBackRest does well is move a *lot* of WAL and it is
orders of magnitude faster than streaming replication, which is
single-threaded and uncompressed. So, in spite of the additional load
it's generally safest to archive from the primary, especially on high
write volume clusters.

--
-David
david@pgmasters.net

On 27/2/19 6:52 μ.μ., Mark Fletcher wrote:

On Wed, Feb 27, 2019 at 1:39 AM Achilleas Mantzios <achill@matrix.gatewaynet.com <mailto:achill@matrix.gatewaynet.com>> wrote:

Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql

Nice blog post. If you're aiming for a comprehensive run down of tools, I suggest including wal-g. We've been using it since it was released (and its predecessor wal-e for years before that) and
it's been great. We currently use it to back up a replica to S3, and it has no issues doing that. In more recent versions, it supports delta backups, which decrease the time/load required for a
backup immensely in our case.

Thanks, I know about wal-e, wal-g, I may include this in some future update.

Cheers,
Mark

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

On 28/2/19 1:08 π.μ., Ahmed, Nawaz wrote:

Hi,

I believe the "file copy" method (listed in the table) in pgbackrest is based on pg_basebackup, so i think it should be "pg_basebackup over ssh" as pgbackrest internally calls pg_basebackup. David
Steele can correct me.

No, apparently pgbackrest does not rely on pg_basebackup. Have you ever heard pg_basebackup supporting diff and incr backups ? Or checksums? Or compression ? Or aborted backup resumption ?

Best Regards,

**

*Nawaz Ahmed
Software Development Engineer

Fujitsu Australia Software Technology Pty Ltd*
14 Rodborough Road, Frenchs Forest NSW 2086, Australia
*T* +61 2 9452 9027
Nawaz@fast.au.fujitsu.com <mailto:Nawaz@fast.au.fujitsu.com>
fastware.com.au <http://fastware.com.au/&gt;

*From:*Achilleas Mantzios <achill@matrix.gatewaynet.com>
*Sent:* Wednesday, 27 February 2019 8:40 PM
*To:* pgsql-general@lists.postgresql.org
*Subject:* Re: Barman disaster recovery solution

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!

Barman will fit most requirements. PgBackRest excels when WAL traffic goes on 100000 files/day or more. I have written an article, not yet publised, on a comparison on the 3 most known
solutions. Will post a link as soon as it gets published.

Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql

--

Achilleas Mantzios

IT DEV Lead

IT DEPT

Dynacom Tankers Mgmt

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

Disclaimer

The information in this e-mail is confidential and may contain content that is subject to copyright and/or is commercial-in-confidence and is intended only for the use of the above named addressee.
If you are not the intended recipient, you are hereby notified that dissemination, copying or use of the information is strictly prohibited. If you have received this e-mail in error, please
telephone Fujitsu Australia Software Technology Pty Ltd on + 61 2 9452 9000 or by reply e-mail to the sender and delete the document and all copies thereof.

Whereas Fujitsu Australia Software Technology Pty Ltd would not knowingly transmit a virus within an email communication, it is the receiver’s responsibility to scan all communication and any files
attached for computer viruses and other defects. Fujitsu Australia Software Technology Pty Ltd does not accept liability for any loss or damage (whether direct, indirect, consequential or economic)
however caused, and whether by negligence or otherwise, which may result directly or indirectly from this communication or any files attached.

If you do not wish to receive commercial and/or marketing email messages from Fujitsu Australia Software Technology Pty Ltd, please email unsubscribe@fast.au.fujitsu.com

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

On 2/28/19 9:21 AM, Achilleas Mantzios wrote:

On 28/2/19 1:08 π.μ., Ahmed, Nawaz wrote:

Hi,

I believe the "file copy" method (listed in the table) in pgbackrest
is based on pg_basebackup, so i think it should be "pg_basebackup over
ssh" as pgbackrest internally calls pg_basebackup. David Steele can
correct me.

No, apparently pgbackrest does not rely on pg_basebackup.

pgBackRest implements file copy internally and does not rely on any
command-line tools such as rsync, tar, pg_basebackup, etc.

Regards,
--
-David
david@pgmasters.net

Thanks for clarifying guys.

Best Regards,

Nawaz Ahmed
Software Development Engineer

Fujitsu Australia Software Technology Pty Ltd
14 Rodborough Road, Frenchs Forest NSW 2086, Australia
T +61 2 9452 9027
Nawaz@fast.au.fujitsu.com
fastware.com.au

-----Original Message-----
From: David Steele <david@pgmasters.net>
Sent: Thursday, 28 February 2019 7:41 PM
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; Ahmed, Nawaz <Nawaz@fast.au.fujitsu.com>; pgsql-general@lists.postgresql.org
Subject: Re: Barman disaster recovery solution

On 2/28/19 9:21 AM, Achilleas Mantzios wrote:

On 28/2/19 1:08 π.μ., Ahmed, Nawaz wrote:

Hi,

I believe the "file copy" method (listed in the table) in pgbackrest
is based on pg_basebackup, so i think it should be "pg_basebackup
over ssh" as pgbackrest internally calls pg_basebackup. David Steele
can correct me.

No, apparently pgbackrest does not rely on pg_basebackup.

pgBackRest implements file copy internally and does not rely on any command-line tools such as rsync, tar, pg_basebackup, etc.

Regards,
--
-David
david@pgmasters.net
Disclaimer

The information in this e-mail is confidential and may contain content that is subject to copyright and/or is commercial-in-confidence and is intended only for the use of the above named addressee. If you are not the intended recipient, you are hereby notified that dissemination, copying or use of the information is strictly prohibited. If you have received this e-mail in error, please telephone Fujitsu Australia Software Technology Pty Ltd on + 61 2 9452 9000 or by reply e-mail to the sender and delete the document and all copies thereof.

Whereas Fujitsu Australia Software Technology Pty Ltd would not knowingly transmit a virus within an email communication, it is the receiver’s responsibility to scan all communication and any files attached for computer viruses and other defects. Fujitsu Australia Software Technology Pty Ltd does not accept liability for any loss or damage (whether direct, indirect, consequential or economic) however caused, and whether by negligence or otherwise, which may result directly or indirectly from this communication or any files attached.

If you do not wish to receive commercial and/or marketing email messages from Fujitsu Australia Software Technology Pty Ltd, please email unsubscribe@fast.au.fujitsu.com

Achilleas,

On 2/27/19 11:39 AM, Achilleas Mantzios wrote:

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!

Barman will fit most requirements. PgBackRest excels when WAL traffic
goes on 100000 files/day or more. I have written an article, not yet
publised, on a comparison on the 3 most known solutions. Will post a
link as soon as it gets published.

Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql

Indeed, the pgBackRest user guide is a bit out of date. I've been
meaning to update to a newer version of Postgres but haven't had the
chance. This gave me the extra nudge I needed.

The docs have been update to PG10 for the next release, though the only
visible change is to remove the `stop-auto` option since it is not
relevant to PG10.

https://github.com/pgbackrest/pgbackrest/commit/6ce3310f8a2900d1af717da8d4c3345a9016933b

Thanks!
--
-David
david@pgmasters.net