Re: canceling statement due to conflict with recovery after pg_basebackup

At Wed, 3 Jun 2020 10:07:14 +0300, "Andrus" <kobruleht2(at)hot(dot)ee> wrote in
> Hi!

Hi.

> Async binary replication hot standby was started after pg_basebackup.
> Running query in slave throws error
>
> ERROR: canceling statement due to conflict with recovery
>
> Why ?

As written in the messages.
> 2020-06-03 09:54:23 EEST 85.253.131.166 user(at)sba DETAIL: User query
> might have needed to see row versions that must be removed.

Despite the master had removed some rows by vacuum and that must be
reflected to your standby, the standby cannot do that because of the
transaction that has started before the rows are vacuumed. More
technically, the transaction on the standby was using a snapshot with
older transaction ids than the vacuum cutoff transaction id on the
master. So the standby needed to cancel the the statement or session
in order to continue replication.

> Query should return table and other sizes in decreasing order.
> How to improve it so that this error does not occur.

Hot-standby-feedback would work.

https://www.postgresql.org/docs/12/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK

> Log:
>
> 2020-06-03 09:40:52 EEST LOG: database system was interrupted; last
> known up at 2020-06-03 07:59:56 EEST
> 2020-06-03 09:41:10 EEST LOG: entering standby mode
> 2020-06-03 09:41:10 EEST LOG: redo starts at 2E2/28
> 2020-06-03 09:41:19 EEST LOG: consistent recovery state reached at
> 2E2/B5A56C8
> 2020-06-03 09:41:19 EEST LOG: database system is ready to accept read
> only connections
> 2020-06-03 09:41:19 EEST LOG: started streaming WAL from primary at
> 2E2/C000000 on timeline 1
> 2020-06-03 09:54:23 EEST 85.253.131.166 user(at)sba ERROR: canceling
> statement due to conflict with recovery
> 2020-06-03 09:54:23 EEST 85.253.131.166 user(at)sba DETAIL: User query
> might have needed to see row versions that must be removed.
> 2020-06-03 09:54:23 EEST 85.253.131.166 user(at)sba STATEMENT: select
> company_name(n.nspname)::char(20) as company,
> relname::char(25),
> pg_size_pretty(pg_total_relation_size(c.oid))::char(10) as totalsize,
> n.nspname::char(12),
> case
> when c.relkind='i' then 'index'
> when c.relkind='t' then 'toast'
> when c.relkind='r' then 'table'
> when c.relkind='v' then 'view'
> when c.relkind='c' then 'composite type'
> when c.relkind='S' then 'sequence'
> else c.relkind::text
> end ::char(14) as "type"
> from
> pg_class c
> left join pg_namespace n on n.oid = c.relnamespace
> left join pg_tablespace t on t.oid = c.reltablespace
> where
> (pg_total_relation_size(c.oid)>>21)>0 and c.relkind!='t'
> order by
> pg_total_relation_size(c.oid) desc

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center