Postgresql Replication issues of clarity
Analeea last edited by
In an effort to document myself I examined a few sources; https://www.postgresql.org/docs/12/runtime-config-replication.html two https://www.postgresql.org/docs/current/logical-replication-quick-setup.html and this https://linuxconfig.org/how-to-create-a-hot-standby-with-postgresql
Now the latter one seems to be updated in 2020, but refers to version 9.2 which is somewhat dated [angst emoji]. There are some discrepancies I want to clear up.
• my understanding is that a hot-standby is open for read-only SQL statements. However the defaults for postgresql.conf on version 12 are:
#wal_level = replica # minimal, replica, or logical
while the https://www.postgresql.org/docs/12/runtime-config-replication.html states
wal_level must be set to replica or higher to allow connections from standby serversnot
hot_standbyas is alluded to the external reference. So
hot_standbyis not longer a valid value? But to further the confusion, I have not found what distinguishes a
logicalreplication, just https://www.postgresql.org/docs/14/runtime-config-wal.html
• The https://www.postgresql.org/docs/12/logical-replication-quick-setup.html feels too 'quick' for my tastes. While it tracks pretty much the external source... what's missing from the 'quick' approach?
• I also have had difficulty in searching the docs for how the replica should be configured. How to determine whether as server is hot or warm?
note: I realise this is not a single question, but the issues are still part of the object of configuring a main db and a replica db
jeanid last edited by
I think you are mixing up things from logical and WAL (physical) replication, which adds to the confusion. Despite being both called "replication", these have quite different mechanisms and require different configuration. Depending on the kind of replication you want to set up, you should refer to the appropriate section of the documentation.
wal_levelis set on the primary and determines the amount of information written to the WAL. The
replicavalue causes more information to be written than
logicalmore information yet.
In releases prior to 9.6, this parameter also allowed the values
hot_standby. These are still accepted but mapped to replica
hot_standbyparameter (not the
wal_levelvalue) only affects the replica because it changes the replica behaviour. The distinction between "hot" and "warm" only applies to physical standby servers, as they are in a continuous recovery state, accepting and applying WAL records from the primary. A database would not normally be accessible in this state, but setting
hot_standby = onallows you limited (read only) access.
Logical replicas are always "hot" in the sense that the receiving databases are in the fully operational state.