pg_dump with –schema-only option
- pg_dump is a PostgreSQL command with the –schema-only option used to extract the database schema, including tables, views, functions, and other database objects, without the data.
- In the output, it creates an SQL script that can be helpful to recreate the database structure.
Syntax:
pg_dump --schema-only [options] dbname
- –schema-only: This option tells pg_dump to exclude data and only dump the schema.
- dbname: This is the name of the database you want to export.
Example: Exporting the gfg database schemas
Query:
prana>pg_dump -U postgres -h localhost -p 5432 -d gfg --schema-only > C:\Users\prana\OneDrive\Desktop\gfg_dump.sql
Explanation: After executing the pg_dump command with the provided options and –schema-only flag, a file named gfg_dump.sql (or any specified name) will be created in the directory specified (C:\Users\prana\OneDrive\Desktop). This file contains SQL statements to set up the database structure. Since the command includes the –schema-only option, it excludes data from the dump, focusing only on schema information.
How to Export PostgreSQL Database Without Data Using SQL?
When we are working with the PostgreSQL database, there are multiple times we need to export the database structure. This approach is useful when we create a skeleton database or migrate the schema changes for different environments or systems.
In this article, we will explore the process of exporting a database without data using SQL with the help of syntax and examples of each approach.