43

I have tried to find a way to backup my database design without including the data stored in the database. Actually, one might say that I want to have a file that holds all "CREATE Scripts" in the database. Ideally, this file could be used to recreate the database (with no data of course).

How to avoid data in a database backup?

I am using pgAdmin 4.1.3 and PostgreSQL 9.6.

Mel
  • 5,837
  • 10
  • 37
  • 42
Helge
  • 673
  • 2
  • 6
  • 9

5 Answers5

70

You can use this from psql(terminal):

pg_dump -s databasename > file.dump

from pg_dump documentation the "-s" dump only the object definitions (schema), not data.

pg_dump documentation

z44.nelther
  • 898
  • 5
  • 8
  • I am rather new to PostgreSQL and has accessed databases only using pgAdmin. So how to open a command interpreter that will accept the 'pg_dump' command? (The database is not on my PC, but on some remote server.) – Helge Apr 06 '17 at 11:53
  • is it a linux server? what operating system are you using? – z44.nelther Apr 06 '17 at 14:24
  • I am using Windows 7. – Helge Apr 06 '17 at 15:19
  • @Helge In my installation it is here: `C:/Program Files (x86)/pgAdmin 4/v1/runtime/pg_dump.exe` – Abelisto Apr 07 '17 at 07:25
  • you can download the Postgre SQL from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads to run `pg_dump` command – Asad Shakeel Jun 27 '19 at 08:22
21
pg_dump --host localhost --port 5432 --username "userName" --schema-only --verbose --file "file path" "db_dev_local"
evandrix
  • 6,041
  • 4
  • 27
  • 38
Md Ayub Ali Sarker
  • 10,795
  • 4
  • 24
  • 19
  • 14
    Explaining what the command does and why the given options were selected would make this answer more informative and useful. – Joe McMahon Nov 03 '17 at 21:31
  • 1
    The example is incorrect. If you use the POSIX-style options with double-dash then you need to use the `=` symbol as well, e.g. `--username=postgres`. Space is used with the single dash options, e.g. `-U postgres`. – isapir Jan 08 '18 at 21:10
7

TL;DR: Set --schema-only with pg_dump.

The pg_dump utility is distributed with Postgres in the bin directory. The utility accepts many options as documented, and the format is:

pg_dump <options> <database-name>

To answer specifically the question here, open a Command Prompt window (Shell in *nix), and navigate to the Postgres bin directory. Then issue the following command (Windows syntax shown):

> set DB_NAME=mydatabase
> pg_dump.exe --username=postgres --schema-only %DB_NAME% >%DB_NAME%-schema.sql

The DB_NAME environment variable is purely for convenience, and the name can be inlined for simplicity.

The --username switch is required in most cases, as you are probably not logged in to your OS as a valid Postgres user.

The --schema-only switch indicates that you only want the DDL statements, without the actual data.

The >%DATABASE_NAME%-schema.sql portion redirects the output from the console to a file named %DATABASE_NAME%-schema.sql, in this example "mydatabase-schema.sql".

The produced file is a valid SQL script, which can be executed via any valid client in order to recreate all the objects.

Here are a couple of other useful examples:

I usually prefix MATERIALIZED VIEWs with mv_, so to dump the definitions for only the relations that start with "mv_*" I use:

> pg_dump --username=postgres --schema-only --table=mv_* %DB_NAME% >%DB_NAME%-matviews.sql

Use multiple --table= arguments for multiple patterns.

To script only the POST-DATA objects, e.g. indexes, triggers, etc., use --section=post-data

> pg_dump --username=postgres --section=post-data %DB_NAME% >%DB_NAME%-post-data.sql

This assumes default host, port, authentication, etc. To specify non-default settings refer to the documentation

isapir
  • 21,295
  • 13
  • 115
  • 116
5

Step on the database -< Tools ->Backup

then Dump Options

check the Only schema to be true

enter image description here

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
-1

I had the same problem and this works for me.

pg_dump -U postgres <database_name> > <filename.sql> --schema-only
pmventura
  • 157
  • 1
  • 2
  • 7