PostgreSQL - Loading Database

PostgreSQL - Loading and Restoring Databases

Loading data into a PostgreSQL database is a common task, whether you’re restoring from a backup, migrating from another system, or importing bulk data. This chapter covers the most effective and modern methods to accomplish this.

Choosing the Right Method

The best tool depends on the format of your source data:

• For custom-format backups (.dump, .backup): Use the pg_restore utility. This is the most robust method for restoring backups created with pg_dump -Fc.
• For plain SQL files (.sql): Use the psql command-line client. This is ideal for replaying SQL scripts.
• For structured text files (CSV, TSV): Use the highly efficient COPY SQL command for server-side loading or the \copy meta-command in psql for client-side loading.

Method 1: Using pg_restore for Custom-Format Backups

The pg_restore utility is designed to restore databases from archives created by pg_dump in custom (-Fc), directory (-Fd), or tar (-Ft) formats. This format is generally preferred over plain SQL because it allows for more flexibility, such as parallel restoration.

Syntax and Common Options

pg_restore --username=your_user --host=your_host --dbname=target_db --jobs=4 --clean backup_file.dump

Key parameters:

• --username (-U): The PostgreSQL user for the connection.
• --dbname (-d): The target database to restore into. The database must already exist.
• --jobs (-j): Specifies the number of concurrent jobs to run, significantly speeding up the restore process.
• --clean (-c): Instructs pg_restore to drop existing database objects before recreating them.

Method 2: Using psql for Plain SQL Files

If your backup is a .sql file (a plain text script), you can execute it directly using psql.

psql --username=your_user --host=your_host --dbname=target_db --file=backup_file.sql

Best Practice: For critical restores, wrap the execution in a single transaction to ensure that either the entire script succeeds or it fails completely, leaving the database unchanged.

psql --username=your_user --dbname=target_db --single-transaction --file=backup_file.sql

Method 3: Using COPY for Bulk Data Import

The COPY command is the fastest way to load large amounts of data from structured text files like CSV. It’s crucial to understand the difference between COPY (SQL command) and \copy (psql meta-command).

Server-Side Loading with COPY

COPY runs on the database server and reads a file from the server’s filesystem. This requires superuser privileges or specific grants, as it involves direct server file access.

-- Assumes 'products.csv' is on the database server's filesystem
COPY products(name, price, category)
FROM '/var/lib/postgresql/data/imports/products.csv'
WITH (FORMAT CSV, HEADER true, DELIMITER ',');

Client-Side Loading with \copy

\copy is a psql command that reads a file from your local (client) machine and streams the data to the server, executing a COPY FROM STDIN. It does not require superuser privileges.

-- Run this command inside a psql session
-- Assumes 'products.csv' is on your local machine
\copy products(name, price, category) FROM '~/local_data/products.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ',');

Method 4: Using a GUI Tool like pgAdmin

For users who prefer a graphical interface, pgAdmin provides a user-friendly way to restore a database. Under the hood, it constructs and runs the same pg_restore command.

Step 1: Open pgAdmin and connect to your PostgreSQL server. Step 2: If it doesn’t exist, create a new, empty database by right-clicking ‘Databases’ and selecting ‘Create -> Database…’. Step 3: Right-click on the newly created target database and select ‘Restore…’. Step 4: In the dialog, select the custom-format backup file (.dump) and configure options like ‘Clean before restore’ or the number of jobs. Step 5: Click the ‘Restore’ button to begin the process. You can view the command-line output in the ‘Processes’ tab.

These methods provide a comprehensive toolkit for loading data into PostgreSQL, from full database restores to bulk data ingestion from external sources.