Re: BDR and Backup and Recovery - Mailing list pgsql-general

On 11/18/2015 09:31 AM, Will McCormick wrote:

Ccing list
> Thanks Adrian. I think I have it
>
> Lets say we have 2 nodes:
>
> Node A
> Node B
>
>
> GOOD
>
> Application Writes only occurring against Node A
>
> 1) Node A Base Backup taken
> 2) User Error occurs that replicates
>
> Can restore and Recover Node A to PITR before 2)
>
>
> BAD
>
> 1) Writes at Node A
> 2) Backups of Node A and Node B taken
> 3) Hardware Failure on Node A
> 4) Traffic now on Node B
> 5) Node B user error
> 6) Restore of Node B from 2) possible
>
> As logs not shipped from Node A to Node B, PITR would only have a
> partial view?
>
> Is this right?

Someone more versed in BDR than I will need to comment on the above.
Though it seems to me a possible solution would be to have a third
machine that has WAL file archive directories for each node. This could
get complicated though. First keeping the WAL files from each server
going to the correct directory. Second, determining which node in the
universe of nodes you want do PITR on.

>
> On Wed, Nov 18, 2015 at 12:12 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     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.Nasby@bluetreble.com <mailto:Jim.Nasby@bluetreble.com>
>         <mailto:Jim.Nasby@bluetreble.com
>         <mailto:Jim.Nasby@bluetreble.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com