Обсуждение: Need quick help with standalone mode
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
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: > 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 >