> ## Documentation Index
> Fetch the complete documentation index at: https://artie.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Database migrations

> Learn how to safely perform database migrations while maintaining data replication with Artie.

## Overview 🎯

Database migrations are a critical part of maintaining and upgrading your database infrastructure. This guide explains how to safely perform database migrations while ensuring your data replication continues to work correctly with Artie.

## Blue/green source database migration

Use this pattern when you are moving your application from a **blue** source database to a **green** replacement and cannot pause application writes. Artie reads from both databases during an overlap period, while both pipelines write to the same downstream tables. There is no downstream consumer switchover and no Artie backfill of the green source.

### Before you start

* Green must contain the same logical dataset and schema as blue at the start of the overlap.
* Green must be able to emit CDC changes before or as part of its promotion. For PostgreSQL, confirm the logical replication and replication-slot sequence for your hosting provider.
* Create a separate Artie source reader and pipeline for green. Configure it with the same destination, destination-table mapping, and primary keys as blue.
* Set `skip_backfill = true` for every green table. This causes Artie to stream new changes without reloading historical rows that blue already replicated.
* Use this only for tables that Artie merges/upserts by stable primary key. Do not use it for append-only or history-mode tables.
* Retain enough source log history to cover the full overlap and handoff window.

### Run the migration

<Steps>
  <Step title="Start the green pipeline with backfill disabled">
    Keep blue running. Deploy the green pipeline to the same destination tables with `skip_backfill = true` on every table.

    Artie begins streaming green changes immediately. While blue and green represent the same data, some logical changes can arrive from both sources. The shared destination table remains correct only when both pipelines use identical mappings and idempotent merge keys.
  </Step>

  <Step title="Verify the overlap">
    Confirm that green is receiving and applying CDC while blue is still live. Validate recent inserts, updates, and deletes in the downstream destination, and monitor replication lag for both pipelines.

    Use an observed handoff barrier, not a fixed overlap duration: green must be healthy and current before application writes move to it.
  </Step>

  <Step title="Move application writes from blue to green">
    Switch the application to green. Keep both Artie pipelines running while blue drains, so any final blue changes reach the destination and green continues to process new writes.
  </Step>

  <Step title="Validate green, then retire blue">
    After blue has stopped receiving writes and its pipeline has drained, verify that green remains current and that downstream tables contain the expected recent changes. Keep blue available for your rollback window, then remove the blue pipeline and source reader.
  </Step>
</Steps>

<Warning>
  This procedure is for a source-database cutover, not a destination migration. It requires matching data, schemas, and merge keys across blue and green. If green cannot emit CDC safely during its promotion sequence, or if the tables are append-only, history-mode, or lack stable primary keys, contact Artie before proceeding.
</Warning>

## In-place major version upgrade ⚠️

<Note>
  Major version upgrades (e.g., PostgreSQL 14 to 15) require stopping database replication to perform the necessary upgrade and restart your database. This is because major version upgrades often include breaking changes that can affect replication.
</Note>

**Steps to perform major version upgrades without data loss:**

<Info>
  If you are using Postgres, make sure to drop the replication slot after you paused the Artie pipeline. See the [Postgres tips](#postgres-tips) section for more information.
</Info>

1. Schedule an application downtime and pause your application.
2. Check that Artie is no longer processing any data from our Analytics portal and pause your Artie pipeline.
3. Perform the database upgrade and restart your database server.
4. Resume your Artie pipeline.
5. Resume your application.

### Database-specific considerations

<Accordion title="PostgreSQL">
  * Ensure `wal_level` is set to `logical` in your new version
  * Verify that all required extensions are available in the new version
  * Check that your replication slots are properly configured
</Accordion>

<Accordion title="MySQL">
  * Verify `binlog_format` is set to `ROW`
  * Ensure `binlog_retention_hours` is set appropriately (recommended 24+ hours)
  * Check that GTID is enabled if you're using it
</Accordion>

<Accordion title="MongoDB">
  * Ensure your replica set is properly configured in the new version
  * Verify that change streams are enabled
  * Check that your service account has the correct permissions
</Accordion>

## Minor version upgrade

Minor version upgrades (e.g., PostgreSQL 14.1 to 14.2) do not require stopping database replication. These upgrades are typically handled automatically by your database provider and applied during your maintenance window.

<Info>
  While minor version upgrades are generally safe, it's still recommended to:

  * Monitor your replication lag during the upgrade
  * Have a rollback plan ready
  * Test the upgrade in a staging environment first
</Info>

## Best practices ⭐

1. Always test migrations in a staging environment first
2. Back up your data before any major version upgrade
3. Monitor replication lag during and after the upgrade
4. Keep your Artie pipeline paused until the upgrade is complete
5. Verify data consistency after the upgrade

## Troubleshooting 🔧

<Accordion title="What happens when Artie is resumed?">
  For Postgres, Artie will find or create a replication slot (default name is `artie`, unless you specified an override) and we will start capturing changes moving forward.

  For other data sources, Artie will seek the earliest checkpoint from the database and start replicating from that point.
</Accordion>

<Accordion title="What should I do if there is data loss? 🚨">
  If you experience data loss, please reach out to us and we will kick off a backfill process. We recommend:

  1. Document the extent of data loss
  2. Note the approximate time window affected
  3. Contact our support team immediately
  4. Keep your pipeline paused until we can assist
</Accordion>

<Accordion title="Postgres tips">
  For Postgres, you can run this query to see all the replication slots on the server:

  ```sql theme={null}
  SELECT
      slot_name,
      wal_status,
      pg_size_pretty(
          pg_wal_lsn_diff(
          pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
      active,
      restart_lsn FROM pg_replication_slots;
  ```

  Ensure that `active` is false and then drop the replication slot.

  ```sql theme={null}
  SELECT pg_drop_replication_slot('artie');
  ```

  <Warning>
    Dropping replication slots will delete any unprocessed WAL files. Make sure you have paused your Artie pipeline before dropping slots.
  </Warning>
</Accordion>

<Accordion title="Common issues">
  1. **Replication slot overflow**: If you see this error, you may need to increase your WAL retention period
  2. **Connection issues**: Verify your database credentials and network connectivity
  3. **Permission errors**: Ensure your service account has the correct permissions after the upgrade
  4. **Schema changes**: Check if any schema changes were made during the upgrade that might affect replication
</Accordion>