PostgreSQL Dump, Restore, and Transfer

PostgreSQL backup and restore is one of those things you do rarely enough to forget the flags every time. This is the reference I keep coming back to.

pg_dump — Export a Database

# Plain SQL dump (most portable)
pg_dump -U <user> -h <host> -p <port> <dbname> > backup.sql

# Example
pg_dump -U sample_user -h localhost -p 5432 mydb > mydb_$(date +%Y%m%d).sql

The plain SQL format is a single text file you can inspect, diff, and pipe directly into psql. Use it for most cases.

Custom Format (faster, parallel restore)

# Custom binary format — supports parallel restore with pg_restore
pg_dump -U sample_user -h localhost -Fc mydb > mydb.dump

# Restore with parallel jobs
pg_restore -U sample_user -h localhost -d mydb -j 4 mydb.dump

Custom format is significantly faster for large databases. -j 4 runs 4 parallel restore workers.

Dump a Single Table

pg_dump -U sample_user -h localhost -t orders_order mydb > orders_only.sql

Dump Schema Only (no data)

pg_dump -U sample_user -h localhost --schema-only mydb > schema_only.sql

Dump Data Only (no DDL)

pg_dump -U sample_user -h localhost --data-only mydb > data_only.sql

pg_restore — Restore from Custom Format

# Restore to an existing (empty) database
pg_restore -U sample_user -h localhost -d mydb mydb.dump

# Drop and recreate objects before restore
pg_restore -U sample_user -h localhost -d mydb --clean mydb.dump

# Restore with verbose output
pg_restore -U sample_user -h localhost -d mydb -v mydb.dump

--clean drops existing objects before recreating them. Use this to overwrite a database that already has tables.

psql — Restore from Plain SQL

# Restore a plain SQL dump
psql -U sample_user -h localhost -d mydb < backup.sql

# As the postgres system user (no password prompt)
sudo su - postgres
psql mydb < /var/lib/postgresql/backup.sql

Creating the Target Database First

pg_dump dumps data; it doesn’t create the database itself. Create it first:

sudo su - postgres
psql -c "CREATE DATABASE mydb OWNER sample_user;"
psql -d mydb < backup.sql

Or from a template:

psql -c "CREATE DATABASE mydb_new TEMPLATE mydb_old;"

Transferring Between Servers with scp

Download from Remote to Local

# Download backup from remote server
scp user@remote_host:/var/lib/postgresql/mydb.sql ./mydb_local.sql

# Example
scp root@203.0.113.10:/var/lib/pgsql/prod.sql .

Upload from Local to Remote

# Upload to remote server
scp ./mydb.sql user@remote_host:~/mydb.sql

# Example
scp mydb.sql deploy@203.0.113.10:/home/deploy/mydb.sql

Full Dump + Transfer Pipeline

# 1. SSH in and dump
ssh user@source_server "pg_dump -U sample_user -Fc mydb > /tmp/mydb.dump"

# 2. Download the dump
scp user@source_server:/tmp/mydb.dump ./mydb.dump

# 3. Upload to target
scp ./mydb.dump user@target_server:/tmp/mydb.dump

# 4. Restore on target
ssh user@target_server "pg_restore -U sample_user -d mydb /tmp/mydb.dump"

One-Liner: Dump Directly from Remote Without Storing Locally

ssh user@source "pg_dump -U sample_user mydb" | psql -U sample_user -d mydb_local

This streams the dump directly into the local database — no intermediate file.

Handling Passwords

.pgpass File (No Prompt)

Create ~/.pgpass to avoid password prompts in scripts:

# ~/.pgpass format: hostname:port:database:username:password
localhost:5432:mydb:sample_user:mypassword
*:5432:*:sample_user:mypassword  # wildcard

chmod 600 ~/.pgpass  # required — postgres ignores it if world-readable

PGPASSWORD Environment Variable

PGPASSWORD=mypassword pg_dump -U sample_user mydb > backup.sql

Fine for one-off scripts; avoid in .bashrc.

Common Patterns

Nightly Backup Script

#!/bin/bash
set -e

DB_USER="sample_user"
DB_NAME="mydb"
BACKUP_DIR="/backups/postgres"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR"
pg_dump -U "$DB_USER" -Fc "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"

# Retain last 7 days only
find "$BACKUP_DIR" -name "*.dump" -mtime +7 -delete

echo "Backup complete: ${DB_NAME}_${DATE}.dump"

Copy Production to Staging

# On staging server
pg_dump -U prod_user -h prod_host -Fc prod_db > /tmp/prod.dump
pg_restore -U staging_user -h localhost -d staging_db --clean /tmp/prod.dump

Restore a Specific Table

pg_restore -U sample_user -h localhost -d mydb -t orders_order mydb.dump

Common Errors

role "postgres" does not exist

# The user doesn't exist on this machine. Create it or use the correct user:
sudo su - postgres
createuser --superuser sample_user

database "mydb" does not exist

# Create the database first
psql -c "CREATE DATABASE mydb;"
psql -d mydb < backup.sql

pg_dump: error: query was canceled due to conflict with recovery Dump is running on a hot standby replica in recovery mode. Either dump from primary or set:

SET default_transaction_isolation = 'repeatable read';
-- or in postgresql.conf on replica:
hot_standby_feedback = on

FATAL: Peer authentication failed

# Connect explicitly with -h to use password auth instead of peer auth
pg_dump -U sample_user -h 127.0.0.1 mydb > backup.sql
#                       ^^^^^^^^^^^ forces TCP, not Unix socket

Quick Reference

Task	Command
Dump plain SQL	pg_dump -U user db > file.sql
Dump binary	pg_dump -U user -Fc db > file.dump
Restore plain SQL	psql -U user -d db < file.sql
Restore binary	pg_restore -U user -d db file.dump
Restore parallel	pg_restore -U user -d db -j 4 file.dump
Dump single table	pg_dump -U user -t tablename db > file.sql
Schema only	pg_dump --schema-only db > schema.sql
Stream remote to local	ssh user@host "pg_dump db" | psql -d localdb