[postgis-users] postgresql backup

[Greg Williamson]

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
>