Database Replication | September 1, 2026

How to Sync Two PostgreSQL Databases [5 Proven Methods]

Need to sync two PostgreSQL databases? This guide covers 5 proven methods, from pg_dump and logical replication to pgsync, bidirectional replication, and physical replication, with practical tips for choosing, verifying, and managing each approach.

Get 60-Day FREE Trial

No credit card required for trial registration.

By: Emma

What Kind of Sync Do You Actually Need?

Before you run any replication command, identify your data requirements and infrastructure limits. The wrong method can cause network latency, performance issues, or even data loss.

Evaluate your setup against these core dimensions to find the right way to keep PostgreSQL databases in sync:

One-time vs. continuous sync: One-time fits developer environments or migrations; continuous keeps data updated in real time.
One-way vs. bidirectional sync: One-way moves data to a replica target; bidirectional allows writes on both sides but needs conflict resolution.
Full database vs. specific tables or schemas: Physical replication copies the entire cluster byte-for-byte; logical methods target specific tables or schemas.
Same version vs. cross-version or cross-platform: Physical replication needs matching PostgreSQL versions; logical replication or a sync tool handles cross-version setups.

Use this simple framework to quickly select the best approach for your workload and engineering requirements:

If your goal is…	And your constraint is…	Recommended approach
Developer database clone	Off‑hours execution	Method 1: pg_dump / pg_restore
Real‑time reporting or warehousing	One‑way sync only	Method 2: Logical Replication
Staging data refreshes	Specific tables, fast speed	Method 3: pgsync
Multi‑primary distributed apps	Active‑active writes	Method 4: pglogical / Bidirectional Sync
High availability or disaster recovery	Identical Postgres versions	Method 5: Physical Streaming Replication

5 Methods to Sync Two PostgreSQL Databases

Depending on your data size, network setup, and uptime requirements, different sync strategies fit different workflows. DBAs and backend engineers typically choose from these five practical methods.

Method 1: pg_dump + pg_restore (One-Time or Occasional Sync)

This method uses native PostgreSQL utilities to export the schema and data from a source database and restore it to a target database.

It gives you a clean, consistent snapshot when continuous replication isn’t needed.

When to use it

This method works well for:

Seeding development environments
Running weekly staging refreshes
Migrating small to medium databases

It’s a good fit when you can tolerate a short data gap during the export and import process.

Command walkthrough

Run the following command to export the source database to a custom-format dump file:

pg_dump -Fc -h source_host -U db_user -d source_db -f source_backup.dump

The -Fc flag outputs a compressed, custom-format archive. Once the dump file is ready, restore it to the target database:

pg_restore -v -c --no-owner --no-privileges -h target_host -U db_user -d target_db source_backup.dump

The -c flag drops existing database objects before recreating them. The --no-owner and --no-privileges flags prevent permission errors when roles differ between the source and target environments.

Limitations

This method doesn’t support real-time sync. Each run copies the full database or selected schemas from scratch. For databases in the hundreds of gigabytes, export and import times can grow significantly and put heavy load on CPU and disk.

Note: If pg_restore runs against a database with active connections, the drop commands can fail. Terminate active connections on the target database before restoring.

Pro Tip

For large databases, the directory format (-Fd) is usually a better choice than the custom format (-Fc). It supports parallel jobs through the -j flag, which splits the dump or restore across multiple tables at once and can cut total time significantly on multi-core systems.

Method 2: Native Logical Replication (Continuous, Real-Time, One-Way)

PostgreSQL logical replication uses a publish-and-subscribe model. Unlike physical replication, which copies the entire database byte-for-byte, logical replication streams individual data changes on a per-table basis.

This relies on PostgreSQL reading its Write-Ahead Log (WAL), a sequential record of transactional changes made before they’re committed to disk.

The system decodes these WAL entries into logical operations (inserts, updates, deletes) and streams them to the subscriber.

Requirements

Your primary database should run PostgreSQL 10 or higher. You’ll also need to adjust a few settings in postgresql.conf on the publisher server:

wal_level: Set to logical to write the extra metadata required for logical decoding.
max_replication_slots: Should match or exceed the number of subscriptions you plan to connect.
max_wal_senders: Should be high enough to cover active replication connections.

Note: Changing wal_level to logical requires a database restart in most PostgreSQL versions, which can briefly disrupt client connections.

Publisher and subscriber syntax

You’ll need to manually copy the schema from the publisher to the subscriber first. Logical replication doesn’t replicate schema definitions or structural changes automatically.

Once the table structures match, connect to the source database and create a publication:

CREATE PUBLICATION my_db_pub FOR TABLE customers, orders;

To replicate every table in the database instead, create a global publication:

CREATE PUBLICATION my_all_pub FOR ALL TABLES;

Then connect to the subscriber database and create the subscription to start streaming changes:

CREATE SUBSCRIPTION my_db_sub
CONNECTION 'host=publisher_host port=5432 dbname=source_db user=repl_user password=repl_pass'
PUBLICATION my_db_pub;

Use cases

Logical replication works well for consolidations, real-time reporting dashboards, and zero-downtime major version upgrades.

It lets engineers aggregate data from multiple source servers into one unified database without copying staging or test tables.

Limitations

DDL changes: Schema changes like ALTER TABLE aren’t replicated automatically. Run matching DDL commands manually on both databases.
Primary key requirement: Subscriber tables need a primary key or unique index. Without one, UPDATE and DELETE operations on the publisher won’t replicate.
Sequences: Auto-incrementing ID values aren’t replicated in real time. If you switch applications over to the subscriber, update sequence values manually to avoid duplicate key conflicts.

Method 3: pgsync (Fast, Flexible Open-Source Tool)

pgsync is an open-source command-line tool built specifically to sync data between two PostgreSQL databases. Unlike slow dumps or rigid logical replication setups, it’s designed to be fast, customizable, and safe by default.

This PostgreSQL database synchronization tool works through high-speed batch transfers rather than a permanent streaming replication loop, making it a lightweight option for ad-hoc tasks.

What it is

Developed by Andrew Kane, pgsync transfers table data in parallel over a secure connection. It handles minor schema differences on the target end and lets you sync specific tables, rows, or related record groups instead of the whole database.

Installation

pgsync is built on Ruby, so you can install it with RubyGems or Homebrew:

gem install pgsync

brew install pgsync

After installation, run the setup command inside your project folder to generate a configuration file:

pgsync --init

This creates a .pgsync.yml file, where you define your source and destination connections, exclude specific columns or tables, and set other defaults.

Key commands

Run pgsync on its own to sync all tables defined in your configuration file. To sync only specific tables:

pgsync table1,table2

The tool also supports wildcards, useful for syncing tables by naming pattern:

pgsync "orders_*"

To copy only rows matching a condition while keeping existing destination rows intact, add a query filter with --preserve:

pgsync products "where store_id = 5" --preserve

Without --preserve, matching rows on the destination are overwritten.

Safety features

To prevent accidental overwrites of a live environment, pgsync limits the default destination host to localhost or 127.0.0.1. To sync to a remote database, add to_safe: true to your .pgsync.yml file first.

You can also exclude sensitive columns, like passwords or email addresses, from ever leaving the source database by listing them in your configuration file.

When to choose it over other methods

pgsync is a good fit when you need a developer-friendly tool to refresh staging environments with real production data. It’s faster than pg_dump when you only need a subset of tables or rows, and it skips the setup overhead of logical replication slots.

Method 4: pglogical / Native Bidirectional Replication

Bidirectional replication allows writes on both servers, keeping them in sync by propagating changes in both directions. This isn’t a built-in PostgreSQL feature. Setting it up requires an extension like pglogical.

Why bidirectional sync is harder than one-way

In a one-way setup, the primary database is the single source of truth. With bidirectional replication, both databases accept writes, which opens the door to data divergence.

If two users update the same row on different servers at nearly the same moment, the system faces a write conflict. Handling these conflicts, avoiding replication loops, and keeping auto-incrementing ID sequences aligned across nodes are real engineering challenges.

Setup walkthrough

Install the pglogical extension on both servers. Update postgresql.conf on both to load the library and set the replication level:

wal_level = logical
shared_preload_libraries = 'pglogical'

Restart PostgreSQL on both servers to apply the change. Then connect to both databases and load the extension:

CREATE EXTENSION pglogical;

On Server A, create the first node:

SELECT pglogical.create_node(
    node_name := 'node_a',
    dsn := 'host=server_a_ip port=5432 dbname=my_db user=repl_user password=pass'
);

Add your tables to the default replication set:

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

On Server B, create the second node the same way:

SELECT pglogical.create_node(
    node_name := 'node_b',
    dsn := 'host=server_b_ip port=5432 dbname=my_db user=repl_user password=pass'
);

Finally, create subscriptions in both directions: subscribe Server B to Server A, then subscribe Server A to Server B. This reciprocal setup establishes the two-way flow, and pglogical handles loop-back prevention automatically.

Note: Sequences are replicated separately from tables in pglogical. Add them explicitly to the replication set, or auto-incrementing IDs can collide across nodes.

Conflict-handling strategies

When write conflicts occur, pglogical decides which update wins based on the pglogical.conflict_resolution parameter:

last_update_wins: Keeps the version with the most recent commit timestamp, overwriting the older write.
first_update_wins: Keeps the first write applied and discards the incoming conflicting change.
error: Stops replication and raises an alert so engineers can resolve the conflict manually.

Tip: Keep in mind this setting adds some performance overhead, so it’s worth testing before enabling it in production.

Best practices for avoiding conflicts

To keep a bidirectional setup stable, design your schema with multi-master writes in mind:

Avoid standard sequential keys: Auto-incrementing integers can cause ID collisions across nodes. UUIDs guarantee uniqueness instead.
Segment your writes: Route specific customer segments or regions to specific database nodes through your application’s load balancer, reducing the chance of conflicting writes on the same row.
Minimize schema updates: pglogical doesn’t replicate DDL changes automatically, so plan and coordinate schema migrations during maintenance windows.

Method 5: Streaming/Physical Replication & Managed Cloud Options (HA/DR)

Physical replication copies the exact byte-level structure of a database. It’s the industry standard for real-time read replicas, high availability (HA), and disaster recovery (DR).

Physical replication basics

Instead of streaming logical operations like individual table inserts, physical replication streams raw write-ahead log (WAL) data from a primary server to a standby server. The standby receives these WAL changes and applies them directly to its own database files.

Because it copies the exact data layout on disk, the standby is a byte-for-byte clone of the primary. Setting this up on self-managed servers typically involves running pg_basebackup to copy the initial data, then configuring replication settings like wal_level = replica in postgresql.conf.

When physical replication is the wrong tool

Physical replication is highly performant, but it isn’t suitable for every sync project. Avoid it if you need to:

Sync specific tables: Physical replication works at the system level and copies the entire database cluster, including every schema and table.
Write to the secondary database: The standby is strictly read-only and can’t accept write queries, not even to temporary tables.
Replicate across different PostgreSQL versions: The primary and standby need to run the same major PostgreSQL version.
Replicate across different operating systems: Source and target should use compatible CPU architectures and matching OS-level libraries.

Azure and AWS managed options overview

If your databases run in the cloud, you can skip managing physical replication manually. Both major providers offer managed services for this.

AWS offers AWS Database Migration Service (DMS), which handles both homogeneous and heterogeneous database migrations with minimal downtime. For ongoing replication within AWS, RDS for PostgreSQL read replicas use the same physical streaming replication under the hood, including cross-Region options for disaster recovery.

Azure Database for PostgreSQL Flexible Server offers read replicas for both same-region and cross-region setups. Cross-region replicas help serve read-only queries closer to users while providing a geographic failover option.

Physical vs. logical replication comparison

The table below summarizes the core differences between these two replication methods:

Feature / Capability	Physical Replication	Logical Replication
Replication granularity	Entire server instance	Specific tables or databases
Target database state	Read‑only	Read‑write
Cross‑version support	No (versions must match)	Yes (e.g., PostgreSQL 14 to 17)
DDL schema changes	Replicated automatically	Not replicated
Write performance impact	Very low overhead	Moderate overhead
Primary use cases	High availability, DR	Analytics, reporting, migrations

How to Verify If the Two PostgreSQL Databases Are Synced

Setting up a sync method is only half the job. You should also confirm data is streaming correctly, check for replication lag, and test that your secondary database stays updated.

Check the Subscription Status

If you’re running logical replication, check your subscriber node’s status. Run this query on the subscriber database to see the health of your replication workers:

SELECT subid, subname, pid, received_lsn, latest_end_lsn 
FROM pg_stat_subscription;

This view returns diagnostic details for your active logical replication workers:

pid: The process ID of the replication worker. If this is null, the subscription is disabled or has an active connection failure.
received_lsn: The latest Log Sequence Number (LSN) the subscriber has received from the publisher, marking its position in the WAL data stream.
latest_end_lsn: The LSN position up to which the subscriber has finished applying changes. If received_lsn and latest_end_lsn match, the subscriber is caught up with the incoming WAL stream.

Check Replication Activity on the Publisher

To monitor replication from the sender side, query the publisher database for active outgoing streams. Run this on the primary server:

SELECT application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn 
FROM pg_stat_replication;

This view shows one row per connected standby or subscription. The state field should read streaming for healthy connections.

To calculate replication lag in bytes, use the pg_wal_lsn_diff() function to find the numeric difference between sent_lsn and replay_lsn.

Test an INSERT

For a direct functional test, insert a mock record into the source database and confirm it arrives on the target. Run this on the primary database:

INSERT INTO customers (name, email) 
VALUES ('Alice', 'alice@example.com');

Then connect to the secondary database and check for the record:

SELECT * FROM customers 
WHERE email = 'alice@example.com';

If the connection is configured correctly, the record should appear in the destination table within milliseconds.

Test UPDATE and DELETE Operations

Testing an insert is a good start, but you should also test UPDATE and DELETE operations. Logical replication handles these differently than inserts, so this step matters.

If a replicated table on the subscriber lacks a primary key or a valid unique index, incoming updates and deletes will fail and can halt the replication worker. Run a quick update test and check your PostgreSQL error logs to confirm changes propagate without errors.

Best Practices Checklist Before You Sync

Before moving real database traffic or starting synchronization, follow these standard safeguards. This checklist helps you avoid common sync mistakes and reduce risk to database performance.

Always test in a non-production environment first: Simulate the full sync process on sandbox or staging servers before running commands on your production cluster. This is the best way to catch mismatched tables, permission issues, or resource constraints early.
Monitor replication lag: Lag can grow during high-traffic hours, consuming storage and dragging down performance. Set up active monitoring alerts using pg_stat_replication or logical slot metrics to catch delays before they affect downstream applications.
Secure your connection: Synchronization often sends sensitive data across networks. Secure these data paths with an SSH tunnel, a private VPN, or a connection string that requires full SSL verification with sslmode=verify-full.
Plan for schema drift: If you add, remove, or modify columns on the primary database, logical replication keeps running but can fail when schema-dependent writes come through. Coordinate schema updates on both sides before running sync tasks.
Have a rollback plan: Even routine sync tasks can trigger locking issues or run out of disk space. Prepare a rollback checklist that covers dropping replication slots, stopping running sync processes, and restoring client traffic to its original state.

Simplify PostgreSQL Database Synchronization with i2Stream

Manually managing logical replication, conflict resolution, and replication lag across multiple PostgreSQL databases takes real engineering time. Every method in this guide comes with its own setup steps, monitoring queries, and edge cases to track.

i2Stream is an enterprise-grade database replication solution that handles real-time sync, disaster recovery, migration, and integration across homogeneous and heterogeneous databases, including PostgreSQL. It’s built on log parsing and stream data processing, so it captures changes at the source without adding load to your production database.

Real-time, low-latency sync: i2Stream achieves millisecond-level sync in high-concurrency environments while keeping transaction-level consistency, with integrated DDL and DML sync so schema changes don’t need separate manual handling.
Agentless architecture: There’s no need to install software on the production system, so replication runs with zero impact on database performance.
Built-in data integrity checks: i2Stream automates validation through MD5 checksum comparisons, with visual drift analysis and one-click repair when discrepancies show up.
Flexible topology support: It supports one-to-one, one-to-many, many-to-one, and cascading replication, useful for teams consolidating data from multiple PostgreSQL sources or distributing it across regions.
Visual management console: A web-based interface shows sync status, throughput, and latency in real time, so you don’t have to rely on manual pg_stat_replication queries to check health.

For teams also managing disaster recovery across data centers, i2Backup complements i2Stream by handling backup and recovery for the broader infrastructure these databases run on.

FREE Trial for 60-Day

FAQ

Q1: What is the easiest way to sync two PostgreSQL databases?

pg_dump and pg_restore work best for a one-time sync. For ongoing sync, native logical replication is simpler to set up than bidirectional replication.

Q2: Can I sync PostgreSQL databases across different major versions?

Yes. Physical replication requires matching versions, but logical replication and tools like pgsync support cross-version sync.

Q3: Does logical replication sync schema changes automatically?

No. It only streams data changes. Schema changes like ALTER TABLE need to be applied manually on both databases.

Q4: How do I check if my PostgreSQL databases are in sync?

Query pg_stat_subscription on the subscriber and pg_stat_replication on the publisher to check status and lag. A test INSERT, UPDATE, or DELETE also confirms sync is working.

Q5: What causes replication to fail on UPDATE or DELETE operations?

A missing primary key or unique index on the subscriber table. Without one, logical replication can’t identify which row to update or delete, and the worker stops with an error.

Q6: Is bidirectional replication worth the added complexity?

Only for multi-master, active-active setups. It introduces write conflicts and sequence collisions that need active management. For one-way sync, logical or physical replication is simpler to maintain.

Conclusion

Syncing two PostgreSQL databases comes down to matching the method to the job. pg_dump and pg_restore work for one-time transfers, logical replication handles continuous one-way sync, and physical replication remains the standard for high availability and disaster recovery. Bidirectional setups and tools like pgsync fill in the gaps for multi-master writes or fast, flexible staging refreshes.

Whichever method you choose, verifying the sync and monitoring replication lag matter as much as the initial setup. For teams managing this across multiple databases or environments, Info2soft offers tools like i2Stream to simplify ongoing replication and reduce the manual overhead involved.

Emma

Emma is the bridge between complex engineering and the people who need it. As a content creator at Info2soft, she spends her days translating "tech-speak" into clear, actionable stories about data resilience. She’s not just documenting software; she's uncovering how data replication and recovery actually change the way businesses run.