Re: BDR and Backup and Recovery

On 11/18/2015 08:54 AM, Will McCormick wrote:
> Re-sending to group as well Jim :D
>
> Regarding testing backups, Well said Jim. Thanks for taking the time to
> respond. I will test regularly whatever we decide to put in place.
>
> The below is from the 0.9.3 BDR documentation:
>
> "Because logical replication is only supported in streaming mode (rather
> than WAL archiving) it isn't suitable for point-in-time recovery.
> Logical replication may be used in conjunction with streaming physical
> replication and/or PITR, though; it is not necessary to choose one or
> the other."
>
> Am I misinterpreting that BDR uses Logical Decoding and as such I cannot
> perform PITR?

As I read it as, you can not use the BDR stream to do PITR, if for no
other reason then that it can be a subset of a database or database
cluster. Further reason, it does not transfer WAL files that have the
entire picture of the database cluster. As the above says though, there
is nothing stopping you from doing WAL archiving/PITR in parallel to the
BDR stream.

>
> On Wed, Nov 18, 2015 at 11:19 AM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com
> <mailto:Jim(dot)Nasby(at)bluetreble(dot)com>> wrote:
>
> On 11/18/15 9:46 AM, Will McCormick wrote:
>
> What viable options exist for Backup & Recovery in a BDR
> environment?
> From the reading I have done PITR recovery is not an option
> with BDR.
> It's important to preface this that I have almost no exposure to
> postgres backup and recovery. Is PITR not an option with BDR?
>
> If a user fat fingers something and deletes records from a table
> without
> a where clause what is the correct course of action is to recover as
> much data as possible. What type of backup do I require to
> restore as
> much data as possible before the incident in a BDR environment.
>
> Sorry for such an open ended question. :D I'm continuing to read
> as I
> solicit feedback.
>
> Is there a document outlining recovery with BDR?
>
>
> I don't know why PITR wouldn't work with BDR, other than you can't
> use binary backups across incompatible versions and BDR might be
> considered incompatible with community Postgres. I would think it
> should still work fine if you try to restore to a BDR server.
>
> That said, remember that if you are not regularly (preferably
> automatically) testing your backups by doing a restore and testing
> the restore, then you don't have a backup. You have a hope and a
> prayer. :)
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com