Re: Postgres Point in time Recovery (PITR),

Hi,

On Sat, Oct 19, 2019 at 11:16 PM Daulat Ram <Daulat(dot)Ram(at)exponential(dot)com>
wrote:

> Hi All,
>
>
>
> Thanks for your suggestions.
>
> 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?
>
1. What if you realized that someone has dropped a table or accidentally
made a change that requires you to recover some table/database from old
backups ?
2. Some organizations requires you to store backups for a few days/months
and even years. This is because, you should be able to perform recovery
from past at any given point of time.

Streaming Replication (unless delayed explicitly) applies the changes
immediately. So, it may be very late by the time you realize that some
accidental change has made some damage. Because, the damage has happened on
both Master & Standby.

Regards,
Avinash Vallarapu.

>
>
> Thanks
>
>
>
> *From:* Avinash Kumar <avinash(dot)vallarapu(at)gmail(dot)com>
> *Sent:* Friday, October 18, 2019 5:28 PM
> *To:* David Steele <david(at)pgmasters(dot)net>
> *Cc:* Luca Ferrari <fluca1978(at)gmail(dot)com>; Andreas Joseph Krogh <
> andreas(at)visena(dot)com>; Daulat Ram <Daulat(dot)Ram(at)exponential(dot)com>;
> pgsql-general(at)lists(dot)postgresql(dot)org
> *Subject:* Re: Postgres Point in time Recovery (PITR),
>
>
>
> Hi Daulat,
>
>
>
> PITR entirely depends on what type of backups you choose.
> Sometimes, to reduce the amount of downtime involved while restoring and
> recovering a backup, you may also use a additional delayed standby.
>
> You could use the PG built-in feature to delay the replication and
> fast-forward it to the safest point to achieve PITR. But this requires you
> to have an additional standby.
>
>
> https://www.percona.com/blog/2018/06/28/faster-point-in-time-recovery-pitr-postgresql-using-delayed-standby/
>
>
>
> If you have several TBs of database, pgBackRest is of course a way to go
> for backups (there are few more open source solutions), but also consider
> the amount of time it takes for recovery. Keeping all of this in mind, your
> approach to PITR changes.
>
>
>
> So i would ask you this question, what is the backup tool you use and what
> is your backup strategy ? Are you taking a physical backup and performing
> continuous archiving of WALs ? The answer to your question entirely depends
> on this. :)
>
>
>
> Regards,
> Avinash Vallarapu.
>
>
>
>
>
>
>
> On Fri, Oct 18, 2019 at 5:17 PM David Steele <david(at)pgmasters(dot)net> wrote:
>
> On 10/18/19 11:29 AM, Luca Ferrari wrote:
> > On Fri, Oct 18, 2019 at 10:30 AM Andreas Joseph Krogh
> > <andreas(at)visena(dot)com> wrote:
> >> We use barman (https://www.pgbarman.org/) for continuous streaming
> backup and I had to restore from it once, and it went like this:
> >
> > Just for the records, here's an example of restore with pgbackrest:
> >
> > % sudo -u postgres pgbackrest --stanza=miguel \
> > --log-level-console=info --delta restore
> > ...
> > INFO: restore backup set 20190916-125652F
> > INFO: remove invalid files/paths/links from /postgres/pgdata/11
> > INFO: cleanup removed 148 files, 3 paths
> > ...
> > INFO: write /postgres/pgdata/11/recovery.conf
> > INFO: restore global/pg_control (performed last
> > to ensure aborted restores cannot be started)
> > INFO: restore command end: completed successfully (5113ms)
>
> pgBackRest also has a tutorial on PITR:
> https://pgbackrest.org/user-guide.html#pitr
>
> --
> -David
> david(at)pgmasters(dot)net
>
>
>
>
> --
>
> 9000799060
>

--
9000799060