Re: Need quick help with standalone mode

On 7/31/07, RW <postgres(at)tauceti(dot)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(at)commandprompt(dot)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