current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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
    
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

3 Answers 3

up vote 2 down vote

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 1 down vote

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 postgis_restore.pl 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
up vote 1 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 nead to add c:\program files\postgresql\9.3\bin> to path(system variables) of windows or use its direct executable file:

export:

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

import:

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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