Обсуждение: PITR

Поиск
Список
Период
Сортировка

PITR

От
Rajesh Kumar
Дата:
Hi all,


I want to verify one thing. If I am logging only ddl and if somebody update data incorrectly and if we don't know the time, can we do pitr or not?

Re: PITR

От
Kashif Zeeshan
Дата:
Hi Rajesh

As per my understanding you need to know the time to which you want to recover.

Thanks
Kashif Zeeshan
Bitnine Global

On Fri, May 17, 2024 at 4:35 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
Hi all,


I want to verify one thing. If I am logging only ddl and if somebody update data incorrectly and if we don't know the time, can we do pitr or not?

Re: PITR

От
Laurenz Albe
Дата:
On Fri, 2024-05-17 at 17:05 +0530, Rajesh Kumar wrote:
> I want to verify one thing. If I am logging only ddl and if somebody
> update data incorrectly and if we don't know the time, can we do pitr or not?

- you cannot WAL log only DDL, you log everything

- if you don't know which time to recover to, you cannot recover to that time

Yours,
Laurenz Albe

Re: PITR

От
Ron Johnson
Дата:
On Fri, May 17, 2024 at 7:35 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
Hi all,


I want to verify one thing. If I am logging only ddl and if somebody update data incorrectly and if we don't know the time, can we do pitr or not?

 If your system is busy, then log_statement = 'mod' is going to generate a LOT of pg_log data.

Re: PITR

От
MichaelDBA
Дата:
To answer your question, logging DDL does not have ANYTHING to do with PITR.  So the answer is "NO, you cannot do PITR by just logging logging DDL".

You have to use binary backups and continuous WAL logging to be able to do PITR.  There are many tools out there to do that.  The best one in my opinion is PGBackrest.

Regards,
Michael Vitale


Ron Johnson wrote on 5/17/2024 9:26 AM:
On Fri, May 17, 2024 at 7:35 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
Hi all,


I want to verify one thing. If I am logging only ddl and if somebody update data incorrectly and if we don't know the time, can we do pitr or not?

 If your system is busy, then log_statement = 'mod' is going to generate a LOT of pg_log data.



Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343 


Вложения

Re: PITR

От
Rajesh Kumar
Дата:
Thank you all

On Fri, 17 May 2024, 19:12 MichaelDBA, <MichaelDBA@sqlexec.com> wrote:
To answer your question, logging DDL does not have ANYTHING to do with PITR.  So the answer is "NO, you cannot do PITR by just logging logging DDL".

You have to use binary backups and continuous WAL logging to be able to do PITR.  There are many tools out there to do that.  The best one in my opinion is PGBackrest.

Regards,
Michael Vitale


Ron Johnson wrote on 5/17/2024 9:26 AM:
On Fri, May 17, 2024 at 7:35 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
Hi all,


I want to verify one thing. If I am logging only ddl and if somebody update data incorrectly and if we don't know the time, can we do pitr or not?

 If your system is busy, then log_statement = 'mod' is going to generate a LOT of pg_log data.



Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343 


Вложения

Re: PITR

От
Rui DeSousa
Дата:

On May 17, 2024, at 7:35 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:

I want to verify one thing. If I am logging only ddl and if somebody update data incorrectly and if we don't know the time, can we do pitr or not?

I think everyone misunderstood what you meant by logging only DDL.  I’m under the impression that you’re only logging DDL to the log file and not DML thus you don’t know when the event occurred but you do have valid backup and WAL files to go with it.

Yes, you can restore it will just take a little guess work.  If you know what you are looking for then start a recovery and look for the data that you want.

 i.e. We deleted client ‘X’ and want to restore client ‘X’ data to last state but don’t know when it was deleted.  

1, Just start you recovery at a known point. If the data was deleted some time on Tuesday, start your recovery from a Monday backup.
2. Advance the recovery forward by hour.
3. Repeat until the event has occurred.
4. Rollback prior to the event repeat steps 2 and 3 using a narrower timeframe; i.e. 5 minutes.. etc.

You can advance the database by setting the recovery_tartget_time and recovery_target_action to pause.  To advance it just update the recovery target time and restart the recovery process.
standby_mode = 'on'
recovery_target_timeline=latest
restore_command = '~/bin/fetch_wal.sh -d $SRCDB -w %f -x "%p"'
recovery_target_time = '${RECOVERY_TIME}'
recovery_target_action = 'pause'


Then you’ll have a better idea when the event occurred and can narrow down the best recovery time to use.

Hope that helps.  Tedious but it works as I’ve used this technique in the past. 

Re: PITR

От
Ron Johnson
Дата:
On Sat, May 18, 2024 at 7:42 AM Rui DeSousa <rui.desousa@icloud.com> wrote:

On May 17, 2024, at 7:35 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:

I want to verify one thing. If I am logging only ddl and if somebody update data incorrectly and if we don't know the time, can we do pitr or not?

I think everyone misunderstood what you meant by logging only DDL.  I’m under the impression that you’re only logging DDL to the log file and not DML thus you don’t know when the event occurred but you do have valid backup and WAL files to go with it.

Yes, you can restore it will just take a little guess work.  If you know what you are looking for then start a recovery and look for the data that you want.

 i.e. We deleted client ‘X’ and want to restore client ‘X’ data to last state but don’t know when it was deleted.  
 
The problem is that PG PITR is "all or nothing".  You can't PITR restore a single database, schema or table.  Thus, you'd need to restore the whole instance to a separate, new instance.  That's easy on AWS, but not so much in a (locked down, stove-piped) corporate environment.