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

From Craig Ringer
Subject Re: BDR and Backup and Recovery
Date
Msg-id CAMsr+YEnGYUi10yQbH9FD3f2WHudVNwkuFE6UBd7i=LLVv2kQg@mail.gmail.com
Whole thread Raw
In response to Re: BDR and Backup and Recovery  (Will McCormick <wmccormick@gmail.com>)
List pgsql-general


On 19 November 2015 at 00:54, Will McCormick <wmccormick@gmail.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

pgsql-general by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: Fetching from psql procedures
Next
From: Sridhar N Bamandlapally
Date:
Subject: How to drop stats on table