canceling statement due to conflict with recovery after pg_basebackup

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Subject: canceling statement due to conflict with recovery after pg_basebackup
Date: 2020-06-03 07:07:14
Message-ID: 6B27EF9A9A8F4CD1BB9ECAA950BE3AF4@dell2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(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

Andrus.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oleksandr Shulgin 2020-06-03 07:38:49 Re: When to use PARTITION BY HASH?
Previous Message Adrian Klaver 2020-06-03 01:04:56 Re: LOG: could not send data to client: Broken pipe