When working with PostgreSQL, you'll often need to export just the database structure without the actual data. This is particularly useful for:

• Creating development/test environments
• Version controlling database schemas
• Documenting database architecture
• Comparing schema versions

The primary tool for this is PostgreSQL's pg_dump command with the --schema-only flag:

pg_dump -U username -h hostname -p port --schema-only -f schema.sql database_name

Key options to consider:

• --schema-only: Dumps only the schema, no data
• --no-owner: Excludes ownership information
• --no-privileges: Skips permission settings

For more granular control:

# Export only tables (no sequences, views, etc.)
pg_dump -U postgres --schema-only -t 'schema_name.table*' -f tables.sql db_name

# Export specific schemas
pg_dump -U postgres --schema-only -n schema1 -n schema2 -f schemas.sql db_name

# Export with clean formatting
pg_dump -U postgres --schema-only --no-comments --inserts -f clean_schema.sql db_name

For programmatic access to schema information, you can query PostgreSQL's system catalogs:

SELECT table_schema, table_name, column_name, data_type 
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

Or generate DDL statements directly:

SELECT pg_get_functiondef(f.oid) 
FROM pg_proc f
JOIN pg_namespace n ON (f.pronamespace = n.oid)
WHERE n.nspname = 'public';

Note that some options vary between PostgreSQL versions. For example, newer versions (12+) offer improved handling of partitioning in schema dumps.

PostgreSQL's pg_dump utility provides a powerful way to export database schemas without the actual data. The key parameter for this operation is --schema-only:

pg_dump --schema-only -U username -d database_name > schema_dump.sql

To dump specific tables' structures, combine --schema-only with -t:

pg_dump --schema-only -U postgres -d mydb -t customers -t orders > tables_structure.sql

PostgreSQL offers multiple output formats for different use cases:

# Custom format (ideal for later restoration)
pg_dump --schema-only -Fc -U user -d dbname -f schema.dump

# Directory format (for large databases)
pg_dump --schema-only -Fd -U user -d dbname -f schemadir

Fine-tune your exports with these additional flags:

• --no-owner: Exclude ownership information
• --no-privileges: Skip permission settings
• --no-tablespaces: Remove tablespace assignments

Here's a complete command sequence for staging to production migration:

# Dump staging schema
pg_dump --schema-only -h staging.db.example.com -U deploy_user -d app_db -Fc -f staging_schema.dump

# Restore to production
pg_restore -h production.db.example.com -U deploy_user -d app_db --clean --create staging_schema.dump

When working with schema dumps, watch for:

• Extension dependencies not included by default (use --if-exists flag)
• Materialized views requiring special handling
• Sequence values that might need resetting