阅读中文版本

Clone PostgreSQL Schema Without Data

Use pg_dump schema-only output to scaffold a staging database that matches production structure for safe query planning tests.

pg_dump --schema-only --no-owner --no-privileges \
  -d production -f schema.sql

createdb staging_clone
psql -d staging_clone -f schema.sql
psql -d staging_clone -c "\dt"

What this recipe does

Create a structure-only clone of a production schema for safe experimentation.

You need to test index and migration changes on a realistic schema without copying production data.

Prerequisites

• pg_dump and psql matching the server version
• A target database with create privileges
• Network access to the source

Procedure

1. Dump only the schema.

pg_dump --schema-only --no-owner --no-privileges \
  -d production -f schema.sql

Expected result: schema.sql contains DDL for tables, indexes, and constraints but no rows.

2. Load it into a fresh database.

createdb staging_clone
psql -d staging_clone -f schema.sql
psql -d staging_clone -c "\dt"

Expected result: The staging clone lists the same tables, so plans can be tested realistically.

How to confirm it worked

Run the commands above and confirm every expected result in order. If a step does not produce the stated output, use the troubleshooting notes before continuing. A passing run shows the clone has identical table and index definitions and you can create a row plus read it via psql dt output.

Troubleshooting

Extensions missing — Install the same extensions before loading; DDL may reference types such as citext.

Ownership errors — Use –no-owner and –no-privileges to make the dump portable.

Limitations

• No data means cardinality is wrong; seed representative rows.
• Sequences are present but not advanced.
• Does not include role or grant state.