Supabase to AWS RDS: A Practical PostgreSQL Migration Walkthrough

Backend
1

Migrating from Supabase to AWS RDS PostgreSQL: A Hands-On Guide

Table of Contents

  1. Introduction
  2. Prerequisites
  3. Sample Project Setup
  4. Understanding the Migration Options
  5. Setting Up AWS RDS PostgreSQL
  6. Migrating the Database
  7. Validating the Migration
  8. Limitations and Lessons Learned
  9. Conclusion

1. Introduction

As applications grow, teams may need to move their databases to infrastructure that better fits their scalability, security, or operational requirements. Supabase makes backend development easier, while Amazon RDS for PostgreSQL offers managed PostgreSQL within the AWS ecosystem.

Although both use PostgreSQL, their surrounding services and infrastructure differ. Let’s understand the key differences and why a migration might make sense.

What is Supabase?

Supabase is an open-source backend platform built around PostgreSQL. It provides a managed database along with features such as:

  • Auto-generated REST APIs
  • Authentication
  • Row-Level Security (RLS)
  • File storage
  • Realtime
  • Edge Functions

What is Amazon RDS for PostgreSQL?

Amazon RDS for PostgreSQL is an AWS-managed PostgreSQL database service. It simplifies database administration by providing automated backups, recovery, monitoring, security configurations, and scaling options.

Unlike Supabase, it does not automatically provide the same integrated backend services, such as authentication and auto-generated REST APIs.

Why might a team migrate from Supabase to AWS?

Reason What it means
AWS integration Aligning the database with existing AWS infrastructure and backend services
Infrastructure control Configuring database resources and networking to meet application requirements
Security and governance Integrating with existing AWS security and monitoring practices
Cost and performance Evaluating whether a different database configuration better suits the workload

Note: Migration is not always necessary. It should be driven by specific technical or business needs.

What will this tutorial demonstrate?

This tutorial walks through migrating a sample PostgreSQL database from Supabase to Amazon RDS for PostgreSQL, covering:

  • Database export and restoration
  • Compatibility considerations
  • Data validation

We’ll also explore common migration challenges and the checks needed to verify the migration. Supabase-specific services such as Authentication and Storage will be considered separately where relevant.

2. Prerequisites

Before starting the migration, ensure you have access to the source database, a target AWS environment, and the tools required to export and restore PostgreSQL data.

Supabase project

You need an active Supabase project with a PostgreSQL database. Ensure you have the necessary database connection details and permissions to access the schema and data you intend to migrate.

AWS account

An AWS account is required to create and configure an Amazon RDS for PostgreSQL instance. You should have the permissions needed to provision the database and configure its networking and security settings.

PostgreSQL client tools

Install the PostgreSQL client tools on your local machine or migration environment:

  • pg_dump: exports the source database.
  • pg_restore: restores database backups created in a supported archive format.
  • psql: connects to PostgreSQL databases and executes SQL commands.

Use client tool versions compatible with your source and target PostgreSQL versions.

Basic PostgreSQL knowledge

A basic understanding of PostgreSQL, including tables, schemas, roles, permissions, indexes, and constraints, will help you follow the migration process and troubleshoot common issues.

3. Sample Project Setup

To demonstrate the migration, we will use a simple e-commerce database in Supabase containing customers, products, and orders, along with their relationships.

Design a sample e-commerce database

We will use three tables:

Table Purpose
customers Stores customer details
products Stores product names and prices
orders Stores customer purchases and order quantities

Create tables and relationships

Run the following SQL in the Supabase SQL Editor:

CREATE TABLE customers (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL
);

CREATE TABLE products (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

CREATE TABLE orders (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id BIGINT REFERENCES customers(id),
    product_id BIGINT REFERENCES products(id),
    quantity INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The orders table references customers and products, demonstrating how relationships and foreign keys are preserved during migration.

Insert sample records

Add a few records to test the migration:

INSERT INTO customers (name, email) VALUES
('Rahul Sharma', 'rahul@example.com'),
('Priya Nair', 'priya@example.com');

INSERT INTO products (name, price) VALUES
('Laptop', 55000.00),
('Headphones', 2500.00);

INSERT INTO orders (customer_id, product_id, quantity) VALUES
(1, 1, 1),
(2, 2, 2);

Verify the source database

Run this query in Supabase to confirm that the tables and relationships work correctly:

SELECT
    c.name AS customer,
    p.name AS product,
    o.quantity,
    p.price * o.quantity AS total_amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = o.product_id;

You should see two order records with the corresponding customer names, products, quantities, and calculated totals.

This database will serve as our source for the migration to Amazon RDS for PostgreSQL.

Reference resources

Before proceeding, review these resources to understand the available approaches:

Note: These resources demonstrate different migration scenarios. This tutorial uses them as references while focusing on migrating a sample e-commerce database from Supabase to Amazon RDS for PostgreSQL. Supabase-specific features, such as Authentication, Storage, and Edge Functions, may require separate migration steps.

4. Understanding the Migration Options

Before migrating the database, we need to choose a suitable approach. For PostgreSQL-to-PostgreSQL migration, two common options are native backup and restore tools, and AWS Database Migration Service (DMS).

pg_dump and pg_restore

These PostgreSQL utilities export and restore database backups.

  • pg_dump: creates a backup of the source database.
  • pg_restore: restores a backup created in a supported archive format.
  • psql: connects to PostgreSQL and executes SQL commands.

This approach suits our sample e-commerce database because we can export the existing tables and restore them to Amazon RDS for PostgreSQL.

For example, create a backup in custom format:

pg_dump -Fc \
  -h <SUPABASE_HOST> \
  -U <SUPABASE_USER> \
  -d <SUPABASE_DATABASE> \
  -f ecommerce.dump

The -Fc option creates a custom-format archive that can be restored using pg_restore.

AWS Database Migration Service (DMS)

AWS DMS is a managed service that helps migrate data between databases. It supports different migration strategies:

Strategy Description
Full load Copies existing data from the source to the target
Full load + CDC Copies existing data and captures ongoing changes from the source
CDC only Replicates ongoing changes when the initial data has already been copied

DMS is particularly useful when migrating a live database where changes continue to occur and downtime needs to be minimized. However, source compatibility and replication prerequisites must be checked before using it.

For more details, see the AWS PostgreSQL migration guide: Migrating PostgreSQL Databases to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL - Database Migration Guide

When to choose each approach

Requirement Recommended approach
Small database or demonstration project pg_dump and pg_restore
One-time migration with an acceptable maintenance window pg_dump and pg_restore
Large or actively changing database requiring ongoing replication Evaluate AWS DMS
Migration requiring minimal downtime Full load + CDC, if supported by the source and target setup

For this tutorial, we will use pg_dump and pg_restore to keep the migration straightforward and easy to reproduce.

5. Setting Up AWS RDS PostgreSQL

Now that we have chosen our migration approach, let’s prepare the target database in AWS.

Create the target database

Follow these steps in the AWS Management Console:

  1. Open Amazon RDS and select Create database.
  2. Choose PostgreSQL as the database engine.
  3. Select a PostgreSQL version compatible with your Supabase database.
  4. Enter a DB instance identifier, such as ecommerce-postgres.
  5. Configure the master username, password, and instance settings.
  6. Configure the initial database and storage settings as required.
  7. Review the estimated cost and click Create database.

For a demonstration, use an appropriately sized instance or an eligible free-tier configuration if available. Pricing and eligibility depend on your AWS account and selected configuration.

Once the instance status becomes Available, open its connectivity details and note the endpoint, port, and database name.

Configure networking and security

The RDS instance must be reachable from the machine where you will run the migration commands.

  1. Open the RDS instance’s Connectivity & security section.
  2. Identify the VPC and security group associated with the instance.
  3. Configure the security group to allow inbound PostgreSQL traffic on port 5432 from your trusted migration machine or its security group.
  4. Keep public access disabled whenever possible and use private connectivity, such as a VPN or an EC2 instance within the VPC.

Warning: For a temporary demonstration, public access may be considered only if necessary and properly restricted to your current public IP. Never expose PostgreSQL to 0.0.0.0/0.

Also, use SSL/TLS for database connections and avoid storing database passwords directly in scripts or source control.

Verify connectivity

Install the PostgreSQL client tools on your migration machine, then connect to the RDS instance:

psql "host=<RDS_ENDPOINT> \
port=5432 \
dbname=<RDS_DATABASE> \
user=<RDS_USERNAME> \
sslmode=require"

Replace the placeholders with your actual RDS details. The command prompts for the password if required.

Once connected, verify the target database:

SELECT version();

You can also check the current database:

SELECT current_database();

If the connection fails, check the endpoint, credentials, security group rules, VPC connectivity, and whether the PostgreSQL client can reach port 5432.

6. Migrating the Database

With the source database and target RDS instance ready, we can begin the migration. For this example, we will migrate the sample e-commerce database containing the customers, products, and orders tables.

Export the Supabase database

First, obtain the PostgreSQL connection details from your Supabase project’s Connect dialog. Use a connection method suitable for database backup operations. Supabase recommends a direct connection or session-mode pooler, depending on your network. See the Supabase connection documentation: Connect to your database | Supabase Docs

Run the following command to export the public schema:

pg_dump -Fc \
  --schema=public \
  -h <SUPABASE_HOST> \
  -p <SUPABASE_PORT> \
  -U <SUPABASE_USER> \
  -d <SUPABASE_DATABASE> \
  -f ecommerce.dump

This creates a backup file named ecommerce.dump containing the selected schema’s objects and data.

The --schema=public option is appropriate for our demonstration because the sample tables were created in the public schema. For a real application, identify all required schemas and dependencies before deciding what to export.

Verify that the backup file exists and has a reasonable size before proceeding.

Restore it to AWS RDS

Before restoring, ensure that the target database exists and that the RDS user has the necessary permissions.

Connect to the target database and restore the archive:

pg_restore \
  --no-owner \
  --no-privileges \
  -h <RDS_ENDPOINT> \
  -p 5432 \
  -U <RDS_USERNAME> \
  -d <RDS_DATABASE> \
  ecommerce.dump

The options used here have specific purposes:

  • --no-owner: avoids trying to restore the original object’s ownership.
  • --no-privileges: skips restoring source grant and revoke statements.
  • -d: specifies the destination database.

These options help when the source and target use different database roles. They do not automatically recreate the original permissions, so configure the required target roles and grants separately.

If the restore reports errors, review the output before assuming the migration succeeded. For example, an object may already exist, an extension may be unavailable, or the target user may lack the required permissions.

Handle roles, permissions, extensions, and compatibility issues

A PostgreSQL backup does not mean every part of a Supabase project can be transferred unchanged. Keep these points in mind:

  • Roles and permissions: Supabase roles and grants may not exist on RDS. Create the required application roles and configure appropriate permissions on the target.
  • Extensions: Check which extensions are installed in Supabase and whether they are supported by your selected RDS PostgreSQL version. Enable supported extensions separately where necessary.
  • Ownership: The target database may use a different owner. Assign ownership and permissions according to your application’s requirements.
  • Sequences and constraints: Verify identity columns, sequences, foreign keys, and indexes after restoring.
  • Supabase-specific dependencies: Authentication, Storage, Edge Functions, and application integrations may require separate migration or replacement work. Moving the PostgreSQL tables alone does not migrate these services.

For the sample database, the goal is to restore the three tables and their relationships successfully. Once the restore is complete, we can compare the source and target data to confirm that the migration worked.

7. Validating the Migration

A successful restore does not automatically guarantee that all data and database objects were migrated correctly. We need to compare the source and target databases and verify that application queries still work as expected.

Compare table counts and data

Start by comparing the number of records in each table on Supabase and Amazon RDS. Run this query on both databases:

SELECT 'customers' AS table_name, COUNT(*) AS total
FROM public.customers

UNION ALL

SELECT 'products', COUNT(*)
FROM public.products

UNION ALL

SELECT 'orders', COUNT(*)
FROM public.orders;

The counts should match between the source and target databases.

For a more thorough check, compare important records and values, not just row counts. Two databases can have the same number of records but contain different data.

Verify constraints, indexes, and sequences

Next, verify that important database objects were preserved.

Check constraints:

SELECT
    table_name,
    constraint_name,
    constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'public'
ORDER BY table_name, constraint_name;

Check indexes:

SELECT
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

Compare the results between Supabase and RDS, paying particular attention to primary keys, foreign keys, unique constraints, and indexes.

For identity columns and sequences, insert a test record in a controlled environment and confirm that the generated ID works correctly. Sequence state should also be checked if explicit IDs were imported, since the next generated ID must not conflict with existing records.

Execute sample queries

Run the same application-style query against both databases:

SELECT
    c.name AS customer,
    p.name AS product,
    o.quantity,
    p.price * o.quantity AS total_amount
FROM public.orders o
JOIN public.customers c
    ON c.id = o.customer_id
JOIN public.products p
    ON p.id = o.product_id
ORDER BY o.id;

The results should match between the source and target.

Also test inserts, updates, and deletes in a development environment. This helps verify that constraints, permissions, and generated IDs work as expected.

Troubleshoot common errors

Issue What to check
Permission denied The target database user may lack the necessary privileges. Review ownership, roles, and grants.
Extension not available Confirm that the extension is supported by the selected RDS PostgreSQL version and enable it if required.
Duplicate key errors Check for existing data, primary-key conflicts, and sequence state.
Connection failures Verify the RDS endpoint, credentials, security group rules, network connectivity, and SSL settings.
Missing objects or data Check the backup command’s schema selection and review the complete restore output for errors.

Resolve any issues and repeat the relevant validation checks before connecting the production application to the new database.

8. Limitations and Lessons Learned

Migrating PostgreSQL data from Supabase to Amazon RDS is only one part of moving an application. Supabase provides additional services that are not automatically transferred when you restore a database backup.

Supabase-specific dependencies

Supabase offers services such as Authentication, Storage, Edge Functions, and APIs built around the database.

A PostgreSQL backup primarily covers database objects and data within the selected scope. It does not automatically migrate all these services or their configurations.

Before switching to RDS, identify which Supabase features the application uses and decide whether to migrate, replace, or retain each one.

RLS policies and authentication dependencies

Supabase applications commonly use PostgreSQL Row Level Security (RLS) policies to control which records users can access.

Although database backups can include RLS policies, those policies may depend on Supabase-specific roles, functions, or JWT claims. These dependencies need to be reviewed and tested on the target.

Amazon RDS does not automatically provide Supabase Authentication or the same JWT integration. If the application relies on Supabase Auth, you must plan how user identities, authentication tokens, and authorization rules will work after migration.

Do not disable security policies simply to make the migration succeed. Ensure equivalent access controls are in place before exposing the target database to the application.

API and application changes

Supabase provides APIs that allow applications to interact with database tables and other services. Moving the database to RDS does not automatically recreate those APIs.

Depending on the existing architecture, you may need to:

  • Update database connection settings and secrets.
  • Add or modify backend endpoints to access RDS securely.
  • Replace dependencies on Supabase client libraries or services.
  • Reimplement authentication and authorization integrations where necessary.
  • Update environment variables and test application workflows.

For example, an application that previously queried Supabase directly from its frontend may need a backend API to access RDS safely. Database credentials must never be exposed in browser-side code.

Security, cost, and downtime considerations

Before completing the migration, consider the operational differences between the two platforms.

  • Security: Configure private networking where possible, restrict inbound database access, use SSL/TLS, and store credentials securely.
  • Cost: Amazon RDS charges depend on instance size, storage, backups, data transfer, and other configuration choices. Estimate costs before deploying.
  • Downtime: A pg_dump and restore migration generally requires a plan to handle writes made during the migration. For a live application, schedule a maintenance window or evaluate a replication strategy that reduces downtime.
  • Testing: Validate application functionality, permissions, query performance, and backup recovery before switching production traffic.
  • Rollback: Keep the source database available until the target has been verified and a rollback plan has been established.

Key lesson: A database migration is not complete just because the tables have been restored. Data integrity, security, application compatibility, and operational readiness must all be verified before production traffic is moved.

Supabase to AWS: Service Mapping

Supabase bundles several backend features into one platform. On AWS, each is a separate service. Here is the closest equivalent for each:

Capability Supabase AWS equivalent
Database Supabase Database Amazon RDS for PostgreSQL
Authentication Supabase Auth Amazon Cognito
File storage Supabase Storage Amazon S3
Auto-generated REST API PostgREST Amazon API Gateway + AWS Lambda
Serverless functions Edge Functions AWS Lambda
Realtime Supabase Realtime AWS AppSync
Connection pooling Supavisor Amazon RDS Proxy

“Closest equivalent” does not mean identical behavior. Features, limits, and pricing differ, so confirm details in the current documentation. Only the database moves with pg_dump and pg_restore. Auth, storage, functions, and APIs need separate migration or replacement work.

9. Conclusion

Migrating from Supabase to Amazon RDS for PostgreSQL involves more than copying database tables. It requires careful planning, data validation, security configuration, and an understanding of the services your application depends on.

In this guide, we explored how to prepare a sample e-commerce database, choose a migration approach, configure Amazon RDS, and migrate PostgreSQL data using backup and restore tools. We also covered validation techniques and potential compatibility issues when moving away from Supabase.

Summary

The walkthrough covered the key stages of a database migration:

  • Setting up a sample e-commerce database in Supabase.
  • Understanding the differences between pg_dump/pg_restore and AWS Database Migration Service (DMS).
  • Configuring an Amazon RDS for PostgreSQL instance and securing database connectivity.
  • Exporting and restoring database objects and data.
  • Validating table counts, constraints, indexes, and sample queries.
  • Identifying Supabase-specific dependencies that may require separate migration steps.

These steps provide a foundation for understanding the migration process. Actual migration success should be confirmed by executing the commands and validation checks in your own environment.

Next steps for a production migration

A production migration requires additional planning beyond the sample workflow demonstrated here.

  1. Assess application dependencies: Identify the Supabase features in use, including Authentication, Storage, Edge Functions, database APIs, and RLS policies.
  2. Choose the migration strategy: Evaluate database size, write activity, acceptable downtime, and whether a one-time backup and restore or a replication-based approach is more appropriate.
  3. Test in a staging environment: Validate data integrity, permissions, application behavior, and query performance before touching production traffic.
  4. Plan the cutover: Coordinate application changes, connection settings, and write handling to avoid losing data created during migration.
  5. Prepare for recovery: Establish a rollback plan, configure backups and monitoring, and verify that the target database can be recovered if something goes wrong.

Ultimately, a successful migration is not just about moving data from one database to another. It is about ensuring that the application remains secure, reliable, and functional after the transition.

By combining a well-defined migration strategy with thorough testing and validation, teams can make the move to Amazon RDS for PostgreSQL more predictable and reduce the risks associated with production database migrations.

Reference resources

1 Like