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:
Nexus: A Brief History of Information Networks from the Stone Age to AI
$21.66 (as of January 22, 2025 11:32 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.)
The Singularity Is Nearer: When We Merge with AI
$17.72 (as of January 22, 2025 11:32 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.)
Logitech MK270 Wireless Keyboard And Mouse Combo For Windows, 2.4 GHz Wireless, Compact Mouse, 8 Multimedia And Shortcut Keys, For PC, Laptop - Black
$22.99 (as of January 22, 2025 11:32 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.
