From: Rui DeSousa <rui.desousa@icloud.com>
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: PITR
Date: Fri, 17 May 2024 13:24:33 -0400
Message-ID: <3A9ECDF0-41F9-449A-B37E-BAFEDF02D203@icloud.com> (raw)
In-Reply-To: <CAJk5AtYCh7fWShBqo6L3WQjAm2-PEqpYEZ5D=1Vp_xAympU=tw@mail.gmail.com>
References: <CAJk5AtYCh7fWShBqo6L3WQjAm2-PEqpYEZ5D=1Vp_xAympU=tw@mail.gmail.com>
> 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. =
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rui.desousa@icloud.com, rajeshkumar.dba09@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: PITR
In-Reply-To: <3A9ECDF0-41F9-449A-B37E-BAFEDF02D203@icloud.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox