Postgresql Replication issues of clarity



  • 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 servers not hot_standby as 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 replica from a logical replication, 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



  • 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_level is set on the primary and determines the amount of information written to the WAL. The replica value causes more information to be written than minimal, and logical more information yet.

    In releases prior to 9.6, this parameter also allowed the values archive and hot_standby. These are still accepted but mapped to replica

    In contrast, hot_standby parameter (not the wal_level value) 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 = on allows you limited (read only) access.

    Logical replicas are always "hot" in the sense that the receiving databases are in the fully operational state.




Suggested Topics