Backup and Recovery

Code

-- Export table data
COPY users TO '/tmp/users.csv' WITH CSV HEADER;

COPY (SELECT * FROM users WHERE active = true) TO '/tmp/active.csv'
    WITH CSV HEADER;

-- Import data
COPY users FROM '/tmp/users.csv' WITH CSV HEADER;

-- Export as SQL
-- pg_dump -U user -d mydb -t users > users.sql

-- Import SQL
-- psql -U user -d mydb < users.sql

-- Export structure only
-- pg_dump -U user -d mydb --schema-only > schema.sql

-- Export data only
-- pg_dump -U user -d mydb --data-only > data.sql

-- Create table backup
CREATE TABLE users_backup AS SELECT * FROM users;

-- Point-in-time recovery (PITR)
-- Requires WAL archiving configuration

Explanation

The COPY command quickly imports/exports CSV; pg_dump generates SQL backup files.

More SQL Snippets