Thread: Need quick help with standalone mode

Need quick help with standalone mode

From

Date:

31 July 2007, 14:59:15

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.

Any hints?

Thanks!
Robert

Re: Need quick help with standalone mode

From

"Merlin Moncure"

Date:

31 July 2007, 15:28:36

On 7/31/07, RW <postgres@tauceti.net> 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@commandprompt.com> 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

Re: Need quick help with standalone mode

From

Date:

31 July 2007, 16:49:46

Thanks a lot! That was a life saver :-)

Greetings
Robert

Merlin Moncure wrote:
> On 7/31/07, RW <postgres@tauceti.net> 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@commandprompt.com> 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
>