Moving a production database to a new server is not always a simple task. You need to transfer existing data, keep the new database updated while the application continues running, and eventually move application traffic without losing transactions.
For applications that cannot remain unavailable for several hours, a traditional database migration can create serious operational challenges. Taking the application offline, copying a large database, restoring it on another server, and then restarting the application may not be acceptable.
PostgreSQL logical replication provides another approach. It allows you to copy existing data to a destination database and continuously replicate changes made on the source. This gives your team an opportunity to prepare the new database while the existing application continues handling requests.
Logical replication is particularly useful when moving between PostgreSQL servers, upgrading database infrastructure, separating workloads, or migrating to a new hosting environment.
However, it is important to understand that logical replication does not automatically make a migration completely downtime-free. You still need a controlled cutover process, a plan for handling writes, and a way to verify that the destination database is ready to receive application traffic.
This article explains how PostgreSQL logical replication works, how to configure it, and how to use it as part of a production database migration with minimal application downtime.
What Is PostgreSQL Logical Replication?
PostgreSQL supports different replication approaches, including physical replication and logical replication. Both can keep database servers synchronized, but they operate at different levels.
Physical replication transfers changes at the storage and WAL level. It generally maintains a close copy of the source database cluster and is commonly used for standby servers and high availability.
Logical replication works with database-level changes. Instead of copying the physical database structure, PostgreSQL identifies changes to published tables and sends those changes to a subscriber.
This distinction makes logical replication useful when the source and destination databases need different configurations or when you want to replicate selected tables rather than an entire database cluster.
Logical replication uses three important concepts:
Publisher: The source database that publishes changes from selected tables.
Publication: The definition of which tables and operations should be replicated.
Subscription: The configuration on the destination database that connects to the publisher and receives changes.
For example, suppose your application currently uses a PostgreSQL database on Server A. You want to move it to Server B without stopping normal application activity for the entire data transfer.
You can configure Server A as the publisher and Server B as the subscriber. PostgreSQL initially copies the published table data and then continues sending new changes.
The destination gradually becomes synchronized with the source while the application continues using the original database.
How Logical Replication Works
Logical replication normally involves two major stages: the initial table synchronization and continuous replication.
During the initial synchronization, PostgreSQL copies the existing data from published tables to the subscriber. This process establishes the starting point for replication.
After the initial copy, PostgreSQL uses the source database's write-ahead log, or WAL, to identify changes that need to be sent to the subscriber. The subscriber applies those changes to its own tables.
For example, suppose a customer places an order while the initial synchronization is still running.
The source database records the transaction as part of its normal database activity. Logical replication then sends the relevant committed change to the subscriber, allowing the destination to catch up.
The application does not need to send the same SQL statement to both databases. Replication handles the transfer of supported database changes.
The overall process looks like this:
Create the required schema on the destination.
Configure the source database for logical replication.
Create a publication containing the required tables.
Create a subscription on the destination.
Allow PostgreSQL to perform the initial data synchronization.
Monitor replication while the application continues using the source.
Stop new writes to the source during cutover.
Wait for the destination to catch up.
Move application connections to the destination.
Verify application behavior before completing the migration.
The most important part is the final cutover. Replication keeps the destination updated, but the application still needs a controlled transition from one writable database to another.
Prepare the Source PostgreSQL Server
Before creating a publication, verify that the source PostgreSQL server supports logical replication and has the required configuration.
The most important setting is wal_level.
Check its current value:
SHOW wal_level;
For logical replication, the setting should be:
logical
If it is not configured correctly, update the PostgreSQL configuration file:
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
The exact values depend on your workload and existing replication configuration. Do not copy these numbers blindly into a production server.
max_replication_slots controls how many replication slots PostgreSQL can maintain. max_wal_senders controls the number of concurrent WAL sender processes.
These settings need to account for existing replication activity as well as the new subscriptions you plan to create.
Some configuration changes require a PostgreSQL restart. Plan this before beginning the migration, especially when the source database is already serving production traffic.
You should also verify that the replication user has the necessary permissions and that the destination server can connect to the source.
Create a Replication User
It is better to use a dedicated database account for logical replication rather than using a general application account or the PostgreSQL superuser.
For example:
CREATE ROLE replication_user
WITH LOGIN REPLICATION PASSWORD 'use-a-secure-password';
The account also needs permission to read the tables being published. Depending on your PostgreSQL version and configuration, grant the required SELECT privileges explicitly:
GRANT CONNECT ON DATABASE application_db
TO replication_user;
GRANT USAGE ON SCHEMA public
TO replication_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public
TO replication_user;
If new tables are created during the migration and they need to be included, review default privileges as well. Existing table grants do not automatically guarantee that future tables will be accessible to the replication account.
Update pg_hba.conf on the source server to permit connections from the destination server.
For example:
host application_db replication_user 10.0.0.20/32 scram-sha-256
Replace the network address with the actual destination server address and use the authentication method supported by your environment.
Do not expose the PostgreSQL replication port publicly unless there is a specific, properly secured requirement. Prefer private networking, firewall restrictions, and encrypted connections.
Create a Publication on the Source Database
A publication defines which tables and operations are available for logical replication.
Connect to the source database and create a publication:
CREATE PUBLICATION application_publication
FOR TABLE
customers,
orders,
order_items;
This publication includes changes from the three specified tables.
If you need to replicate every eligible table in the database, PostgreSQL also supports:
CREATE PUBLICATION application_publication
FOR ALL TABLES;
However, publishing all tables should be a deliberate decision. A production database may contain temporary, administrative, reporting, or application-specific tables that do not need to be copied to the destination.
A publication can also control which operations are replicated. By default, logical publications include INSERT, UPDATE, DELETE, and TRUNCATE operations.
For example, if the destination only needs inserted and updated records, you can define:
CREATE PUBLICATION application_publication
FOR TABLE customers, orders
WITH (
publish = 'insert, update'
);
Choose the publication scope carefully because it determines which changes the subscriber receives.
Prepare the Destination Database
Before creating a subscription, prepare the destination database.
Logical replication does not automatically copy the complete database structure. It replicates table data and supported changes, but it does not automatically recreate every database object from the source.
You should create or migrate the required schema separately.
This includes:
For example, you can use a schema-only dump to transfer database structure:
pg_dump \
--schema-only \
--no-owner \
-d application_db \
> application_schema.sql
Restore the schema on the destination:
psql \
-d application_db \
-f application_schema.sql
In an actual migration, review the generated schema file and adapt the commands to your environment. Avoid blindly restoring source-specific ownership or permissions when the destination uses a different role structure.
Also verify that the destination PostgreSQL version and installed extensions support the schema and application features you depend on.
Create a Subscription on the Destination
Once the destination schema is ready and the publication exists, create a subscription.
Run the following command on the destination database:
CREATE SUBSCRIPTION application_subscription
CONNECTION
'host=10.0.0.10
port=5432
dbname=application_db
user=replication_user
password=use-a-secure-password
sslmode=require'
PUBLICATION application_publication;
Replace the connection details with your source database information.
By default, PostgreSQL creates the necessary replication slot on the publisher and starts the initial table synchronization.
The destination begins copying the existing data from the published tables. Once the initial copy is complete, ongoing changes are applied through logical replication.
Be careful with connection strings containing passwords. Avoid storing production credentials in source control, shell history, deployment logs, or shared scripts. Use an appropriate secrets-management approach.
For environments with strict network and security requirements, configure TLS certificate verification according to your infrastructure rather than relying only on encrypted transport.
Monitor Replication Progress
Creating a subscription does not mean that the migration is immediately ready for cutover.
You need to monitor whether the initial table synchronization has completed and whether ongoing changes are being applied.
On the destination, inspect subscription status:
SELECT
subname,
pid,
received_lsn,
latest_end_lsn,
latest_end_time
FROM pg_stat_subscription;
This view provides information about the subscription's replication worker and received WAL positions.
You can also inspect individual table synchronization states:
SELECT
s.subname,
s.schemaname,
s.relname,
s.srsubstate
FROM pg_subscription_rel s;
The synchronization state helps identify whether a table is still being copied or has completed its initial synchronization.
On the publisher, inspect replication slots:
SELECT
slot_name,
slot_type,
active,
restart_lsn,
confirmed_flush_lsn
FROM pg_replication_slots;
A replication slot is important because PostgreSQL retains the WAL information needed by the subscriber until the subscriber has confirmed that it no longer needs those changes.
This also introduces an operational risk. If the subscriber stops consuming changes for an extended period, the source may retain a large amount of WAL. That can increase disk usage and eventually create storage problems.
Monitor replication activity, WAL retention, available disk space, and subscriber connectivity throughout the migration.
Plan the Production Cutover Carefully
The most sensitive part of a logical replication migration is moving application writes from the source to the destination.
Before cutover, the application is still writing to the original database. The destination is receiving replicated changes, but it should not independently accept application writes while the source remains authoritative.
If both databases accept writes without a coordinated strategy, their data can diverge. Logical replication is not a general-purpose multi-master conflict-resolution system.
A controlled cutover should follow a sequence similar to this:
Step 1: Prepare the Application
Make sure the application can connect to the destination database.
If your application uses environment variables or centralized configuration for database connections, prepare the new connection settings before the migration.
Test authentication, connection pooling, required extensions, permissions, and application migrations in advance.
Step 2: Stop New Writes
Put the application into a maintenance or read-only mode, or otherwise stop every component that can modify the source database.
This includes API instances, background workers, scheduled jobs, message consumers, and administrative processes.
Stopping only the main web application is not sufficient if background services can continue writing to the source.
Step 3: Wait for Replication to Catch Up
After writes have stopped, allow the subscriber to apply the remaining changes.
You can compare the source and destination replication positions, inspect subscription status, and verify that all published tables have completed synchronization.
Do not rely on an arbitrary waiting period. Confirm that the destination has received and applied the required changes.
Step 4: Verify the Destination
Check important tables, record counts, application-specific totals, and other business-critical data.
For example:
SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM order_items;
Matching row counts alone do not prove that two databases contain identical data. For critical migrations, perform additional checks based on primary keys, important business fields, or application-level validation.
Step 5: Move Application Connections
Update the application's database connection settings to point to the destination.
Restart or recycle application instances and connection pools as required so that they establish new connections.
Step 6: Resume Application Traffic
Enable normal application traffic and background processing.
Monitor application errors, database connections, query performance, and business operations closely during the initial period after cutover.
Keep the old source available for investigation, but do not allow applications to resume writing to it after the destination becomes authoritative.
Important Limitations You Should Understand
Logical replication is useful, but it does not replicate every database feature automatically.
One important limitation is that schema changes are not automatically replicated as general DDL operations. If you add a column, create an index, or change a table definition on the source, you must coordinate the corresponding change on the destination.
This becomes especially important when the application continues to evolve during a long migration.
Sequence values also require attention. Logical replication replicates table data changes, but sequence state is not automatically kept synchronized in the same way. Before cutover, verify and synchronize sequences so that new inserts do not generate duplicate or conflicting identifiers.
Large objects are another area that needs separate planning because they are not replicated through ordinary logical table replication.
You should also review tables without primary keys. Logical replication needs a suitable replica identity to identify rows for UPDATE and DELETE operations. By default, a primary key normally provides this identity. Tables without one may require an appropriate unique index or a different replica identity configuration.
Finally, remember that logical replication does not automatically replicate roles, permissions, extensions, functions, triggers, or all other database objects. These must be handled separately as part of the migration.
Common Mistakes During Logical Replication
A frequent mistake is creating a subscription before preparing the destination schema. Logical replication transfers table changes, but it does not automatically build the complete database structure.
Another mistake is forgetting to monitor WAL retention. If the subscriber falls behind or becomes disconnected, replication slots can cause the publisher to retain WAL files for longer than expected.
Some teams also forget to synchronize sequence values before cutover. The destination may contain all expected table records but still generate an identifier that conflicts with an existing row.
A further mistake is allowing background workers or scheduled jobs to continue writing to the source after the application has been switched. These hidden writers can cause the two databases to diverge.
Finally, do not treat a successful subscription status as proof that the entire migration is complete. You still need to verify table synchronization, data consistency, schema compatibility, sequence values, and application behavior.
Summary
PostgreSQL logical replication provides a practical way to move data to another database server while the original application continues operating. It first copies existing table data and then continuously transfers supported changes from the publisher to the subscriber.
This makes it useful for database migrations where a long maintenance window is not acceptable.
However, logical replication is only one part of a production migration strategy. You must prepare the destination schema, configure replication permissions and networking, monitor replication progress, manage WAL retention, synchronize sequences, and coordinate application writes during cutover.
The safest approach is to keep the source as the only writable database until the destination has caught up and passed your validation checks. Then stop writes, verify synchronization, move application connections, and resume traffic against the destination.
With proper preparation and testing, logical replication can significantly reduce the time your application needs to remain unavailable during a PostgreSQL database migration.
Join the conversation! Your thoughts help the community grow.