Best practices
Upgrading the PostgreSQL Peer Of An Ongoing CDC Mirror
This is a guide on how you can upgrade your PostgreSQL instance that is part of an ongoing CDC mirror.
For PeerDB Cloud users: Note that one of the steps below requires the intervention of PeerDB support.
Steps
- Create a dummy table, and add the table to all mirrors
CREATE TABLE _peerdb_heartbeat (
id SERIAL PRIMARY KEY,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
- Make sure all writes to the database stop. In other words, put the application in maintenance/downtime.
- Add a row to the dummy table using :
INSERT INTO _peerdb_heartbeat DEFAULT VALUES;
- Wait for PeerDB to catch up. You can check the syncs of the mirrors in PeerDB UI in the Overview tab.
- Pause all mirrors.
- Upgrade your PostgreSQL instance.
- After the upgrade is complete, create new logical replication slots for the mirrors.
It is very important that the name of these slots are the same as the previous slots which the mirrors were using.
If PeerDB created the replication slot of a mirror, then the slot name will be
peerflow_slot_<mirror_name>
. If you provided the replication slot name, then you should use that name.
-- If PeerDB created the replication slot:
SELECT pg_create_logical_replication_slot('peerflow_slot_<mirror_name>', 'pgoutput');
-- If you provided the replication slot name:
SELECT pg_create_logical_replication_slot('<replication_slot_name>', 'pgoutput');
- If you are using PeerDB OSS:
Set the
last_offset
field of the mirrors to 0 in themetadata_last_sync_state
table in thecatalog
Postgres container. You canpsql
into that container and run :
UPDATE metadata_last_sync_state SET last_offset = 0 ;
If you are using PeerDB Cloud: Contact PeerDB Support on PeerDB Slack.
- Resume all mirrors.
- Remove application from maintenance.
- Check if the mirrors are syncing correctly.