The destination database has the right row count, but the first new customer record fails after cutover. The imported IDs arrived correctly. The sequence that generates the next ID did not. This is the kind of gap a PostgreSQL migration can hide when the team treats logical replication as a complete database copy.
Logical replication can keep selected table data moving while you prepare a replacement database. It still needs a migration plan around it. This guide uses PostgreSQL 17's documented behavior. The release gates and operating checklist below are recommended engineering controls, not a promise of zero downtime or a substitute for checking your actual source and target versions.
Understand what the replication stream covers
PostgreSQL uses a publication on the source and a subscription on the destination. A publication defines a set of table changes. The subscription defines the connection and the publications it follows. Initial synchronization normally copies existing table data, then ongoing changes are applied.
That gives you a way to prepare the destination before moving application traffic. It does not mean every database object is transferred. Treat the work as three separate tracks: table data, database structure and application behavior. A successful replication connection only answers part of the migration question.
| Item | Documented behavior | Migration task |
|---|---|---|
| Published table changes | Replicated according to publication settings | Check operation and table coverage |
| Schema and DDL | Not replicated | Prepare and maintain compatible definitions |
| Sequence state | Not replicated | Align generators before destination writes |
| Large objects | Not replicated | Plan a separate treatment for affected data |
| Views and materialized views | Not supported as replicated relations | Recreate and validate separately |
Step 1: Inventory the database and its writers
List the tables the application reads and writes, including audit, join and background-job tables. Record which tables belong in the publication and which are deliberately excluded. Compare that list with the live application rather than assuming the largest business tables are the whole system.
Then list every writer. Web requests, scheduled tasks, imports, support tools and integration callbacks can all change data during cutover. Assign an owner to pause each path. If one forgotten worker continues writing to the old database, moving the main application connection does not finish the migration.
Review host and provider requirements before choosing the approach. Confirm version compatibility, replication access and capacity in your own environment. Keep those decisions in the private runbook rather than copying settings from a generic example into production.
Step 2: Prepare the destination schema
PostgreSQL does not replicate schema definitions or DDL commands. Its documentation identifies a schema-only dump as one way to prepare the initial structure. Review the resulting definitions and any application dependencies before starting the data stream. Keep schema changes coordinated while both systems are running.
Tables are matched by fully qualified name, and columns are matched by name. The subscriber's column order can differ. Compatible type conversions are possible in text mode, but binary replication is more restrictive. For a migration, minimize unnecessary schema differences until the data is verified.
Avoid combining a database move with a broad table-renaming project. Built-in logical replication does not map directly into differently named target tables. If the destination needs a new model, separate that transformation or build a deliberate mapping process as part of the data migration.
Step 3: Check keys and publication settings
A published table needs a replica identity to replicate UPDATE and DELETE operations. The primary key is the default identity when one exists. An eligible unique index can also be used. REPLICA IDENTITY FULL is a fallback, but it has performance and datatype considerations and should not be a blanket answer for every keyless table.
Test more than inserts. Change an existing record, delete one and verify the intended final state at the destination. A table that accepts new rows but fails on updates can pass an incomplete rehearsal. Review the actual publication operation settings rather than assuming they match the default.
Step 4: Let synchronization finish, then validate
Initial table copies and ongoing replication are distinct phases. PostgreSQL brings copied tables into a synchronized state before normal replication continues for them. Confirm all in-scope tables have completed the required synchronization rather than treating a healthy connection as proof that every table is ready.
Reconcile row counts by table and business segment at a controlled comparison point. Compare important totals, relationship integrity and representative records. Read application screens against the destination in a controlled environment. Counts alone cannot reveal a status field with the wrong meaning or a missing supporting object.
Keep the destination closed to ordinary application writes during this phase as an operating rule. That makes differences easier to explain. If you deliberately allow local writes, design and test the conflict handling separately instead of assuming replication will reconcile them for you.
Step 5: Handle sequences and excluded objects
Values stored in serial and identity columns arrive as table data, but their backing sequence state does not. Before destination writes begin, align each relevant sequence with the final migrated data and its intended allocation rules. Review sequence direction, increment and shared usage rather than applying one guessed adjustment everywhere.
Test an insert that uses the default generator after the final alignment. Also inventory PostgreSQL large objects separately. The documentation states that they are not replicated through this mechanism. Views, materialized views and foreign tables are not supported as replicated relations either. Record how each required object is recreated or moved.
Partitioned tables deserve an explicit review. By default, replication originates from leaf partitions. A publication can use the partition root's identity and schema instead, but the target design must match the selected approach. Test actual partition routes before accepting the destination.
Step 6: Rehearse the application cutover
- Pause all agreed source writers and confirm the pause is effective.
- Verify that final source changes have reached the destination.
- Run the final data and excluded-object checks at that boundary.
- Align and test sequence generators before opening destination writes.
- Switch application connections and check critical read and write paths.
- Monitor failures and confirm no worker is still using the old write path.
Define rollback before the rehearsal. Once the destination accepts new writes, switching back to the old source can lose those changes unless you have a tested reconciliation path. Mark that boundary clearly. A retained source database is useful evidence, but its continued existence does not automatically make rollback safe.
Common mistakes before sign-off
Teams can miss tables added after the original inventory, forget a sequence, or validate only the initial copy. Another mistake is dropping and recreating a subscription as a routine reset. PostgreSQL documents that doing so loses synchronization information and requires resynchronization. Diagnose the state before choosing a destructive recovery step.
Use the same acceptance discipline you would apply to a code migration. Define expected behavior, test the exceptions and keep an accountable release owner. Do not turn an unmeasured replication delay into a delivery guarantee.
How Remqa helps with PostgreSQL migrations
Remqa works on database mappings, validation tools and the application changes around a migration. A useful starting point is a reviewed inventory, a rehearsal dataset and a clear cutover owner. Bring the schema, writer list and business checks to a conversation about migration engineering. We can assess where logical replication fits and which parts need separate handling before agreeing the implementation scope.