How Can I Ensure Data Integrity When Upgrading or Migrating My PostgreSQL Database?
Question
I plan to upgrade my PostgreSQL database to a more recent version, or migrate it. What steps should I take to ensure data integrity?
Environment
- Connector: PostgreSQL
- Incremental update method](/docs/connectors/databases/postgresql#updatingdata): Logical Replication
Answer
Upgrading or migrating your PostgreSQL database will destroy the database replication slot. To avoid data integrity issues, do the following:
- Pause the connection.
- Drop the replication slot.
- Complete the upgrade or migration.
- Re-create the replication slot.
- Perform a historical connection re-sync.
- Resume the connection.