PostgreSQL

PostgreSQL 16 introduces the ability to use logical replication on a standby server.

Steps to enable this:

1. Create dbz_publication publication on the primary database

CREATE PUBLICATION dbz_publication FOR ALL TABLES;

2. Enable hot_standby_feedback on the standby server

postgres> SHOW hot_standby_feedback;
 hot_standby_feedback
----------------------
 on

3. WAL_LEVEL is set to logical on the standby server

postgres> SHOW WAL_LEVEL;
 wal_level
----------------------
 logical

Once you selected Enable heartbeats under Advanced settings, you will then need to run the following command:

CREATE TABLE test_heartbeat_table (id text PRIMARY KEY, ts timestamp);
-- Grant access to the heartbeat table
GRANT UPDATE ON TABLE test_heartbeat_table TO artie_transfer;
-- Then insert one row into this table.
-- Artie's periodic pings will be this:
-- UPDATE test_heartbeat_table set ts = now() WHERE id = '1';
-- Such that we never end up adding additional rows.
INSERT INTO test_heartbeat_table (id, ts) VALUES (1, NOW());

By default, Artie will create a publication that includes all table changes. You can override this behavior by selecting Filtered under the Deployment advanced settings.

Changing the behavior of Postgres publications

We have regular monitors that provide additional guardrails around your database replication, and will do the following:

• Regularly check and monitor your replication slot size in 15-minute intervals and notify if the slot exceeds a certain threshold.
• Heartbeats verification. For folks that are leveraging heartbeats, we will alert you if a heartbeat query fails.
• Terminate any idle queries that are lingering for more than a day. You can avoid having this problem by setting idle_in_transaction_session_timeout.