Re: Recovery inconsistencies, standby much larger than primary - Mailing list pgsql-hackers

On Thu, Feb 6, 2014 at 11:41 PM, Greg Stark <stark@mit.edu> wrote:
>
> That doesn't explain the other instance or the other copies of this
> database. I think the most productive thing I can do is switch my
> attention to the other database to see if it really looks like the
> same problem.

So here's an instance in the other database, this one is on a
different continent from the first one so it's definitely a different
physical machine. I've had to copy the blocks over to another machine
because the database is down and still in standby mode anyways. I
don't have the xlog file yet.

Bad block's page header -- this is in the 56'th relation segment:

=# select
(page_header(E'\\x20050000583b05aa050000002800180500200420000000001098e00f2090e00f088d24061885e00f0000000000000000')).*;
  lsn      | tli | flags | lower | upper | special | pagesize |
 
version | prune_xid
--------------+-----+-------+-------+-------+---------+----------+---------+-----------520/AA053B58 |   5 |     0 |
40|  1304 |    8192 |     8192 | 4 |         0
 
(1 row)

=# select
(heap_page_items(E'\\x20050000583b05aa050000002800180500200420000000001098e00f2090e00f088d24061885e00f000....')).*;lp|
lp_off| lp_flags | lp_len | t_xmin  | t_xmax | t_field3 |
 
t_ctid    | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid

----+--------+----------+--------+---------+--------+----------+-------------+-------------+------------+--------+--------+-------
1|   6160 |        1 |   2032 | 7635393 |      0 |        0 |
 
(4773121,1) |           3 |       2306 |     24 |        | 2 |   4128 |        1 |   2032 | 7635393 |      0 |        0
|
(4773121,2) |           3 |       2306 |     24 |        | 3 |   3336 |        1 |    786 | 7635393 |      0 |        0
|
(4773121,3) |           3 |       2306 |     24 |        | 4 |   1304 |        1 |   2032 | 7635428 |      0 |        0
|
(4773121,4) |           3 |       2306 |     24 |        |
(4 rows)

Looking at the block at offset 4773121 (which is in the 36th segment):

=# select
(heap_page_items(E'\\x20050000a00a0bad050000002c00a00200200420000000001098e00f2090e00f088d24061885e00fa082ec040000000.....')).*;lp
|lp_off | lp_flags | lp_len | t_xmin  | t_xmax | t_field3 |
 
t_ctid    | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid

----+--------+----------+--------+---------+--------+----------+-------------+-------------+------------+--------+--------+-------
1|   6160 |        1 |   2032 | 7635393 |      0 |        0 |
 
(4773121,1) |           3 |       2306 |     24 |        | 2 |   4128 |        1 |   2032 | 7635393 |      0 |        0
|
(4773121,2) |           3 |       2306 |     24 |        | 3 |   3336 |        1 |    786 | 7635393 |      0 |        0
|
(4773121,3) |           3 |       2306 |     24 |        | 4 |   1304 |        1 |   2032 | 7635428 |      0 |        0
|
(4773121,4) |           3 |       2306 |     24 |        | 5 |    672 |        1 |    630 | 7635580 |      0 |        0
|
(4773121,5) |           3 |       2306 |     24 |        |
(5 rows)

d9de7pcqls4ib6=# select

(page_header(E'\\x20050000a00a0bad050000002c00a00200200420000000001098e00f2090e00f088d24061885e00fa082ec040000000000000000....')).*;
  lsn      | tli | flags | lower | upper | special | pagesize |
 
version | prune_xid
--------------+-----+-------+-------+-------+---------+----------+---------+-----------520/AD0B0AA0 |   5 |     0 |
44|   672 |    8192 |     8192 | 4 |         0
 
(1 row)



-- 
greg