Loading...

中文 日本語
By: Dervish

In daily database operation and maintenance, copying a PostgreSQL database to another server is a critical high-frequency task—whether for migrating to a high-performance server, deploying a production-like environment across infrastructure, or building a redundant backup server. Mastering reliable PostgreSQL copy database to another server methods is essential for smooth DevOps and data management workflows. This article will walk you through the core prerequisites, two proven cross-server copying methods (with reusable code examples for every step), and also cover a quick same-server copy workflow as a foundational prep step—all optimized for seamless migration to a remote server.

PostgreSQL Copy Database to Another Server

Why Copy PostgreSQL Database to Another Server?

Copying a PostgreSQL database across servers is a foundational operation for modern data infrastructure, with three key business and technical use cases:

  1. Server Migration & Scaling: Move databases from on-premise legacy servers to cloud instances (AWS, Azure, GCP) or high-performance dedicated servers to support growing business traffic and data volume.
  2. Isolated Testing & Development: Replicate a production database to a remote test server for feature development, bug fixing, and performance testing—eliminating risks of modifying live production data.
  3. Disaster Recovery & Redundancy: Build a remote backup database server to ensure data recoverability in case of hardware failure, network outages, or human error on the primary server.

Prerequisites for Copying PostgreSQL Database to Another Server

Ensure these requirements are met to avoid copy/migration failures:

  1. PostgreSQL is installed on both the source server(original database) and target server (remote destination), with compatible major versions (avoid migrating from a newer PostgreSQL version to an older one).
  2. You have a PostgreSQL user account withSUPERUSER or CREATEDB/pg_dump privileges on both servers.
  3. Network connectivity is enabled between servers: the target server can access the source server, and PostgreSQL’s default port 5432is open in firewalls/security groups on both sides.
  4. SSH access is available between servers (for file transfer in the dump-restore method).

Same-Server Database Snapshot: Prep Step for Cross-Server Copying

While the core goal is copying to another server, a same-server database copy is often a necessary prep step—e.g., creating a clean snapshot of the production database on the source server (to avoid copying live, changing data) before migrating it remotely. Use the CREATE DATABASE…WITH TEMPLATE command for fast local cloning (the only same-server method you’ll need).

Step 1: Terminate Active Connections to the Source Database

Local cloning will fail if the source database has active user/ application connections—first query and end all open sessions:

  1. Log in to the PostgreSQL terminal on the source server:

bash

psql -U postgres
  1. List active connections to your source database (replace dvdrental with your database name):

SQL

SELECT pid, usename, client_addr 
FROM pg_stat_activity
WHERE datname = 'dvdrental';
  1. Terminate all active connections to the source database:

SQL

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'dvdrental';

Step 2: Create a Local Snapshot Copy

Clone the source database to a local snapshot (this clean copy is what you’ll migrate to the remote server):

SQL

CREATE DATABASE dvdrental_snapshot WITH TEMPLATE dvdrental;

This command creates an exact replica of the source database. The runtime depends on the database size—no additional tools are needed for this native PostgreSQL operation.

Step 3: Verify the Local Snapshot

Confirm the snapshot is created successfully (to ensure a valid copy for remote migration):

SQL

\l

You’ll see the snapshot database (e.g., dvdrental_snapshot) in the PostgreSQL database list—this is your stable source for copying to another server.

How to Copy a PostgreSQL Database to Another Server [2 Methods]

These are the two industry-standard methods for copying a PostgreSQL database to a remote target server—each optimized for different network and data volume scenarios, with step-by-step code examples for direct implementation.

Method 1. PostgreSQL Copy Database with Dump, Transfer and Restore

This is the most stable and widely used method for cross-server PostgreSQL copying, ideal for large databases (10GB+) or low-speed network connections between servers. It involves exporting the database to a portable SQL dump file on the source server, transferring the file to the target server via SSH, and restoring the file to a new database on the remote server. No real-time network connection is required during restoration—making it immune to network drops.

Step 1: Export the Database Snapshot to a SQL Dump File (Source Server)

On the source server’s command line (not the PostgreSQL terminal), use pg_dump (PostgreSQL’s native backup tool) to export your local snapshot (or direct source database) to a SQL file. Replace dvdrental_snapshot with your database name:

bash

pg_dump -U postgres -d dvdrental_snapshot -f dvdrental_snapshot.sql
★Parameter Breakdown:
-U postgres: Specify the PostgreSQL privileged user (replace with your custom user if needed).
-d dvdrental_snapshot: The database to export (use your local snapshot for a clean copy).
-f dvdrental_snapshot.sql: The output dump file (saves to the current directory by default).

Step 2: Transfer the Dump File to the Target Server (SSH/SCP)

Use scp (secure copy protocol) to transfer the SQL dump file from the source server to the target server—this is the most secure way to transfer database files over the network. Run this command on the source server:

bash

scp dvdrental_snapshot.sql postgres@target_server_ip:/home/postgres/
★Replace:
target_server_ip: The public/private IP address or hostname of your remote target server.
/home/postgres/:The directory on the target server where the file will be saved (use a path the PostgreSQL user has access to).

Step 3: Create an Empty Database (Target Server)

Log in to the PostgreSQL terminal on the target server and create a blank database to hold the restored data—this database name can match your source database for consistency:

bash

psql -U postgres

SQL

CREATE DATABASE dvdrental;

Step 4: Restore the Dump File to the New Database (Target Server)

On the target server’s command line (exit the PostgreSQL terminal first), use psql to restore the SQL dump file to the empty database you just created. Run this command:

bash

psql -U postgres -d dvdrental -f /home/postgres/dvdrental_snapshot.sql

The restore process will create all tables, indexes, and data exactly as they exist in the source server’s snapshot.

Method 2. PostgreSQL Copy Database with Direct Pipe Sync

For small to medium databases (under 10GB) and high-speed, stable network connections between servers (e.g., same cloud VPC, dedicated private network), use a one-line pipe command to copy the PostgreSQL database directly to another server—no intermediate SQL dump file required. This method is faster and more streamlined, as it pipes the output of pg_dump (source server) directly to psql (target server) in real time.

Run the One-Line Cross-Server Copy Command:

Execute this command on the source server (or your local machine with access to both servers)—this single command handles export, transfer, and restoration all at once:

bash

pg_dump -C -h source_server_ip -U postgres dvdrental_snapshot | psql -h target_server_ip -U postgres dvdrental
★Critical Parameter Breakdown (the -C flag is game-changing for this method):

-C: Automatically creates the target database on the remote server—no need to run CREATE DATABASEmanually (eliminates a step on the target server).

-h source_server_ip: The IP/hostname of the source server (use localhost if running the command directly on the source server).
-h target_server_ip: The IP/hostname of the remote target server.
dvdrental_snapshot: The source database (local snapshot on the source server).
dvdrental: The target database name (will be created automatically by the -C flag).

Post-Copy Verification: Ensure Data Integrity on the Target Server

After copying the database to another server, verify data consistency between the source and target servers—this step is non-negotiable to avoid missing data or corrupted tables in the remote database. Run these checks on the target server:

  1. Verify Database & Table Existence

bash

psql -U postgres -d dvdrental

Sql

-- List all tables (confirm matching table count with source) \dt
-- Check total rows in a core table (replace with your key table) SELECT COUNT(*) FROM customer;

Compare the table count and row numbers with the source server—they must match exactly.

  1. Random Data Spot-Check

Query a sample of data from a critical table to confirm values are identical to the source:

Sql

SELECT * FROM payment LIMIT 10;
  1. Test Database Functionality
  • Verify that indexes, foreign keys, and stored procedures exist and work (e.g., run a simple join query).
  • If the database is linked to an application, point the application to the remote target server and test core business functions (e.g., user login, data insertion/updation).

Alternative Way to PostgreSQL Copy Database to Another Server

While the native pg_dump/pipe methods work for basic cross-server copying, they have clear limitations: manual command-line operations are error-prone (especially for non-technical users), real-time incremental replication is not supported (you can only copy a static snapshot), and there’s no centralized monitoring or visual management. For enterprise environments—where zero downtime, real-time sync, and ease of management are critical—Information2 Software’s i2Stream is a game-changing solution that addresses these pain points and offers unique advantages over native methods.

  • Zero Command-Line Hassle: Native methods demand manual CLI operations and PostgreSQL expertise. i2Stream’s visual web console lets you set up cross-server replication in clicks, accessible to non-DBAs.
  • Real-Time Incremental Sync: Native tools only support static full snapshots. i2Stream offers both full replication and millisecond-level incremental sync, keeping target servers always up-to-date.
  • Zero Production Impact: Native pg_dump consumes heavy CPU/memory on source servers. i2Stream’s agentless design requires no production-side software deployment, ensuring business continuity.
  • Guaranteed Transaction Consistency: Native methods rely on manual data verification. i2Stream supports integrated DDL/DML sync, auto-resumes from network interruptions, and ensures transaction-level data integrity.
  • Unmatched Compatibility & Monitoring: Native tools require identical PostgreSQL versions. i2Stream supports cross-platform/version replication, plus a unified console for real-time progress, latency, and error monitoring.

    If you’d like to learn more information about PostgreSQL copy all database to another Server, please contact technical support to request a demo.

    FREE Trial for 60-Day

    Conclusion

    PostgreSQL copy database from one server to another is key for migration, testing, and disaster recovery. Choose native methods—dump-and-restore for large databases/slow networks, or direct pipe transfer for small data/fast networks—for basic, cost-free needs. For enterprise scenarios requiring real-time sync, zero production impact, and easy management, i2Stream is the superior choice.

    {{ author_info.name }}
    {{author_info.introduction || "No brief introduction for now"}}

    More Related Articles

    {{item.title}}
    {{item.excerpt}}
    Read Article
    Table of Contents:
    Stay Updated on Latest Tips
    Subscribe to our newsletter for the latest insights, news, exclusive content. You can unsubscribe at any time.
    Subscribe
    Ready to Enhance Business Data Security?
    Start a 60-day free trial or view demo to see how Info2Soft protects enterprise data.
    Free Trial Contact Sales
    Table of Contents:
    {{ country.name }}
    Please fill out the form and submit it, our customer service representative will contact you soon.
    By submitting this form, I confirm that I have read and agree to the Privacy Notice.
    {{ isSubmitting ? 'Submitting...' : 'Submit' }}