[postgis-users] postgresql backup

[Gold, Jack L (US SSA)]

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-bounces at postgis.refractions.net [mailto:postgis-users-bounces at 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 <seanc at backroadmapbooks.com>
> To: PostGIS Users Discussion <postgis-users at 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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users