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:
AI Engineering: Building Applications with Foundation Models
$57.74 (as of March 17, 2026 21:10 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.)
Jadaol Cat 6 Ethernet Cable 50 ft, 10Gbps Support Cat8 Cat7 Network, Flat RJ45 LAN Patch Cable, High-Speed Wired Internet Cable for Router, Modem, Switch, Gaming Consoles, PC, Streaming Devices, White
$9.99 (as of March 18, 2026 05:49 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.)
Amazon Basics microSDXC Memory Card with Full Size Adapter, A2, U3, Read Speed up to 100 MB/s, 128GB, Black
$19.91 (as of March 18, 2026 05:49 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.
