Re: BUG #16760: Standby database missed records for at least 1 table

On Wed, 2 Dec 2020 at 17:44, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> Below output from Primary
> select audit_ev_id, when_created from audit_evs_all where audit_ev_id
> between 221535154 and 221535162;
> audit_ev_id | when_created
> -------------+-------------------------------
> 221535154 | 2020-12-01 00:00:20.955348+00
> 221535155 | 2020-12-01 00:00:20.955348+00
> 221535156 | 2020-12-01 00:00:20.955348+00
> 221535157 | 2020-12-01 00:00:20.955348+00
> 221535158 | 2020-12-01 00:00:20.955348+00
> 221535159 | 2020-12-01 00:00:20.955348+00
> 221535160 | 2020-12-01 00:00:20.955348+00
> 221535161 | 2020-12-01 00:00:20.955348+00
> 221535162 | 2020-12-01 00:00:20.955348+00
> (9 rows)
> ---------------------------------------------------------------
> Same query's output from 1st Secondary
> select audit_ev_id, when_created from audit_evs_all where audit_ev_id
> between 221535154 and 221535162;
> audit_ev_id | when_created
> -------------+-------------------------------
> 221535162 | 2020-12-01 00:00:20.955348+00
> (1 row)
>
> I would start this investigation, disabling the index usage for this query.

-- check which index has been used
explain (analyze) select audit_ev_id, when_created from audit_evs_all
where audit_ev_id
between 221535154 and 221535162;
-- disable index usage
begin;
set local enable_indexscan to 0;
set local enable_indexonlyscan to 0;
explain (analyze) select audit_ev_id, when_created from audit_evs_all
where audit_ev_id
between 221535154 and 221535162;
select audit_ev_id, when_created from audit_evs_all where audit_ev_id
between 221535154 and 221535162;
commit;

If the second EXPLAIN isn't using the index shown in the first EXPLAIN and
the
SELECT returns all rows, reindex the index that has been used. If the index
is
not the culprit, you should inspect the page(s) that contain those rows
using
pageinspect.

--
Euler Taveira http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services