Re: Postgres Point in time Recovery (PITR),

[Avinash Kumar <avinash.vallarapu@xxxxxxxxx>]

Hi,

On Sat, Oct 19, 2019 at 11:16 PM Daulat Ram <Daulat.Ram@xxxxxxxxxxxxxxx> 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.vallarapu@xxxxxxxxx>
Sent: Friday, October 18, 2019 5:28 PM
To: David Steele <david@xxxxxxxxxxxxx>
Cc: Luca Ferrari <fluca1978@xxxxxxxxx>; Andreas Joseph Krogh <andreas@xxxxxxxxxx>; Daulat Ram <Daulat.Ram@xxxxxxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxx> wrote:

On 10/18/19 11:29 AM, Luca Ferrari wrote:
> On Fri, Oct 18, 2019 at 10:30 AM Andreas Joseph Krogh
> <andreas@xxxxxxxxxx> 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@xxxxxxxxxxxxx

 

--

9000799060

--

9000799060