How do you use pg_dump and pg_restore for backup and restore?
Answer
pg_dump creates backups of PostgreSQL databases. Plain SQL dump: pg_dump -U postgres -d mydb > backup.sql. Custom format (best for large DBs — compressed, parallelizable): pg_dump -Fc -d mydb -f backup.dump. Directory format (parallel dump): pg_dump -Fd -j 4 -d mydb -f backup_dir/ — uses 4 parallel workers. Dump specific tables: pg_dump -t tablename -d mydb > table.sql. pg_dumpall dumps all databases plus global objects (roles, tablespaces). pg_restore restores custom/directory format: pg_restore -d mydb -Fc backup.dump. Parallel restore: pg_restore -j 4 -d mydb backup_dir/. For continuous backup (PITR): use pg_basebackup + WAL archiving with tools like Barman, pgBackRest, or WAL-G.
Previous
What are exclusion constraints in PostgreSQL?
Next
What is connection pooling with PgBouncer and when should you use it?
More PostgreSQL Questions
View all →- Advanced What is the query planner in PostgreSQL and how does it work?
- Advanced What causes poor query plans and how do you fix them?
- Advanced What is logical replication in PostgreSQL?
- Advanced How do you implement row-level security (RLS) in PostgreSQL?
- Advanced What is table bloat in PostgreSQL and how do you fix it?