Need quick help with standalone mode

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

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

Greetings
Robert

Merlin Moncure wrote: