Short answer: For small databases that can take a short outage, use pg_dump and pg_restore to move PostgreSQL to a managed cloud service. For large or busy databases, use logical replication or the provider’s migration service (AWS DMS, Azure Database Migration Service, or Google Database Migration Service). That way the cloud database stays in sync while the old server keeps running, and you switch over with only a few minutes of downtime.
Moving PostgreSQL from a self-managed server to Amazon RDS for PostgreSQL, Amazon Aurora PostgreSQL, Azure Database for PostgreSQL Flexible Server, or Google Cloud SQL for PostgreSQL is one of the most common database projects today. The cloud provider takes over backups, patching, failover, and storage. You still own schema design, query performance, and cost.
This guide covers the whole process: choosing a managed PostgreSQL service, checking compatibility, picking a migration method, and cutting over safely.
Table of Contents
Why Move PostgreSQL to a Managed Cloud Database?
Running PostgreSQL yourself means you handle everything: OS patches, minor version upgrades, backup scripts, replication, failover, disk monitoring, and restore tests. A managed PostgreSQL service takes over most of that:
- Automated backups and point-in-time recovery (PITR). You can restore to any second within the retention window.
- High availability. A standby in another availability zone takes over automatically if the primary fails.
- Read replicas. You can add read capacity with a few clicks or one CLI command.
- Storage that grows on its own. You no longer get paged at 3 a.m. because the disk is full.
- Monitoring built in. Tools such as Amazon RDS Performance Insights, Azure Query Store, and Cloud SQL Query Insights show slow queries without extra setup.
The trade-off is control. You don’t get a real PostgreSQL superuser, you can’t install any extension you like, and some server parameters are locked. Check these limits before you migrate, not after.
Comparing Managed PostgreSQL Services
| Feature | Amazon RDS for PostgreSQL | Amazon Aurora PostgreSQL | Azure Database for PostgreSQL Flexible Server | Google Cloud SQL for PostgreSQL |
|---|---|---|---|---|
| Storage model | EBS volumes attached to the instance | Distributed storage shared by all replicas | Managed disks | Persistent disks |
| High availability | Multi-AZ standby | Up to 15 replicas on shared storage, fast failover | Zone-redundant HA standby | Regional HA instance |
| Admin role | rds_superuser | rds_superuser | azure_pg_admin | cloudsqlsuperuser |
| Connection pooling | RDS Proxy | RDS Proxy | Built-in PgBouncer option | Run PgBouncer, or use managed pooling where it’s offered |
| Migration service | AWS DMS | AWS DMS | Azure Database Migration Service | Database Migration Service |
| Good fit for | Standard OLTP workloads, predictable cost | Large or read-heavy workloads, fast failover | Teams already on Azure | Teams already on Google Cloud |
Google Cloud also offers AlloyDB for PostgreSQL, and Aurora has a Serverless v2 option. Both are worth a look if you need high throughput or have very spiky traffic. For most migrations, though, standard RDS, Azure Flexible Server, or Cloud SQL is the simplest place to start.
Step 1: Check Version, Size and Extensions
Collect a few facts about the source PostgreSQL server first.
Check the PostgreSQL version:
SELECT version();
Check the size of each database:
SELECT datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datistemplate = false
ORDER BY pg_database_size(datname) DESC;
List the extensions in use. Run this in every database:
SELECT extname, extversion FROM pg_extension ORDER BY extname;
Compare that list with the extensions your target service supports. Common extensions like pg_stat_statements, postgis, pgcrypto, uuid-ossp, hstore, and pgvector are widely available. Custom C extensions you compiled yourself usually aren’t. If an extension is missing, you’ll have to replace it before you migrate.
Find tables without a primary key. Logical replication needs a primary key, or a REPLICA IDENTITY, to replicate UPDATE and DELETE:
SELECT n.nspname AS schema, c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND NOT EXISTS (
SELECT 1 FROM pg_constraint p
WHERE p.conrelid = c.oid AND p.contype = 'p');
Also look for things that depend on the server itself, which won’t carry over to a managed database:
- Roles that rely on
SUPERUSER COPY ... FROM '/path/on/server'commands that read local files- Untrusted procedural languages such as
plpythonu - Custom settings in
postgresql.conf. You’ll recreate these in a parameter group (AWS), server parameters (Azure), or database flags (Cloud SQL).
Step 2: Choose a Migration Method
| Method | Downtime | Best for |
|---|---|---|
pg_dump / pg_restore | Minutes to hours, depending on size | Databases under roughly 50–100 GB, dev/test, apps that can take a maintenance window |
| Logical replication | A few minutes at cutover | Large production databases, version upgrades during the migration |
| AWS DMS / Azure DMS / Google DMS | A few minutes at cutover | Teams that want a managed, monitored migration |
You can also move to a newer PostgreSQL major version as part of the migration. For example, you can go from PostgreSQL 13 on-premises to PostgreSQL 17 on Amazon RDS. Both pg_dump and logical replication support this. Test your application against the new version first.
Method 1: pg_dump and pg_restore (Offline)
This is the simplest approach and it works with every cloud provider.
1. Stop writes to the source database. Put the application into maintenance mode.
2. Dump the roles. Managed services don’t allow SUPERUSER or replication attributes, so drop those from the output:
pg_dumpall -h old-db.example.com -U postgres --roles-only --no-role-passwords \
| sed -E 's/(NO)?SUPERUSER|(NO)?REPLICATION|(NO)?BYPASSRLS//g' > roles.sql
Check roles.sql by hand. Then create the roles on the target and set their passwords again.
3. Dump the database in custom format:
pg_dump -h old-db.example.com -U postgres -d appdb \
-Fc --no-owner --no-acl -f appdb.dump
4. Create the target database and restore it with parallel jobs:
psql "host=mydb.xxxx.us-east-1.rds.amazonaws.com user=dbadmin dbname=postgres sslmode=require" \
-c "CREATE DATABASE appdb;"
pg_restore -h mydb.xxxx.us-east-1.rds.amazonaws.com -U dbadmin -d appdb \
--no-owner --no-acl -j 8 appdb.dump
-j 8 restores tables and builds indexes in parallel, which can cut restore time a lot. Set it to about the number of vCPUs on the cloud instance.
5. Update planner statistics. A restore doesn’t bring statistics over, so queries can be slow until you run:
ANALYZE;
Tip: Run pg_dump and pg_restore from a VM in the same cloud region as the target. Restoring over a long-distance network link is often the slowest part of the migration.
Method 2: Logical Replication (Near-Zero Downtime)
With logical replication, the cloud database copies the existing data and then keeps receiving changes while the application still writes to the old server.
1. Enable logical replication on the source. In postgresql.conf:
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
Changing wal_level requires a restart. If the source is itself a managed service, set the provider’s option instead: rds.logical_replication = 1 on RDS, wal_level = logical in Azure server parameters, or the cloudsql.logical_decoding flag on Cloud SQL.
2. Copy the schema only to the target. Logical replication doesn’t replicate DDL:
pg_dump -h old-db.example.com -U postgres -d appdb --schema-only --no-owner --no-acl \
| psql "host=mydb.xxxx.us-east-1.rds.amazonaws.com user=dbadmin dbname=appdb sslmode=require"
3. Create a publication on the source:
CREATE PUBLICATION cloud_migration FOR ALL TABLES;
4. Create a subscription on the target. This starts the initial data copy:
CREATE SUBSCRIPTION cloud_migration
CONNECTION 'host=old-db.example.com port=5432 dbname=appdb user=replicator password=******'
PUBLICATION cloud_migration;
The cloud database must be able to reach the source over the network, through a VPN, AWS Direct Connect, Azure ExpressRoute, Cloud Interconnect, or an allow-listed public IP. On Amazon RDS and Aurora, the user running CREATE SUBSCRIPTION needs the rds_superuser role.
5. Monitor progress on the source:
SELECT slot_name, active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;
On the target, check the state of each table:
SELECT srrelid::regclass AS table_name, srsubstate
FROM pg_subscription_rel;
srsubstate = 'r' (ready) means that table has finished its initial copy and is streaming changes. Once every table is r and the lag stays close to zero, you’re ready to cut over.
Method 3: Cloud Migration Services
Each cloud provider has a managed migration tool that runs logical replication (or change data capture) for you, with a console showing progress and errors:
- AWS Database Migration Service (AWS DMS): moves PostgreSQL into Amazon RDS or Aurora, with a full load followed by ongoing replication.
- Azure Database Migration Service, and the migration service built into Azure Database for PostgreSQL Flexible Server.
- Google Database Migration Service: migrates to Cloud SQL for PostgreSQL or AlloyDB using native PostgreSQL logical decoding.
They still have the same requirements as manual logical replication: primary keys, network access, and wal_level = logical. What they add is monitoring, retries, and an easier cutover workflow. For a large production migration with no PostgreSQL replication specialist on the team, they’re usually worth it.
Step 3: Cut Over to the Cloud Database
Plan the cutover as a checklist and practice it on a staging copy first.
- Lower DNS TTLs a day ahead if the application connects through a DNS name.
- Stop application writes to the old database.
- Wait for replication lag to reach zero (Method 2 or 3).
- Sync sequences. Logical replication doesn’t copy sequence values, so new inserts on the target would fail with duplicate key errors. Generate
setvalstatements on the source:SELECT format('SELECT setval(%L, %s, true);', schemaname || '.' || sequencename, last_value) FROM pg_sequences WHERE last_value IS NOT NULL;Run the output on the target database. - Drop the subscription on the target:
DROP SUBSCRIPTION cloud_migration; - Point the application at the new endpoint, for example the Amazon RDS endpoint, the Azure server name, or the Cloud SQL private IP. Require SSL (
sslmode=requireor stricter). - Run smoke tests and compare row counts on key tables.
- Keep the old server read-only for a few days in case you need to roll back.
Common Migration Problems
“permission denied” during restore. Managed PostgreSQL doesn’t give you a true superuser. Restore with --no-owner --no-acl and grant privileges afterwards.
“extension is not available”. The cloud service doesn’t support that extension. On AWS you may need to add it to shared_preload_libraries in the parameter group, or in the database flags on Cloud SQL, then reboot. If it isn’t supported at all, replace it.
Duplicate key errors after cutover. The sequences weren’t synced. Run the setval statements from Step 3.
Slow queries right after migration. Statistics are missing, or the instance is undersized. Run ANALYZE, then compare shared_buffers, work_mem, and instance memory with the old server.
Replication slot filling up the source disk. An inactive slot keeps WAL forever. Drop slots you no longer need:
SELECT pg_drop_replication_slot('slot_name');
Too many connections. Cloud instances limit max_connections based on memory. Put a connection pooler in front of the database: RDS Proxy, PgBouncer on Azure Flexible Server, or PgBouncer on Google Cloud.
Keeping Cloud PostgreSQL Costs Under Control
Managed PostgreSQL saves operational work, but the monthly bill can grow quickly. Your cost depends on:
- Instance size (vCPU and memory). This is usually the biggest line item.
- High availability. A standby roughly doubles compute cost.
- Storage and provisioned IOPS.
- Backup storage beyond the free allowance.
- Data transfer between availability zones, regions, and the internet.
Ways to keep the bill down:
- Right-size after migration. Watch CPU and memory for two weeks, then scale down if there’s room.
- Buy reserved instances or committed-use discounts for production databases that run all the time.
- Stop dev and test instances outside working hours.
- Upgrade old major versions before they reach end of standard support. Some providers charge extra for extended support on older PostgreSQL versions.
- Use read replicas only where they’re actually needed, and remove unused ones.
Use the official calculators to estimate costs before you migrate: the AWS Pricing Calculator, the Azure Pricing Calculator, and the Google Cloud Pricing Calculator.
Summary
- Pick the managed PostgreSQL service that matches your existing cloud: Amazon RDS or Aurora, Azure Database for PostgreSQL Flexible Server, or Google Cloud SQL / AlloyDB.
- Before migrating, check the PostgreSQL version, database size, extensions, and primary keys.
- Use pg_dump / pg_restore for small databases that can take a maintenance window.
- Use logical replication or a cloud migration service for large production databases that need near-zero downtime.
- At cutover, sync sequences, run ANALYZE, switch connection strings, and keep the old server around for rollback.
- After migration, right-size the instance and keep watching cost.
Related guides:


