On 03/07/2015 12:23, Bill Moran wrote:
On Fri, 03 Jul 2015 13:16:02 +0200
Jan Lentfer <Jan.Lentfer@xxxxxx> wrote:
Am 2015-07-03 13:00, schrieb howardnews@xxxxxxxxxxxxx:
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.
Thanks everyone.
I am trying to move away from pg_dump as it is proving too slow. The
size of the database clusters are approaching 1TB (with multiple
individual compressed pg_dumps of around 100GB each, but the pace of
change is relatively glacial compared to the size so I am hoping that
WAL backups will prove to be much more efficient. As you all point out
it looks like I will need to test the various methods to find the best
solution for me.
A supplementary question would be: would rsync be a viable alternative
to pg_basebackup when performing the file system copy. I have seen a few
posts on this subject which suggest rsync is more prone to mistakes but
is potentially the faster option. Love to hear all your thoughts on this
subject before I risk trying rsync.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
