Re: Recovery inconsistencies, standby much larger than primary - Mailing list pgsql-hackers
| From | Greg Stark |
|---|---|
| Subject | Re: Recovery inconsistencies, standby much larger than primary |
| Date | |
| Msg-id | CAM-w4HPJo0NaXk9U7eWOsejb5qt8FTRtU65vsXunkfVDcb7Ukg@mail.gmail.com Whole thread Raw |
| In response to | Re: Recovery inconsistencies, standby much larger than primary (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: Recovery inconsistencies, standby much larger than primary
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-hackers |
On Sun, Feb 2, 2014 at 6:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Greg Stark <stark@mit.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
pgsql-hackers by date: