Re: BDR and Backup and Recovery

On 19 November 2015 at 00:54, Will McCormick <wmccormick(at)gmail(dot)com> wrote:

> 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?
>

The point is that you cannot use the logical decoding data stream for
point-in-time recovery. Nothing stops you archiving WAL like normal from a
node that's participating in logical replication as an upstream and/or
downstream. You just can't use the logical replication data stream its self
for that purpose. Sounds like I need to clarify that part of the docs.

Note the caveats in my prior mail re PITR and BDR, though; you can't just
PITR-restore a replacement for a failed node and have it catch up and
rejoin replication.

Regarding logical PITR: Theoretically we could actually save a base pg_dump
and a change stream as logical changes from pg_recvlogical, then use that
for transaction-level logical PITR. It's not impossible, but it'd require
new tools and require changes to BDR/UDR to allow the stream to be applied.
Nobody's written them yet. I don't have any plans to do this in the near to
mid term.

It'd be an interesting project to build with pglogical. Its protocol is
better suited to this than BDR's. You could do selective PITR of just a
subset of tables you were interested in. If anyone's keen to tackle that,
get in touch and I'll see if I can offer any help.

>> 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.
>>
>
It does, with the caveat that it can't be a drop-in replacement for a
failed node due to the timeline increment. The data is there, but it won't
participate in replication. See the steps outlined in my prior mail for
details.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services