5

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.

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

4 Answers 4

6

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:

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
1
  • 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! Aug 28, 2014 at 22:58
3

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.

1

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.

2
  • 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. Aug 29, 2014 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, 2014 at 4:04
0

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

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