Import Data with COPY & pg_restore

You've built your PostgreSQL database, defined your schemas, and maybe even populated a few rows manually with INSERT. But the moment you face a real load — a CSV with a million log entries, a nightly dump from a legacy system, or a full database migration — you'll find that naive inserts grind your whole operation to a halt, and manual import becomes a nightmare of file I/O and error handling. This lesson is here to end that pain. You'll learn how to use PostgreSQL's two most powerful import tools — the COPY command for lightning-fast bulk loading from files, and pg_restore for restoring full backups — and you'll know exactly when to reach for each, so you can move data into PostgreSQL quickly, safely, and with confidence.

The problem this lesson solves

Importing data is rarely as simple as reading a file and inserting rows. The classic approach — writing a loop that issues thousands of individual INSERT statements — has two fatal flaws. First, performance: each INSERT commits its own transaction (unless you wrap them in an explicit transaction), which means you're paying for a disk sync on every single row. Second, complexity: your script must handle data type conversions, NULLs, quoting, and errors row-by-row, which leads to slow, buggy code. Meanwhile, if you're importing from another database, you might need to move the entire set of tables, indexes, constraints, and data — not just a flat file. The problem is not just getting data in; it's getting it in efficiently, without losing anything, and without breaking your database.

Consider a scenario: you receive a 500 MB CSV file with hourly IoT sensor readings. Using INSERT, you might be looking at hours of runtime, whereas COPY can ingest it in minutes, often at speeds of thousands of rows per second. Similarly, restoring a full database dump with pg_restore can parallelize the load across multiple cores, cutting downtime drastically compared to re-executing a giant SQL script. This lesson directly solves that performance and complexity pain by giving you a tool for each job: COPY for flat files with a simple schema, and pg_restore for entire database snapshots.

Core concept / mental model

Think of COPY and pg_restore as two different types of transport vehicles. COPY is a courier van — it moves a specific, flat payload (a delimited text file) directly into one table. It's fast, efficient, and perfect for a single-point delivery. pg_restore is a moving van — it unpacks an entire house (all tables, indexes, constraints, even custom data types) from a backup archive, arranging everything back in its original rooms. It knows about the relationships between objects and ensures everything lands in the right place.

Here's a quick definitional map:

• COPY (or \copy in psql): Reads or writes a file from a single table to or from a text format (CSV, etc.) on the client (with \copy) or server (with COPY). It works row-by-row but is highly optimized, bypassing SQL-level overhead.
• pg_dump: A utility that creates a logical backup of a database (schema + data) in a proprietary, platform-independent format. The output can be plain SQL, a custom archive, or a tar.
• pg_restore: The twin of pg_dump — it reads the custom or tar archive produced by pg_dump and recreates the database objects and data. It can also be selective, letting you restore just certain tables.

Why do you need both? Because they serve different purposes. COPY is for data loading — you have a clean file, you need it inside a table fast. pg_restore is for database recovery or migration — you have a backup, you need the whole thing back (or parts of it). In practice, you might use COPY to load staging tables, and then use SQL INSERT ... SELECT to transform the data into final tables. For full database imports, pg_restore is your safest bet, especially with --jobs for parallelism.

How it works step by step

Let's deconstruct both tools into a logical sequence.

How COPY works

1. Prepare your data file — Ensure your CSV (or other delimited) file has a consistent structure: column order matches the table, delimiter matches (often comma, tab, or pipe), and quoting is correct. This means handling commas within values using quotes, and defining your NULL representation (default \N).
2. Choose your interface — Use \copy from psql if you're working on a remote client; \copy reads the file locally and sends data to the server. Use server-side COPY when the file is on the same machine as the PostgreSQL server (e.g., /tmp/data.csv).
3. Match columns — If your file doesn't have exactly the same columns as the table, you have options: use the COPY table (col1, col3) FROM ... syntax to select only specific columns, or use the FREEZE and FORMAT options to control behavior.
4. Execute the import — Run COPY with FROM for import, and TO for export. The command runs inside a transaction. If it errors, the whole statement rolls back, keeping the table unchanged.
5. Handle errors — By default, COPY stops at the first error. For faster debugging, use ON_ERROR option (available in PostgreSQL 12+) to control whether to stop, skip, or log, but be careful — skipping rows can silently lose data.

How pg_restore works

1. Generate a dump — Before restoring, you need a valid dump file created with pg_dump. Use custom format (-Fc) for pg_restore because it supports parallel restore and selective restoration.
2. Create an empty database — pg_restore will not create the target database; you must create it first (CREATE DATABASE newdb;).
3. Run pg_restore — Use the --dbname option to connect to that database, --jobs to spawn parallel workers, and optionally --clean to drop existing objects before recreating.
4. Monitor the restore — pg_restore gives verbose output with --verbose; check for any errors at the end. Errors in recreating certain objects (like indexes) can happen, but the data may still be restored.

Both tools rely on the same underlying mechanism: bulk network/file I/O. But their step-by-step flows are tailored to their respective use cases.

Hands-on walkthrough

Let's get practical. We'll go through two complete examples: one with COPY, one with pg_restore. You'll see the exact commands and expected output.

Example 1: Import a CSV with \copy

Assume you have a file logs.csv with 100,000 rows of web server logs: timestamp, level, message. The table is created as follows:

CREATE TABLE server_logs (
    id SERIAL PRIMARY KEY,
    log_time TIMESTAMPTZ NOT NULL,
    log_level TEXT NOT NULL,
    message TEXT NOT NULL
);

Your CSV looks like:

2025-01-01 08:00:00,INFO,Server started
2025-01-01 08:01:15,ERROR,Connection refused
...

Now, from psql, use \copy to load the file:

\copy server_logs (log_time, log_level, message) FROM 'logs.csv' WITH (FORMAT csv);

This command reads the file, removes the double quotes (if any), and inserts rows. The (log_time, log_level, message) column list lets you skip the id column, which will be auto-generated. Expected output:

COPY 100000

That's it — 100,000 rows in one command, typically in under a second on modern hardware. To verify, run:

SELECT COUNT(*) FROM server_logs;

Output:

count
-------
100000

Example 2: Restore a full database with pg_restore

First, create a database dump in custom format:

pg_dump -Fc -f backup.dump mydatabase

Then, on the target server, create a fresh database and restore:

createdb -h newhost restored_db
pg_restore -h newhost -d restored_db --jobs=4 backup.dump

The --jobs=4 tells pg_restore to use 4 parallel connections to speed up the restore. Expected output (with --verbose):

pg_restore: connecting to database for restore
pg_restore: creating TABLE "public.users"
pg_restore: creating TABLE "public.orders"
...
pg_restore: creating INDEX "index_orders_on_user_id"
pg_restore: completed restoration

The restore finishes with all schema objects and data in place. Verify by connecting to restored_db and checking that row counts match the original.

Pro tip: Always use --if-exists with --clean to avoid errors when objects already exist, but make sure you really want to drop existing objects.

Example 3: Loading data into a staging table and transforming

Often you have raw data that needs cleanup. Use COPY to load into a staging table, then run INSERT ... SELECT with transformations.

-- Create staging table
CREATE TABLE staging_logs (raw_line TEXT);

-- Load raw lines
\copy staging_logs FROM 'logs_raw.txt';

-- Transform and insert
INSERT INTO server_logs (log_time, log_level, message)
SELECT
    (split_part(raw_line, ',', 1))::timestamptz,
    split_part(raw_line, ',', 2),
    split_part(raw_line, ',', 3)
FROM staging_logs;

This pattern decouples I/O from transformation, keeping your scripts clean.

Compare options / when to choose what

When to choose what

• Use COPY when: You have a clean, delimited file that maps directly to an existing table. Performance is crucial, and you don't need complex transformations. For client-side files, use \copy; for server-side, use COPY.
• Use pg_restore when: You have a full backup from pg_dump and need to recreate the database on a new server or after an incident. It's also useful for partial restores (e.g., only one table) with --table option.
• Alternatives: For very large imports, consider partitioning tables, using pg_bulkload (third-party) or file_fdw to treat a file as a foreign table. For simpler needs, psql running an SQL script is sometimes sufficient, but it lacks parallelism and is slower.

Troubleshooting & edge cases

Common errors and fixes

• ERROR: invalid byte sequence for encoding "UTF8" — Your file has an encoding that doesn't match the database. Use --encoding in \copy or set client_encoding before the copy. Alternatively, convert the file with iconv.
• ERROR: missing data for column — Your CSV has fewer columns than expected. Check for missing commas or lines with fewer fields. Use \copy ... WITH (FORMAT csv, HEADER true) if the first line is a header, or use NULL option to specify how to represent NULLs.
• ERROR: row size exceeds maximum — A single row is larger than PostgreSQL's maximum row size (~1.6 GB). This is rare, but check for huge text fields. You might need to break the import into smaller chunks.
• pg_restore: error: could not execute query — This often happens when the target database has constraints that conflict with the dump (e.g., existing data). Use --clean --if-exists to drop conflicting objects before restoring.
• COPY TO/FROM permission denied — Server-side COPY requires superuser or pg_write_server_files privilege. If you don't have that, use \copy from the client instead.
• Performance issues: If COPY is slow, check that you are using COPY (not INSERT), disable indexes during load (e.g., drop indexes before COPY and recreate after), and avoid triggers that cause row-by-row processing.

Edge cases

• Binary format: COPY BINARY is faster but less portable; use it for internal transfers.
• Data with quotes and commas: For CSV, always use FORMAT csv, which handles quoting correctly. Without it, COPY assumes tab-delimited and treats commas as part of the field.
• Restoring with dependencies: pg_restore automatically orders objects to respect foreign key constraints, but if you use --section=data only, you may need to disable triggers or constraints temporarily.

What you learned & what's next

Today you have mastered the two primary import tools in PostgreSQL: COPY for fast, row-level loading of flat files, and pg_restore for restoring complete dumps. You can now explain the core idea behind them, and you've completed practical exercises showing both a CSV import and a database restore. You understand when to prefer one over the other, and you know the typical pitfalls to avoid.

Next up in the track, we'll explore exporting data with COPY ... TO and pg_dump in reverse, so you can create backups and move data out of PostgreSQL just as efficiently as you bring it in. That skill will complete your data portability toolkit.