Migrating an existing PostgreSQL database to a Digital Ocean Managed PostgreSQL cluster

Question

I want to migrate a standalone PostgreSQL cluster to a Managed cluster on Digital Ocean using continuous migration. I have everything working and started the migration. 2 Questions: Postgresql docs say that the last_value of sequences will not properly migrate. Dig Ocean docs say nothing about this. When I use “Ask Docs” assistant, it claims that the sequences will fully migrate, including the last_value property. What is the actual case? Will I need to manually assign the last_column values for every sequence or does Dig Ocean somehow handle it? How do I know when the migration is complete? Will the control panel show any indication and where can I see the change in state?

KFSys · Answer

Heya, good instinct double checking this, because you’ve caught a real gotcha. Sequences: trust the PostgreSQL docs, not the assistant DigitalOcean’s continuous migration works via PostgreSQL logical replication, and logical replication does not replicate sequence values. This is a fundamental Postgres limitation, not a DO quirk. It replicates table data, but a sequence’s last_value stays wherever it started on the target. DO’s docs just don’t mention it, which is likely why the “Ask Docs” assistant filled the gap with a confident but incorrect answer. So yes, you will need to sync sequences manually at cutover, or your first inserts on the new cluster will collide with existing primary keys. The clean way: right at cutover (after you’ve stopped writes to the source, so the values are final), run this on the source to generate the setval statements: SELECT 'SELECT setval(' || quote_literal(quote_ident(schemaname) || '.' || quote_ident(sequencename)) || ', ' || last_value || ', true);' FROM pg_sequences WHERE schemaname NOT IN ('pg_catalog', 'information_schema'); That outputs a list of SELECT setval(…) commands. Run that output against the target cluster and every sequence will be bumped to the correct position. Alternatively, you can do it per table on the target: SELECT setval('mytable_id_seq', (SELECT MAX(id) FROM mytable)); But the first query handles them all at once. Timing matters: do this after writes to the source have stopped and replication has caught up, so you capture the final values. Knowing when it’s “complete” For continuous migration, keep in mind it doesn’t really “finish” on its own. It does an initial load and then stays connected, streaming changes until you stop it. In the control panel, on the database cluster’s Overview page, you’ll see a migration status banner while data transfers. Once the initial sync is done it moves into the ongoing replication state (caught up, roughly zero lag). That “caught up” point is when you’re ready to cut over. You finalize it yourself by clicking Stop Migration in that banner. That’s your cutover control, not something DO triggers automatically. Heads up: migrations also auto stop after two weeks, so don’t leave it running indefinitely. Suggested cutover order Let the initial sync finish and replication catch up (watch the banner). Stop writes to the source app (short maintenance window). Sync sequences with the setval script above. Spot check row counts and a few key tables on the target. Click Stop Migration. Point your app at the new cluster. Doing sequences before you flip the app is the key step. That’s the one thing the continuous migration won’t do for you. Hope that saves you a nasty primary key surprise!

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today

Report this

Migrating an existing PostgreSQL database to a Digital Ocean Managed PostgreSQL cluster

Related questions

Is CA_CERT available via the connection pool variable syntax in App Platform?

Django App & PostgreSQL Database Connectivity Between Two Droplets

Managed PostgresQL in ATL1 Timeline

Assigning pg_monitor role to users in managed PostgreSQL database

How to create a schema in a Dev Database?

When can we expect Postgres 15.14 Managed Database support to be available?

DO Managed Postgresql doadmin password automatically changing after manually changed.

Postgresql: terminating connection due to administrator command

PostgreSQL on App Platform – Self-Signed Certificate in Certificate Chain issue

Request for Billing Review – PostgreSQL Database Still Being Charged After Deletion

Further PostgreSQL Extension Support Roadmap

Can't connect from my K8s Cluster to my Managed Database

Report this

Migrating an existing PostgreSQL database to a Digital Ocean Managed PostgreSQL cluster

Related questions

Is CA_CERT available via the connection pool variable syntax in App Platform?

Django App & PostgreSQL Database Connectivity Between Two Droplets

Managed PostgresQL in ATL1 Timeline

Assigning pg_monitor role to users in managed PostgreSQL database

How to create a schema in a Dev Database?

When can we expect Postgres 15.14 Managed Database support to be available?

DO Managed Postgresql doadmin password automatically changing after manually changed.

Postgresql: terminating connection due to administrator command

PostgreSQL on App Platform – Self-Signed Certificate in Certificate Chain issue

Request for Billing Review – PostgreSQL Database Still Being Charged After Deletion

Further PostgreSQL Extension Support Roadmap

Can't connect from my K8s Cluster to my Managed Database