Re: Recovery inconsistencies, standby much larger than primary

On Sun, Feb 2, 2014 at 6:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Greg Stark <stark(at)mit(dot)edu> writes:
>> The relfilenodes that have nul blocks before the last block are:
>
> Can we see the associated WAL records (ie, the ones matching the LSNs
> in the last blocks of these files)?

Sorry, I've lost track of what information I already shared or didn't,
i've been staring at these records all day. It would be so much easier
if xlogdump was a fdw or set returning function so I could do joins
with pageinspect data:

This is the information from the final block headers:

relfilenode | blockno | segnum | offset | lsn | tli | flags
| lower | upper | special | pagesize | version | prune_xid
-------------+----------+--------+----------+------------+-----+-------+-------+-------+---------+----------+---------+------------
473158 | 18090059 | 138 | 18090059 | EA1/625E28 | 6 | 0
| 144 | 896 | 8192 | 8192 | 4 | 1401029863
1366221 | 2208159 | 16 | 2208159 | EA1/62DDF0 | 6 | 0
| 1180 | 3552 | 8176 | 8192 | 4 | 0
1261982 | 7141472 | 54 | 7141472 | EA1/638988 | 6 | 0
| 1240 | 3312 | 8176 | 8192 | 4 | 0
1364767 | 3631161 | 27 | 3631161 | EA1/63A0A8 | 6 | 0
| 1180 | 3552 | 8176 | 8192 | 4 | 0
1519286 | 311808 | 2 | 311808 | EA1/708B08 | 6 | 1
| 312 | 3040 | 8192 | 8192 | 4 | 0

These are all the records from the tx that corresponds to the lsn in
the first relfilenode:

[cur:EA1/6240C0, xid:1418089146, rmid:10(Heap), len/tot_len:28/7524,
info:72, prev:EA1/624080] hot_update: s/d/r:1663/16385/473158 block
9386399 off 29 to block 9386399 off 30
[cur:EA1/6240C0, xid:1418089146, rmid:10(Heap), len/tot_len:28/7524,
info:72, prev:EA1/624080] bkpblock[1]: s/d/r:1663/16385/473158
blk:9386399 hole_off/len:144/752
[cur:EA1/625E28, xid:1418089146, rmid:1(Transaction),
len/tot_len:32/64, info:0, prev:EA1/6240C0] d/s:16385/1663 commit at
2014-01-21 05:41:11 UTC

The middle three blocks have LSNs that are all part of the same tx,
here are all the records for this tx:

[cur:EA1/625F28, xid:1418089147, rmid:10(Heap), len/tot_len:21/6441,
info:8, prev:EA1/625E68] insert: s/d/r:1663/16385/473158
blk/off:9386398/33 header: none
[cur:EA1/625F28, xid:1418089147, rmid:10(Heap), len/tot_len:21/6441,
info:8, prev:EA1/625E68] bkpblock[1]: s/d/r:1663/16385/473158
blk:9386398 hole_off/len:156/1828
[cur:EA1/627868, xid:1418089147, rmid:11(Btree), len/tot_len:18/8214,
info:8, prev:EA1/625F28] insert_leaf: s/d/r:1663/16385/473176 tid
1319804/405
[cur:EA1/627868, xid:1418089147, rmid:11(Btree), len/tot_len:18/8214,
info:8, prev:EA1/625F28] bkpblock[1]: s/d/r:1663/16385/473176
blk:1319804 hole_off/len:1644/52
[cur:EA1/629898, xid:1418089147, rmid:11(Btree), len/tot_len:18/6494,
info:8, prev:EA1/627868] insert_leaf: s/d/r:1663/16385/473182 tid
1186167/147
[cur:EA1/629898, xid:1418089147, rmid:11(Btree), len/tot_len:18/6494,
info:8, prev:EA1/627868] bkpblock[1]: s/d/r:1663/16385/473182
blk:1186167 hole_off/len:1300/1772
[cur:EA1/62B210, xid:1418089147, rmid:11(Btree), len/tot_len:18/5314,
info:8, prev:EA1/629898] insert_leaf: s/d/r:1663/16385/1270734 tid
1294137/2
[cur:EA1/62B210, xid:1418089147, rmid:11(Btree), len/tot_len:18/5314,
info:8, prev:EA1/629898] bkpblock[1]: s/d/r:1663/16385/1270734
blk:1294137 hole_off/len:1064/2952
[cur:EA1/62C6E8, xid:1418089147, rmid:11(Btree), len/tot_len:18/5894,
info:8, prev:EA1/62B210] insert_leaf: s/d/r:1663/16385/1366221 tid
1364573/66
[cur:EA1/62C6E8, xid:1418089147, rmid:11(Btree), len/tot_len:18/5894,
info:8, prev:EA1/62B210] bkpblock[1]: s/d/r:1663/16385/1366221
blk:1364573 hole_off/len:1180/2372
[cur:EA1/62DDF0, xid:1418089147, rmid:11(Btree), len/tot_len:18/4814,
info:8, prev:EA1/62C6E8] insert_leaf: s/d/r:1663/16385/1404440 tid
1195953/51
[cur:EA1/62DDF0, xid:1418089147, rmid:11(Btree), len/tot_len:18/4814,
info:8, prev:EA1/62C6E8] bkpblock[1]: s/d/r:1663/16385/1404440
blk:1195953 hole_off/len:964/3452
[cur:EA1/62F0D8, xid:1418089147, rmid:11(Btree), len/tot_len:18/6862,
info:8, prev:EA1/62DDF0] insert_leaf: s/d/r:1663/16385/1410405 tid
1894183/39
[cur:EA1/62F0D8, xid:1418089147, rmid:11(Btree), len/tot_len:18/6862,
info:8, prev:EA1/62DDF0] bkpblock[1]: s/d/r:1663/16385/1410405
blk:1894183 hole_off/len:988/1404
[cur:EA1/630BC0, xid:1418089147, rmid:11(Btree), len/tot_len:18/7254,
info:8, prev:EA1/62F0D8] insert_leaf: s/d/r:1663/16385/1521566 tid
1691110/132
[cur:EA1/630BC0, xid:1418089147, rmid:11(Btree), len/tot_len:18/7254,
info:8, prev:EA1/62F0D8] bkpblock[1]: s/d/r:1663/16385/1521566
blk:1691110 hole_off/len:1044/1012
[cur:EA1/632830, xid:1418089147, rmid:11(Btree), len/tot_len:18/5174,
info:8, prev:EA1/630BC0] insert_leaf: s/d/r:1663/16385/5285587 tid
386419/102
[cur:EA1/632830, xid:1418089147, rmid:11(Btree), len/tot_len:18/5174,
info:8, prev:EA1/630BC0] bkpblock[1]: s/d/r:1663/16385/5285587
blk:386419 hole_off/len:1036/3092
[cur:EA1/633C68, xid:1418089147, rmid:15(Sequence),
len/tot_len:158/190, info:0, prev:EA1/632830] seq
[cur:EA1/633D28, xid:1418089147, rmid:10(Heap), len/tot_len:188/220,
info:128, prev:EA1/633C68] insert(init): s/d/r:1663/16385/16523
blk/off:12933288/1 header: t_infomask2 11 t_infomask 2051 t_hoff 32
[cur:EA1/633E08, xid:1418089147, rmid:11(Btree), len/tot_len:18/5234,
info:8, prev:EA1/633D28] insert_leaf: s/d/r:1663/16385/1261892 tid
3008033/256
[cur:EA1/633E08, xid:1418089147, rmid:11(Btree), len/tot_len:18/5234,
info:8, prev:EA1/633D28] bkpblock[1]: s/d/r:1663/16385/1261892
blk:3008033 hole_off/len:1048/3032
[cur:EA1/635290, xid:1418089147, rmid:11(Btree), len/tot_len:18/7834,
info:8, prev:EA1/633E08] insert_leaf: s/d/r:1663/16385/184672 tid
1704550/5
[cur:EA1/635290, xid:1418089147, rmid:11(Btree), len/tot_len:18/7834,
info:8, prev:EA1/633E08] bkpblock[1]: s/d/r:1663/16385/184672
blk:1704550 hole_off/len:1568/432
[cur:EA1/637140, xid:1418089147, rmid:11(Btree), len/tot_len:18/6194,
info:8, prev:EA1/635290] insert_leaf: s/d/r:1663/16385/1261982 tid
3634978/282
[cur:EA1/637140, xid:1418089147, rmid:11(Btree), len/tot_len:18/6194,
info:8, prev:EA1/635290] bkpblock[1]: s/d/r:1663/16385/1261982
blk:3634978 hole_off/len:1240/2072
[cur:EA1/638988, xid:1418089147, rmid:11(Btree), len/tot_len:18/5894,
info:8, prev:EA1/637140] insert_leaf: s/d/r:1663/16385/1364767 tid
2746914/219
[cur:EA1/638988, xid:1418089147, rmid:11(Btree), len/tot_len:18/5894,
info:8, prev:EA1/637140] bkpblock[1]: s/d/r:1663/16385/1364767
blk:2746914 hole_off/len:1180/2372
[cur:EA1/63A0A8, xid:1418089147, rmid:1(Transaction),
len/tot_len:32/64, info:0, prev:EA1/638988] d/s:16385/1663 commit at
2014-01-21 05:41:11 UTC

The last block does a fair amount of traffic, these are the first four
records the first two are the only ones that touch this relfilenode:

[cur:EA1/707548, xid:1418089185, rmid:10(Heap), len/tot_len:21/5541,
info:8, prev:EA1/707488] insert: s/d/r:1663/16385/1519286
blk/off:281032/13 header: none
[cur:EA1/707548, xid:1418089185, rmid:10(Heap), len/tot_len:21/5541,
info:8, prev:EA1/707488] bkpblock[1]: s/d/r:1663/16385/1519286
blk:281032 hole_off/len:312/2728
[cur:EA1/708B08, xid:1418089185, rmid:11(Btree), len/tot_len:18/6814,
info:8, prev:EA1/707548] insert_leaf: s/d/r:1663/16385/1519292 tid
110564/335
[cur:EA1/708B08, xid:1418089185, rmid:11(Btree), len/tot_len:18/6814,
info:8, prev:EA1/707548] bkpblock[1]: s/d/r:1663/16385/1519292
blk:110564 hole_off/len:1364/1452

--
greg