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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxxxxx <mailto:Jim.Nasby@xxxxxxxxxxxxxx> <mailto:Jim.Nasby@xxxxxxxxxxxxxx <mailto:Jim.Nasby@xxxxxxxxxxxxxx>>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
