Re: Backups - Mailing list pgsql-general

From Albe Laurenz
Subject Re: Backups
Date
Msg-id D960CB61B694CF459DCFB4B0128514C2079CF0BF@exadv11.host.magwien.gv.at
Whole thread Raw
In response to Backups  (Richard Harley <richard@scholarpack.com>)
Responses Re: Backups  (Bret Stern <bret_stern@machinemanagement.com>)
List pgsql-general
Richard Harley wrote:
> Very simple question -  does pg_dump/dumpall hit the server in terms
of database performance? We
> currently do nightly backups and I want to move to hourly backups but
not at the expense of hogging
> all the resources for 5 mins.
>
> Pg_dumpall is currently producing a 1GB file - that's the combined
size of around 30 databases and it
> takes around 5 mins to run.

pg_dump will be a performance hit, because it consumes disk I/O
capacity.
You could measure how the system is affected by your current backup.

On the other hand, instead of doing an hourly pg_dump, it might be
preferable to do a filesystem backup and PITR. That way you have to
do a backup only once a day (or week, depends how much traffic you have
and how fast you have to restore) and can still recover to an
arbitrary point in time.

Yours,
Laurenz Albe

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Fetch from cursor with indexed sorting
Next
From: "George Weaver"
Date:
Subject: Using copy with a file containing blank rows