PostgreSQL: Exporting a database on Windows?

Question

I need to export a database I created to get the code for creating the database and inserting rows.

I understand there is a method of using pg_dump, but all the walkthroughs of using it I can find seem to be on Linux.

Can anyone tell me how to do this on Windows?

pg_dump works just the same on Windows, there is no difference (except for the slightly different syntax for file names) — user330315
– user330315, Commented Nov 6, 2015 at 13:36
@a_horse_with_no_name Is this meant to be via the SQL Shell? If so, once I get past the username stage, it won't let me type anything for the password EDIT: I discovered the password just shows up blank instead of bullet points. — Wolff
– Wolff, Commented Nov 6, 2015 at 13:43
You start a Windows command line (cmd.exe) and then run pg_dump: imgur.com/srGeShG — user330315
– user330315, Commented Nov 6, 2015 at 13:51
What did you try so far, and what did not work for you? Did you try to replicate any of the Linux examples on your Windows box? — A. Scherbaum
– A. Scherbaum, Commented Nov 6, 2015 at 21:21
It's easier to use PgAdmin on Windows if you are not used to working with the command prompt. — Craig Ringer
– Craig Ringer, Commented Nov 7, 2015 at 7:32

JMG · Accepted Answer · 2015-11-07 05:44:13Z

4

You have to execute pg_dump located in the bin folder of your PostgreSQL install. Ex : C:\Program Files\PostgreSQL\9.4\bin.

The command is pg_dump -U *username* -p *port* -d *database* -W -f *filename*

All the parameters are case sensitive ! (Check your username !)

U is for specifying the user that will connect to the DB. If you don't specify it, pg_dump will use the login you're logged on with.
p for the port. (Default is 5432)
d for the database name
W to force pg_dump to ask for password
f the name of the file where the export should be stored. If you don't specify this, the dump will be displayed in the console.

Example : pg_dump -U postgres -p 5432 -d postgres -W -f c:\vm\dump.sql

You may need special permissions to export the file to some folders. (i.e. : C:\program files\ requires administrative rights for writing.)

answered Nov 7, 2015 at 5:44

JMG

1562 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

And to restore simply do psql -d newdb -f dump.sql. See the examples here (at the bottom of the linked page):postgresql.org/docs/13/app-pgdump.html