Re: PITR

On Sat, May 18, 2024 at 7:42 AM Rui DeSousa <rui(dot)desousa(at)icloud(dot)com> wrote:

>
> On May 17, 2024, at 7:35 AM, Rajesh Kumar <rajeshkumar(dot)dba09(at)gmail(dot)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.