Database Reference
In-Depth Information
Using
max_Standby
parameter settings of
-1
may not be useful for normal running because
this is a very timid setting. No user query will ever be canceled if it conflicts with applying
changes, causing the apply process to wait indefinitely. As a result, the apply delay can
increase significantly over time, depending upon the frequency and duration of queries, and
the frequency of conflicts. To work out an appropriate setting for these parameters, we need to
understand more about the other types of conflict, though there is also a simple way to avoid
this problem entirely.
Cleanup records and other special cases also conflicts. The special cases are rare and/or
obvious, for example, if you drop a database on the Master, then queries running on Standby
will be canceled.
Cleanup records remove old tuple versions from the database, as part of the internal workings
of MVCC. The easiest way to understand this is to review what happens on the Master node.
When a
VACUUM
runs, it will only remove row versions that can no longer be seen by any
current query. To do this, the
VACUUM
asks for and receives feedback about which queries are
running, and then ensures that it doesn't remove expired row versions too early. When running
in Hot Standby mode, the Master doesn't receive visibility feedback about what queries are
running on the Standby nodes. As a result, it is possible that the tasks running on the Master
node will remove row versions that may be required on the Standby nodes. When those
row versions are removed they generate
cleanup
records
in the transaction log that cause
conflicts with queries running on the Standby.
You can provide some protection against canceled queries by setting
vacuum_defer_
cleanup_age
to a value higher than 0. That parameter is fairly hard to set accurately, though
I would suggest starting with a value of 1,000 and tune upwards. A vague and inaccurate
assumption would be to say that each 1,000 will be approximately 1 second of additional
delay. A vague and inaccurate assumption is probably helpful more often than it is wrong,
though it will often be wrong.
The repmgr project provides a mechanism to provide more accurate visibility feedback, and is
specifically designed to help Hot Standby in PostgreSQL 9.0. repmgr will reduce cancelations
caused by cleanup record conflicts by providing dynamic and accurate visibility feedback from
the Standby node to the Master.
If you want to completely
freeze
a Standby database, so that no further changes are applied,
then you can do this by stopping the server, modifying
recovery.conf
so that neither
restore_command
and
primary_conninfo
are set, yet
Standby_mode
=
on
, then
restarting the server. You can come back out of this mode, though, only if the archive contains
the required WAL files to catch up, otherwise you will need to re-configure the Standby from a
base backup again.
If cancelations do occur, they will throw either an
error
or
fatal
level errors. These will be
marked with
SQLSTATE
40001
SERIALIZATION
FAILURE
. That could be trapped by an
application, and the SQL can be resubmitted.
