It turned out that the problem is not caused by dead rows removing.
The problem is that autovacuum process truncates empty pages at the
end of the relation and takes AccessExclusiveLock on the relation. WAL
receiver process, in turn, tries to take this log while replaying WAL
segment with corresponding standby_redo command and fails because of
the long-running query holding AccessShareLock on standby. Since
max_standby_streaming_delay setting is set to -1 WAL receiver waits
until query is finished and as a result the replication lag is
increasing.
There is a patch proposed in 2018/11 commit fest but it is not even
reviewed yet: https://commitfest.postgresql.org/20/1683/
On Tue, Nov 13, 2018 at 10:53 PM Rui DeSousa <rui@crazybean.net> wrote:
>
>
>
> > On Nov 13, 2018, at 4:18 AM, Andrey Zhidenkov <andrey.zhidenkov@gmail.com> wrote:
> >
> > Yes, they are streaming replicas. Could you please tell me how to
> > check xmin of the replica in pg_stat_activity? I didn't get the point.
> >
>
> select pid
> , usename
> , application_name
> , backend_start
> , backend_xmin
> , state
> from pg_stat_replication
> ;
>
> select pid
> , state
> , backend_xid
> , backend_xmin
> , backend_type
> , backend_start
> from pg_stat_activity
> where backend_xmin is not null
> ;
>
> Vacuum will use the xmin to determine if a record is still needed or not thus I just wanted to make sure the replica
aresending that information to the master.
>
> What is the exact error message?
>
--
-
With best regards, Andrey Zhidenkov