Thread: canceling statement due to conflict with recovery after pg_basebackup
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 ?
Query should return table and other sizes in decreasing order.
How to improve it so that this error does not occur.
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@sba ERROR: canceling statement due to conflict with recovery
2020-06-03 09:54:23 EEST 85.253.131.166 user@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@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
Andrus.
Re: canceling statement due to conflict with recovery afterpg_basebackup
From
Kyotaro Horiguchi
Date:
At Wed, 3 Jun 2020 10:07:14 +0300, "Andrus" <kobruleht2@hot.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@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@sba ERROR: canceling > statement due to conflict with recovery > 2020-06-03 09:54:23 EEST 85.253.131.166 user@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@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