Re: Barman disaster recovery solution
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 10 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 da...@pgmasters.net
RE: Barman disaster recovery solution
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 na...@fast.au.fujitsu.com fastware.com.au -Original Message- From: David Steele Sent: Thursday, 28 February 2019 7:41 PM To: Achilleas Mantzios ; Ahmed, Nawaz ; 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 da...@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 unsubscr...@fast.au.fujitsu.com
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 da...@pgmasters.net
Re: Barman disaster recovery solution
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 na...@fast.au.fujitsu.com <mailto:na...@fast.au.fujitsu.com> fastware.com.au <http://fastware.com.au/> *From:*Achilleas Mantzios *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 10 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 unsubscr...@fast.au.fujitsu.com -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: Barman disaster recovery solution
On 27/2/19 6:52 μ.μ., Mark Fletcher wrote: On Wed, Feb 27, 2019 at 1:39 AM Achilleas Mantzios mailto:ach...@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
Re: Barman disaster recovery solution
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 μ.μ., rich...@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 da...@pgmasters.net
Re: Barman disaster recovery solution
On Wed, Feb 27, 2019 at 1:39 AM Achilleas Mantzios < ach...@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
Re: Barman disaster recovery solution
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 μ.μ., rich...@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 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 10 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
Re: Barman disaster recovery solution
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 μ.μ., rich...@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 10 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
Re: Barman disaster recovery solution
Em 27/02/2019 09:31, Achilleas Mantzios escreveu: On 27/2/19 1:58 μ.μ., rich...@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 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 10 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
Re: Barman disaster recovery solution
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 μ.μ., rich...@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
Re: Barman disaster recovery solution
On 2/27/19 2:31 PM, Achilleas Mantzios wrote: On 27/2/19 1:58 μ.μ., rich...@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 da...@pgmasters.net
Re: Barman disaster recovery solution
On 27/2/19 1:58 μ.μ., rich...@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 10 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
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 10 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
Re: Barman disaster recovery solution
Greetings, * Edson Carlos Ericksson Richter (rich...@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 signature.asc Description: PGP signature
Re: Barman disaster recovery solution
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
Re: Barman disaster recovery solution
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
Re: 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 10 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
Barman disaster recovery solution
Does anyone use this solution? any recommenations? Thanks!
