From: Alexandre Leclerc <aleclerc@ipso.ca>
To: pgsql-admin@postgresql.org
Subject: Re: Vacuum Full (PG 8.1) - Urgent help needed - Cancel & transaction "liberation"
Date: Fri, 16 Apr 2010 15:56:03 -0400
Message-ID: <4BC8C0D3.70406@ipso.ca> (raw)
In-Reply-To: <13971.1271447063@sss.pgh.pa.us>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: aleclerc@ipso.ca
Subject: Re: Vacuum Full (PG 8.1) - Urgent help needed - Cancel & transaction "liberation"
In-Reply-To: <4BC8C0D3.70406@ipso.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox