[GENERAL] checkpoint clarifications needed

Hi, I need some clarifications about checkpoints.

Below here a log from my standby server when started and then some parts of the interested WAL in the master's cluster  obtained by pg_xlogdump.

Just to have an example to talk on.

1) I see: "LOG:  redo starts at 1/F00A7448" . I was expecting a checkpoint record around 1/F00A7448 but the related checkpoint record is at lsn: 1/FCBD7510 instead.

And I interpreted that as "All information written before that checkpoint RECORD" but now I guess that one thing is a checkpoint point and one thing is a checkpoint record. Right?

2) I see that a checkpoint position can be right in the middle of a group of records related to a transaction (in the example, transaction id 10684). So a checkpoint position is NOT a consistency state point, right?

3) According to doc at 29.5,  in pg_control the position of the last checkpoint record (not the checkpoint position itself) is saved  right?

4) If I'm right at 2) then, between the checkpoint position (1/F00A7448 ) and the checkpoint record position (1/FCBD7510) there must be a point where the DB is in a consistency state. If not, in case of crash just after writing the checkpoint record to the WAL and its position to pg_control, the system would replay from the checkpoint position (known by  last checkpoint record) without finding a consistency state. Right?

5) How can we define, in terms of log records, a consistency state position?

Best regards

Pupillo

STANDBY SERVER LOG

LOG:  redo starts at 1/F00A7448

....

LOG:  consistent recovery state reached at 2/426DF28

LOG:  invalid record length at 2/426DF28: wanted 24, got 0

LOG:  database system is ready to accept read only connections

LOG:  started streaming WAL from primary at 2/4000000 on timeline 1

FROM PG_XLOGDUMP OF MASTER 

........

rmgr: Heap        len (rec/tot):     14/  1186, tx:      10684, lsn: 1/F009EEE0, prev 1/F009EEA8, desc: UPDATE off 1 xmax 10684 ; new off 3 xmax 0, blkref #0: rel 1663/16384/16422 blk 20054, blkref #1: rel 1663/16384/16422 blk 19774

rmgr: Btree       len (rec/tot):      2/    64, tx:      10684, lsn: 1/F009F388, prev 1/F009EEE0, desc: INSERT_LEAF off 2, blkref #0: rel 1663/16384/16428 blk 711

rmgr: XLOG        len (rec/tot):      0/  8193, tx:          0, lsn: 1/F009F3C8, prev 1/F009F388, desc: FPI_FOR_HINT , blkref #0: rel 1663/16384/16484 blk 9663 FPW

rmgr: XLOG        len (rec/tot):      0/  8193, tx:          0, lsn: 1/F00A13E8, prev 1/F009F3C8, desc: FPI_FOR_HINT , blkref #0: rel 1663/16384/16484 blk 9664 FPW

rmgr: XLOG        len (rec/tot):      0/  8193, tx:          0, lsn: 1/F00A3408, prev 1/F00A13E8, desc: FPI_FOR_HINT , blkref #0: rel 1663/16384/16484 blk 9665 FPW

rmgr: XLOG        len (rec/tot):      0/  8193, tx:          0, lsn: 1/F00A5428, prev 1/F00A3408, desc: FPI_FOR_HINT , blkref #0: rel 1663/16384/16484 blk 9666 FPW

rmgr: XLOG        len (rec/tot):      0/  8193, tx:          0, lsn: 1/F00A7448, prev 1/F00A5428, desc: FPI_FOR_HINT , blkref #0: rel 1663/16384/16484 blk 9667 FPW

rmgr: XLOG        len (rec/tot):      0/  8193, tx:          0, lsn: 1/F00A9468, prev 1/F00A7448, desc: FPI_FOR_HINT , blkref #0: rel 1663/16384/16484 blk 9668 FPW

rmgr: XLOG        len (rec/tot):      0/  8193, tx:          0, lsn: 1/F00AB488, prev 1/F00A9468, desc: FPI_FOR_HINT , blkref #0: rel 1663/16384/16484 blk 9669 FPW

rmgr: Heap        len (rec/tot):      8/  8063, tx:      10682, lsn: 1/F00AD4A8, prev 1/F00AB488, desc: LOCK off 5: xid 10682: flags 0 LOCK_ONLY EXCL_LOCK , blkref #0: rel 1663/16384/16422 blk 19787 FPW

rmgr: Heap        len (rec/tot):      8/  8063, tx:      10684, lsn: 1/F00AF440, prev 1/F00AD4A8, desc: LOCK off 2: xid 10684: flags 0 LOCK_ONLY EXCL_LOCK , blkref #0: rel 1663/16384/16422 blk 19774 FPW

rmgr: Heap        len (rec/tot):     14/  4657, tx:      10682, lsn: 1/F00B13D8, prev 1/F00AF440, desc: UPDATE off 5 xmax 10682 ; new off 4 xmax 0, blkref #0: rel 1663/16384/16422 blk 20075 FPW, blkref #1: rel 1663/16384/16422 blk 19787

rmgr: Heap        len (rec/tot):     14/  4657, tx:      10684, lsn: 1/F00B2628, prev 1/F00B13D8, desc: UPDATE off 2 xmax 10684 ; new off 4 xmax 0, blkref #0: rel 1663/16384/16422 blk 20054 FPW, blkref #1: rel 1663/16384/16422 blk 19774

rmgr: Btr

.......

rmgr: Heap2       len (rec/tot):      8/    68, tx:          0, lsn: 1/FCBD7448, prev 1/FCBD7400, desc: CLEAN remxid 10903, blkref #0: rel 1663/16384/16422 blk 2001

rmgr: Heap2       len (rec/tot):      8/    66, tx:          0, lsn: 1/FCBD7490, prev 1/FCBD7448, desc: CLEAN remxid 10903, blkref #0: rel 1663/16384/16422 blk 2003

rmgr: Standby     len (rec/tot):     24/    50, tx:          0, lsn: 1/FCBD74D8, prev 1/FCBD7490, desc: RUNNING_XACTS nextXid 10907 latestCompletedXid 10906 oldestRunningXid 10907

rmgr: XLOG        len (rec/tot):     80/   106, tx:          0, lsn: 1/FCBD7510, prev 1/FCBD74D8, desc: CHECKPOINT_ONLINE redo 1/F00A7448; tli 1; prev tli 1; fpw true; xid 0:10685; oid 24665; multi 1; offset 0; oldest xid 584 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 10682; online

rmgr: Heap        len (rec/tot):      3/   164, tx:      10907, lsn: 1/FCBD7580, prev 1/FCBD7510, desc: INSERT off 25, blkref #0: rel 1663/16384/16484 blk 16398

rmgr: Btree       len (rec/tot):      2/    64, tx:      10907, lsn: 1/FCBD7628, prev 1/FCBD7580, desc: INSERT_LEAF off 60, blkref #0: rel 1663/16384/16490 blk 2722

........