How To Guides

How to Extract the Schema for a PostgreSQL Database

by

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:

ShellScript
pg_dump -U postgres -s -d mydb > schema.sql

In this example:

  • -U specifies the PostgreSQL username.
  • -s ensures that only the schema (DDL) is dumped.
  • -d mydb targets 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:

ShellScript
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.sql

Conclusion

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.

Share this:

Related Posts:

Postgres

Leave a Reply