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.