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

• 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

Similar utilities in other databases:

SQL Server

MySQL

• PostgreSQL Reference
• pg_dump Utility - PostgreSQL 9.1. Documentation
• mysqldump - A Database Backup Program - MySQL 5.6 Reference Manual