On Fri, 03 Jul 2015 13:16:02 +0200
Jan Lentfer <Jan.Lentfer@web.de> wrote:
> Am 2015-07-03 13:00, schrieb howardnews@selestial.com:
> > On 03/07/2015 11:39, Guillaume Lelarge wrote:
> >>
> >> > In that case is there any recommendation for how often to make
> >> base backups in relation to the size of the cluster and the size of
> >> the WAL?
> >> >
> >>
> >> Nope, not really. That depends on a lot of things. Our customers
> >> usually do one per day.
> >>
> >>
> > Excuse my ignorance... Is the base backup, in general, faster than
> > pg_dump?
>
> It is a different approach. With the base backup you are actually
> backing up files from the filesystem ($PGDATA directory), whereas with
> pg_dump your saving the SQL commands to reload and rebuild the database.
> "Usually" a file based backup will be faster, both on backup and
> restore, but it is - as mentioned - a different approach and it might
> also not serve all your purposes.
One of the things that makes a lot of difference is the amount of
redundant data in the database. For example, indexes are completely
redundant. They sure do speed things up, but they're storing the same
data 2x for each index you have. When you do a base backup, you have
to copy all that redundancy, but when you do a pg_dump, all that
redundant data is reduced to a single CREATE INDEX command. The
result being that if your database has a lot of indexes, the pg_dump
might actually be faster.
But the only way to know is to try it out on your particular system.
--
Bill Moran