[postgis-users] database backup?

[Paul Ramsey]

Christoph Spoerri wrote:

> Thanks for the help. The psql actually worked. I got some errors during 
> loading, but the DB seems to be in order and workable.
> 
> Now, I have two more questions (not urgent, but still worth discussing)
> 
> 1) what do you do if you have objects (blobs) in the database. In this case 
> you can't use the psql export, since it does only support the '-F p' options 
> (if I'm correct).

Cold backups are probably the safest bet. Shut down the database and 
copy the complete data directory elsewhere for later backup to offline 
media.

> 2) How do you back up a 100GB+ database? Split the static and dynamic data 
> into two separate databases, and back up only the dynamic nightly?

Again, a cold backup would probably be best. Failing that, using 
master/slave replication and doing hot backups off the slave is a 
possibility.

As you note, if you only have one table being updated regularly, hot 
backing that table would be sufficient.

Everyone's solution will differ depending on their usage patterns, data 
volume, etc.

Paul

> 
> Btw. Anybody interested to write up some HOWTO on backing up a postgis 
> database? I'm not an expert on Postgesql so some help from an expert would be 
> great. And it would be a nice thing to have in the documentation. let me 
> know.

-- 
       __
      /
      | Paul Ramsey
      | Refractions Research
      | Email: pramsey at refractions.net
      | Phone: (250) 885-0632
      \_