Re: dealing with file size when archiving databases

On Monday 20 June 2005 10:14 pm, Alvaro Herrera wrote:
> On Mon, Jun 20, 2005 at 09:28:51PM -0500, Andrew L. Gould wrote:
> > I've been backing up my databases by piping pg_dump into gzip and
> > burning the resulting files to a DVD-R. Unfortunately, FreeBSD has
> > problems dealing with very large files (>1GB?) on DVD media. One
> > of my compressed database backups is greater than 1GB; and the
> > results of a gzipped pg_dumpall is approximately 3.5GB. The
> > processes for creating the iso image and burning the image to DVD-R
> > finish without any problems; but the resulting file is
> > unreadable/unusable.
>
> Tom's response is certainly something to consider; also, note that if
> you "pg_dump -t" each table separately, the dumps are not necessarily
> consistent with one another, meaning that you could end up with an
> unrecoverable backup if a transaction modifying two (foreign key-)
> dependant tables happens to run after backing up one but before
> backing up the other.

Ouch! I hadn't thought of that. (Isn't it wonderful that I've only had
to restore dumps for upgrades?!)

Thanks.

Andrew Gould