Re: [HACKERS] Hot Backup Ability

> On Tue, 29 Jun 1999, Bruce Momjian wrote:
>
> > > Just out of curiosity, I did a DUMP on the database while running a script
> > > that ran a pile of updates. When I restored the database files, it was so
> > > corrupted that I couldn't even run a select. vacuum just core dumped...
> >
> > When you say DUMP, you mean pg_dump, right? Are you using 6.5?
>
> Erm. Well, no. I was running ufsdump. Once I read the section on mvcc and
> re-did the test with the pg_dump, I realised that it does work as
> documented...

Woh! Not a good idea. We can't get a proper snapshot if the ufs blocks
are moving around while we are doing the backup. We need pg_dump.

Glad it worked when you did it with pg_dump.

> I should think this is a good feature to broadcast to everyone. I don't
> think other free systems support it.

Probably not. We have it as one of our main items in the release notes,
and on the web page describing the release. We need people like you to
tell others about it.

>
> The thing I got confuzed with that blocked transactions was the pg_vacuum.
> Seeing as how it physically re-arranges data inside the tables and
> indexes, is there any hope for not blocking the table for a long time as
> it re-arranges a 15 gig table?

Not really. In fact, it even shrinks the table to give back free space.
The 6.5 pg_vacuum is much faster than earlier versions, but on a 15gig
table, it is going to take some time.

Some day, it would be nice to allow re-use of expired rows without
vacuum. It is on our TODO list.

> Will re-usable page support (whenever it is expected) eliminate the need
> for vacuum?

It will allow you to vacuum less frequently, and perhaps never if you
don't want space back from expired rows.

> Would it be easy to come up with a scheme for the vacuum function defrag a
> set number of pages and such, release its locks if there is another
> process blocked and waiting, then resume after that process is finished?

That is a very nice idea. We could just release and reaquire the lock,
knowing that if there is someone waiting, they would get the lock.
Maybe someone can comment on this?

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026