query cancellations in postgresql standby server
After configuring streaming replication in PostgreSQL. We can set “hot_standby” parameter in postgresql.conf in standby server to true, so that we can execute read only queries on standby server.
There are mainly two situations where the long running queries on standby server get cancelled due to conflicting locks held in primary.
Due to MVCC implementation in PostgreSQL, if the standby server is executing a query to see the data, the data was already deleted in primary and executed vacuum to clean the space. When the WAL file data which contain this vacuum information reached to standby, it will cancel the current running query in the standby server.
On primary:
Begin;
Delete from test_123 where id between 20 and 30;
Commit;
On standby:
postgres=# begin;
BEGIN
postgres=# set transaction isolation level REPEATABLE READ;
SET
postgres=# select * from test_123 where id between 20 and 30;
FATAL: terminating connection due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
Here the query in standby trying to see the old data version or data removed by vacuum on primary.
To overcome this type of errors we can set “hot_standby_feedback” parameter but this will stop vacuum process from cleaning the old data in primary and ultimately bloat the tables.
Access Exclusive locks taken on the primary server, including both explicit
LOCK commands and various DDL actions, conflict with table accesses in standby queries.Primary:
Begin;
lock TABLE test_123 in access exclusive mode ;
standby:
begin;
==== wait for a minute
postgres=# select * from test_123 where id between 20 and 30;
FATAL: terminating connection due to conflict with recovery
DETAIL: User was holding a relation lock for too long.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
To overcome this we need to set the max_standby_archive_delay,max_standby_streaming_delay parameters based on how much time the query takes. But this parameters stop applying the WAL data and the standby will be far behind primary in terms of data.
Viswamitra's blog 