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?
1
-
@MilenA.Radev Link gives 404.Itération 122442– Itération 1224422020-02-18 13:10:10 +00:00Commented Feb 18, 2020 at 13:10
Add a comment
|
5 Answers
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.
- In pgAdmin, right click on the database and click Backup.
- Enter an appropriate path and filename (i.e.
/some/path/my_script.sql). - Select Plain as the format in the format dropdown.
- Go to the Dump Options #1 tab and check "Only schema".
- 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.
9 Comments
Kat
It turns out I just had to specify the Postgres binary path (where pg_dump is). This is done in the options of pgAdmin (File > Options > Binary paths and set the PG bin path). This is likely because I did not have a standalone Postgres client installed at the time that I installed pgAdmin.
Angry 84
This should have been the accepted answer, Two years after the NON pgadmin given.. So you get my upvote as its the valid answer for the OP's question
Kesem David
Three years later, still helpful
lucid_dreamer
4 years and counting.
Itération 122442
Still up to date (pgAdmin4)
|
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.
3 Comments
raffian
Does this output all objects, triggers, procedures, etc?
Peeter Kokk
"pg_dump -U postgres --schema-only --no-owner documents > create_the_tables.sql", if the user is postgres and you are using Windows OS. Otherwise, it would use your Windows user account name by default.
Farrukh Najmi
The question was how to use pgAdmin not pg_dump.
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
Comments
You can achieve this through phpPgAdmin just like phpMyAdmin for MySQL.
Login to phpPgAdmin select the database and then choose export.
Comments
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.
3 Comments
Tristan
I think this answer is interesting, but when I click on "Scripts" I can see only the "SELECT" option
Wayneio
Not sure why this is downvoted, it works for me
Jeremy
This works, but probably not up-voted much because the original question is worded in a way that sounded like they were asking for a way to dump out a script for the whole db in one go, rather than do it individually table by table.