Re: Recovery/Restore and Roll Forward Question.

From: Bruce McAlister <bruce(dot)mcalister(at)blueface(dot)ie>
To: Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at>
Subject: Re: Recovery/Restore and Roll Forward Question.
Date: 2007-06-21 12:06:41
Message-ID: 467A69D1.3090105@blueface.ie
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Henka 2007-06-21 13:05:59 Re: 8.2.3 PANIC with "corrupted item pointer"
Previous Message Bruce McAlister 2007-06-21 12:03:07 Re: Recovery/Restore and Roll Forward Question.