Postgres Pro Enterprise : Documentation: 13: H.2. pg_failover

H.2. pg_failover_slots
Prev	Up	Appendix H. Third-Party Modules and Extensions Shipped as Individual Packages	Home	Next

H.2. pg_failover_slots

H.2.1. Installation and Configuration
H.2.2. How to Check the Standby Is Ready
H.2.3. Prerequisite Settings
H.2.4. Configuration Options

pg_failover_slots is a Postgres Pro Enterprise extension designed for automatic creation and synchronization of logical replication slots on physical replicas.

Since logical replication slots are only maintained on the primary node, downstream subscribers don’t receive any new changes from a newly promoted primary until the slot is created. This is unsafe because the information that includes which data a subscriber has confirmed receiving and which log data still needs to be retained for the subscriber will have been lost, resulting in an unknown gap in data changes. The pg_failover_slots extension makes logical replication slots usable across a physical failover using the following features:

Copies any missing replication slots from the primary to the standby.
Removes any slots from the standby that aren’t found on the primary.
Periodically synchronizes the position of slots on the standby based on the primary.
Ensures that selected standbys receive data before any of the logical slot walsenders can send data to consumers.

H.2.1. Installation and Configuration

The pg_failover_slots extension is provided with Postgres Pro Enterprise as a separate pre-built package pg-failover-slots-ent-13 (for the detailed installation instructions, see Chapter 17).

Once you have Postgres Pro Enterprise installed, do the following:

Add the pg_failover_slots extension to shared_preload_libraries on both the primary instance and any standby that is used for high availability (failover or switchover) purposes:
```
shared_preload_libraries = 'pg_failover_slots'
```
Configure prerequisite settings as described in Section H.2.3.

H.2.2. How to Check the Standby Is Ready

The slots are not synchronized to the standby immediately because of consistency reasons. The standby can be too behind logical slots, or too ahead of logical slots on primary when the pg_failover_slots module is activated, so the module does verification and only synchronizes slots when it’s actually safe.

This, however, brings a need to verify that the slots are synchronized and that the standby is actually ready to be a failover target with consistent logical decoding for all slots. This only needs to be done initially. Once the slots are synchronized for the first time, they will always be consistent as long as the module is active in the cluster.

The check for whether slots are fully synchronized with primary is relatively simple. The slots just need to be present in the pg_replication_slots view on standby and have the active state set to false. The active state set to true means the slots is currently being initialized.

For example, consider the following psql session:

# SELECT slot_name, active FROM pg_replication_slots WHERE slot_type = 'logical';
slot_name    | active
-----------------+--------
regression_slot1 | f
regression_slot2 | f
regression_slot3 | t

This means that slots regression_slot1 and regression_slot2 are synchronized from primary to standby and regression_slot3 is still being synchronized. If failover happens at this stage, the regression_slot3 will be lost.

Now let’s wait a little and query again:

# SELECT slot_name, active FROM pg_replication_slots WHERE slot_type = 'logical';
slot_name    | active
-----------------+--------
regression_slot1 | f
regression_slot2 | f
regression_slot3 | f

Now all three slots are synchronized and the standby can be used for failover without losing logical decoding state for any of them.

H.2.3. Prerequisite Settings

The module throws hard errors if the following settings are not adjusted:

hot_standby_feedback should be on
primary_slot_name should be non-empty

These are necessary to connect to the primary so it can send the xmin and catalog_xmin separately over hot_standby_feedback.

H.2.4. Configuration Options

The module itself must be added to shared_preload_libraries on both the primary instance as well as any standby that is used for high availability (failover or switchover) purposes.

The behavior of pg_failover_slots is configurable using these configuration options (set in postgresql.conf).

pg_failover_slots.synchronize_slot_names (text)

This standby option allows setting which logical slots should be synchronized to this physical standby. It’s a comma-separated list of slot filters.

A slot filter is defined as key:value pair (separated by colon) where key can be one of:

name specifies to match exact slot name
name_like specifies to match slot name against SQL LIKE expression
plugin specifies to match slot plugin name against the value

The key can be omitted and will default to name in that case.

For example, 'my_slot_name,plugin:test_decoding' will synchronize the slot named my_slot_name and any slots that use the test_decoding plugin.

If this is set to an empty string, no slots will be synchronized to this physical standby.

The default value is 'name_like:%', which means all logical replication slots will be synchronized.

pg_failover_slots.drop_extra_slots (boolean)

This standby option controls what happens to extra slots on the standby that are not found on the primary using the pg_failover_slots.synchronize_slot_names filter. If it’s set to true (which is the default), they will be dropped, otherwise they will be kept.

pg_failover_slots.primary_dsn (string)

A standby option for specifying the connection string to use to connect to the primary when fetching slot information.

If empty (default), then the same connection string as primary_conninfo is used.

Note

The primary_conninfo parameter cannot be used if there is a password field in the connection string because it gets obfuscated by Postgres Pro and pg_failover_slots can’t actually see the password. In this case, pg_failover_slots.primary_dsn must be configured.

pg_failover_slots.standby_slot_names (text)

This option is typically used in failover configurations to ensure that the failover-candidate streaming physical replica(s) have received and flushed all changes before they ever become visible to any subscribers. That guarantees that a commit cannot vanish on failover to a standby for the consumer of a logical slot.

Replication slots which names are listed in the comma-separated pg_failover_slots.standby_slot_names list are treated specially by the walsender on the primary.

Logical replication walsenders will ensure that all local changes are sent and flushed to the replication slots in pg_failover_slots.standby_slot_names before the walsender sends those changes for the logical replication slots. Effectively, it provides a synchronous replication barrier between the named list of slots and all the consumers of logically decoded streams from the walsender.

Any replication slot may be listed in pg_failover_slots.standby_slot_names; both logical and physical slots work, but it’s generally used for physical slots.

Without this safeguard, two anomalies are possible where a commit can be received by a subscriber and then vanish from the provider on failover because the failover candidate hadn’t received it yet:

For 1+ subscribers, the subscriber may have applied the change but the new provider may execute new transactions that conflict with the received change, as it never happened as far as the provider is concerned;

and/or

For 2+ subscribers, at the time of failover, not all subscribers have applied the change. The subscribers now have inconsistent and irreconcilable states because the subscribers that didn’t receive the commit have no way to get it now.

Setting pg_failover_slots.standby_slot_names will (by design) cause subscribers to lag behind the provider if the provider’s failover-candidate replica(s) are not keeping up. Monitoring is thus essential.

pg_failover_slots.standby_slots_min_confirmed (integer)

Controls how many of the pg_failover_slots.standby_slot_names have to confirm before sending data through the logical replication slots. Setting -1 (the default) means to wait for all entries in pg_failover_slots.standby_slot_names.

pg_failover_slots.worker_nap_time (integer)

Time to sleep (in ms) between two synchronization attempts. Defaults to 60s.

pg_failover_slots.maintenance_db (text)

Database name to use when using primary_conninfo to connect to the primary server and fetch the replication slots list. Defaults to postgres.

Prev	Up	Next
H.1. oracle_fdw	Home	Appendix I. Additional Supplied Programs

epub pdf