When working with a PostgreSQL database, you might need to extract the DDL (Data Definition Language) statements, which define the structure of your database. This could be necessary for various reasons, such as backing up schema definitions, migrating to a new environment, or simply documenting your database design. In this post, I’ll walk you through how I extract the DDL for a PostgreSQL database.
Thank me by sharing on Twitter 🙏
Why Extracting DDL is Important
Before diving into the how-to, let’s discuss why you might want to extract DDL. DDL statements contain the blueprint of your database, defining tables, indexes, constraints, and other structures. Extracting this information is crucial when:
- You want to replicate your schema in a new environment.
- You need to document your database structure for future reference.
- You’re preparing for a migration or version control over your schema.
- You want to review changes in your database design before deployment.
How to Extract PostgreSQL DDL
There are several ways to extract DDL from a PostgreSQL database but my perfered way is with pg_dump.
Using pg_dump
The pg_dump utility is one of the most straightforward ways to export your DDL. It’s powerful because it can export just the schema or the entire database with data.
To extract just the schema (i.e., DDL), I run the following command:
Highwings 8K 10K 4K HDMI Cable 48Gbps 6.6FT/2M, Certified Ultra High Speed HDMI Cable Braided Cord-4K@120Hz 8K@60Hz, DTS:X, HDCP 2.2 & 2.3, HDR 10 Compatible with Roku TV/PS5/HDTV/Blu-ray
$7.99 (as of January 12, 2026 03:21 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
SZHAIYIJIN SD Card Reader for iPhone, Memory Card Reader with USB Camera Adapter Plug and Play Trail Game Camera Viewer Supports SD and TF Card MicroSD Card Adapter for iPad No App Required
$9.99 (as of January 12, 2026 03:21 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
If Anyone Builds It, Everyone Dies: Why Superhuman AI Would Kill Us All
$19.68 (as of January 12, 2026 19:36 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)pg_dump -U postgres -s -d mydb > schema.sqlIn this example:
-Uspecifies the PostgreSQL username.-sensures that only the schema (DDL) is dumped.-d mydbtargets the database I want to export.- The output is saved into
schema.sql.
Run pg_dump from a docker container locally if you do not have pg_dump installed:
docker run --rm \
-e PGPASSWORD=$POSTGRES_PASSWORD \
-v $OUTPUT_DIR:/backup \
postgres:latest \
pg_dump -h $POSTGRES_HOST -p $POSTGRES_PORT -U $POSTGRES_USER -d $POSTGRES_DB -s -f /backup/schema.sqlConclusion
Extracting the current DDL from a PostgreSQL database is something I frequently need to do, whether for migrations, documentation, or backups. Whether I’m using pg_dump, querying pg_catalog, working with pgAdmin, or using the psql command line, each method provides a flexible way to get the information I need. Choosing the right method often depends on the complexity of the task and my preferences at the moment.
By following these steps, you’ll be able to easily extract and work with your PostgreSQL database’s DDL, ensuring your schema is well-documented and ready for whatever task comes next.
