Migrate PostgreSQL from RDS, Cloud SQL or a managed DB to a VPS

Managed databases solve a real problem: they eliminate day-one operations (installation, replication, automated backups). But their pricing model is designed so the bill scales non-linearly with growth. On AWS RDS, every component is billed separately: instance compute, gp3 storage per GB, provisioned IOPS, the Multi-AZ secondary node, backups beyond the included window, outbound data transfers. A db.r6g.xlarge instance in Multi-AZ with 200 GB of gp3 storage can easily exceed $800/month on us-east-1; at 500 GB, you cross $3,000/month. The same workload on a dedicated VPS runs at a fraction of that cost.

Cost is not the only variable. Extension constraints are a concrete technical limitation: pgvector on RDS has been available since 2023, but pg_graphql or certain versions of d_btree_gist are not. On Supabase, the catalogue is broader, but it is tied to the platform — moving the database means re-evaluating every extension. Data compliance is a third driver: storing a database in the datacenter of your choice, without billable cross-region transfers, changes the equation for architectures subject to GDPR or data residency policies. Finally, latency between the database and the application decreases mechanically when both reside on the same machine or in the same private network — a free optimisation on a VPS, impossible without extra cost on a managed offering.

Before launching the first pg_dump, four points determine the success of the migration.

PostgreSQL version: source vs destination. A dump produced by pg_dump from version N can be restored to version N or higher, never lower. Check the source version: psql -h <endpoint> -U <user> -c 'SELECT version();'. On the VPS, install the same major version or higher from the PGDG repository. A version migration (e.g. PostgreSQL 14 → 17) is possible in a single pass, but behavioural changes (data types, deprecated functions) warrant a full application test before cutover.

Active extensions. List extensions installed on the source: SELECT extname, extversion FROM pg_extension ORDER BY extname;. Some are universally available (uuid-ossp, hstore, pgcrypto). Others require an additional system package (postgis → postgresql-17-postgis-3) or compilation from source (pgvector → postgresql-17-pgvector package on Debian/Ubuntu). Supabase-specific extensions (pg_graphql, pg_net, pgsodium) do not exist in vanilla PostgreSQL: plan their replacement before migrating.

Database size. SELECT pg_size_pretty(pg_database_size('<database>')); gives the logical size. A pg_dump -Fc dump produces a significantly smaller compressed file. On the VPS, plan disk space for the dump itself, the restored database, and 20% headroom for WAL and temporary restoration indices.

Active connections. A database under traffic cannot be cleanly dumped with long-running transactions in progress. Check: SELECT count(*), state FROM pg_stat_activity GROUP BY state;. If connections in idle in transaction state persist for several minutes, identify and terminate those sessions before launching the migration dump.

RDS enforces TLS on incoming connections (rds.force_ssl = 1 by default on recent instances). The --sslmode=require flag is therefore mandatory on pg_dump, otherwise the connection is refused or established without encryption depending on the instance configuration.

Use a Read Replica endpoint for the dump whenever possible: this avoids adding load to the primary instance during migration.

# Dump from RDS (Read Replica endpoint if available)
export PGPASSWORD='your-password'
pg_dump \
  -h your-instance.xxxx.rds.amazonaws.com \
  -U your_user \
  --sslmode=require \
  -Fc \
  -f /tmp/dump-rds.pgc \
  your_database

Then restore on the VPS in parallel to speed up the import:

pg_restore \
  --no-owner \
  --no-privileges \
  -h localhost \
  -U your_vps_user \
  -d your_database \
  -j 4 \
  /tmp/dump-rds.pgc

--no-owner and --no-privileges are essential: RDS creates internal roles (rdsadmin, rds_superuser) absent from any non-AWS PostgreSQL. Without these flags, pg_restore fails trying to reassign object ownership to these non-existent roles.

Non-transferable RDS variables: rds.force_ssl, rds.log_retention_period, rds_superuser and any parameter prefixed with rds. are AWS proprietary system parameters — they do not exist in standard postgresql.conf. If your application checks the value of rds.force_ssl via SHOW rds.force_ssl, it will get an error on the VPS. Replace this check with SHOW ssl.

Cloud SQL offers two export paths: the Cloud SQL Auth Proxy (recommended for programmatic connections) and export to a GCS bucket (recommended for large dumps or one-way migrations).

Via Cloud SQL Auth Proxy (direct connection, suitable for databases < 10 GB):

# Start the proxy in the background
cloud_sql_proxy -instances=my-project:europe-west1:my-instance=tcp:5433 &

# Dump via the proxy (local port 5433)
export PGPASSWORD='your-password'
pg_dump \
  -h 127.0.0.1 \
  -p 5433 \
  -U your_user \
  -Fc \
  -f /tmp/dump-cloudsql.pgc \
  your_database

Via GCS export (recommended for large databases):

gcloud sql export sql my-instance \
  gs://my-bucket/dump-cloudsql.sql \
  --database=your_database \
  --offload

gsutil cp gs://my-bucket/dump-cloudsql.sql /tmp/

psql -h localhost -U your_vps_user -d your_database \
  -f /tmp/dump-cloudsql.sql

Note that gcloud sql export sql produces a plain SQL file, not a pg_dump -Fc custom-format file. Import with psql -f is slower than pg_restore -j but works on this format. For a custom-format dump compatible with pg_restore, use the Cloud SQL Auth Proxy with pg_dump -Fc instead.

Cloud SQL pitfalls: the --offload flag moves dump processing to a secondary worker and reduces impact on the primary instance. Without it, a long dump can affect production performance. Cloud SQL also creates a cloudsqladmin user that appears in some database objects — apply --no-owner --no-privileges to the restore as with RDS.

Supabase exposes direct PostgreSQL access via a connection URI available in project settings (Settings > Database > Connection string > URI). This access works with standard pg_dump.

# Retrieve the URI from the Supabase dashboard
# Format: postgresql://postgres:[PASSWORD]@db.[PROJECT_REF].supabase.co:5432/postgres

export DATABASE_URL="postgresql://postgres:[email protected]:5432/postgres"

pg_dump \
  "$DATABASE_URL" \
  --sslmode=require \
  -Fc \
  --no-owner \
  --no-privileges \
  -f /tmp/dump-supabase.pgc

Extensions present on Supabase but absent from a vanilla VPS. This is the main pitfall when migrating from Supabase. Supabase's extension catalogue is significantly broader than a standard PostgreSQL installation:

- pgvector: available on Supabase, must be installed manually on the VPS (sudo apt install postgresql-17-pgvector on Debian/Ubuntu, then CREATE EXTENSION vector;).
- pg_graphql: a Supabase proprietary extension that exposes an automatic GraphQL endpoint on the database. It does not exist in standard APT repositories. If your application uses it, you must either compile the extension from source (Rust + cargo-pgrx required) or rewrite the GraphQL access layer outside the database.
- pg_net: allows HTTP requests from SQL functions. Absent from standard packages — rewrite usages outside the database.
- pgsodium: database-level encryption (column-level encryption). Available in some third-party repositories, but its Vault integration is Supabase-specific.
- http: community extension available as postgresql-17-http on Debian/Ubuntu.

Before launching the dump, exhaustively list active extensions with SELECT extname FROM pg_extension WHERE extname NOT IN ('plpgsql'); and verify for each whether an APT package exists, whether compilation is needed, or whether the functionality must migrate outside the database.

Four checks to perform in the hour following cutover.

Row counts. On the ten most voluminous tables, compare source and destination counts. A zero gap confirms all transactions were captured; a residual gap of a few rows may indicate transactions still in flight at cutover time — check the application logs to identify them.

Checksums. On critical tables (transactions, orders, sensitive data), compute a control checksum: SELECT md5(string_agg(id::text || amount::text, ',' ORDER BY id)) FROM orders; and compare the result on both instances. An identical checksum confirms data integrity row by row.

Extension tests. For each extension used by the application, run a minimal test query: SELECT 'test'::vector(3) for pgvector, SELECT gen_random_uuid() for pgcrypto, SELECT ST_AsText(ST_MakePoint(1, 1)) for PostGIS. A missing or misconfigured extension surfaces immediately.

Application connections. Check in the application logs that connections are being established on the VPS and not the old database: grep 'DATABASE_URL\|database connection' /var/log/app/*.log. Also verify SSL connections are active: SELECT ssl, client_addr FROM pg_stat_ssl JOIN pg_stat_activity USING (pid) WHERE client_addr IS NOT NULL LIMIT 5;.

This guide covers one-way migration — the most common procedure for leaving a managed provider. Three complementary topics are worth exploring depending on your context.

Self-hosted PostgreSQL vs Amazon RDS — a cost-based ROI comparison between both models, with the database size and traffic thresholds at which self-hosting becomes clearly advantageous.

Hosting PostgreSQL on a VPS — detailed post-install configuration: postgresql.conf, pg_hba.conf, pgBouncer, monitoring with pg_stat_statements, automated backups and network hardening.

PostgreSQL high availability with Patroni on VPS — if your SLA requires automatic failover, Patroni on a three-node cluster (primary + two replicas) replicates the guarantees of RDS Multi-AZ or Cloud SQL HA, without the provider premium.