Re: Do I understand commit timestamps correctly?

Chapman Flack wrote:
> Hi,
>
> Can somebody confirm or correct what I (think I)'ve gleaned from
> the code?
>
> - Commit timestamps are always WAL logged, and so in principle
> determinable after the fact (with some amount of effort), regardless
> of the track_commit_timestamp setting. (I guess this must have long
> been true, to support recovery_target_time.)

Right.

> - The extra machinery turned on by track_commit_timestamp maintains
> a cache of recent ones so they can be efficiently queried from SQL
> in normal operation.

Yes.

> ? 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.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services