Re: Do I understand commit timestamps correctly?

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().

Regards,
-Chap