Re: Postgres Point in time Recovery (PITR),

On 21/10/2019 12:10, Avinash Kumar wrote:
>
>
> On Mon, Oct 21, 2019 at 4:19 PM Fabio Ugo Venchiarutti
> <f(dot)venchiarutti(at)ocado(dot)com <mailto:f(dot)venchiarutti(at)ocado(dot)com>> wrote:
>
> On 21/10/2019 09:52, Luca Ferrari wrote:
> > On Sat, Oct 19, 2019 at 7:46 PM Daulat Ram
> <Daulat(dot)Ram(at)exponential(dot)com <mailto:Daulat(dot)Ram(at)exponential(dot)com>> wrote:
> >> One more questions is, how backups are useful if we have
> streaming replication . As I know, we can promote the standby as
> primary in case of disaster at primary side. Do we need to schedule
> backups if we have streaming replication?
> >
> > Let's speculate a little on that: do you need backups if you have a
> > RAID-1 configuration?
> > Replication helps you reduce almost to zero the time to handle a
> > disaster, backups allow you to recover in a more large time window.
> >
> > Luca
> >
> >
>
>
> TBH I hear this argument more often than I wish.
>
>
> Offline backups and data replication are nearly entirely orthogonal.
>
>
>
> Any form of instantaneous redundancy (RAID, instantaneous replication
> and so on) primary is a mitigation measure to protect data &
> availability against loss of infrastructure.
>
>
> Backups (preferably with PITR) also do that, but that's not their
> primary purpose unless you can't afford live redundancy on top of them.
>
>
> Offline backups address many failure scenarios that any form of live
> replication is defenseless against (eg: logical damage to the data as a
> result of human errors/bugs/vandalism would hit all your replicas, but
> you always can perform DR from a backup).
>
> I think we are way ahead of the days where we need to live on Offline
> backups involving downtimes.
> Today, we have several Online Consistent Backup solutions such as
> pg_basebackup (built-in with PG), pgBackRest, BARMAN, WAL-G etc ....
> Online Consistent Backups + Archiving of WALs are perfectly reliable.
> We need to ensure that we have safe backup locations, for example, push
> them to AWS S3 and forget about redundancy.
> Why do you think only Offline Backups are reliable today ?
>
>
>
> Delayed replicas, or any online rollback capability (such as pg_rewind
> off the server's own pg_wal or Oracle's flashback), somewhat live in a
> grey area in between, and their effectiveness varies depending on which
> level was compromised.
>
> What if you delay your replica by a day, before you making a change
> tomorrow (that may need a rollback). Delayed Replica is for emergencies
> IMO and of course does not satisfy every possible scenario.
>
>

Trade-offs come down to individual implementers, so fair enough.

Our biases must come from different use cases/experience: a good
fraction of recovery/sanitisation operations I had to carry out were
very selective/application specific and involved buildup of anomalies
over time, sometimes months.

Wouldn't have been possible without an old frozen reference for users to
compare with, and that hat to come from very cold storage.

--
Regards

Fabio Ugo Venchiarutti
OSPCFC Network Engineering Dpt.
Ocado Technology

--

Notice:
This email is confidential and may contain copyright material of
members of the Ocado Group. Opinions and views expressed in this message
may not necessarily reflect the opinions and views of the members of the
Ocado Group.

If you are not the intended recipient, please notify us
immediately and delete all copies of this message. Please note that it is
your responsibility to scan this message for viruses.

References to the
"Ocado Group" are to Ocado Group plc (registered in England and Wales with
number 7098618) and its subsidiary undertakings (as that expression is
defined in the Companies Act 2006) from time to time. The registered office
of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way,
Hatfield, Hertfordshire, AL10 9UL.