Re: Recovery inconsistencies, standby much larger than primary

On Thu, Feb 6, 2014 at 11:41 PM, Greg Stark <stark(at)mit(dot)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