On Sat, May 18, 2024 at 7:42 AM Rui DeSousa <rui.desousa@xxxxxxxxxx> wrote:
On May 17, 2024, at 7:35 AM, Rajesh Kumar <rajeshkumar.dba09@xxxxxxxxx> 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.
