CODEWITHSUNDEEP
sqldatabasepostgresql
serv-bot 22
serv-bot 22

Reputation: 1298

Export a CREATE script for a database

Say I've created a database in pgAdmin, but I want to export a CREATE sql file.

How would I go about generating the dump?

Upvotes: 89

Views: 150241

Answers (5)

Nate Cook
Nate Cook

Reputation: 8585

Here's how to use pgAdmin to create a schema script that can be used with a PostgreSql database schema comparison tool such as apgdiff. These instructions are for pgAdmin3.

  1. In pgAdmin, right click on the database and click Backup.
  2. Enter an appropriate path and filename (i.e. /some/path/my_script.sql).
  3. Select Plain as the format in the format dropdown.
  4. Go to the Dump Options #1 tab and check "Only schema".
  5. Then click Backup. Then click Done.

Note: Yes, I realize that pgAdmin uses pg_dump behind the scenes to create the script, but the question was about pgAdmin, so this is the GUI method.

Upvotes: 192

fresheed
fresheed

Reputation: 113

At least in PgAdmin III 1.22.1 you can get CREATE script doing: 1) right-click on table name 2) "Scripts" -> "CREATE script" There are options to get SELECT, DELETE etc.

Upvotes: 1

Tanveer Ali
Tanveer Ali

Reputation: 393

pgAdmin however does have a facility to do what you want:

Right-click on the database you want to export

Select Backup from the pop-up menu

Choose "format" Plain.

Choose "plain option" Only schema

Upvotes: 25

Anurag
Anurag

Reputation: 15

You can achieve this through phpPgAdmin just like phpMyAdmin for MySQL.

Login to phpPgAdmin select the database and then choose export.

Upvotes: 3

unmounted
unmounted

Reputation: 34418

To generate a sql script that will create the tables as they exist in a given database do:

pg_dump --schema-only --no-owner the_database > create_the_tables.sql

This will give you a bunch of create table statements. Just to see how portable it was I tried the above as follows:

bvm$ pg_dump -s --no-owner devdb | sqlite3 so_ans.db

And then:

bvm$ sqlite3 so_ans.db .schema
CREATE TABLE courses (
    id integer NOT NULL,
    name text,
    created_by integer,
    jc text
);

Kind of cool.

Upvotes: 67

Related Questions