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

You could temporarily increase the fsm size in the postgres configuration
so as to be able to properly map all the free space. I think you're going
to do a dump/restore in due course in order to return the database to
something like it's normal size, at which point (if you're RAM constrained)
you might want to revert that parameter.

Cheers,
Robin

On Fri, 16 Apr 2010 15:56:03 -0400, Alexandre Leclerc <aleclerc(at)ipso(dot)ca>
wrote:
> Le 2010-04-16 15:44, Tom Lane a écrit :
>> "Kevin Grittner"<Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>>
>>> "Joshua D. Drake"<jd(at)commandprompt(dot)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