Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s8ISm-00BYnM-Md for pgsql-admin@arkaria.postgresql.org; Sat, 18 May 2024 11:42:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s8ISm-005P8z-0x for pgsql-admin@arkaria.postgresql.org; Sat, 18 May 2024 11:42:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s81Jv-00Frai-1j for pgsql-admin@lists.postgresql.org; Fri, 17 May 2024 17:24:39 +0000 Received: from qs51p00im-qukt01072102.me.com ([17.57.155.11]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s81Jr-000kUQ-KD for pgsql-admin@lists.postgresql.org; Fri, 17 May 2024 17:24:38 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=1a1hai; t=1715966675; bh=fMrNFu1aZ2+wbWJSVbZ/GoxsnWxPmx0fHbySmHkxegA=; h=From:Message-Id:Content-Type:Mime-Version:Subject:Date:To; b=deJqqy5Swa/oSjwMudUAqUjKRuQriBTdwAkoy/tXqy14SxjiEOmskjSTSnqalhzMS UQonSa4hAh9HzbJbMcyN8c4Pyl3u/zSo0pg1mqXUSfD9LeiJ5Zzad56CN68flQkHHm VxMiZ8NhlNsom/iLxRSjHdAppTL3f9dpsfW6OI9vQvOVyQa321Dy5BY8YA0fAPCXSb 0uG9+vh8Y1YH3yEBwiVDx69AVC/2LiuhMQt7ed2ZhJxEHcIhMU4Jyoj32zr242bwXt vgQW9CkKmFk+zZ2JqcnDX6u5yy0TftjdaQtEa2jf/cELwmKshnEmh2N9u4TMkLdpCZ mM0attK827hgw== Received: from smtpclient.apple (qs51p00im-dlb-asmtp-mailmevip.me.com [17.57.155.28]) by qs51p00im-qukt01072102.me.com (Postfix) with ESMTPSA id 793AE3403C0; Fri, 17 May 2024 17:24:34 +0000 (UTC) From: Rui DeSousa Message-Id: <3A9ECDF0-41F9-449A-B37E-BAFEDF02D203@icloud.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_6AF6D585-866C-4161-B755-BF9299D92E8E" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.8\)) Subject: Re: PITR Date: Fri, 17 May 2024 13:24:33 -0400 In-Reply-To: Cc: Pgsql-admin To: Rajesh Kumar References: X-Mailer: Apple Mail (2.3696.120.41.1.8) X-Proofpoint-GUID: OkvMymq7rrSJKbH_gclEXOMD8zJqPNoi X-Proofpoint-ORIG-GUID: OkvMymq7rrSJKbH_gclEXOMD8zJqPNoi X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.272,Aquarius:18.0.1039,Hydra:6.0.650,FMLib:17.11.176.26 definitions=2024-05-17_07,2024-05-17_03,2023-05-22_02 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 bulkscore=0 mlxlogscore=999 spamscore=0 clxscore=1011 suspectscore=0 mlxscore=0 phishscore=0 adultscore=0 malwarescore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.19.0-2308100000 definitions=main-2405170136 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_6AF6D585-866C-4161-B755-BF9299D92E8E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On May 17, 2024, at 7:35 AM, Rajesh Kumar = wrote: >=20 > 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=E2=80=99m under the impression that you=E2=80=99re only logging DDL to = the log file and not DML thus you don=E2=80=99t 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 =E2=80=98X=E2=80=99 and want to restore client = =E2=80=98X=E2=80=99 data to last state but don=E2=80=99t know when it = was deleted. =20 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 =3D 'on' recovery_target_timeline=3Dlatest restore_command =3D '~/bin/fetch_wal.sh -d $SRCDB -w %f -x "%p"' recovery_target_time =3D '${RECOVERY_TIME}' recovery_target_action =3D 'pause' Then you=E2=80=99ll 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=E2=80=99ve used this = technique in the past.=20= --Apple-Mail=_6AF6D585-866C-4161-B755-BF9299D92E8E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
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=E2=80=99m under the impression that you=E2=80=99re only logging = DDL to the log file and not DML thus you don=E2=80=99t 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 =E2=80=98X=E2=80=99 and want to = restore client =E2=80=98X=E2=80=99 data to last state but don=E2=80=99t = 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 =3D = 'on'
recovery_target_timeline=3Dlatest
restore_command =3D= '~/bin/fetch_wal.sh -d $SRCDB -w %f -x "%p"'
recovery_target_time =3D = '${RECOVERY_TIME}'
recovery_target_action =3D = 'pause'


Then you=E2=80=99ll 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=E2=80=99ve used this technique in the = past. 
= --Apple-Mail=_6AF6D585-866C-4161-B755-BF9299D92E8E--