Re: File system level backup

On Wed, 22 Oct 2003, Ganesan Kanavathy wrote:

> All the while I was doing pg_dump as backup method for my pgsql db.
> Today I came across File system level backup on Postgresql
> Documentation.
>
> After reading it, I am quite unsure whether File system level backup is
> better than pg_dump.
>
> Furthermore, do we really need to do File system level backup or pg_dump
> is good enough?

The two are more complementary than equivalent. Here's a short list of
the pros and cons of each:

Advantages of pg_dump:
* transportable between versions of postgresql / other RDBMSs
* can be run while the database is up and get a consistent snapshot
* can be run from a remote machine easily.

Disadvantages of pg_dump:
* slower than a file system backup
* may require hand editing of the dump before restoring (rare, but has
happened due to some obscure bugs.)

Advantages of file level backup:
* Very fast
* Can be used to clone a database quickly
* Is a great way to backup a database before a minor revision (i.e. 7.3.3
to 7.3.4) in case things go horribly wrong.

Disadvantages of file level backup:
* Server needs to be shut down to get a consistent backup
* file level database backups are not very portable. I.e. if you want to
restore a file level backup, the target database must be running the same
base version of postgresql, must be on the same architecture, and must
have many of the compile time options be the same, or it won't work.

Generally, pg_dump is preferred, but file level backups have their place.