Database Reference
In-Depth Information
F
While performing a point in time recovery
F
When using a permanently frozen Standby
For the first two replication mechanisms, you will need to configure replication as described in
earlier recipes. In addition, you will need to configure the following parameters:
On the Master set:
wal_level = 'hot_Standby' in postgresql.conf
On the Standby set:
hot_Standby = on
in postgresql.conf
Neither of those settings are the default, so you will need to make changes. You will need
to do a clean restart of the database server on both the Master and the Standby for these
changes to take effect.
You will need to allow a short delay between the restarts, as the new mode is not immediately
picked up on the Standby. The delay is usually the same duration as
checkpoint_timeout
,
though may in some cases be longer. If you restart the Standby too quickly, it will still be
reading older transaction log data, and it will fail to start and give a log message saying "you
need to enable Hot Standby", so please be patient. You only need to configure this once, not
every time you restart.
Optional parameters on the Standby node are set in
postgresql.conf
as follows:
F
max_Standby_archive_delay
F
max_Standby_streaming_delay
F
vacuum_defer_cleanup_age
F
trace_recovery_messages
A permanently frozen Standby can be created by specific settings in the
recovery.conf
ile.
Neither
restore_command
nor
primary_conninfo
should be set, while
Standby_mode
=
on
. In this mode, the server will start, but always remain at the exact state of the database as
it was when the
pg_stop_backup()
function completes.
Another point to note is that during the initial catch-up period, the replication delay will be
much higher than we would normally expect it to be. You are advised to set
hot_Standby
=
off
for the initial period immediately following creation of the Standby only. User connections
during that initial period may use system resources or cause conflicts that could extend the
catch-up delay. When the Standby is fully caught up with the primary, then we can set
hot_
Standby
=
on
, and restart.
