Migrating from RDS Postgres to Shared Postgres

Since the Shuttle AWS RDS resource is only available as a paid add-on, if you cancel your subscription you will lose access. Below are the steps you can take to migrate from the AWS RDS PostgreSQL resource to the Shuttle shared database resource.

1. Without removing the Shuttle AWS RDS pool from your application, follow the steps in Shuttle shared DB to provision add a shared Postgrse database to your project. You will now have two databases in your main function. You can ignore the new shared database pool for now.

#[shuttle_runtime::main]
async fn main(
    #[shuttle_aws_rds::Postgres] pool: PgPool,
    #[shuttle_shared_db::Postgres] _pool: PgPool,
) -> ShuttleAxum { ... }

2. Run cargo shuttle deploy, this will create the shared database.

3. We want to dump the RDS database, for which we’ll need the psql CLI installed.

sudo apt-get install postgresql-client-15

4. Run cargo shuttle resource list --show-secrets to get the connection strings of both databases, we’ll need these to dump the RDS database and restore it as a shared database.

5. We can use the psql CLI we installed in step 3 to run some queries against the RDS database before dumping. We can run the same queries against the shared database after completing the migration. We can compare the results of these queries to verify the migration was complete.

Note: this is a very simple way to verify that the migration was successful. Instructions on how to do a more complete verification is out of scope for this guide, but there are many resources that expand on this subject available elsewhere.

# Replace <rds url> with the full RDS connection string from step 4.
psql <rds url> -c "SELECT count(*) FROM your_table;"

6. Next, we proceed with the RDS database dump. We use a custom format so we can change the owner from master (this is the username that is used for the database in the RDS instance) to the shared database user name when we’re ready to restore the database in the shared database. This command will not delete the database from your RDS instance.

# Replace <RDS url> with the full RDS connection string from step 4.
# This saves the dump do a local file called db.dump.
pg_dump -Fc <RDS url> > db.dump

7. The next step is to restore the dumped database to the new shared database. This step also replaces the RDS user as the owner of your tables with the user from your shared database connection string.

# Replace <shared pg url> with the full shared postgres connection string from step 4.
# Replace <shared db user name> with the username from the shared postgres connection string.
pg_restore -d '<shared pg url>' --no-owner --role=<shared db user name> db.dump

# With the example connection string: postgres://user-postgres-axum-app:password@db.unstable.shuttle.rs:5432/db-postgres-axum-app,
# the command would look like this.
pg_restore -d 'postgres://user-postgres-axum-app:password@db.unstable.shuttle.rs:5432/db-postgres-axum-app' \ 
    --no-owner --role=user-postgres-axum-app db.dump

8. We should verify that our application works after the switch to the database. We can use the psql CLI to run queries against the connection string, and compare them with the results from step 5.

# Replace <shared db url> with the full shared postgres connection string from step 4.
psql <shared db url> -c "SELECT count(*) FROM your_table;"

9. We can now remove the RDS database from our main function, and we can remove the shuttle_aws_rds dependency from our Cargo.toml. We will also remove the underscore in front of the shared database pool. The pool is the same type, so you should be able to seamlessly switch to using it in your app.

#[shuttle_runtime::main]
async fn main(
    #[shuttle_shared_db::Postgres] pool: PgPool,
) -> ShuttleAxum { ... }

10. When you are satisfied with the migration, you can delete your RDS instance.

cargo shuttle resource delete database::aws_rds::postgres