Checklist: migrate PostgreSQL to a managed service

Who this is for

This guide is for founders, CTOs, IT leads, and platform engineers who run PostgreSQL on servers or virtual machines and want to move it to a managed service. It assumes you can read a connection string, run SQL, and plan a maintenance window. The database may be small, but the same steps apply when it carries production traffic.

Checklist: migrate PostgreSQL to a managed service is written for teams that need a repeatable cutover, not a one way copy. You will still need to decide what to do with superuser access, extensions, and connection pooling. Those three areas cause more trouble than the dump and restore itself.

Before you begin

Start with an inventory. List every database, role, extension, schema, table, sequence, view, function, trigger, and foreign data wrapper. Include row counts, table sizes, index sizes, and daily write volume. Record the PostgreSQL major version, encoding, collation, and timezone. Check whether the application uses prepared transactions, advisory locks, listen and notify, large objects, or unlogged tables.

Decide which managed service features you need: private networking, point in time recovery, read replicas, and a maintenance window that fits your traffic. Confirm supported PostgreSQL versions and extensions before you promise a date. Some services block superuser, filesystem access, custom C libraries, and certain extensions. That changes the migration plan.

Set a target for downtime. A small database can take a short outage with pg_dump and pg_restore. A large or busy database needs logical replication or a physical base backup, plus a short write freeze. Choose the method now, because it shapes the test plan and the rollback plan.

The checklist

Work through the list in order. Do not skip the test restore, and do not treat a successful dump as a successful migration. A dump can finish and still hide missing roles, wrong ownership, or an extension that the managed service does not allow.

Run the same steps in staging with production sized data or a close copy. Measure the restore time, the replication lag, and the time to switch the application. Write down who runs each step, when they run it, and how they signal success. The checklist is a control, not a suggestion.

• Inventory every database, role, schema, extension, table, sequence, function, trigger, and foreign data wrapper, including owners and privileges.
• Confirm the target managed service supports your PostgreSQL major version, encoding, collation, timezone, and every required extension at a compatible version.
• Test whether the application needs superuser rights, custom C libraries, filesystem access, prepared transactions, advisory locks, or listen and notify, and remove or redesign each dependency.
• Create roles and groups in the target with the same names, membership, and default privileges before you load data, so object ownership does not drift.
• Run a full dump and restore into staging, then compare row counts, index counts, constraint counts, extension versions, and object owners against the source.
• Reset sequence values on the target after the initial copy, because logical replication does not carry sequence state.
• Check large objects, unlogged tables, temporary tables, and foreign data wrappers, because logical replication does not carry all of them.
• Set up logical replication with a publication on the source and a subscription on the target, and verify that initial copy and ongoing changes apply cleanly.
• Check replication slot limits, wal_level, max_wal_senders, max_replication_slots, and network access, and monitor lag during a busy write period.
• Test connection pooling, SSL settings, authentication rules, DNS names, and secrets in staging, and confirm the application reconnects after a target failover.
• Run EXPLAIN on your top queries on the target, refresh planner estimates with ANALYZE, and compare plans, because default parameters and hardware differ.
• Schedule a cutover window, freeze writes at a known point, wait for replication lag to reach zero, and promote the target or switch the connection string.
• Keep the source read only and available for rollback until the target passes smoke tests, backup checks, and a full business transaction.
• Record the rollback steps, the exact commands, the owner, and the decision point, and rehearse them in staging before the real cutover.

What good looks like

Good migrations are boring. The target accepts the same application connections, the same roles can read and write, and the same extensions are present at compatible versions. Backups run, point in time recovery is enabled, and you have restored a copy into a separate environment. Monitoring shows connection counts, replication lag, disk use, and slow queries from the first hour.

A clean cutover has a short write freeze, not a long outage. You know the row counts and sequence values match, and you have checked the tables the application writes most. The rollback plan is still valid after cutover, and the source is not deleted until the target has run through a full business cycle.

Watch for drift after cutover: stale planner estimates, missing grants, sequence values that start behind, and queries that behave differently because of collation or timezone. Run ANALYZE, check default privileges, and review slow query logs for the first few days.

What to do next

Assign an owner for the migration and a separate reviewer for the restore and cutover evidence. Put the checklist in your tracker and mark each item with a link to the command output, query result, or screenshot. A checked box without evidence is just a hope.

If your team lacks the time, bring in help for the parts you cannot test safely. The Cloudverg Discovery and roadmap sprint at $9,500 fixed price can produce the target architecture, a phased plan, and a fixed quote for phase one, and the fee is credited if you start within 60 days. Compare that with doing the inventory and dry runs in house.

After cutover, keep the source for a defined period, then decommission it with a final dump and a written record. Update runbooks, connection secrets, and monitoring dashboards. Schedule a review two weeks later to check backups, slow queries, and connection limits.

Frequently asked questions