Re: Do I understand commit timestamps correctly?

From: Chapman Flack <chap(at)anastigmatix(dot)net>
To: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Do I understand commit timestamps correctly?
Date: 2020-05-13 04:28:20
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 03/23/18 11:40, Alvaro Herrera wrote:
> Chapman Flack wrote:
>> ? Given a base backup and a bunch of WAL from a cluster that had
>> track_commit_timestamps turned off, is it possible (in principle?)
>> to do a PITR with the switch turned on, and have the commit_ts
>> cache get populated (at least from the transactions encountered
>> in the WAL)? Could that be done by changing the setting in
>> postgresql.conf for the recovery, or would it take something more
>> invasive, like poking the value in pg_control? Or would that just
>> make something fall over? Would it require dummying up some commit_ts
>> files first?
> I don't remember if this is explicitly supported, but yeah AFAIR it
> should work to just start the "promoted standby" (in your case just a
> recovered backup) on after setting the option in postgresql.conf. This
> is because StartupCommitTs() activates the commit_ts module just before
> starting recovery.

Getting around to trying it out, simply changing the setting in
postgresql.conf before starting the server does not seem sufficient:
once it comes online, it has track_commit_timestamp on, but has not
populated the cache from transactions it applied during recovery.

On the other hand, changing the setting in postgresql.conf *and*
poking a 1 in the track_commit_timestamp byte in pg_control,
and fudging the CRC accordingly, *then* starting the server, does
seem to do just as I had hoped. Nothing seems to complain or fall over,
and the transactions recovered from WAL now have timestamps visible
with pg_xact_commit_timestamp().


In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Dilip Kumar 2020-05-13 04:59:09 Re: Problem with logical replication
Previous Message Suraj Kharage 2020-05-13 04:01:26 Re: refactoring basebackup.c