Thanks a lot! That was a life saver :-)
Greetings
Robert
Merlin Moncure wrote:
> On 7/31/07, RW <postgres@xxxxxxxxxxx> wrote:
>
>> Hi!
>>
>> I'm really in big trouble with a production database. It doesn't accept
>> connections anymore:
>>
>> 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be
>> vacuumed within 999832 transactions
>> 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute
>> a full-database VACUUM in "userbase".
>> 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be
>> vacuumed within 999832 transactions
>> 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute
>> a full-database VACUUM in "userbase"...
>>
>> So I tried this script (after shutting down the postmaster):
>>
>> #!/bin/bash
>>
>> /usr/local/pgsql81/bin/postgres -D /data/pgsql/data/lindau userbase << SQL
>> VACUUM FULL VERBOSE ANALYZE
>> SQL
>>
>> But all I get is:
>>
>> backend> 2007-07-31 19:27:33 CEST WARNING: database "userbase" must
>> be vacuumed within 999831 transactions
>> 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute
>> a full-database VACUUM in "userbase".
>> 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be
>> vacuumed within 999830 transactions
>> ...
>> 2007-07-31 19:27:33 CEST WARNING: database "userbase" must be
>> vacuumed within 999809 transactions
>> 2007-07-31 19:27:33 CEST HINT: To avoid a database shutdown, execute
>> a full-database VACUUM in "userbase".
>> 2007-07-31 19:27:33 CEST ERROR: could not access status of
>> transaction 539227074
>> 2007-07-31 19:27:33 CEST DETAIL: could not open file "pg_clog/0202":
>> No such file or directory
>>
>> I've provided the data path. So I don't understand the entry "could not
>> open file...". The database version is 8.1.5
>> and it runs on Redhat Linux 4 AS x86_64.
>>
>
>
> First of all, update the postmater. This was a bug that was fixed in
> the 8.1 series.
>
> There is a resolution to this problem. Here is Alvaro's notes on the subject:
>
> On 2/6/07, Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> wrote:
>
>> Merlin Moncure wrote:
>>
>>
>>> ya, it doesn't seem to match, as this seems to be repeating quite
>>> regularly. interesting that my 'clog' files start at 06B6 and count
>>> up. 0207 is way off the charts.
>>>
>>> a lot of applications are hitting this database, and so far everything
>>> seems to be running ok (i found this log msg by accident), but I am
>>> now officially very nervous.
>>>
>> I don't think there's much cause for concern here. If my theory is
>> correct, this is an autovacuum bug which was fixed in 8.1.7.
>>
>> What I'd do is create a 0207 clog file, fill it with 0x55 (which is
>> "transactions committed" for all transactions in that interval), and do
>> a VACUUM FREEZE on that database. You'll need to set
>> pg_database.datallowconn=true beforehand.
>>
>> Of course, I'd copy the files somewhere else and experiment on a scratch
>> postmaster, running on a different port, just to be sure ...
>>
>
>
> Good news is you haven't lost any data. update the binaries and
> schedule a maintenance window if you have to.
>
> merlin
>
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
