Export Data with COPY and pg_dump

You've built a beautiful PostgreSQL schema, loaded it with valuable data, and now you need to move that data somewhere else — a backup server, a data warehouse, a colleague's laptop, or a fresh local environment for testing. The moment you try to copy rows by hand or write a script that SELECTs everything into a CSV, you realize how painfully slow and error-prone that can be. PostgreSQL gives you two powerful, battle-tested tools for export: COPY for quick, targeted data dumps, and pg_dump for complete, consistent database snapshots. In this lesson, you'll learn exactly how to use both, when to reach for each, and how to avoid the gotchas that trip up even experienced developers.

The problem this lesson solves

Exporting data from PostgreSQL sounds trivial — just run a SELECT and save the results, right? But real-world exports are rarely that simple. You might need to move only a subset of rows (say, orders from the last 30 days) to a CSV file for a data scientist. You might need to clone an entire database schema plus its data to a staging server. Or you might need to restore a backup after a mishap.

Manually crafting delimited files with Python or Bash scripts is brittle: you have to handle quoting, escaping, newlines within fields, NULL values, and type conversions. And for full-database exports, relying on pg_dump is non-negotiable if you want consistency across tables and indexes. Without a proper export strategy, you'll waste hours debugging malformed files or end up with backups that can't be restored.

This lesson solves that pain by giving you two complementary tools: COPY for precise, table-level or query-level exports, and pg_dump for whole-cluster or whole-database exports. You'll learn their syntax, options, and the mental model for choosing between them.

Core concept / mental model

Think of PostgreSQL's export tools as two different vehicles for the same highway:

• COPY is like a pickup truck — perfect for hauling a specific load (a table or query result) quickly, with fine control over file format and columns. It operates at the SQL level, runs inside the database, and writes plain files (CSV, text, binary) on the database server's filesystem.
• pg_dump is like a moving van — it packs up the entire database (schema, data, indexes, constraints, even roles) into a single backup file that can be restored later with pg_restore or psql. It runs as a separate client utility and talks to the server over the network.

Key distinction: COPY exports data only (no schema), while pg_dump exports schema + data (or schema only, if you prefer). If you need to recreate the table structure, use pg_dump. If you just need rows, use COPY.

Another way to see it: COPY is for moving data out into a portable format; pg_dump is for creating a backup of the whole database object.

💡 Pro tip: Think of COPY as the SQL-level SELECT ... INTO OUTFILE (though more robust), and pg_dump as the physical backup. Both are essential in different scenarios.

How it works step by step

Using COPY for table-level export

COPY has two flavors: the server-side COPY (which writes files on the PostgreSQL server's machine) and the client-side \copy (which writes files on your local machine). In most interactive sessions, you'll use \copy because it's easier to access files.

Step-by-step with COPY:

1. Open a psql session (or use any SQL client).
2. Choose the table or query you want to export.
3. Specify the file path and format (CSV, text, binary).
4. Optionally include a WHERE clause or SELECT query to filter rows.
5. Run the command and verify output.

Using pg_dump for full-database export

pg_dump is a command-line utility. It connects to the database, reads the catalog, and produces a script or archive file.

Step-by-step with pg_dump:

1. Open a terminal.
2. Run pg_dump with connection parameters (host, port, database, user).
3. Choose output format: plain SQL (-Fp), custom archive (-Fc), directory (-Fd), or tar (-Ft).
4. Optionally dump only schema (--schema-only) or only data (--data-only).
5. Timerestore using psql (for plain) or pg_restore (for custom/directory).

Hands-on walkthrough

Let's get practical. We'll use a simple sales table with a few rows for demonstration.

Setting up a sample table

-- Create a sample sales table
CREATE TABLE sales (
    id serial PRIMARY KEY,
    product text NOT NULL,
    amount numeric(10,2) NOT NULL,
    sold_at date NOT NULL
);

INSERT INTO sales (product, amount, sold_at) VALUES
    ('Laptop', 1200.00, '2023-01-15'),
    ('Mouse', 25.50, '2023-01-16'),
    ('Keyboard', 75.00, '2023-01-17'),
    ('Monitor', 300.00, '2023-02-01');

Exporting with COPY to CSV

Server-side COPY (must be superuser or have pg_write_server_files):

COPY sales TO '/tmp/sales_export.csv' WITH (FORMAT CSV, HEADER true);

Client-side \copy (works from any psql session):

\copy sales TO '/home/user/sales_export.csv' WITH (FORMAT CSV, HEADER true)

Expected output in the CSV file:

id,product,amount,sold_at
1,Laptop,1200.00,2023-01-15
2,Mouse,25.50,2023-01-16
3,Keyboard,75.00,2023-01-17
4,Monitor,300.00,2023-02-01

Exporting a query result with COPY

You can export arbitrary query results — a huge advantage over full-table dumps.

\copy (SELECT product, amount FROM sales WHERE sold_at >= '2023-02-01') TO '/home/user/feb_sales.csv' WITH (FORMAT CSV, HEADER true)

Output:

product,amount
Monitor,300.00

Dumping an entire database with pg_dump

Open a terminal and run:

pg_dump -h localhost -U postgres -d mydb -Fc -f mydb.dump

• -h host, -U user, -d database
• -Fc custom archive format (compressed, flexible for pg_restore)

To restore this dump:

pg_restore -h localhost -U postgres -d newdb mydb.dump

For a plain SQL script (human-readable), use -Fp and restore with psql:

pg_dump -h localhost -U postgres -d mydb -Fp -f mydb.sql
psql -h localhost -U postgres -d newdb -f mydb.sql

What you get: the full schema (CREATE TABLE, indexes, constraints) plus all data, ready to replay.

Compare options / when to choose what

When to preserve data integrity: pg_dump can lock the database (with consistent snapshot) to ensure a point-in-time backup, while COPY alone might be inconsistent if tables have dependencies.

Troubleshooting & edge cases

1. Permission denied writing files with server-side COPY

• Error: must be superuser or have privileges of the pg_write_server_files role
• Fix: Grant the role or use \copy (client-side) which writes to your local filesystem and doesn't require special server privileges.

2. COPY to a file on the client from a remote server

If your client isn't on the same machine as the server, server-side COPY writes to the server's filesystem, which you might not access. Use \copy to write locally.

3. Encoding issues

• Error: invalid byte sequence for encoding "UTF8": 0x..
• Fix: Specify the encoding with ENCODING 'SQL_ASCII' or 'LATIN1' in the COPY command, or convert the file afterwards. For pg_dump, use --no-owner if you don't need to preserve ownership.

4. Restoration errors due to missing extensions/roles

When restoring a pg_dump file to another server, you may hit errors like role "someuser" does not exist. Use --no-owner or create the role first.

5. Large exports timing out

pg_dump and COPY are server-side; they won't time out, but your client might. Use -Fc for compressed dumps and avoid network timeouts by running backups from a script.

6. Exporting only specific rows with pg_dump

pg_dump doesn't support WHERE clauses directly. For filtered exports, use COPY (SELECT ...) or create a temporary view/table and dump that.

What you learned & what's next

You now have two powerful arrows in your PostgreSQL quiver: COPY for precise data exports and pg_dump for full database backups. You understand the difference between server-side and client-side file writes, how to export query results, and how to restore dumps. You've also seen how to troubleshoot common permission, encoding, and restoration issues.

What's next? In the next lesson, we'll dive into restoring data — the reverse side of this coin. You'll learn advanced pg_restore options, selective table restoration, and how to handle conflicts when data already exists. You'll also apply these export techniques to real-world scenarios like data migration and backup automation.