public inbox for pgsql-novice@postgresql.org
help / color / mirror / Atom feedVery basic question about Archive logs
7+ messages / 3 participants
[nested] [flat]
* Very basic question about Archive logs
@ 2026-03-26 12:09 Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-26 12:30 ` Re: Very basic question about Archive logs David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Subramanian,Ramachandran @ 2026-03-26 12:09 UTC (permalink / raw)
To: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
Hallo,
Newbie here. Kindly forgive my ignorance. A question about archive logs.
If I make a pg_basebackup on Monday (backup ended ) at 0100 Hours and have all the archive logs from this time onwards till the next backup on Tuesday (backup ended) at 0100 Hours, I could theoretically go to any PIT between Monday 0100 Hours to Tuesday 0100Hours . Is my understanding correct?
Further more, if I execute the PIT-recovery On Tuesday at 0005 Hours and specify a Target recovery time of Monday 2300 Hours, then Postgres will look at the active logs and any archive logs ( if it does not already exist in the active logs ) that were written between Tuesday 0005 Hours and Monday 2300 Hours and use these logs to Undo all the changes to the current Database. Is my understanding correct?
In Postgres PIT recovery, there is only rollback allowed ... No roll forward allowed ? For example Could I restore the backup from Monday 0100 Hours and somehow rollforward the logs?
Additionally, where does postgres keep a record of which log was cut at what time and which active log was archived at what time to be reused ?
LG
Ram
Freundliche Grüße
i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie
Alte Leipziger Lebensversicherung a.G.
Hallesche Krankenversicherung a.G.
Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
Hallesche Krankenversicherung a.G., Löffelstraße 34-38, 70597 Stuttgart
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
Wiltrud Pekarek, Udo Wilcsek
Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum
______________________
ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel.: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: ramachandran.subramanian@alte-leipziger.de
www.alte-leipziger.de
www.hallesche.de
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Very basic question about Archive logs
2026-03-26 12:09 Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
@ 2026-03-26 12:30 ` David G. Johnston <david.g.johnston@gmail.com>
2026-03-26 12:38 ` AW: Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2026-03-26 12:30 UTC (permalink / raw)
To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; +Cc: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
On Thursday, March 26, 2026, Subramanian,Ramachandran <
ramachandran.subramanian@alte-leipziger.de> wrote:
> Further more, if I execute the PIT-recovery On Tuesday at 0005 Hours and
> specify a Target recovery time of Monday 2300 Hours, then Postgres will
> look at the active logs and any archive logs ( if it does not already exist
> in the active logs ) that were written between Tuesday 0005 Hours and
> Monday 2300 Hours and use these logs to Undo all the changes to the current
> Database. Is my understanding correct?
>
> There is no rolling back. You deploy the base backup first to 0100
Monday, then it would apply the WAL up to the target time.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* AW: Very basic question about Archive logs
2026-03-26 12:09 Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-26 12:30 ` Re: Very basic question about Archive logs David G. Johnston <david.g.johnston@gmail.com>
@ 2026-03-26 12:38 ` Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-26 12:47 ` Re: Very basic question about Archive logs David G. Johnston <david.g.johnston@gmail.com>
2026-03-26 13:00 ` Re: AW: Very basic question about Archive logs Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 7+ messages in thread
From: Subramanian,Ramachandran @ 2026-03-26 12:38 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
Oh… Thank you for your reply.
so there is ONLY roll forward !!
So If I have to go to any time backwards from NOW , I WILL DEFINITELY need a backup older than NOW and all the Logs Active/Archive inbetween.
Just a theoretical question to better my understanding. If I take a backup on Monday at 0100 Hours and never make any further Backups but hold on to all the Logs ( Active and Archive),
I could theoretically go to any PIT between Monday 0100 Hours and NOW?
If I apply the base backup from Monday 0100 , how does Postgres know which Archive Logs need to be applied from it’s current state to get to the PIT. Obviously when the basebackup was taken, the now needed logs did not exist. So this information cannot have been recorded during the backup.
There is no rolling back. You deploy the base backup first to 0100 Monday, then it would apply the WAL up to the target time.
David J.
Freundliche Grüße
i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie
Alte Leipziger Lebensversicherung a.G.
Hallesche Krankenversicherung a.G.
Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
Hallesche Krankenversicherung a.G., Löffelstraße 34-38, 70597 Stuttgart
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
Wiltrud Pekarek, Udo Wilcsek
Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum
______________________
ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel.: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: ramachandran.subramanian@alte-leipziger.de
www.alte-leipziger.de
www.hallesche.de
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Very basic question about Archive logs
2026-03-26 12:09 Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-26 12:30 ` Re: Very basic question about Archive logs David G. Johnston <david.g.johnston@gmail.com>
2026-03-26 12:38 ` AW: Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
@ 2026-03-26 12:47 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2026-03-26 12:47 UTC (permalink / raw)
To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; +Cc: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
On Thursday, March 26, 2026, Subramanian,Ramachandran <
ramachandran.subramanian@alte-leipziger.de> wrote:
>
> If I apply the base backup from Monday 0100 , how does Postgres know which
> Archive Logs need to be applied from its current state to get to the
> PIT. Obviously when the basebackup was taken, the now needed logs did not
> exist. So this information cannot have been recorded during the backup.
>
> We’ve documented how to actually use the software.
https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-PITR-RECOVERY
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: AW: Very basic question about Archive logs
2026-03-26 12:09 Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-26 12:30 ` Re: Very basic question about Archive logs David G. Johnston <david.g.johnston@gmail.com>
2026-03-26 12:38 ` AW: Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
@ 2026-03-26 13:00 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-03-26 13:42 ` AW: AW: Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
1 sibling, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2026-03-26 13:00 UTC (permalink / raw)
To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
On Thu, 2026-03-26 at 12:38 +0000, Subramanian,Ramachandran wrote:
> Just a theoretical question to better my understanding. If I take a backup
> on Monday at 0100 Hours and never make any further Backups but hold on to
> all the Logs ( Active and Archive),
> I could theoretically go to any PIT between Monday 0100 Hours and NOW?
Yes. That is, if the backup *ended* at 1:00 a.m.
> If I apply the base backup from Monday 0100 , how does Postgres know which
> Archive Logs need to be applied from it’s current state to get to the PIT.
> Obviously when the basebackup was taken, the now needed logs did not exist.
> So this information cannot have been recorded during the backup.
PostgreSQL knows where to start, because that is in the backup_label file.
If recovery reaches the end of the current WAL segment, and we have not
yet reached the recovery target, PostgreSQL will restore the next WAL segment
and continue recovery (actually, PostgreSQL prefetches WAL segments, but that
doesn't affect the principle).
Every commit record in the WAL has the commit timestamp, and that is how
PostgreSQL can determine if it has reached the recovery target (if
recovery_target_time was used).
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* AW: AW: Very basic question about Archive logs
2026-03-26 12:09 Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-26 12:30 ` Re: Very basic question about Archive logs David G. Johnston <david.g.johnston@gmail.com>
2026-03-26 12:38 ` AW: Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-26 13:00 ` Re: AW: Very basic question about Archive logs Laurenz Albe <laurenz.albe@cybertec.at>
@ 2026-03-26 13:42 ` Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-26 17:17 ` Re: AW: AW: Very basic question about Archive logs Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 7+ messages in thread
From: Subramanian,Ramachandran @ 2026-03-26 13:42 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
I see two files backup_label and backup_label.old in the backups ( which I assume is the backup_label from the previous backup ) .
When I restore the base backup, from Monday 0100 Hours, Postgres will look at 0/53000028 and look for newer Logs beginning from this log file in the Archivelog dir.
Only in the archive log dir because we would have over written the active log dir. Postgres will rollforward till end of Logs OR a particular timestamp if one is specified.
Am I understanding this correctly?
What purpose does the .old serve?
LG
Ram
START WAL LOCATION: 0/53000028 (file 000000010000000000000053)
CHECKPOINT LOCATION: 0/53000060
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2026-03-23 00:55:57 CET
LABEL: This is fullbackup
START TIMELINE: 1
START WAL LOCATION: 0/3D000028 (file 00000001000000000000003D)
CHECKPOINT LOCATION: 0/3D000060
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2026-02-28 02:15:02 CET
LABEL: This is fullbackup
START TIMELINE: 1
Yes. That is, if the backup *ended* at 1:00 a.m.[Subramanian,Ramachandran IT-md-db] I understand what you mean
Freundliche Grüße
i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie
Alte Leipziger Lebensversicherung a.G.
Hallesche Krankenversicherung a.G.
Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
Hallesche Krankenversicherung a.G., Löffelstraße 34-38, 70597 Stuttgart
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
Wiltrud Pekarek, Udo Wilcsek
Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum
______________________
ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel.: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: ramachandran.subramanian@alte-leipziger.de
www.alte-leipziger.de
www.hallesche.de
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: AW: AW: Very basic question about Archive logs
2026-03-26 12:09 Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-26 12:30 ` Re: Very basic question about Archive logs David G. Johnston <david.g.johnston@gmail.com>
2026-03-26 12:38 ` AW: Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-26 13:00 ` Re: AW: Very basic question about Archive logs Laurenz Albe <laurenz.albe@cybertec.at>
2026-03-26 13:42 ` AW: AW: Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
@ 2026-03-26 17:17 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2026-03-26 17:17 UTC (permalink / raw)
To: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
On Thu, 2026-03-26 at 13:42 +0000, Subramanian,Ramachandran wrote:
> When I restore the base backup, from Monday 0100 Hours, Postgres will look
> at 0/53000028 and look for newer Logs beginning from this log file ...
Yes.
> ... in the Archivelog dir.
PostgreSQL knows no such directory. It executes restore_command and expects
it to restore the desired WAL segment to pg_wal.
> Only in the archive log dir because we would have over written the
> active log dir.
I don't understand that sentence.
> Postgres will rollforward till end of Logs OR a particular timestamp if one is specified.
Yes.
> Am I understanding this correctly?
I guess so.
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-03-26 17:17 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-26 12:09 Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-26 12:30 ` David G. Johnston <david.g.johnston@gmail.com>
2026-03-26 12:38 ` AW: Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-26 12:47 ` David G. Johnston <david.g.johnston@gmail.com>
2026-03-26 13:00 ` Re: AW: Very basic question about Archive logs Laurenz Albe <laurenz.albe@cybertec.at>
2026-03-26 13:42 ` AW: AW: Very basic question about Archive logs Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
2026-03-26 17:17 ` Re: AW: AW: Very basic question about Archive logs Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox