Re: Backup setup

On Wednesday 23 April 2008, "Gabor Siklos" <gabor(at)karamaan(dot)com> wrote:
> I need to back up our database off-site for disaster recovery. If I just
> back up the entire database data directory (i.e. /var/lib/pgsql/data)
> will I be able to restore from there?

Technically you can do this, if you do it per the PITR instructions in the
manual (ie. you also need all the WAL logs produced during and around the
backup window).

> Or should I instead just dump the
> data, using pg_dump, and back up the dump?

This is easier to restore from.

> The advantage of the first method would be that I would not have to wait
> for pg_dump (it takes quite long on our 60G+ database) and would just be
> able to configure the backup agent to monitor the data directory and do
> differential backups of the files there every hour or so.

Differential backups of a running database will produce garbage. The proper
way to keep running backups of the database is through the PITR mechanism.

Very few applications that write to disk can be safely backed up while
running. You might want to look at your whole backup strategy to see if
you're really backing up what you think you're backing up.

--
Alan