How to export schema in Redshift

How do I export a schema from Amazon Redshift?

Exporting a schema in Amazon Redshift creates a DDL-only dump you can version-control, share, or recreate in another cluster.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why export a Redshift schema?

Schema dumps let you version control table definitions, rebuild environments quickly, and share reproducible DDL across teams.

What is the quickest way to dump only DDL?

Use the PostgreSQL pg_dump utility with the --schema-only flag because Redshift speaks the same wire protocol as PostgreSQL.

Basic command

pg_dump -h redshift-cluster.abc123.us-east-1.redshift.amazonaws.com -p 5439 -U analytics -d prod --schema public --schema-only -f public_schema.sql

How do I export several schemas?

Repeat --schema for each schema or omit the option to dump every schema: --schema public --schema sales.

How can I save the dump in S3 automatically?

Pipe pg_dump into gzip, then stream to S3 with the AWS CLI:
pg_dump ...| gzip | aws s3 cp - s3://team-backups/redshift/public_schema.sql.gz

Step-by-step with ecommerce tables

After running the command, the output includes DDL for Customers, Orders, Products, and OrderItems. You can apply the file to another cluster with psql -f public_schema.sql.

Best practices for production clusters

Run dumps from a read-only user, schedule them during off-peak hours, compress the result, and store at least three previous versions.

Can I export both schema and small seed data?

Yes. Omit --schema-only and add --table for seed tables such as Products.Keep large fact tables out to avoid huge files.

How do I restore the exported schema?

Create a new database or schema, then execute psql -h new-cluster ... -f public_schema.sql.

Why How to export schema in Redshift is important

How to export schema in Redshift Example Usage


-- Export the 'public' schema containing ecommerce tables
pg_dump -h redshift-cluster.abc123.us-east-1.redshift.amazonaws.com \
        -p 5439 -U analytics -d prod \
        --schema public --schema-only \
        -f public_schema.sql

-- Snippet from resulting SQL
CREATE TABLE Customers (
    id         BIGINT IDENTITY(1,1)   NOT NULL,
    name       VARCHAR(255),
    email      VARCHAR(255),
    created_at TIMESTAMP,
    PRIMARY KEY(id)
);

CREATE TABLE Orders (
    id           BIGINT IDENTITY(1,1) NOT NULL,
    customer_id  BIGINT               NOT NULL,
    order_date   DATE,
    total_amount NUMERIC(12,2),
    PRIMARY KEY(id)
);

How to export schema in Redshift Syntax


pg_dump \
  -h <endpoint>               # e.g., redshift-cluster.abc123.us-east-1.redshift.amazonaws.com
  -p 5439                     # Redshift default port
  -U <user>                   # Redshift database user
  -d <database>               # Target database
  --schema <schema_name>      # public | sales | marketing (repeatable)
  --schema-only               # export DDL, no data
  [-f <output_file.sql>]      # write to file; omit to send to STDOUT

# Example for ecommerce public schema
pg_dump -h redshift-cluster.abc123.us-east-1.redshift.amazonaws.com \
        -p 5439 -U analytics -d prod \
        --schema public --schema-only \
        -f public_schema.sql

Common Mistakes

Including data by forgetting <code>--schema-only</code> makes the dump huge and slow to restore. Always add the flag when you only need DDL.
Connecting without SSL causes <code>psql: FATAL: no pg_hba.conf entry</code>. Set <code>sslmode=require</code> or add <code>?ssl=true</code> to your connection string.