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.
