This website use cookies to help you have a superior and more admissible browsing experience on the website.
Share This Article：
Copying a PostgreSQL database or duplicating it is an essential, day-to-day task for database administrators, developers, and DevOps engineers working with PostgreSQL.
Copying or duplicating a PostgreSQL database is a core task for DBAs, developers, and DevOps teams. It’s essential for creating test/staging environments, backing up critical PostgreSQL data, migrating to new servers, and cloning production databases for safe development work.
This guide covers the key workflows for postgresql copy database, including duplicating on the same server and copying across different servers. And clears up the common confusion between full database duplication and PostgreSQL’s table-level COPY command.
Copy database in PostgreSQL and PostgreSQL duplicate database refer to the same goal: creating an exact replica of a source database (schema, tables, data, and objects) in a new target. PostgreSQL has native tools for this work, with the CREATE DATABASE WITH TEMPLATE statement for PostgreSQL copy database same server tasks and the pg_dump/psql CLI tools for cross-server copying.
How to Copy a PostgreSQL Database on the Same Server
Duplicating a PostgreSQL database on the same server is the fastest and simplest duplication method, using PostgreSQL’s native CREATE DATABASE statement with a template. This method requires just a few basic SQL commands.
Prerequisites:
- Superuser or owner privileges for the source PostgreSQL database
- No active connections to the source database (a common issue we’ll resolve below)
- Access to a PostgreSQL client (psql) or a database management tool (pgAdmin)
Basic Same-Server Duplication Command
Use the WITH TEMPLATE clause in the CREATE DATABASE statement to create an exact replica of your source database. This command clones the source’s schema, tables, data, and all associated database objects in one step.
Core Syntax:
CREATE DATABASE targetdb
WITH TEMPLATE sourcedb;
Practical Example:
To copy the sample dvdrental database to a test replica dvdrental_test:
CREATE DATABASE dvdrental_test
WITH TEMPLATE dvdrental;Copy time depends on the source database size—small databases finish instantly, while larger ones take a few minutes.
Verify the Copied Database
Confirm the replica is complete with two quick psql commands (run in your PostgreSQL client):
1. Connect to the target database:
\c targetdb;
2. List all tables to validate the schema matches the source:
\dt;
You can also run a simple SELECT query on a sample table to check that data was copied correctly. The target database is now a fully functional replica of the source.
How to Copy a PostgreSQL Database From One Server to Another
Duplicating a PostgreSQL database across different servers is critical for migrations, cloud deployments, or syncing data between on-prem and remote environments. For this task, pg_dump (to export the source database) and psql (to restore it on the target) are PostgreSQL’s native, reliable tools.
We’ll cover two methods, one for large databases/slow networks, and one for small databases/fast connections.
Prerequisites:
- pg_dump and psql are installed on the source server (included with standard PostgreSQL setups)
- Network access between servers (PostgreSQL’s default port 5432 must be open)
- Superuser privileges on both source and target PostgreSQL instances
- Enough storage on the target server for the copied database
Method 1: Copy Database via transferring dump file to another server (Best for Large Databases/Slow Connections)
This method creates a local SQL dump file first, then transfers and restores it—avoids timeouts with big datasets.
Step 1: Create a dump file of the source database
Syntax:
pg_dump -U postgres -d sourcedb -f sourcedb.sql
Example (dump the dvdrental database):
pg_dump -U postgres -d dvdrental -f dvdrental.sql
- -U postgres: Use the postgres superuser (replace with your admin user)
- -d sourcedb: Name of the source database
- -f sourcedb.sql: Save the dump to a SQL file
Step 2: Transfer the dump file to the target server
Use SCP (Secure Copy) for Linux/macOS (replace placeholders with your server details):
scp sourcedb.sql postgres@target_server_ip:/home/postgres/
Step 3: Create an empty target database
Connect to the target server’s PostgreSQL instance and run:
CREATE DATABASE targetdb;
Example:
CREATE DATABASE dvdrental_remote;
Step 4: Restore the dump file to the target database
On the target server, run:
psql -U postgres -d targetdb -f sourcedb.sql
Example:
psql -U postgres -d dvdrental_remote -f dvdrental.sql
Tip: Add -O to pg_dump (e.g., pg_dump -U postgres -O -d dvdrental -f dvdrental.sql) to skip owner info—prevents permission errors on the target.
Method 2: Direct Pipe Copy (Best for Small Databases/Fast Connections)
This one-line command skips the local dump file, piping the dump directly to the target server—faster for small databases with stable networks.
Core Syntax:
pg_dump -C -h source_server_ip -U postgres sourcedb | psql -h target_server_ip -U postgres targetdb
Example (copy dvdrental from localhost to a remote server):
pg_dump -C -h 192.168.1.100 -U postgres dvdrental | psql -h 192.168.1.200 -U postgres dvdrental_remote
- -C: Automatically creates the target database (no manual CREATE DATABASE needed)
- -h: Specify the IP of the source/target server
- Pipe (|): Sends the dump output directly to the target’s psql command
4. Verify the Cross-Server Copied Database
On the target server, confirm the replica is complete:
1. Connect to the target database: \c targetdb;
\c targetdb;
2. List tables to check schema matches the source:
\dt;
3. Validate data integrity with a sample query:
SELECT COUNT(*) FROM customer;
Compare the result to the source database—they should match exactly.
PostgreSQL COPY Command vs. Full Database Copying
A major mistake for new PostgreSQL users is confusing the built-in COPY command with full database duplication—these are two entirely different tools for distinct tasks. Understanding this difference is key to avoiding wasted time and failed copy attempts.
What the PostgreSQL COPY Command Actually Does?
The official PostgreSQL COPY command only moves data between a single table and a file (or standard input/output). It does not copy an entire database’s schema, relationships, indexes, or stored procedures—only the rows of one table.
Basic COPY Command Examples
Export data from a single table to a file:
— Copy data from the “customer” table to a CSV file
COPY customer TO '/tmp/customer_data.csv' (FORMAT csv, HEADER);
Import data from a file into a table:
— Import CSV data into the “customer” table
COPY customer FROM '/tmp/customer_data.csv' (FORMAT csv, HEADER);
Use cases for COPY: Exporting a single table for reporting, importing bulk data into one table, or migrating table-specific data—never full database duplication.
Full Database Copying:
In contrast, full database copying (the focus of this guide) replicates an entire PostgreSQL database—including all tables, schemas, indexes, and objects. The tools for this are:
- CREATE DATABASE WITH TEMPLATE: For same-server duplication
- pg_dump/psql: For cross-server duplication
These tools handle the complete database structure, not just individual table data.
Automate PostgreSQL Database Migration with i2Stream
Manual CLI-based PostgreSQL copy and migration works for simple tasks, but it’s slow, error-prone, and risky for production databases. i2Stream is a professional replication and migration tool built to streamline PostgreSQL database migration and PostgreSQL copy database workflows.
It supports both PostgreSQL copy database same server and cross-server scenarios, with enterprise-grade stability and near-zero downtime.
Key Benefits for PostgreSQL Users:
- One-click database duplication: No more pg_dump or psql commands.
- Near-zero downtime migration for live production databases.
- Full replication: schema, data, indexes, constraints, and stored procedures.
- High-speed sync for large PostgreSQL databases.
- Agentless, no performance impact on the source database.
- Built-in data validation to ensure 100% consistency.
- Supports both full and incremental data synchronization.
i2Stream turns complex manual database copying into a simple, reliable workflow. It is the ideal solution for DBAs, DevOps, and development teams handling postgresql duplicate database tasks at scale.
You can click the button below to get a free trial.
FREE Trial for 60-Day
Secure Download
Troubleshooting Common PostgreSQL Copy Database Errors
Even with the right commands, you may run into errors when copying a PostgreSQL database. Below are the most common issues and quick fixes.
1. Error: source database is being accessed by other users
This is the most common error when using CREATE DATABASE WITH TEMPLATE on the same server.
- Cause: Active connections are still using the source database.
- Fix: Terminate idle connections first, then retry the copy command.
Fix it by first checking active connections, then terminating them.
Step 1: List active connections to the source database
Syntax:
SELECT pid, usename, client_addr
FROM pg_stat_activity
WHERE datname = 'sourcedb';
Step 2: Terminate active connections
Syntax:
SELECT pg_terminate_backend (pid)
FROM pg_stat_activity
WHERE datname = 'sourcedb';
Rerun the original CREATE DATABASE WITH TEMPLATE command—this time it will execute successfully.
2. Error: Permission denied
You may see this during pg_dump, psql, or database creation.
Cause: Your user lacks superuser or database ownership rights.
Fix: Run commands with a superuser (e.g., postgres), or grant proper privileges.
3. Error: Restore fails on target database
Cause: The target database does not exist, or already contains data.
Fix:
- Create an empty database first.
- Do not restore into a database with existing tables or schemas.
4. Error: Network timeout during cross-server copy
Cause: Unstable network or large database size.
Fix:
- Avoid the direct pipe method.
- Use pg_dump to create a local file first, then transfer and restore.
5. Error: Missing data or tables after copying
Cause: Incomplete dump, interrupted transfer, or active writes during copying.
Fix:
- Recreate the dump file.
- Verify the file transfer fully completes.
- Avoid writing to the source database during the copy process.
Best Practices for PostgreSQL Database Duplication
Following best practices ensures your PostgreSQL copy database and PostgreSQL duplicate database tasks are fast, safe, and error-free.
1. Always Back Up Before Copying
Please don’t copy a database without first backing up the source. Create a PostgreSQL backup to protect against data loss if the copy process fails.
2. Minimize Source Database Load
- For same-server copies: Avoid copying during peak hours (reduces performance impact).
- For cross-server copies: Use incremental sync (via i2Stream) instead of full dumps for large databases.
- Never run heavy queries on the source while copying—this slows down both tasks.
3. Handle Permissions Properly
Use the -O flag with pg_dump to omit owner information (prevents permission errors on the target).
pg_dump -U postgres -O -d sourcedb -f sourcedb.sql
Grant only necessary privileges to users performing copy tasks (avoid overusing superuser access).
4. Optimize for Large Databases
Compress dump files to save storage and speed up transfers:
pg_dump -U postgres -d sourcedb | gzip > sourcedb.sql.gz
Use i2Stream for large databases—its high-speed sync avoids timeouts and reduces downtime.
5. Verify the Copied Database Immediately
After copying, always validate the replica:
1. Check table counts (match source and target).
2. Run sample SELECT queries to confirm data integrity.
3. Verify indexes, constraints, and stored procedures work as expected.
6. Automate Repetitive Tasks
For frequent postgresql copy database same server or cross-server tasks:
- Use shell scripts to automate pg_dump/psql workflows.
- Use i2Stream’s scheduling feature to run copies at off-peak times (no manual intervention needed).
7. Clean Up Temporary Files
After cross-server copies, delete dump files from both source and target servers to free up storage—unless you need them for backup.
These practices reduce errors, protect data, and make your PostgreSQL database duplication workflows reliable at scale.
Conclusion
Copying or duplicating a PostgreSQL database doesn’t have to be complex. Whether you need to copy a PostgreSQL database to the same server or cross-server migration (copy database in PostgreSQL), PostgreSQL’s native tools—CREATE DATABASE WITH TEMPLATE, pg_dump, and psql—get the job done for simple tasks.
For production environments, large databases, or frequent duplication tasks, manual CLI methods fall short. i2Stream automates the entire process, delivering near-zero downtime, high-speed sync, and built-in validation.
Frequently Asked Questions (FAQs)
Q1: Can I copy a PostgreSQL database while it’s in use?
It’s not recommended. Active writes to the source database during copying can cause data corruption or missing data. For same-server copies, terminate active connections first (see Section 6.1). For production databases, use i2Stream’s near-zero downtime migration to avoid disruption.
Q2: How do I copy a PostgreSQL database with all users and privileges?
Use pg_dumpall instead of pg_dump. This tool exports all databases, roles, and privileges. For a single database’s users, add the-x flag to pg_dump to include privileges, or use i2Stream to auto-replicate all user permissions.
Q3: Does the PostgreSQL COPY command work for full database copying?
No. The COPY command only handles single-table data (export/import between a table and a file). It cannot copy schemas, indexes, or entire databases—use CREATE DATABASE WITH TEMPLATE or pg_dump/psql for full PostgreSQL copy database tasks.
Q4: Is i2Stream better than manualpg_dump/psql for cross-server migration?
Yes, for most enterprise use cases. i2Stream automates the entire process (no manual commands), supports incremental sync (faster for large databases), ensures near-zero downtime, and includes built-in data validation—all things manual methods lack. Manual commands work for small, simple migrations.
Q5: How do I copy a PostgreSQL database on Windows?
Use the same CLI commands as Linux/macOS. Open PowerShell or Command Prompt, navigate to the PostgreSQL bin directory (e.g., C:\Program Files\PostgreSQL\15\bin), then run pg_dump and psql commands.
Q6: Why is my copied database missing tables or data?
Common causes include interrupted dump/transfer, active writes during copying, or incomplete dump commands. Fixes: Recreate the dump file, ensure the transfer completes, avoid writing to the source during copying, and verify the copy immediately.
{{ author_info.name }}
{{author_info.introduction || "No brief introduction for now"}}
More Related Articles
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.
Ready to Enhance Business Data Security?
Start a 60-day free trial or view demo to see how Info2Soft protects enterprise data.
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.
