Database migrations

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

An AWS blue/green deployment copies your database to a new green environment and then switches your application to it.

Amazon RDS and Aurora PostgreSQL

Use these steps when you upgrade an Amazon RDS or Aurora PostgreSQL database with an AWS blue/green deployment. The steps follow the limits that AWS documents:

• AWS does not create a blue/green deployment when non-physical replication slots exist. Creation fails with the error Replica creation is canceled due to external replication. See the AWS troubleshooting article.
• The blue database cannot be a logical replication publisher. See the AWS blue/green deployment considerations.

Do these steps in this order:

1. Stop application writes.
2. Let Artie read all changes.
3. Pause the pipeline.
4. Drop the Artie replication slot.
5. Create the blue/green deployment.
6. Switch over to green.
7. Point the network path to the green writer.
8. Resume the pipeline.
9. Make sure that a test row gets to the destination.
10. Start application writes again.

1. Stop application writes

   Stop all application writes to blue. Let open transactions finish. Keep writes stopped until the last step.

2. Let Artie read all changes

   Keep the pipeline running. Do the read check in step 2 of the PostgreSQL in-place upgrade.

3. Pause the pipeline

   Pause the pipeline. Do the pause check in step 3 of the PostgreSQL in-place upgrade. The slot must show active as false.

4. Drop the Artie replication slot

   Drop the slot. Then make sure that no other non-physical slots remain on blue.

   SQLCopyCopied

   SELECT pg_drop_replication_slot('artie');
   
   SELECT slot_name, plugin, active
   FROM pg_replication_slots
   WHERE slot_type <> 'physical';

   The second query must return no rows. If slots remain, other consumers use them. Decide what to do with them before you create the deployment.

5. Create the blue/green deployment

   Create the deployment with the target engine version. Make sure that rds.logical_replication is 1 in the green parameter group.

   Do not run DDL, GRANT or REVOKE on blue during the deployment. AWS blocks the switchover after these changes.

6. Switch over to green

   Do the switchover. Green takes the original endpoints. Connect to the writer endpoint and record the writer IP for the next step.

   SQLCopyCopied

   SELECT current_setting('server_version') AS postgres_version, inet_server_addr() AS writer_ip;

7. Point the network path to the green writer

   Make sure that the network path from Artie gets to the green writer before you resume Artie. If Artie resumes before this, it connects to the old blue database.

   For PrivateLink with an NLB IP target, a Lambda function keeps the target IP current. Run the Lambda, or wait for its next run. Then check the target group.

   BashCopyCopied

   aws elbv2 describe-target-health \
     --target-group-arn arn:aws:elasticloadbalancing:us-east-1:123456789012:targetgroup/artie-postgres/0a1b2c3d4e5f6a7b \
     --query 'TargetHealthDescriptions[].[Target.Id,TargetHealth.State]'

   The only target must be the writer IP from the last step, with the state healthy.

8. Resume the pipeline

   Resume the pipeline. Artie finds no slot, so it resets its saved position and creates a new slot on green. Do the resume check in step 6 of the PostgreSQL in-place upgrade.

9. Make sure that a test row gets to the destination

   Insert a test row on green and find it in the destination. Use the test row check in step 7 of the PostgreSQL in-place upgrade.

10. Start application writes again

    Start application writes again when the test row is in the destination. Monitor the pipeline lag in the analytics portal.

In-place major version upgrade

PostgreSQL in-place upgrade

To upgrade with an AWS blue/green deployment, use the Amazon RDS and Aurora PostgreSQL steps.

Do these steps in this order:

1. Stop application writes.
2. Let Artie read all changes.
3. Pause the pipeline.
4. Drop the Artie replication slot.
5. Upgrade the database.
6. Resume the pipeline.
7. Make sure that a test row gets to the destination.
8. Start application writes again.

AWS keeps logical replication slots during a major version upgrade only when the source is PostgreSQL 17 or later. These steps drop the slot on all versions. When you resume, Artie finds that its slot is missing or that the major version changed. Artie then resets its saved position and starts on a new slot.

1. Stop application writes

   Stop all application writes to the source database. Let open transactions finish. Keep writes stopped until the last step.

2. Let Artie read all changes

   Keep the pipeline running. Wait until the analytics portal shows that Artie has caught up. Then do the read check on the source.

   SQLCopyCopied

   SELECT
       slot_name,
       active,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS behind_bytes
   FROM pg_replication_slots
   WHERE slot_name = 'artie';

   Run the query again after one minute. behind_bytes must be small, and it must not increase.

3. Pause the pipeline

   In the analytics portal, click Pause pipeline and choose the Pause pipeline option. This option also stops Artie from reading the source. Then do the pause check.

   SQLCopyCopied

   SELECT slot_name, active, active_pid
   FROM pg_replication_slots
   WHERE slot_name = 'artie';

   The slot must show active as false.

4. Drop the Artie replication slot

   The default slot name is artie. If you set an override, use that name.

   SQLCopyCopied

   SELECT pg_drop_replication_slot('artie');

5. Upgrade the database

   Upgrade the database to the new major version and restart it. Check the PostgreSQL considerations before you resume.

6. Resume the pipeline

   Resume the pipeline in the analytics portal. Artie creates a new slot. Then do the resume check.

   SQLCopyCopied

   SELECT
       current_setting('server_version') AS postgres_version,
       slot_name,
       active,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS behind_bytes
   FROM pg_replication_slots
   WHERE slot_name = 'artie';

   postgres_version must show the new version. active must be true. behind_bytes must be zero or more. A negative value means that Artie uses an old position. If you see a negative value, keep writes stopped and contact Artie.

7. Make sure that a test row gets to the destination

   The slot checks can pass when no data gets to the destination. Do the test row check to see that data moves from the source to the destination. Insert a row with a unique ID into a small table that the pipeline replicates.

   SQLCopyCopied

   -- Source
   INSERT INTO public.upgrade_checks (check_id, note)
   VALUES ('pg-upgrade-2026-11-07', 'post-upgrade check');

   Then find the row in the destination.

   SQLCopyCopied

   -- Destination
   SELECT check_id, note
   FROM analytics.upgrade_checks
   WHERE check_id = 'pg-upgrade-2026-11-07';

   The query must return the row with the same values.

8. Start application writes again

   Start application writes again when the test row is in the destination. Monitor the pipeline lag in the analytics portal.

Other databases

1. Schedule an application downtime and pause your application.
2. In the analytics portal, check that Artie has caught up. Then click Pause pipeline and choose the Pause pipeline option, which also stops Artie from reading the source.
3. Perform the database upgrade and restart your database server.
4. Resume your Artie pipeline.
5. Resume your application.

Database-specific considerations

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.

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

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;

SELECT pg_drop_replication_slot('artie');