In addition, postgis has a perl script (postgis_restore.pl) for handling restores of the custom hard dump described in the postgreSQL manual.
First dump the old db: pg_dump -Fc MYDB > MYDB.dmp Then build your new DB, install PostGIS, install topology and/or raster if you are using them, and finally pass the MYDB.dmp file to the perl script. createdb MYDB psql -f postgis/postgis.sql psql -f topology/topology.sql psql -f raster/raster.sql postgis_restore.pl MYDB.dmp | psql MYDB The instructions are all in the script. Jack Gold BAE SYSTEMS -----Original Message----- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Greg Williamson Sent: Tuesday, June 12, 2012 2:56 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] postgresql backup Sean -- Check the postgres manual for your version for details. pg_dumpall -- used to backup an entire cluster (all databases); can be used to backup global info such as users / roles as well. pg_dump -- used to backup a single database (no global info); like fumpall can be told to do schema only, data only, or both. Both can output to a plain text SQL file that can be used to restore from the file, such as psql -d mynewdb < dump.sql Both can output in compressed formats (highly desirable in some circumstances) which are then restored using pg_restore. The manual will provide more details; try dumping to a text file first and study what it has. Then move to the more compressed formats. You can use these to migrate to a new version of postgres; in that case you want to use the most recent version of pg_dump / restore to do the work. The newer versions know how to read older databases and collect all the needed data. HTH, Greg Williamson ps the postgres admin mailing list might be a good place to ask more pointed questions since it has a lot of DBAs looking at it; this forum probably has more GIS types who may well rely on a regular DBA to do this sort of (necessary) work. ----- Original Message ----- > From: Sean Christopher Conway <se...@backroadmapbooks.com> > To: PostGIS Users Discussion <postgis-users@postgis.refractions.net> > Cc: > Sent: Tuesday, June 12, 2012 10:10 AM > Subject: [postgis-users] postgresql backup > > > Hello, I am fairly new to POSTGIS and postgresql in general and am > wondering if somebody can explain to me the best and most efficient > way to backup and restore postgresql databases. Kind regards, > > > Sean. > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users