How to Copy a Table Between Databases in PostgreSQL

Why would I copy a table between PostgreSQL databases?

Moving a single table to another database is common when separating tenants, archiving data, or migrating workloads without moving the whole cluster.

What is the quickest way to copy a table between two ParadeDB databases?

Use the built-in COPY command to export the source table to a file, then run COPY again in the target database to import.This approach avoids extra extensions and leverages PostgreSQL’s high-throughput bulk I/O.

Step 1 – Export with COPY TO

Connect to the source ParadeDB database and stream the table to a compressed file:

\! gzip -c <(\copy Customers TO STDOUT (FORMAT csv, HEADER, DELIMITER ',')) > customers.csv.gz

Step 2 – Transfer the file

Move customers.csv.gz to the destination server with scp, cloud storage, or a shared volume.

Step 3 – Create the table on the target

Run the DDL that matches the source schema:

CREATE TABLE Customers (id INT, name TEXT, email TEXT, created_at TIMESTAMPTZ);

Step 4 – Import with COPY FROM

Decompress and load in one step:

gunzip -c customers.csv.gz | psql -d target_db -c "\copy Customers FROM STDIN WITH (FORMAT csv, HEADER, DELIMITER ',')"

Can I skip files and copy directly?

Yes.Use dblink or postgres_fdw to stream rows directly with INSERT INTO ... SELECT.This eliminates disk I/O at the cost of some setup.

Direct copy via postgres_fdw

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER src_db FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'src', dbname 'paradedb');
CREATE USER MAPPING FOR CURRENT_USER SERVER src_db OPTIONS (user 'app', password 'secret');
IMPORT FOREIGN SCHEMA public LIMIT TO (Customers) FROM SERVER src_db INTO public;
INSERT INTO Customers SELECT * FROM Customers_foreign;

Which method is fastest?

COPY is fastest for large tables (>1 GB) because it bypasses row-level overhead.postgres_fdw is convenient for frequent, smaller transfers or when you need WHERE filtering during the copy.

Best practices for ParadeDB table copies

1. Disable indexes and constraints on the target until after the import.
2. Use FORMAT binary for even faster loads when both databases have identical versions.
3.Wrap steps in a transaction to keep the target consistent.

Common mistakes and fixes

Wrong delimiter in CSV. Always specify DELIMITER to match the export.
Timestamp mismatch. Use TIMESTAMPTZ to avoid timezone drift.

Need a GUI instead?

Galaxy’s desktop SQL editor lets you run COPY with templates and AI-generated scripts for one-click table transfers between ParadeDB databases.

.