How to Copy Table Between Databases in PostgreSQL

How to Copy Table Between Databases in PostgreSQL

Copy a table between PostgreSQL databases with pg_dump | psql, COPY pipelines, or the postgres_fdw extension. Choose the tool that matches your privileges and downtime limits.

Which method is fastest for most users?

Streaming pg_dump into psql is the quickest one-liner when both databases live on the same server. It copies the table definition and data in one step without temp files.

How do I copy a table with pg_dump & psql?

Run pg_dump against the source DB, pipe it straight into psql connected to the target DB, and include the -t schema.table flag to limit the dump to a single table.

# Linux/macOS
pg_dump -U src_user -d src_db -t public.orders | \
psql -U dst_user -d dst_db

How can I move a table without shell access?

Use the postgres_fdw extension inside the destination database. Import the remote table as a foreign table, then create a local copy with CREATE TABLE AS.

-- target_db
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER src_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'src_db');
CREATE USER MAPPING FOR CURRENT_USER SERVER src_srv OPTIONS (user 'src_user');
IMPORT FOREIGN SCHEMA public LIMIT TO (orders) FROM SERVER src_srv INTO foreign_schema;
CREATE TABLE public.orders AS TABLE foreign_schema.orders;

What about databases on different servers?

The same pg_dump | psql pipeline works by adding -h flags for each host. Alternatively, run \COPY with STDOUT/STDIN to move data through your client machine.

psql -h src_host -U src_user -d src_db -c "\COPY public.orders TO STDOUT" | \
psql -h dst_host -U dst_user -d dst_db -c "CREATE TABLE IF NOT EXISTS public.orders ( ... ); \COPY public.orders FROM STDIN"

How do I avoid blocking writes on the source table?

Add --no-synchronized-snapshots in older versions or run pg_dump from PostgreSQL 13+ which uses the "snapshot=export" default to keep the dump consistent without holding locks.

Best practices for reliable transfers

Include --column-inserts when table has generated columns, verify row counts on both sides, and wrap the import inside a transaction if the table isn’t huge.

Can I copy only the structure?

Yes. Add -s (schema-only) to pg_dump or use CREATE TABLE LIKE over postgres_fdw, then populate later.

How do I keep indexes and constraints?

Piping pg_dump keeps everything. With postgres_fdw, run CREATE TABLE new AS TABLE foreign … INCLUDING ALL on PostgreSQL 15+ or add indexes manually.