Re: Vacuum Full (PG 8.1) - Urgent help needed - Cancel & transaction "liberation"

Le 2010-04-16 15:44, Tom Lane a écrit :
> "Kevin Grittner"<Kevin.Grittner@wicourts.gov>  writes:
>
>> "Joshua D. Drake"<jd@commandprompt.com>  wrote:
>>
>>> if you actually managed to start two services against the
>>> same data directory, I hope you have a backup, you can restore
>>> from.
>>>
>
>
>> This is 8.1 under Windows, and he connected to a different database
>> with each backend.  He got errors writing the WAL files, and it
>> apparently wouldn't let him start a second VACUUM on the other
>> database.  I'm hoping that the initial VACUUM (of the big database)
>> can continue and the WAL problems will cycle out without corrupting
>> anything.  Is that overly optimistic?
>>
> Maybe, but if he doesn't have a recent backup then that's probably the
> best thing to try.  I'm not actually sure how he would've started two
> standalone backends though --- there *is* an interlock against that,
> just as there is for two postmasters in the same data directory.
> Maybe if he was bullheaded enough to remove the lock file manually :-(
>
>

The backup should work ok. The postmaster was closed every night for
file-backup.

The vacuum raised a "max_fsm_pages" of 142000 not enought and stopped.

Is increasing the number enought to have it continue or other parameters
are required? (Or is there a way in 8.1 to increate the memory for
maintenance?) (Is there a quick hint to calculate the size required?)

Spec of the Server:
- Windows Server 2003 / 32 bits
- 3 GB ram

(Now I understand why an initial DB of 6 GB is now 38 GB: vacuuming has
been stopped and space wasted since!)

As a side question, is it possible to make a pg_dumpall on a DB that
could have been potentially damaged by the two postgres.exe executions
at the same time? (We did play arround with file read-only state in the
/base folder but not in this purpose: it was to make sure the DB was not
read only. Maybe the error message arrived after this manipulation, I
can't remember. But yes the two postgres program executed on the same
"base" folder, but not the same DB.)

Maybe our best solution is start over from the backup.

>> Also, the
>> "full-database vacuum" terminology seems too likely to be
>> interpreted as VACUUM FULL for best results.  Perhaps it's worth
>> changing that to just "database vacuum" or "vacuum of the entire
>> database"?
>>
> We did change that ...
> http://archives.postgresql.org/pgsql-committers/2008-12/msg00096.php
>
>

That is great.

--
Alexandre Leclerc