PostgreSQL logical replication transfers selected data changes through publications and subscriptions. It can support migrations and controlled data distribution, but it is not automatically a complete physical copy or a ready-to-use failover system. Schema, sequences, permissions, and other database objects have separate requirements.
A reliable design defines what is replicated and how the subscriber may be used. This guide explains initial synchronization, identity, operational gaps, and cutover so replicated rows do not create a false impression that every recovery dependency is already covered.
Define publication scope and ownership
Identify the tables and change types that belong in the publication. Keep the business purpose explicit: reporting, migration, or another approved use. A broad publication can transfer private data beyond the intended audience.
Review supported row and column filtering behavior for the server versions involved. Filtering requires deliberate design and testing. Do not assume every table added later is automatically handled according to the same policy.
Assign owners for both publisher and subscriber. The data transfer can remain technically active while nobody is responsible for a schema mismatch, failed apply worker, or unauthorized consumer.
Plan the initial synchronization
Logical replication typically starts with a snapshot and copies existing table data before ongoing changes catch up. That initial work consumes resources and can take significant time. Test expected volume and capacity in an approved environment.
Inspect table synchronization state rather than treating subscription creation as completion. Some tables can be ready while others are still copying or failing. Acceptance should reflect all required tables and an appropriate consistency check.
Avoid uncontrolled concurrent writes on the subscriber. The intended conflict model must be explicit. A subscriber used as a write target can diverge or encounter apply conflicts even if the replication connection remains healthy.
Establish replica identity for changes
Updates and deletes need a supported way to identify corresponding rows. A primary key commonly provides that identity, but the actual requirements depend on the table and publication behavior. Verify the documented replica identity configuration.
Using a full-row identity has performance and datatype considerations. It is not a free substitute for a suitable key. Review supported matching behavior, workload volume, and subscriber indexing before choosing it.
Test updates and deletes, not only inserted rows. A migration demonstration that copies new inserts successfully can miss identity failures that appear later when existing records change.
Coordinate schema changes separately
Database schema and DDL are not generally replicated by the logical data stream. Prepare the subscriber schema through an approved process and keep subsequent changes coordinated. A publisher change can make incoming rows incompatible with the subscriber.
For suitable additive changes, updating the subscriber first can reduce interruption, but migration ordering depends on the specific change. Test the supported sequence rather than applying a universal rule to every alteration.
Include indexes, constraints, functions, roles, and permissions in the surrounding migration plan where needed. Equal row counts do not establish that application behavior is equivalent across the two databases.
Account for sequences and unsupported objects
Sequence state is not replicated as ordinary table row changes. Identity or serial values appearing in replicated rows do not prove the subscriber’s sequence is ready for new local inserts after cutover.
Before a write cutover, synchronize or advance required sequences through a controlled process appropriate to the actual data. Test representative inserts and uniqueness constraints. Do not discover a reused identifier only after production writes begin.
Review other restrictions, including large objects and unsupported relation types, in the version-specific documentation. Required application data outside normal replicated tables needs a separate transfer and validation path.
Monitor lag, slots, and retained storage
A lagging or disconnected subscriber can affect replication-slot retention and publisher storage according to configuration. Monitor useful progress and retained WAL, not only whether a subscription object exists.
Set an operational response for failed apply workers and growing lag. Determine whether the issue is connectivity, permissions, schema mismatch, conflict, or capacity before repeatedly restarting components. Preserve evidence for the actual failure.
Review the impact of long initial synchronization and subscriber outages. A migration system should not unexpectedly exhaust the publisher’s storage while waiting for a target that no longer has an owner.
Protect replication access and data boundaries
Use approved authentication, encrypted transport where required, and scoped privileges. Replication access can expose substantial data. It deserves a separate permission review rather than inheriting a broad administrator account by convenience.
Keep credentials out of broad logs and configuration exports. Connection details and subscription definitions may have sensitive handling requirements. Use the supported secret-management workflow for the environment.
Test the subscriber’s own access controls. Replicated data should not become broadly readable because the target was treated as a temporary staging database. Reporting and migration systems still need appropriate tenant and operator boundaries.
Prove cutover and recovery deliberately
A write cutover needs an owned procedure for stopping or coordinating old writes, establishing catch-up, validating data, preparing sequences, and switching application connections. Replication alone does not perform that business decision.
Define how rollback works after new writes reach the target. Repointing to the old database can lose those writes unless a supported reconciliation plan exists. Do not describe a connection-string change as a complete rollback.
For a migration, rehearse the process with representative transactions and failure cases. Verify not only row counts but meaningful records, constraints, application queries, and new inserts. Record the exact acceptance state before releasing the old system.
Confirm the subscriber serving model
Decide whether readers can tolerate replication lag and what consistency the application promises. A report generated from a lagging subscriber may be useful, but it should not be presented as an immediate authoritative view when that matters.
If the target will eventually accept writes, rehearse the permission and connection transition as well as data synchronization. A subscriber prepared only for read access can be missing application grants or operational jobs needed at cutover. Record those dependencies explicitly.
Frequently asked questions
Does logical replication copy all database objects?
No. Schema, sequence state, and other unsupported objects require separate planning.
Does a healthy connection prove zero lag?
No. Inspect replication progress and required table state through supported monitoring.
Where should I verify current restrictions?
Read the logical replication restrictions and the logical replication overview.
For a complementary workflow, read PostgreSQL pg_dump: Define the Export and Prove the Restore.
