# Database migration

## When to use each method

This document describes two methods to migrate data from RDS to Aurora Serverless. Both require a maintenance window with expected downtime.

- **Method 1 (RDS snapshot):** Use when migrating from AWS RDS to Aurora PostgreSQL (non-Serverless)
- **Method 2 (pg_dump/pg_restore):** Use when migrating from any database to Aurora Serverless or Aurora PostgreSQL

Check each method for limitations and special considerations.

## Prerequisites

You must be [logged in to your AWS account](../aws-account/access-to-aws.md) with a role that has administrator rights.

## Migrate using RDS snapshot

By using a snapshot of the RDS instance, you can create a new Aurora cluster based on the data that exists
in the database at the moment the snapshot was taken.

Any data written to the database after the snapshot was taken is not migrated. Consider
turning off any services that might write to the database during the migration process.

### Limitations

Major version upgrades are not supported, for example PostgreSQL 9.6 to 10.7.

### Step-by-step using snapshot

1. Get the RDS instance identifier and engine version of the database you want to migrate data from:

   ```bash
   aws rds describe-db-instances --query 'DBInstances[*].DBInstanceIdentifier'
   ```

1. Get the engine version of the database you want to migrate data from:

   ```bash
   # Replace <db-instance-identifier> with the RDS instance identifier from step 1
   aws rds describe-db-instances --db-instance-identifier <db-instance-identifier> --query 'DBInstances[*].EngineVersion'
   ```

1. Create a snapshot of the RDS instance:

   ```bash
   # Replace <rds-instance-id> with the RDS instance identifier from step 1
   # Replace <snapshot-name> with the name of the snapshot
   aws rds create-db-snapshot --db-instance-identifier <rds-instance-id> --db-snapshot-identifier <snapshot-name>
   ```

1. Wait for the snapshot to be created:

   You can check the progress in [RDS console](https://console.aws.amazon.com/rds/home){:target="_blank"} or by waiting
   for the `aws-cli` command below to complete.

   ```bash
   # Replace <snapshot-name> with the name of the snapshot used in step 3.
   aws rds wait db-snapshot-completed --db-snapshot-identifier <snapshot-name>
   # This command will return when the snapshot is created, but you won't see anything in the process
   ```

1. Get the ARN of the snapshot for use in the next step:

   ```bash
   # Replace <snapshot-name> with the name of the snapshot used in step 3
   aws rds describe-db-snapshots --db-snapshot-identifier <snapshot-name> --query 'DBSnapshots[*].DBSnapshotArn'
   ```

1. Update the Terraform file which represents the new database

   This file is typically in the `infra/` directory in your IaC repository and is normally one of either `postgres-aurora` or `postgres-aurora-severless`.

   ```hcl
      module "args_rds" {
         source = "git@github.com:oslokommune/golden-path-iac//terraform/modules/args-rds-aurora?ref=args-rds-aurora-v0.1.0"
      }

      locals {
         args_rds = module.args_rds.data.serverless.small
      }

      module "rds_aurora_serverless" {
         source  = "terraform-aws-modules/rds-aurora/aws"

         name        = local.db_name
         engine      = local.args_rds.engine
         engine_mode = local.args_rds.engine_mode
         # Replace <engine-version> with the engine version from step 2, or upgrade to another valid version
         engine_version = "<engine-version>"
         ..

         # Replace <snapshot-arn> with the ARN of the snapshot from step 5
         snapshot_identifier = "<snapshot-arn>"

         tags = local.common_tags
      }

   ```

1. The migration starts when the Terraform plan is applied. The migration takes some time
   depending on the size of the database. You can check the progress of the migration in the
   [AWS RDS console](https://console.aws.amazon.com/rds/home){:target="_blank"} and look at the `Status` column.

1. You can now update endpoints and other services to point to the new database.

1. Once the migration is done you can remove the snapshot identifier from the Terraform file and
   apply again. This removes the snapshot identifier from the Terraform state.

1. Verify that the migration was successful and remove the old database.

## Migrate using `pg_dump` and `pg_restore`

By using `pg_dump` and `pg_restore` you can create a dump of the database and then restore it to the
new database. As with the snapshot method, any data written to the database after the dump was
taken is not migrated. Consider turning off any services that might write to the database
during the migration process.

### Step-by-step using `pg_dump` and `pg_restore`

You need to have access to the database you want to migrate data from. This can be done by
locally connecting to the database using a bastion host and then dumping the database to your
machine, or by running the commands from within a EC2 instance that has access to the database and
a storage medium where the dump can be stored to and subsequently restored from to new the new
database.

<!-- prettier-ignore-start -->
!!! info "EC2"
    Team Kjøremiljo recommends dumping and restoring from an EC2 instance, which is faster, secure and more reliable than doing it from a local machine.
<!-- prettier-ignore-end -->

#### Dump the database

1. Have a shell open to the EC2 instance or bastion host that has access to the database you want
   to migrate data from.

1. Create a dump of the database:

   ```bash
   # Replace <hostname> with the hostname of the database you want to migrate data from
   # Replace <username> with the master username
   # Replace <database> with the name of the database
   # Replace <output-file> with the name of the dump file
   pg_dump --host <hostname> --format=directory --create --jobs 5 --dbname <database> --username <username> --file <output-file>.dump
   ```

1. Copy the dump file to a storage medium that can be accessed from the new database. This can be
   done by copying the file to an S3 bucket or to a shared drive.

#### Restore the database

1. Create a new database using the Terraform file from the first method. You can use the same
   Terraform file as the one used in the first method, but you need to remove the snapshot identifier
   from the file.

1. Have a shell open to the EC2 instance or bastion host that has access to the database you want
   to migrate data to.

1. Restore the dump file to the new database:

   ```bash
   # Replace <hostname> with the hostname of the new database
   # Replace <username> with the master username
   # Replace <database> with the name of the database
   # Replace <input-file> with the name of the dump file
   pg_restore --host <hostname> --jobs 5 --dbname <database> --username <username> --file <input-file>.dump
   ```

1. You can now update endpoints and other services to point to the new database.
1. Verify that the data has been migrated correctly and delete the dump file from the storage medium.

## See also

[Best practices for migrating PostgreSQL databases to Amazon RDS and Amazon Aurora](https://aws.amazon.com/blogs/database/best-practices-for-migrating-postgresql-databases-to-amazon-rds-and-amazon-aurora){:target="_blank"}