Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to export my postgresql db with pgadmin.
I search all of its GUI, I also searched the web and I don't find some sueful information.
I know how to import the db into postgresql: create a db with `template0' and then:

 c:\program files\postgresql\9.3\bin> psql -h localhost -p 5432 
                                -U postgres newDBname < D:\backup.sql

But I don't know how to export it. please help me to export my db.

share|improve this question

closed as off-topic by PolyGeo Mar 31 at 1:10

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions relating to general Information Technology, with no clear GIS component, are off-topic here, but can be researched/asked at Stack Overflow (software development), Super User (computing hardware and software) and Database Administrators (relational databases)" – PolyGeo
If this question can be reworded to fit the rules in the help center, please edit the question.

Are you looking to backup/copy to a new server? How do you feel about command line? – Michael Miles-Stimson Aug 28 '14 at 21:59
yes, but I think it does not matter ! – Dariush Aug 28 '14 at 22:00
Look at the Tools menu in pgAdmin3. Do you see there "Backup" and "Restore" as I do? – user30184 Aug 28 '14 at 22:34
It is disabled ! – Dariush Aug 28 '14 at 22:47
Do you have Administrator Rights to the system on the PostGres user you are using? – Mark C Aug 29 '14 at 0:23

The pair that you want is PG_Dump and PG_Restore...

I use PG_Dump on a scheduled task to backup our PostGIS databases in a format that can be restored easily - and more importantly will work!

pg_dump.exe --file=c:\Your\path\BACKUP_Name.Backup -Fc -Z9 -o DatabaseName
  • Custom format
  • Z9 compression (to save space)
  • Include objectIDs

and then to restore on the other end..

Create a new empty database with the correct name and run:

pg_restore -c -Fc -d DatabaseName c:\Your\path\BACKUP_Name.Backup
  • clean the database first
  • Custom format

Works for me... but as with anything TEST IT FIRST, just because it works for me in PostGIS/SDE on this version doesn't guarantee it will work for you in your environment.

share|improve this answer
up vote 2 down vote accepted

My problem was that I could not find the pg_dump command. I thought it was a subcommand of psql command.
You need to add c:\program files\postgresql\9.3\bin> to path(system variables) of windows or use its direct executable file:


c:\program files\postgresql\9.3\bin> pg_dump.exe -U postgres test2 >"d:\backup.sql"


c:\program files\postgresql\9.3\bin> psql -h localhost -p 5432  -U postgres newDBname < D:\backup.sql

export special table:

C:\Program Files\PostgreSQL\9.0\bin\pg_dump.exe --host localhost --port 5432 --username postgres --format plain --ignore-version --verbose --file "C:\temp\filename.backup" --table public.tablename dbname
share|improve this answer
Yeh, me too when I started out. I was surprised to see how simple it was. To test it I created a dummy database and put data into it, backed it up, dropped the database and then restored - everything worked as before so I adopted that procedure... I've only needed to restore from backup twice (RAID failure) and both times it's worked - much better than starting again! – Michael Miles-Stimson Aug 28 '14 at 22:58

For PostGIS, make sure you are following the hard upgrade procedure:

pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f file.backup thedatabase

This way it can be restored in the existing database, or it can be discovered with a future version of PostGIS using the command. For Windows, you need to tailor a simple BAT file described here. Note that this is similar to the pg_restore utility described by other answers, but is specifically tailored to PostGIS.

share|improve this answer
Good advice Mike, I'll look into that... Does it work for SDE databases that use PG_GEOMETRY? Will that help upgrade from one version to the next version? Previously I have upgraded by using Catalog to backup features to fGDB and then Esri copy to the new database - this could be a simpler procedure. – Michael Miles-Stimson Aug 29 '14 at 1:16
I don't know much about SDE on PostgreSQL, but the pg_geometry data type will not be touched by PostGIS, just like any other data type, and will be restored on a host DB only if the pg_geometry datatype exists. – Mike T Aug 29 '14 at 4:04

If you prefer a graphical user interface, you can use pgAdmin III (gratis, Linux/Windows/OS X). Simply right click on the table of your choice, then "backup". It will create a pg_dump command for you, if latter on you want to do it with CLI.

enter image description here

enter image description here

enter image description here

enter image description here

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.