One of the most powerful features in the postgres is Hot Standby. Although the terms standby and hot standby are often used interchangeably, they represent different concepts. A standby server is a postgres instance that continuously receives and replays WAL (Write-Ahead Log) records from the primary server. It is commonly used for high availability, disaster recovery, and offloading read workloads from the primary database. A hot standby, on the other hand, is a standby server that also allows read-only queries while recovery is in progress.
PostgreSQL provides several configuration parameters that control the behaviour of standby and hot standby servers. You can easily view the current values of these parameters using the SHOW command or retrieve detailed metadata, including descriptions, default values, contexts, and categories from the pg_settings system catalog.
In this article, we'll explore each hot standby-related parameter in detail, understand its purpose, learn how it affects replication behaviour, and see practical examples of when and how to use it.
Main parameters related to hot_standby:
- hot_standby
- hot_standby_feedback
- In_hot_standby
- max_standby_archive_delay
- max_standby_streaming_delay
1.hot_standby;
Checking the current value:
show hot_standby;
Result:
hot_standby ------------- on(1 row)
Check this parameter’s metadata from the pg_settings catalogue like this.
select * from pg_settings where name = 'hot_standby';
Result:
-[ RECORD 1 ]---+------------------------------------------------
name | hot_standby
setting | on
unit |
category | Replication / Standby Servers
short_desc | Allows connections and queries during recovery.
extra_desc |
context | postmaster
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | on
reset_val | on
sourcefile |
sourceline |
pending_restart | f
Check this parameter’s definition in the guc_parameters.dat file from postgres 19 source code.
{ name => 'hot_standby', type => 'bool', context => 'PGC_POSTMASTER', group => 'REPLICATION_STANDBY',
short_desc => 'Allows connections and queries during recovery.',
variable => 'EnableHotStandby',
boot_val => 'true',
},Practical examples:
If we have
Primary server – runs on port 5440
Standby server – runs on port 5441
Suppose we set the hot_standby parameter to off on the standby server, and then we try to connect to the standby server; we get an error like this.
Here, we are using postgres by compiling its source code. So, enter the psql login for the standby like this.
cybrosys@cybrosys:~/postgres/postgres$ bin/psql postgres -p 5441 -h /tmp
Result:
psql: error: connection to server on socket "/tmp/.s.PGSQL.5441" failed: FATAL: the database system is not accepting connections
DETAIL: Hot standby mode is disabled.
Now, if you enabled the hot_standby parameter in postgres.conf and after the restart of postgresql, you can get into the psql terminal of standby and we can execute the select-only queries like this.
cybrosys@cybrosys:~/postgres/postgres$ bin/psql postgres -p 5441 -h /tmp
Result:
psql (19rc1)
Type "help" for help.
Now, check whether the standby is in recovery mode or not.
select pg_is_in_recovery();
Result:
pg_is_in_recovery
-------------------
t
(1 row)
Execute a simple select query like this.
select * from emp ;
Result:
id | name
----+---------
1 | Alice
2 | Bob
3 | Charlie
(3 rows)
Now, execute any update or delete command and check the result.
delete from emp ;
Result:
ERROR: cannot execute DELETE in a read-only transaction
2. hot_standby_feedback
Check the value of the second parameter named hot_standby_feedback.
show hot_standby_feedback;
Result:
hot_standby_feedback
----------------------
off
(1 row)
Check this parameter’s metadata from the pg_settings catalogue like this.
select * from pg_settings where name = 'hot_standby_feedback';
Result:
-[ RECORD 1 ]---+-----------------------------------------------------------------------------------
name | hot_standby_feedback
setting | off
unit |
category | Replication / Standby Servers
short_desc | Allows feedback from a hot standby to the primary that will avoid query conflicts.
extra_desc |
context | sighup
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
Check this parameter’s definition in the guc_parameters.dat file from the postgres 19 source code.
{ name => 'hot_standby_feedback', type => 'bool', context => 'PGC_SIGHUP', group => 'REPLICATION_STANDBY',
short_desc => 'Allows feedback from a hot standby to the primary that will avoid query conflicts.',
variable => 'hot_standby_feedback',
boot_val => 'false',
},
Practical examples:
Execute this on the standby postgres server.
show hot_standby_feedback ;
Result:
hot_standby_feedback
----------------------
on
(1 row)
Now, disable this by using the ALTER command.
ALTER SYSTEM SET hot_standby_feedback = off;
Reload the configuration.
SELECT pg_reload_conf();
Result:
pg_reload_conf
----------------
t
(1 row)
Create a sample and insert some sample values in the primary server.
CREATE TABLE t AS SELECT g id, 'x' val FROM generate_series(1,100000) g;
Now, open a transaction in standby and don't commit.
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM t;
Don't commit this transaction.
Execute an update and vacuum operation in standby.
UPDATE t SET val = 'y';
VACUUM t;
Now, in standby, open transaction and execute the same count query again like this.
postgres=*# SELECT count(*) FROM t;
Result:
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.
3. in_hot_standby
Checking current value.
show in_hot_standby;
Result:
in_hot_standby
----------------
off
(1 row)
Check this parameter’s metadata from the pg_settings catalogue like this.
select * from pg_settings where name = 'in_hot_standby';
Result:
-[ RECORD 1 ]---+-----------------------------------------------
name | in_hot_standby
setting | off
unit |
category | Preset Options
short_desc | Shows whether hot standby is currently active.
extra_desc |
context | internal
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
Check this parameter’s definition in the guc_parameters.dat file from the postgres 19 source code.
{ name => 'in_hot_standby', type => 'bool', context => 'PGC_INTERNAL', group => 'PRESET_OPTIONS',
short_desc => 'Shows whether hot standby is currently active.',
flags => 'GUC_REPORT | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE',
variable => 'in_hot_standby_guc',
boot_val => 'false',
show_hook => 'show_in_hot_standby',
},Practical examples:
Check the value of this parameter on the primary server.
show in_hot_standby ;
Result:
in_hot_standby
----------------
off
(1 row)
Check this on the standby server.
show in_hot_standby ;
Result:
in_hot_standby
----------------
on
(1 row)
This is because the parameter named in_hot_standby is a read-only parameter, and it represents whether the current postgres server is in hot standby mode or not.
4.max_standby_archive_delay
Check the current value of this parameter named max_standby_archive_delay like this.
show max_standby_archive_delay;
Result:
max_standby_archive_delay
---------------------------
30s
(1 row)
Check this parameter’s metadata from the pg_settings catalogue like this.
select * from pg_settings where name = 'max_standby_archive_delay';
Result:
-[ RECORD 1 ]---+-----------------------------------------------------------------------------------------------------------
name | max_standby_archive_delay
setting | 30000
unit | ms
category | Replication / Standby Servers
short_desc | Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data.
extra_desc | -1 means wait forever.
context | sighup
vartype | integer
source | default
min_val | -1
max_val | 2147483647
enumvals |
boot_val | 30000
reset_val | 30000
sourcefile |
sourceline |
pending_restart | f
Check this parameter’s definition in the guc_parameters.dat file from the postgres 19 source code.
{ name => 'max_standby_archive_delay', type => 'int', context => 'PGC_SIGHUP', group => 'REPLICATION_STANDBY',
short_desc => 'Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data.',
long_desc => '-1 means wait forever.',
flags => 'GUC_UNIT_MS',
variable => 'max_standby_archive_delay',
boot_val => '30 * 1000',
min => '-1',
max => 'INT_MAX',
},Purpose:
This parameter determines how long Postgres should wait before cancelling conflicting queries running on a hot standby server while the standby is replaying WAL files received from the primary server to keep both servers synchronized.
5.max_standby_streaming_delay
Check the current value of this parameter named max_standby_streaming_delay like this.
show max_standby_streaming_delay;
Result:
max_standby_streaming_delay
-----------------------------
30s
(1 row)
Check this parameter’s metadata from the pg_settings catalogue like this.
select * from pg_settings where name = 'max_standby_streaming_delay';
Result:
-[ RECORD 1 ]---+-----------------------------------------------------------------------------------------------------------
name | max_standby_streaming_delay
setting | 30000
unit | ms
category | Replication / Standby Servers
short_desc | Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data.
extra_desc | -1 means wait forever.
context | sighup
vartype | integer
source | default
min_val | -1
max_val | 2147483647
enumvals |
boot_val | 30000
reset_val | 30000
sourcefile |
sourceline |
pending_restart | f
{ name => 'max_standby_streaming_delay', type => 'int', context => 'PGC_SIGHUP', group => 'REPLICATION_STANDBY',
short_desc => 'Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data.',
long_desc => '-1 means wait forever.',
flags => 'GUC_UNIT_MS',
variable => 'max_standby_streaming_delay',
boot_val => '30 * 1000',
min => '-1',
max => 'INT_MAX',
},Purpose:
Hot standby plays an important role in postgres replication by allowing read-only access on standby servers while they continue applying changes from the primary server. However, the overall behaviour of a standby server depends on several configuration parameters, each serving a different purpose.
Understanding parameters such as hot_standby, hot_standby_feedback, in_hot_standby, max_standby_archive_delay, and max_standby_streaming_delay helps database administrators balance query availability, replication lag, and conflict handling according to their requirements.
By experimenting with these parameters and observing their behaviour in real environments, you can better understand how postgres manages standby servers and choose the most appropriate configuration for your workload. Proper tuning of these settings can improve the reliability and efficiency of replication while ensuring that standby servers behave as expected in different scenarios.