PostgreSQL - How To Dump Database to SQL Script - pg_dump Utility

pg_dump utility located in bin directory of PostgreSQL installation can be used to export database definitions and data to a SQL script.

Quick Example pg_dump -U username dbname > dump.sql
Extract Schema Only -s option
Extract Data Only -a option
Generate DROP statements -c option
Export OIDs -o option
How to Restore Use psql utility

pg_dump Examples

  • Extact both schema and data from postgres database:
  pg_dump -U postgres postgres > dump.sql
  • Extact schema only from postgres database:
  pg_dump -U postgres -s postgres > dump.sql
  • Extact data only from postgres database:
  pg_dump -U postgres -a postgres > dump.sql

PostgreSQL pg_dump in Other Databases

Similar utilities in other databases:

SQL Server

SQL Server Management Studio (GUI) Extracts schema to SQL scripts Exports data to flat files
BCP Utility Exports and imports data only

MySQL

mysqldump Command line utility extracts schema and data

Resources