RE: Postgres Point in time Recovery (PITR),

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?

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<mailto: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<mailto: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<mailto:david(at)pgmasters(dot)net>

--
9000799060