Message-ID: <4BC8C0D3.70406@ipso.ca>
Date: Fri, 16 Apr 2010 15:56:03 -0400
From: Alexandre Leclerc <aleclerc@ipso.ca>
Organization: Ipso Technologies
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr;
	rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Vacuum Full (PG 8.1) - Urgent help needed - Cancel &
	transaction "liberation"
References: <4BC8639C.4010202@ipso.ca> <9048.1271429162@sss.pgh.pa.us>
	<4BC8A623.8090201@ipso.ca>
	<4BC8638E0200002500030A01@gw.wicourts.gov>
	<4BC8B0CE.2010401@ipso.ca>
	<1271444725.21263.80.camel@jd-desktop.unknown.charter.com>
	<4BC871F90200002500030A0D@gw.wicourts.gov>
	<13971.1271447063@sss.pgh.pa.us>
In-Reply-To: <13971.1271447063@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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