pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPITR
30+ messages / 16 participants
[nested] [flat]
* PITR
@ 2006-08-31 17:36 Mr. Dan <bitsandbytes88@hotmail.com>
2006-08-31 18:09 ` Re: PITR Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 30+ messages in thread
From: Mr. Dan @ 2006-08-31 17:36 UTC (permalink / raw)
To: pgsql-admin
Hi,
Every day I'm arguing with 3 or 4 people on this point.
My point is that I have to do a tarball, a tar command as part of online
backup with postgresql v814. I keep saying that the reason we do a tarbal
(online backup) and not individual dumps is that we want the PITR
capability. I've been fighting so many people on this point I want to
double check with the group that.
1. There is no way to do a PITR recovery from a dump file at one time during
the day.
2. There is no way to do a PITR from SLONY1.
Does everyone agree with me on those points?
Thanks,
~DjK
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2006-08-31 17:36 PITR Mr. Dan <bitsandbytes88@hotmail.com>
@ 2006-08-31 18:09 ` Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 30+ messages in thread
From: Joshua D. Drake @ 2006-08-31 18:09 UTC (permalink / raw)
To: Mr. Dan <bitsandbytes88@hotmail.com>; +Cc: pgsql-admin
Mr. Dan wrote:
> Hi,
>
> Every day I'm arguing with 3 or 4 people on this point.
> My point is that I have to do a tarball, a tar command as part of online
> backup with postgresql v814. I keep saying that the reason we do a
> tarbal (online backup) and not individual dumps is that we want the PITR
> capability. I've been fighting so many people on this point I want to
> double check with the group that.
>
> 1. There is no way to do a PITR recovery from a dump file at one time
> during the day.
> 2. There is no way to do a PITR from SLONY1.
>
> Does everyone agree with me on those points?
Yes.
You can however perform a psuedo 5 minute delay replication with PITR.
Sincerely,
Joshua D. Drake
>
> Thanks,
> ~DjK
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 30+ messages in thread
* PITR
@ 2014-02-22 16:06 Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Murthy Nunna @ 2014-02-22 16:06 UTC (permalink / raw)
To: pgsql-admin
All,
I am testing PITR.... I am looking for recovery.conf parameters where you can recovery the WALs available in the restore_command but do not complete recovery. I want to be able to connect to the database and check database in read only and if I am not there yet, I will feed more WALs in the archive directory and resume recovery. I would like to prevent multiple base restorations. I want to roll forward with WALs but check in between. Also, I do not want to set up replication standby.
Is it possible? If so, could you tell me what are the relevant recovery.conf params?
Thanks,
Murthy
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
@ 2014-02-22 16:33 ` desmodemone <desmodemone@gmail.com>
2014-02-22 17:03 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-23 04:27 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
0 siblings, 2 replies; 30+ messages in thread
From: desmodemone @ 2014-02-22 16:33 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; +Cc: pgsql-admin
2014-02-22 17:06 GMT+01:00 Murthy Nunna <mnunna@fnal.gov>:
> All,
>
>
>
> I am testing PITR.... I am looking for recovery.conf parameters where you
> can recovery the WALs available in the restore_command but do not complete
> recovery. I want to be able to connect to the database and check database
> in read only and if I am not there yet, I will feed more WALs in the
> archive directory and resume recovery. I would like to prevent multiple
> base restorations. I want to roll forward with WALs but check in between.
> Also, I do not want to set up replication standby.
>
>
>
> Is it possible? If so, could you tell me what are the relevant
> recovery.conf params?
>
>
>
> Thanks,
>
> Murthy
>
Hi Murthy,
look at parameter "pause_at_recovery_target" and
"recovery_target_time" with these parameters in the recovery.conf you could
reach a point in the timeline to recover the database and then see if it's
all ok and then finish the recovery with pg_xlog_replay_resume. If you want
instead continue the recovery you have to stop the backend and change the
parameter of recovery_target_time in the recovery.conf and restart the
postmaster.
Mat DBA
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
@ 2014-02-22 17:03 ` Murthy Nunna <mnunna@fnal.gov>
2014-02-22 17:31 ` Re: PITR Julien Rouhaud <julien.rouhaud@dalibo.com>
1 sibling, 1 reply; 30+ messages in thread
From: Murthy Nunna @ 2014-02-22 17:03 UTC (permalink / raw)
To: desmodemone <desmodemone@gmail.com>; +Cc: pgsql-admin
Mat,
Thank you for your quick response....
The documentation says for pause_at_recovery_target:
This setting has no effect if hot_standby<http://www.postgresql.org/docs/9.2/static/runtime-config-replication.html#GUC-HOT-STANDBY; is not enabled, or if no recovery target is set.
In my case hot_standby is not enabled.
Thanks,
Murthy
From: desmodemone [mailto:desmodemone@gmail.com]
Sent: Saturday, February 22, 2014 10:34 AM
To: Murthy Nunna
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR
2014-02-22 17:06 GMT+01:00 Murthy Nunna <mnunna@fnal.gov<mailto:mnunna@fnal.gov>>:
All,
I am testing PITR.... I am looking for recovery.conf parameters where you can recovery the WALs available in the restore_command but do not complete recovery. I want to be able to connect to the database and check database in read only and if I am not there yet, I will feed more WALs in the archive directory and resume recovery. I would like to prevent multiple base restorations. I want to roll forward with WALs but check in between. Also, I do not want to set up replication standby.
Is it possible? If so, could you tell me what are the relevant recovery.conf params?
Thanks,
Murthy
Hi Murthy,
look at parameter "pause_at_recovery_target" and "recovery_target_time" with these parameters in the recovery.conf you could reach a point in the timeline to recover the database and then see if it's all ok and then finish the recovery with pg_xlog_replay_resume. If you want instead continue the recovery you have to stop the backend and change the parameter of recovery_target_time in the recovery.conf and restart the postmaster.
Mat DBA
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-22 17:03 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
@ 2014-02-22 17:31 ` Julien Rouhaud <julien.rouhaud@dalibo.com>
2014-02-22 18:29 ` Re: PITR desmodemone <desmodemone@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Julien Rouhaud @ 2014-02-22 17:31 UTC (permalink / raw)
To: pgsql-admin
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Le 22/02/2014 18:03, Murthy Nunna a écrit :
> Mat,
>
>
>
> Thank you for your quick response….
>
>
>
> The documentation says for pause_at_recovery_target:
>
>
>
> This setting has no effect if hot_standby
> <http://www.postgresql.org/docs/9.2/static/runtime-config-replication.html#GUC-HOT-STANDBY;
> is not enabled, or if no recovery target is set.
>
>
>
> In my case hot_standby is not enabled.
>
>
Hi,
If you want to connect to your database in read only to check the
recovery point, you have to enable it. It doesn't have any impact when
the cluster is not in recovery mode.
Regards.
>
> Thanks,
>
> Murthy
>
>
>
>
>
>
>
>
>
> *From:*desmodemone [mailto:desmodemone@gmail.com] *Sent:* Saturday,
> February 22, 2014 10:34 AM *To:* Murthy Nunna *Cc:*
> pgsql-admin@postgresql.org *Subject:* Re: [ADMIN] PITR
>
>
>
>
>
>
>
> 2014-02-22 17:06 GMT+01:00 Murthy Nunna <mnunna@fnal.gov
> <mailto:mnunna@fnal.gov>>:
>
> All,
>
>
>
> I am testing PITR…. I am looking for recovery.conf parameters where
> you can recovery the WALs available in the restore_command but do
> not complete recovery. I want to be able to connect to the database
> and check database in read only and if I am not there yet, I will
> feed more WALs in the archive directory and resume recovery. I
> would like to prevent multiple base restorations. I want to roll
> forward with WALs but check in between. Also, I do not want to set
> up replication standby.
>
>
>
> Is it possible? If so, could you tell me what are the relevant
> recovery.conf params?
>
>
>
> Thanks,
>
> Murthy
>
>
>
> Hi Murthy, look at parameter "pause_at_recovery_target" and
> "recovery_target_time" with these parameters in the recovery.conf
> you could reach a point in the timeline to recover the database and
> then see if it's all ok and then finish the recovery with
> pg_xlog_replay_resume. If you want instead continue the recovery
> you have to stop the backend and change the parameter of
> recovery_target_time in the recovery.conf and restart the
> postmaster.
>
> Mat DBA
>
>
>
- --
Julien Rouhaud
http://dalibo.com - http://dalibo.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQEcBAEBAgAGBQJTCN8GAAoJELGaJ8vfEpOqynAH/1Vu2gUwdDww26qbbDreXRid
kpWGUEI2YXBw4e6D3SFiFDG77aPwFF7aGDXs/3Rjck3erLaYrtj/70ZFEwXJ2d4K
W2hDS8KFt8cx6YsNMI2epL/FnDYZKudU2Qcceixlzf2gGSDeexyy/ZLdTOQMgXZF
D4ktuEOdpIxUDipbWe7af2TVzaiTqHEu64RtcmtgPWIlwJHdtShEYFo6Go0BryY9
ToU+8DU45x4tuH6msk94D/7/NQJnkMpPNztxAXXZsDp4Xwtrq3nA20187y69NVu8
uWSpJVFzfPZIV0Cl6vupyw2k6G9ObvTBWcpSPrixVjgcpiHh+15j49z+OTfSB1I=
=FR3m
-----END PGP SIGNATURE-----
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-22 17:03 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 17:31 ` Re: PITR Julien Rouhaud <julien.rouhaud@dalibo.com>
@ 2014-02-22 18:29 ` desmodemone <desmodemone@gmail.com>
0 siblings, 0 replies; 30+ messages in thread
From: desmodemone @ 2014-02-22 18:29 UTC (permalink / raw)
To: Julien Rouhaud <julien.rouhaud@dalibo.com>; +Cc: pgsql-admin
2014-02-22 18:31 GMT+01:00 Julien Rouhaud <julien.rouhaud@dalibo.com>:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Le 22/02/2014 18:03, Murthy Nunna a écrit :
> > Mat,
> >
> >
> >
> > Thank you for your quick response....
> >
> >
> >
> > The documentation says for pause_at_recovery_target:
> >
> >
> >
> > This setting has no effect if hot_standby
> > <
> http://www.postgresql.org/docs/9.2/static/runtime-config-replication.html#GUC-HOT-STANDBY
> >
> > is not enabled, or if no recovery target is set.
> >
> >
> >
> > In my case hot_standby is not enabled.
> >
> >
> Hi,
>
> If you want to connect to your database in read only to check the
> recovery point, you have to enable it. It doesn't have any impact when
> the cluster is not in recovery mode.
>
> Regards.
>
> >
> > Thanks,
> >
> > Murthy
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > *From:*desmodemone [mailto:desmodemone@gmail.com] *Sent:* Saturday,
> > February 22, 2014 10:34 AM *To:* Murthy Nunna *Cc:*
> > pgsql-admin@postgresql.org *Subject:* Re: [ADMIN] PITR
> >
> >
> >
> >
> >
> >
> >
> > 2014-02-22 17:06 GMT+01:00 Murthy Nunna <mnunna@fnal.gov
> > <mailto:mnunna@fnal.gov>>:
> >
> > All,
> >
> >
> >
> > I am testing PITR.... I am looking for recovery.conf parameters where
> > you can recovery the WALs available in the restore_command but do
> > not complete recovery. I want to be able to connect to the database
> > and check database in read only and if I am not there yet, I will
> > feed more WALs in the archive directory and resume recovery. I
> > would like to prevent multiple base restorations. I want to roll
> > forward with WALs but check in between. Also, I do not want to set
> > up replication standby.
> >
> >
> >
> > Is it possible? If so, could you tell me what are the relevant
> > recovery.conf params?
> >
> >
> >
> > Thanks,
> >
> > Murthy
> >
> >
> >
> > Hi Murthy, look at parameter "pause_at_recovery_target" and
> > "recovery_target_time" with these parameters in the recovery.conf
> > you could reach a point in the timeline to recover the database and
> > then see if it's all ok and then finish the recovery with
> > pg_xlog_replay_resume. If you want instead continue the recovery
> > you have to stop the backend and change the parameter of
> > recovery_target_time in the recovery.conf and restart the
> > postmaster.
> >
> > Mat DBA
> >
> >
> >
>
>
> - --
> Julien Rouhaud
> http://dalibo.com - http://dalibo.org
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.11 (GNU/Linux)
> Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
>
> iQEcBAEBAgAGBQJTCN8GAAoJELGaJ8vfEpOqynAH/1Vu2gUwdDww26qbbDreXRid
> kpWGUEI2YXBw4e6D3SFiFDG77aPwFF7aGDXs/3Rjck3erLaYrtj/70ZFEwXJ2d4K
> W2hDS8KFt8cx6YsNMI2epL/FnDYZKudU2Qcceixlzf2gGSDeexyy/ZLdTOQMgXZF
> D4ktuEOdpIxUDipbWe7af2TVzaiTqHEu64RtcmtgPWIlwJHdtShEYFo6Go0BryY9
> ToU+8DU45x4tuH6msk94D/7/NQJnkMpPNztxAXXZsDp4Xwtrq3nA20187y69NVu8
> uWSpJVFzfPZIV0Cl6vupyw2k6G9ObvTBWcpSPrixVjgcpiHh+15j49z+OTfSB1I=
> =FR3m
> -----END PGP SIGNATURE-----
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
+1 for Julien
Mat DBA
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
@ 2014-02-23 04:27 ` Murthy Nunna <mnunna@fnal.gov>
2014-02-23 06:08 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
1 sibling, 1 reply; 30+ messages in thread
From: Murthy Nunna @ 2014-02-23 04:27 UTC (permalink / raw)
To: desmodemone <desmodemone@gmail.com>; +Cc: pgsql-admin
Hi Mat,
Thank you for the pointers on pause_at_recovery_target and recovery_target_time. It worked but I encountered an unexpected situation.
I wanted to test three recovery times by checking data at each point and then proceed to the next. It worked as expected first 2 recovery times but the last one did not give me an opportunity to check data. It simply completed recovery and switched timeline. This means I cannot rollforward anymore unless I restore the database again. Do you think I did something wrong?
Thanks,
Murthy
Following are my recovery.conf settings:
pause_at_recovery_target = true
#recovery_target_time = '2014-02-22 19:15:00'
#recovery_target_time = '2014-02-22 19:44:00'
recovery_target_time = '2014-02-22 19:50:00'
restore_command = 'cp /pgdata/backups/xlogs/minerva_ecl_test/%f %p'
Following is the pg_log of my 3rd recovery:
,2014-02-22 22:04:16 CSTLOG: database system was shut down in recovery at 2014-02-22 22:02:42 CST
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004.history" from archive
,2014-02-22 22:04:16 CSTLOG: starting point-in-time recovery to 2014-02-22 19:50:00-06
,2014-02-22 22:04:16 CSTLOG: restored log file "000000040000000800000004" from archive
,2014-02-22 22:04:16 CSTLOG: redo starts at 8/4000020
,2014-02-22 22:04:16 CSTLOG: restored log file "000000040000000800000005" from archive
,2014-02-22 22:04:16 CSTLOG: consistent recovery state reached at 8/5F51EA8
,2014-02-22 22:04:16 CSTLOG: database system is ready to accept read only connections
,2014-02-22 22:04:16 CSTLOG: restored log file "000000040000000800000006" from archive
,2014-02-22 22:04:16 CSTLOG: restored log file "000000040000000800000007" from archive
,2014-02-22 22:04:16 CSTLOG: restored log file "000000040000000800000008" from archive
,2014-02-22 22:04:16 CSTLOG: restored log file "000000040000000800000009" from archive
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004000000080000000A" from archive
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004000000080000000B" from archive
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004000000080000000C" from archive
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/00000004000000080000000D': No such file or directory
,2014-02-22 22:04:16 CSTLOG: unexpected pageaddr 7/BC000000 in log file 8, segment 13, offset 0
,2014-02-22 22:04:16 CSTLOG: redo done at 8/C0000B8
,2014-02-22 22:04:16 CSTLOG: last completed transaction was at log time 2014-02-22 19:48:29.205898-06
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004000000080000000C" from archive
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/00000005.history': No such file or directory
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/00000006.history': No such file or directory
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/00000007.history': No such file or directory
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/00000008.history': No such file or directory
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/00000009.history': No such file or directory
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/0000000A.history': No such file or directory
,2014-02-22 22:04:16 CSTLOG: selected new timeline ID: 10
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004.history" from archive
,2014-02-22 22:04:17 CSTLOG: archive recovery complete
,2014-02-22 22:04:17 CSTLOG: database system is ready to accept connections
,2014-02-22 22:04:17 CSTLOG: autovacuum launcher started
From: desmodemone [mailto:desmodemone@gmail.com]
Sent: Saturday, February 22, 2014 10:34 AM
To: Murthy Nunna
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR
2014-02-22 17:06 GMT+01:00 Murthy Nunna <mnunna@fnal.gov<mailto:mnunna@fnal.gov>>:
All,
I am testing PITR.... I am looking for recovery.conf parameters where you can recovery the WALs available in the restore_command but do not complete recovery. I want to be able to connect to the database and check database in read only and if I am not there yet, I will feed more WALs in the archive directory and resume recovery. I would like to prevent multiple base restorations. I want to roll forward with WALs but check in between. Also, I do not want to set up replication standby.
Is it possible? If so, could you tell me what are the relevant recovery.conf params?
Thanks,
Murthy
Hi Murthy,
look at parameter "pause_at_recovery_target" and "recovery_target_time" with these parameters in the recovery.conf you could reach a point in the timeline to recover the database and then see if it's all ok and then finish the recovery with pg_xlog_replay_resume. If you want instead continue the recovery you have to stop the backend and change the parameter of recovery_target_time in the recovery.conf and restart the postmaster.
Mat DBA
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 04:27 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
@ 2014-02-23 06:08 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-23 10:13 ` Re: PITR desmodemone <desmodemone@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Raghavendra @ 2014-02-23 06:08 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; +Cc: desmodemone <desmodemone@gmail.com>; pgsql-admin
On Sun, Feb 23, 2014 at 9:57 AM, Murthy Nunna <mnunna@fnal.gov> wrote:
> Hi Mat,
>
>
>
> Thank you for the pointers on pause_at_recovery_target and
> recovery_target_time. It worked but I encountered an unexpected situation.
>
>
>
> I wanted to test three recovery times by checking data at each point and
> then proceed to the next. It worked as expected first 2 recovery times but
> the last one did not give me an opportunity to check data. It simply
> completed recovery and switched timeline. This means I cannot rollforward
> anymore unless I restore the database again. Do you think I did something
> wrong?
>
>
You did right, for the first two time targets you have some wal files
pending hence pause is allowed. Whereas for the last time target there are
no archive files to pause so it has just opened the database as completion
of PITR.
I am able to reproduce this on my local as well.
This is from my logs:
2014-02-06 18:54:54 PST-11634---[] LOG: restored log file
"0000000100000001000000B8" from archive
2014-02-06 18:54:55 PST-11634---[] LOG: restored log file
"0000000100000001000000B9" from archive
2014-02-06 18:54:55 PST-11634---[] LOG: consistent recovery state reached
at 1/B90029C8
2014-02-06 18:54:55 PST-11632---[] LOG: database system is ready to accept
read only connections
cp: cannot stat `/opt/PostgreSQL/9.3/archives93/0000000100000001000000BA':
No such file or directory
B8,B9 applied and looking for next file BA which is not there and no point
of pausing without any files, hence it has opened the database. But my
guess is, what it has done is right, there no more files to pause and allow
you to query, its like completion of PITR.
Same in your case, there may not be any files in your archives directory.
You can check for '00000004000000080000000D' file in your archive directory.
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004000000080000000C"
from archive
cp: cannot stat
`/pgdata/backups/xlogs/minerva_ecl_test/00000004000000080000000D': No such
file or directory
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 04:27 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-23 06:08 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
@ 2014-02-23 10:13 ` desmodemone <desmodemone@gmail.com>
2014-02-23 15:20 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
0 siblings, 1 reply; 30+ messages in thread
From: desmodemone @ 2014-02-23 10:13 UTC (permalink / raw)
To: Raghavendra <raghavendra.rao@enterprisedb.com>; +Cc: Murthy Nunna <mnunna@fnal.gov>; pgsql-admin
2014-02-23 7:08 GMT+01:00 Raghavendra <raghavendra.rao@enterprisedb.com>:
> On Sun, Feb 23, 2014 at 9:57 AM, Murthy Nunna <mnunna@fnal.gov> wrote:
>
>> Hi Mat,
>>
>>
>>
>> Thank you for the pointers on pause_at_recovery_target and
>> recovery_target_time. It worked but I encountered an unexpected situation.
>>
>>
>>
>> I wanted to test three recovery times by checking data at each point and
>> then proceed to the next. It worked as expected first 2 recovery times but
>> the last one did not give me an opportunity to check data. It simply
>> completed recovery and switched timeline. This means I cannot rollforward
>> anymore unless I restore the database again. Do you think I did something
>> wrong?
>>
>>
> You did right, for the first two time targets you have some wal files
> pending hence pause is allowed. Whereas for the last time target there are
> no archive files to pause so it has just opened the database as completion
> of PITR.
>
> I am able to reproduce this on my local as well.
>
> This is from my logs:
>
> 2014-02-06 18:54:54 PST-11634---[] LOG: restored log file
> "0000000100000001000000B8" from archive
> 2014-02-06 18:54:55 PST-11634---[] LOG: restored log file
> "0000000100000001000000B9" from archive
> 2014-02-06 18:54:55 PST-11634---[] LOG: consistent recovery state reached
> at 1/B90029C8
> 2014-02-06 18:54:55 PST-11632---[] LOG: database system is ready to
> accept read only connections
> cp: cannot stat `/opt/PostgreSQL/9.3/archives93/0000000100000001000000BA':
> No such file or directory
>
> B8,B9 applied and looking for next file BA which is not there and no point
> of pausing without any files, hence it has opened the database. But my
> guess is, what it has done is right, there no more files to pause and allow
> you to query, its like completion of PITR.
>
> Same in your case, there may not be any files in your archives directory.
> You can check for '00000004000000080000000D' file in your archive
> directory.
>
> ,2014-02-22 22:04:16 CSTLOG: restored log file "00000004000000080000000C"
> from archive
>
> cp: cannot stat
> `/pgdata/backups/xlogs/minerva_ecl_test/00000004000000080000000D': No such
> file or directory
>
>
> ---
> Regards,
> Raghavendra
> EnterpriseDB Corporation
> Blog: http://raghavt.blogspot.com/
>
Raghavendra is right, the recovery applied successful your archived wal
segment to the database and ended the loop in the xlog.c when it not found
anymore records in the wal segments. Moreover the backend is telling you
that the recovery phase ended at "2014-02-22 19:48:29.205898-06" while your
target timeline was "2014-02-22 19:50:00" and it's interesting.
Did you create transactions and you committed them at that time or after ?
Normally, when you have to do a restore, probably you had a crash and your
filesystem is not ok, so you use a new filesystem on another storage. So
your last transactions are "lost", because those transactions was not still
archived, infact those transactions were in the last wal segment of the
crashed filesystem. If that file is not OK, you could not use it for
complete the recovery.
If you have a RPO policy , you have to look at parameter archive_timeout ,
so you are sure your wal segments will be archived, and if you do that and
your database does not create so much transactions , think about to
compress them or use data deduplication at storage level or filesystem
level , or you will have a lot of wasted space ( the wal segment even if is
not full will write 16Mb of <data>+<00000..000>)
Have a nice day
Mat Dba
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 04:27 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-23 06:08 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-23 10:13 ` Re: PITR desmodemone <desmodemone@gmail.com>
@ 2014-02-23 15:20 ` Murthy Nunna <mnunna@fnal.gov>
2014-02-23 18:06 ` Re: PITR bricklen <bricklen@gmail.com>
2014-02-24 12:30 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
0 siblings, 2 replies; 30+ messages in thread
From: Murthy Nunna @ 2014-02-23 15:20 UTC (permalink / raw)
To: desmodemone <desmodemone@gmail.com>; Raghavendra <raghavendra.rao@enterprisedb.com>; +Cc: pgsql-admin
Raghavendra,
Thanks for testing and confirming the behavior of "pause" setting.
While I understand your explanation, I feel I am still missing something. IMHO, when I say pause using "pause" setting, no matter what, I expect the recovery to wait for manual intervention. I myself can come up with number of reasons for doing so... e.g I may be purposely "hiding" some WALs somewhere else, or maybe I have several thousands of WALs that I want to parallelize the process of applying some logs while I recall some from tapes.
Let me know what you think.
Mat, This is a test database so I purposely lowered checkpoint/archive timeouts. Thank-you, I'll follow your advice for production systems to conserve space.
Thanks,
Murthy
From: desmodemone [mailto:desmodemone@gmail.com]
Sent: Sunday, February 23, 2014 4:13 AM
To: Raghavendra
Cc: Murthy Nunna; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR
2014-02-23 7:08 GMT+01:00 Raghavendra <raghavendra.rao@enterprisedb.com<mailto:raghavendra.rao@enterprisedb.com>>:
On Sun, Feb 23, 2014 at 9:57 AM, Murthy Nunna <mnunna@fnal.gov<mailto:mnunna@fnal.gov>> wrote:
Hi Mat,
Thank you for the pointers on pause_at_recovery_target and recovery_target_time. It worked but I encountered an unexpected situation.
I wanted to test three recovery times by checking data at each point and then proceed to the next. It worked as expected first 2 recovery times but the last one did not give me an opportunity to check data. It simply completed recovery and switched timeline. This means I cannot rollforward anymore unless I restore the database again. Do you think I did something wrong?
You did right, for the first two time targets you have some wal files pending hence pause is allowed. Whereas for the last time target there are no archive files to pause so it has just opened the database as completion of PITR.
I am able to reproduce this on my local as well.
This is from my logs:
2014-02-06 18:54:54 PST-11634---[] LOG: restored log file "0000000100000001000000B8" from archive
2014-02-06 18:54:55 PST-11634---[] LOG: restored log file "0000000100000001000000B9" from archive
2014-02-06 18:54:55 PST-11634---[] LOG: consistent recovery state reached at 1/B90029C8
2014-02-06 18:54:55 PST-11632---[] LOG: database system is ready to accept read only connections
cp: cannot stat `/opt/PostgreSQL/9.3/archives93/0000000100000001000000BA': No such file or directory
B8,B9 applied and looking for next file BA which is not there and no point of pausing without any files, hence it has opened the database. But my guess is, what it has done is right, there no more files to pause and allow you to query, its like completion of PITR.
Same in your case, there may not be any files in your archives directory. You can check for '00000004000000080000000D' file in your archive directory.
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004000000080000000C" from archive
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/00000004000000080000000D': No such file or directory
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
Raghavendra is right, the recovery applied successful your archived wal segment to the database and ended the loop in the xlog.c when it not found anymore records in the wal segments. Moreover the backend is telling you that the recovery phase ended at "2014-02-22 19:48:29.205898-06" while your target timeline was "2014-02-22 19:50:00" and it's interesting.
Did you create transactions and you committed them at that time or after ?
Normally, when you have to do a restore, probably you had a crash and your filesystem is not ok, so you use a new filesystem on another storage. So your last transactions are "lost", because those transactions was not still archived, infact those transactions were in the last wal segment of the crashed filesystem. If that file is not OK, you could not use it for complete the recovery.
If you have a RPO policy , you have to look at parameter archive_timeout , so you are sure your wal segments will be archived, and if you do that and your database does not create so much transactions , think about to compress them or use data deduplication at storage level or filesystem level , or you will have a lot of wasted space ( the wal segment even if is not full will write 16Mb of <data>+<00000..000>)
Have a nice day
Mat Dba
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 04:27 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-23 06:08 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-23 10:13 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 15:20 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
@ 2014-02-23 18:06 ` bricklen <bricklen@gmail.com>
1 sibling, 0 replies; 30+ messages in thread
From: bricklen @ 2014-02-23 18:06 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; +Cc: pgsql-admin
On Sun, Feb 23, 2014 at 7:20 AM, Murthy Nunna <mnunna@fnal.gov> wrote:
> While I understand your explanation, I feel I am still missing
> something. IMHO, when I say pause using “pause” setting, no matter what, I
> expect the recovery to wait for manual intervention.
>
Have you explored using "pg_xlog_replay_pause()" and
"pg_xlog_replay_resume()"? See
http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 04:27 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-23 06:08 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-23 10:13 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 15:20 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
@ 2014-02-24 12:30 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-24 18:14 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
1 sibling, 1 reply; 30+ messages in thread
From: Raghavendra @ 2014-02-24 12:30 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; +Cc: desmodemone <desmodemone@gmail.com>; pgsql-admin
On Sun, Feb 23, 2014 at 8:50 PM, Murthy Nunna <mnunna@fnal.gov> wrote:
> Raghavendra,
>
>
>
> Thanks for testing and confirming the behavior of "pause" setting.
>
>
>
> While I understand your explanation, I feel I am still missing something.
> IMHO, when I say pause using "pause" setting, no matter what, I expect the
> recovery to wait for manual intervention.
>
I very much agree with your point that it has to pause when you ask for it,
however, as per design (some other might comment on this well) am guessing
it will open the database if no wals are there though you intentionally
hide them.
You can use (HOT STANDBY) standby_mode=on which does the same thing, it
just waits for the WAL files but it won't open the database until you pass
the trigger file. In hot standby, it apply the existing wals fed and wait
for coming wals and it won't come out of recovery. This you can try with
below link.
http://wiki.postgresql.org/wiki/Hot_Standby
> I myself can come up with number of reasons for doing so... e.g I may be
> purposely "hiding" some WALs somewhere else, or maybe I have several
> thousands of WALs that I want to parallelize the process of applying some
> logs while I recall some from tapes.
>
>
>
> Let me know what you think.
>
>
Agreed it might be possible of not having wals at the moment and waiting
for them to copy, however, I prefer in that case to use hot_standby. Pause
just works in case if it sees some pending file in Arch.. location.
My explanation might not reach to your expectation, but I am sure few
other's here might share their inputs.
--Raghav
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 04:27 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-23 06:08 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-23 10:13 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 15:20 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-24 12:30 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
@ 2014-02-24 18:14 ` Murthy Nunna <mnunna@fnal.gov>
2014-02-26 05:33 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
0 siblings, 1 reply; 30+ messages in thread
From: Murthy Nunna @ 2014-02-24 18:14 UTC (permalink / raw)
To: Raghavendra <raghavendra.rao@enterprisedb.com>; +Cc: desmodemone <desmodemone@gmail.com>; pgsql-admin
Hi Raghavendra,
I used standby_mode=on and it worked. I can put checkpoints (not database checkpoint :)) in between and still be in recovery state. This is what I wanted.
Thanks for your help!
Murthy
From: Raghavendra [mailto:raghavendra.rao@enterprisedb.com]
Sent: Monday, February 24, 2014 6:31 AM
To: Murthy Nunna
Cc: desmodemone; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR
On Sun, Feb 23, 2014 at 8:50 PM, Murthy Nunna <mnunna@fnal.gov<mailto:mnunna@fnal.gov>> wrote:
Raghavendra,
Thanks for testing and confirming the behavior of "pause" setting.
While I understand your explanation, I feel I am still missing something. IMHO, when I say pause using "pause" setting, no matter what, I expect the recovery to wait for manual intervention.
I very much agree with your point that it has to pause when you ask for it, however, as per design (some other might comment on this well) am guessing it will open the database if no wals are there though you intentionally hide them.
You can use (HOT STANDBY) standby_mode=on which does the same thing, it just waits for the WAL files but it won't open the database until you pass the trigger file. In hot standby, it apply the existing wals fed and wait for coming wals and it won't come out of recovery. This you can try with below link.
http://wiki.postgresql.org/wiki/Hot_Standby
I myself can come up with number of reasons for doing so... e.g I may be purposely "hiding" some WALs somewhere else, or maybe I have several thousands of WALs that I want to parallelize the process of applying some logs while I recall some from tapes.
Let me know what you think.
Agreed it might be possible of not having wals at the moment and waiting for them to copy, however, I prefer in that case to use hot_standby. Pause just works in case if it sees some pending file in Arch.. location.
My explanation might not reach to your expectation, but I am sure few other's here might share their inputs.
--Raghav
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 04:27 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-23 06:08 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-23 10:13 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 15:20 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-24 12:30 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-24 18:14 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
@ 2014-02-26 05:33 ` Murthy Nunna <mnunna@fnal.gov>
2014-02-26 13:55 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
0 siblings, 1 reply; 30+ messages in thread
From: Murthy Nunna @ 2014-02-26 05:33 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; Raghavendra <raghavendra.rao@enterprisedb.com>; +Cc: desmodemone <desmodemone@gmail.com>; pgsql-admin
All,
My database is in continuous recovery but it is not a traditional standby setup.... I am manually copying WALs (several thousands) from the source server to the server I am recovering. This means recovery is being done while WALs are being copied. It could so happen that a particular WAL could be open for recovery while it is still being copied (and not closed). Is this ok? I am asking because, I am seeing lots of LOG messages "unexpected pageaddr" as below.
I can do this differently if this method is not supported. I don't want to end up with a corrupted database.
Thanks in advance for your advice.
2014-02-25 23:24:27 CST []LOG: unexpected pageaddr 15D1/6E000000 in log file 5587, segment 22, offset 0
cp: cannot stat `/data1/pg_archlogs/ifb_prd/00000003000015D300000016': No such file or directory
2014-02-25 23:24:31 CST []LOG: restored log file "00000003000015D300000016" from archive
2014-02-25 23:24:31 CST []LOG: restored log file "00000003000015D300000017" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D300000018" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D300000019" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D30000001A" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D30000001B" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D30000001C" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D30000001D" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D30000001E" from archive
2014-02-25 23:24:33 CST []LOG: unexpected pageaddr 15D1/77000000 in log file 5587, segment 31, offset 0
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D30000001F" from archive
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D300000020" from archive
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D300000021" from archive
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D300000022" from archive
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D300000023" from archive
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D300000024" from archive
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D300000025" from archive
2014-02-25 23:24:37 CST []LOG: unexpected pageaddr 15D1/7E000000 in log file 5587, segment 38, offset 0
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D300000026" from archive
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D300000027" from archive
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D300000028" from archive
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D300000029" from archive
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D30000002A" from archive
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D30000002B" from archive
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D30000002C" from archive
2014-02-25 23:24:42 CST []LOG: restored log file "00000003000015D30000002D" from archive
2014-02-25 23:24:42 CST []LOG: restored log file "00000003000015D30000002E" from archive
2014-02-25 23:24:42 CST []LOG: unexpected pageaddr 15D1/26000000 in log file 5587, segment 47, offset 0
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Murthy Nunna
Sent: Monday, February 24, 2014 12:14 PM
To: Raghavendra
Cc: desmodemone; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR
Hi Raghavendra,
I used standby_mode=on and it worked. I can put checkpoints (not database checkpoint :)) in between and still be in recovery state. This is what I wanted.
Thanks for your help!
Murthy
From: Raghavendra [mailto:raghavendra.rao@enterprisedb.com]
Sent: Monday, February 24, 2014 6:31 AM
To: Murthy Nunna
Cc: desmodemone; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR
On Sun, Feb 23, 2014 at 8:50 PM, Murthy Nunna <mnunna@fnal.gov<mailto:mnunna@fnal.gov>> wrote:
Raghavendra,
Thanks for testing and confirming the behavior of "pause" setting.
While I understand your explanation, I feel I am still missing something. IMHO, when I say pause using "pause" setting, no matter what, I expect the recovery to wait for manual intervention.
I very much agree with your point that it has to pause when you ask for it, however, as per design (some other might comment on this well) am guessing it will open the database if no wals are there though you intentionally hide them.
You can use (HOT STANDBY) standby_mode=on which does the same thing, it just waits for the WAL files but it won't open the database until you pass the trigger file. In hot standby, it apply the existing wals fed and wait for coming wals and it won't come out of recovery. This you can try with below link.
http://wiki.postgresql.org/wiki/Hot_Standby
I myself can come up with number of reasons for doing so... e.g I may be purposely "hiding" some WALs somewhere else, or maybe I have several thousands of WALs that I want to parallelize the process of applying some logs while I recall some from tapes.
Let me know what you think.
Agreed it might be possible of not having wals at the moment and waiting for them to copy, however, I prefer in that case to use hot_standby. Pause just works in case if it sees some pending file in Arch.. location.
My explanation might not reach to your expectation, but I am sure few other's here might share their inputs.
--Raghav
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 04:27 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-23 06:08 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-23 10:13 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 15:20 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-24 12:30 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-24 18:14 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-26 05:33 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
@ 2014-02-26 13:55 ` Murthy Nunna <mnunna@fnal.gov>
2014-02-26 14:09 ` Re: PITR Geoff Winkless <pgsqladmin@geoff.dj>
0 siblings, 1 reply; 30+ messages in thread
From: Murthy Nunna @ 2014-02-26 13:55 UTC (permalink / raw)
To: pgsql-admin
Could someone please comment on this? Thanks.
From: Murthy Nunna
Sent: Tuesday, February 25, 2014 11:33 PM
To: Murthy Nunna; Raghavendra
Cc: desmodemone; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] PITR
All,
My database is in continuous recovery but it is not a traditional standby setup.... I am manually copying WALs (several thousands) from the source server to the server I am recovering. This means recovery is being done while WALs are being copied. It could so happen that a particular WAL could be open for recovery while it is still being copied (and not closed). Is this ok? I am asking because, I am seeing lots of LOG messages "unexpected pageaddr" as below.
I can do this differently if this method is not supported. I don't want to end up with a corrupted database.
Thanks in advance for your advice.
2014-02-25 23:24:27 CST []LOG: unexpected pageaddr 15D1/6E000000 in log file 5587, segment 22, offset 0
cp: cannot stat `/data1/pg_archlogs/ifb_prd/00000003000015D300000016': No such file or directory
2014-02-25 23:24:31 CST []LOG: restored log file "00000003000015D300000016" from archive
2014-02-25 23:24:31 CST []LOG: restored log file "00000003000015D300000017" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D300000018" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D300000019" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D30000001A" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D30000001B" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D30000001C" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D30000001D" from archive
2014-02-25 23:24:32 CST []LOG: restored log file "00000003000015D30000001E" from archive
2014-02-25 23:24:33 CST []LOG: unexpected pageaddr 15D1/77000000 in log file 5587, segment 31, offset 0
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D30000001F" from archive
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D300000020" from archive
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D300000021" from archive
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D300000022" from archive
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D300000023" from archive
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D300000024" from archive
2014-02-25 23:24:36 CST []LOG: restored log file "00000003000015D300000025" from archive
2014-02-25 23:24:37 CST []LOG: unexpected pageaddr 15D1/7E000000 in log file 5587, segment 38, offset 0
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D300000026" from archive
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D300000027" from archive
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D300000028" from archive
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D300000029" from archive
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D30000002A" from archive
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D30000002B" from archive
2014-02-25 23:24:41 CST []LOG: restored log file "00000003000015D30000002C" from archive
2014-02-25 23:24:42 CST []LOG: restored log file "00000003000015D30000002D" from archive
2014-02-25 23:24:42 CST []LOG: restored log file "00000003000015D30000002E" from archive
2014-02-25 23:24:42 CST []LOG: unexpected pageaddr 15D1/26000000 in log file 5587, segment 47, offset 0
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Murthy Nunna
Sent: Monday, February 24, 2014 12:14 PM
To: Raghavendra
Cc: desmodemone; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR
Hi Raghavendra,
I used standby_mode=on and it worked. I can put checkpoints (not database checkpoint :)) in between and still be in recovery state. This is what I wanted.
Thanks for your help!
Murthy
From: Raghavendra [mailto:raghavendra.rao@enterprisedb.com]
Sent: Monday, February 24, 2014 6:31 AM
To: Murthy Nunna
Cc: desmodemone; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR
On Sun, Feb 23, 2014 at 8:50 PM, Murthy Nunna <mnunna@fnal.gov<mailto:mnunna@fnal.gov>> wrote:
Raghavendra,
Thanks for testing and confirming the behavior of "pause" setting.
While I understand your explanation, I feel I am still missing something. IMHO, when I say pause using "pause" setting, no matter what, I expect the recovery to wait for manual intervention.
I very much agree with your point that it has to pause when you ask for it, however, as per design (some other might comment on this well) am guessing it will open the database if no wals are there though you intentionally hide them.
You can use (HOT STANDBY) standby_mode=on which does the same thing, it just waits for the WAL files but it won't open the database until you pass the trigger file. In hot standby, it apply the existing wals fed and wait for coming wals and it won't come out of recovery. This you can try with below link.
http://wiki.postgresql.org/wiki/Hot_Standby
I myself can come up with number of reasons for doing so... e.g I may be purposely "hiding" some WALs somewhere else, or maybe I have several thousands of WALs that I want to parallelize the process of applying some logs while I recall some from tapes.
Let me know what you think.
Agreed it might be possible of not having wals at the moment and waiting for them to copy, however, I prefer in that case to use hot_standby. Pause just works in case if it sees some pending file in Arch.. location.
My explanation might not reach to your expectation, but I am sure few other's here might share their inputs.
--Raghav
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 04:27 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-23 06:08 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-23 10:13 ` Re: PITR desmodemone <desmodemone@gmail.com>
2014-02-23 15:20 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-24 12:30 ` Re: PITR Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-24 18:14 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-26 05:33 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-26 13:55 ` Re: PITR Murthy Nunna <mnunna@fnal.gov>
@ 2014-02-26 14:09 ` Geoff Winkless <pgsqladmin@geoff.dj>
0 siblings, 0 replies; 30+ messages in thread
From: Geoff Winkless @ 2014-02-26 14:09 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
On 26 February 2014 13:55, Murthy Nunna <mnunna@fnal.gov> wrote:
> Could someone please comment on this? Thanks.
Sounds like it's not doing what you want it to do, huh?
Geoff
^ permalink raw reply [nested|flat] 30+ messages in thread
* PITR
@ 2023-11-22 08:11 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-11-22 08:23 ` Re: PITR Holger Jakobs <holger@jakobs.com>
2023-11-22 09:01 ` Re: PITR Laurenz Albe <laurenz.albe@cybertec.at>
2023-11-22 10:50 ` Re: PITR Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 3 replies; 30+ messages in thread
From: Rajesh Kumar @ 2023-11-22 08:11 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi
A person dropped the table and don't know time of drop.
How do I do PITR. Backup strategy is weekly full backup and daily
differential backup. Using pgbackrest.
Also. In future how do i monitor time of drop commands.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2023-11-22 08:11 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-11-22 08:23 ` Holger Jakobs <holger@jakobs.com>
2 siblings, 0 replies; 30+ messages in thread
From: Holger Jakobs @ 2023-11-22 08:23 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; +Cc: rajeshkumar.dba09@gmail.com
Am 22.11.23 um 09:11 schrieb Rajesh Kumar:
> Hi
>
> A person dropped the table and don't know time of drop.
>
> How do I do PITR. Backup strategy is weekly full backup and daily
> differential backup. Using pgbackrest.
>
> Also. In future how do i monitor time of drop commands.
Monitoring DDL commands works fine using event triggers
https://www.postgresql.org/docs/current/event-triggers.html
Code of event triggers may even fail a command by raising an exception
preventing the command becoming effective.
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../32d5ce00-9624-e072-2b1a-9f6637369c1e@jakobs.com/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2023-11-22 08:11 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-11-22 09:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 0 replies; 30+ messages in thread
From: Laurenz Albe @ 2023-11-22 09:01 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, 2023-11-22 at 13:41 +0530, Rajesh Kumar wrote:
> A person dropped the table and don't know time of drop.
>
> How do I do PITR. Backup strategy is weekly full backup and daily differential backup. Using pgbackrest.
>
> Also. In future how do i monitor time of drop commands.
You can monitor that by setting "log_statement = 'ddl'".
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2023-11-22 08:11 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-11-22 10:50 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-11-22 10:54 ` Re: PITR Andreas Kretschmer <andreas@a-kretschmer.de>
2 siblings, 1 reply; 30+ messages in thread
From: Ron Johnson @ 2023-11-22 10:50 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Wed, Nov 22, 2023 at 3:12 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
wrote:
> Hi
>
> A person dropped the table and don't know time of drop.
>
Revoke his permission to drop tables?
> How do I do PITR. Backup strategy is weekly full backup and daily
> differential backup. Using pgbackrest.
>
> Also. In future how do i monitor time of drop commands.
>
Set log_statement to 'all' or 'ddl', and then grep the log files for "DROP
TABLE ".
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2023-11-22 08:11 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-11-22 10:50 ` Re: PITR Ron Johnson <ronljohnsonjr@gmail.com>
@ 2023-11-22 10:54 ` Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 0 replies; 30+ messages in thread
From: Andreas Kretschmer @ 2023-11-22 10:54 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Am 22.11.23 um 11:50 schrieb Ron Johnson:
> On Wed, Nov 22, 2023 at 3:12 AM Rajesh Kumar
> <rajeshkumar.dba09@gmail.com> wrote:
>
>
>
> How do I do PITR. Backup strategy is weekly full backup and daily
> differential backup. Using pgbackrest.
>
> Also. In future how do i monitor time of drop commands.
>
https://blog.hagander.net/locating-the-recovery-point-just-before-a-dropped-table-230/
Andreas
--
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com
^ permalink raw reply [nested|flat] 30+ messages in thread
* PITR
@ 2024-05-17 11:35 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-05-17 11:46 ` Re: PITR Kashif Zeeshan <kashi.zeeshan@gmail.com>
2024-05-17 11:53 ` Re: PITR Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-17 13:26 ` Re: PITR Ron Johnson <ronljohnsonjr@gmail.com>
2024-05-17 17:24 ` Re: PITR Rui DeSousa <rui.desousa@icloud.com>
0 siblings, 4 replies; 30+ messages in thread
From: Rajesh Kumar @ 2024-05-17 11:35 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi all,
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?
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2024-05-17 11:35 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-05-17 11:46 ` Kashif Zeeshan <kashi.zeeshan@gmail.com>
3 siblings, 0 replies; 30+ messages in thread
From: Kashif Zeeshan @ 2024-05-17 11:46 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi Rajesh
As per my understanding you need to know the time to which you want to
recover.
Thanks
Kashif Zeeshan
Bitnine Global
On Fri, May 17, 2024 at 4:35 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
wrote:
> Hi all,
>
>
> 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?
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2024-05-17 11:35 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-05-17 11:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
3 siblings, 0 replies; 30+ messages in thread
From: Laurenz Albe @ 2024-05-17 11:53 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, 2024-05-17 at 17:05 +0530, Rajesh Kumar 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?
- you cannot WAL log only DDL, you log everything
- if you don't know which time to recover to, you cannot recover to that time
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2024-05-17 11:35 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-05-17 13:26 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-05-17 13:41 ` Re: PITR MichaelDBA <MichaelDBA@sqlexec.com>
3 siblings, 1 reply; 30+ messages in thread
From: Ron Johnson @ 2024-05-17 13:26 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, May 17, 2024 at 7:35 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
wrote:
> Hi all,
>
>
> 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?
>
If your system is busy, then log_statement = 'mod' is going to generate a
LOT of pg_log data.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2024-05-17 11:35 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-05-17 13:26 ` Re: PITR Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-05-17 13:41 ` MichaelDBA <MichaelDBA@sqlexec.com>
2024-05-17 13:47 ` Re: PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: MichaelDBA @ 2024-05-17 13:41 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
To answer your question, logging DDL does not have ANYTHING to do with
PITR. So the answer is "NO, you cannot do PITR by just logging logging
DDL".
You have to use binary backups and continuous WAL logging to be able to
do PITR. There are many tools out there to do that. The best one in my
opinion is PGBackrest.
Regards,
Michael Vitale
Ron Johnson wrote on 5/17/2024 9:26 AM:
> On Fri, May 17, 2024 at 7:35 AM Rajesh Kumar
> <rajeshkumar.dba09@gmail.com <mailto:rajeshkumar.dba09@gmail.com>> wrote:
>
> Hi all,
>
>
> 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?
>
>
> If your system is busy, then log_statement = 'mod' is going to
> generate a LOT of pg_log data.
>
Regards,
Michael Vitale
Michaeldba@sqlexec.com <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../../8fd671ff-52bc-8ecd-d4c5-130eb19cd31b@sqlexec.com/3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2024-05-17 11:35 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-05-17 13:26 ` Re: PITR Ron Johnson <ronljohnsonjr@gmail.com>
2024-05-17 13:41 ` Re: PITR MichaelDBA <MichaelDBA@sqlexec.com>
@ 2024-05-17 13:47 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 0 replies; 30+ messages in thread
From: Rajesh Kumar @ 2024-05-17 13:47 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Thank you all
On Fri, 17 May 2024, 19:12 MichaelDBA, <MichaelDBA@sqlexec.com> wrote:
> To answer your question, logging DDL does not have ANYTHING to do with
> PITR. So the answer is "NO, you cannot do PITR by just logging logging
> DDL".
>
> You have to use binary backups and continuous WAL logging to be able to do
> PITR. There are many tools out there to do that. The best one in my
> opinion is PGBackrest.
>
> Regards,
> Michael Vitale
>
>
> Ron Johnson wrote on 5/17/2024 9:26 AM:
>
> On Fri, May 17, 2024 at 7:35 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
> wrote:
>
>> Hi all,
>>
>>
>> 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?
>>
>
> If your system is busy, then log_statement = 'mod' is going to generate
> a LOT of pg_log data.
>
>
>
> Regards,
>
> Michael Vitale
>
> Michaeldba@sqlexec.com <michaelvitale@sqlexec.com>
>
> 703-600-9343
>
>
>
>
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../../CAJk5AtYk-Oj2LqOfCGpatzQt8uC2T+Jp-q_gc14pWYgMBx2+iw@mail.gmail.com/3-pgadvanced3.jpg)
download | view image
[image/jpeg] pgadvanced3.jpg (20.6K, ../../CAJk5AtYk-Oj2LqOfCGpatzQt8uC2T+Jp-q_gc14pWYgMBx2+iw@mail.gmail.com/4-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2024-05-17 11:35 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-05-17 17:24 ` Rui DeSousa <rui.desousa@icloud.com>
2024-05-18 15:05 ` Re: PITR Ron Johnson <ronljohnsonjr@gmail.com>
3 siblings, 1 reply; 30+ messages in thread
From: Rui DeSousa @ 2024-05-17 17:24 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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. =
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: PITR
2024-05-17 11:35 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-05-17 17:24 ` Re: PITR Rui DeSousa <rui.desousa@icloud.com>
@ 2024-05-18 15:05 ` Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 30+ messages in thread
From: Ron Johnson @ 2024-05-18 15:05 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Sat, May 18, 2024 at 7:42 AM Rui DeSousa <rui.desousa@icloud.com> wrote:
>
> 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.
>
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.
^ permalink raw reply [nested|flat] 30+ messages in thread
end of thread, other threads:[~2024-05-18 15:05 UTC | newest]
Thread overview: 30+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-31 17:36 PITR Mr. Dan <bitsandbytes88@hotmail.com>
2006-08-31 18:09 ` Joshua D. Drake <jd@commandprompt.com>
2014-02-22 16:06 PITR Murthy Nunna <mnunna@fnal.gov>
2014-02-22 16:33 ` desmodemone <desmodemone@gmail.com>
2014-02-22 17:03 ` Murthy Nunna <mnunna@fnal.gov>
2014-02-22 17:31 ` Julien Rouhaud <julien.rouhaud@dalibo.com>
2014-02-22 18:29 ` desmodemone <desmodemone@gmail.com>
2014-02-23 04:27 ` Murthy Nunna <mnunna@fnal.gov>
2014-02-23 06:08 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-23 10:13 ` desmodemone <desmodemone@gmail.com>
2014-02-23 15:20 ` Murthy Nunna <mnunna@fnal.gov>
2014-02-23 18:06 ` bricklen <bricklen@gmail.com>
2014-02-24 12:30 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2014-02-24 18:14 ` Murthy Nunna <mnunna@fnal.gov>
2014-02-26 05:33 ` Murthy Nunna <mnunna@fnal.gov>
2014-02-26 13:55 ` Murthy Nunna <mnunna@fnal.gov>
2014-02-26 14:09 ` Geoff Winkless <pgsqladmin@geoff.dj>
2023-11-22 08:11 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-11-22 08:23 ` Holger Jakobs <holger@jakobs.com>
2023-11-22 09:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-11-22 10:50 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-11-22 10:54 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2024-05-17 11:35 PITR Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-05-17 11:46 ` Kashif Zeeshan <kashi.zeeshan@gmail.com>
2024-05-17 11:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-17 13:26 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-05-17 13:41 ` MichaelDBA <MichaelDBA@sqlexec.com>
2024-05-17 13:47 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-05-17 17:24 ` Rui DeSousa <rui.desousa@icloud.com>
2024-05-18 15:05 ` Ron Johnson <ronljohnsonjr@gmail.com>
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