Albe Laurenz wrote:
> Richard Huxton wrote:
>>> In our environment it takes approx 2 hours to perform a PIT backup of
>>> our live system:
>>>
>>> [1] select pg_start_backup('labe;')
>>> [2] cpio & compress database directory (exclude wals)
>>> [3] select pg_stop_backup()
>>>
>>> However, if we perform a plain dump (pg_dump/pg_dumpall) we
>>> can dump the whole lot in 15 minutes. For us this is more efficient.
>> It sounds like there's something strange with your setup if it's
> quicker
>> for pg_dump to read your data than cp. Do you have *lots* of indexes,
> or
>> perhaps a lot of dead rows?
>
> That sounds like a good bet.
>
> Did you ever do a VACUUM FULL?
> What is the size of the online backup?
> What is the size of the pg_dumpall?
>
I have autovacuum configured on the system, but have only just recently
(yesterday) found out that the autovacuum daemon only appears to be
selecting 1 database to work on, i have another thread open on this
mailing list for that particular issue.
Online backup size compressed is around 3.4GB.
pg_dumpall size compressed is around 2GB.
We do have a couple indexes, however, the only one that has a large
amount of bloat is the one referenced in the autovacuum post on this
mailing list. Hopefully if i can get autovacuum working with that
database, then that would be one less worry :)
> Yours,
> Laurenz Albe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>