How can I prevent data loss when migrating or upgrading my PostgreSQL database?

When you migrate or upgrade your PostgreSQL database, if Pipelines ingesting data from the database remain ACTIVE during the process, it may result in data loss. Additionally, for Pipelines created with Logical Replication ingestion mode, the existing replication slot is dropped, which can lead to incomplete or missing data. These issues can happen during any PostgreSQL version upgrade or when migrating the database to a new server or environment.

To prevent data loss during the upgrade process, perform the following steps:

1. Pause your Pipeline to temporarily stop data ingestion.

2. For log-based Pipelines, drop the existing replication slot in your PostgreSQL database.

   Skip this step if your Pipeline is created with Table or Custom SQL ingestion mode.

3. Complete the database migration or upgrade.

   Note: For log-based Pipelines, ensure that logical replication is enabled for your database. Refer to the required PostgreSQL variant for the steps to set up log-based incremental replication.

4. Resume your Pipeline to resume data ingestion.

   Note: For log-based Pipelines, Hevo automatically recreates the replication slot in the Source when the Pipeline is resumed.

5. Restart the historical load for the Source objects being replicated in the Pipeline to ensure that all data for the objects is ingested after the upgrade.

Revision History