Re: how to vacuum from standalone backend - Mailing list pgsql-general

On Tue, Dec 14, 2010 at 11:04 AM, Steve Clark <sclark@netwolves.com> wrote:
> Help!
>
> This is postgresql 8.1.3 also the database debug can easily be recreated if

Do you have a reason for running a version of postgresql that is
missing over 2 years of security and bug fixes?  The 8.1 branch is up
to 8.1.22 now, I'd really advise updating to it when you can.   If you
can upgrading to 8.4 or 9.0 would be useful, as they're much faster,
and support for 8.1 is gonna go away fairly soon.

>  vacuumdb debug
> vacuumdb: could not connect to database debug: FATAL:  database is not
> accepting commands to avoid wraparound data loss in database "debug"
> HINT:  Stop the postmaster and use a standalone backend to vacuum database
> "debug".
>
> I am getting the above message. I am not quite sure how to proceed.

First, turn autovac back on, or setup a cron job to run at night and
run vacuum for you to prevent this from happening again.

> I did the following:
> postgres -D /usr/local/pgsql/data debug
>
> backend> vacuum full;
> WARNING:  database "debug" must be vacuumed within 999998 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999997 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999996 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999995 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999994 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999993 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999992 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999991 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999990 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999989 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999988 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999987 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999986 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999985 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999984 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999983 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> ERROR:  could not access status of transaction 449971277
> DETAIL:  could not open file "pg_clog/01AD": No such file or directory

So maybe you've run into one of the many bugs in 8.1.3?

> Now what?

Update your pg version first.  (and I know you've dropped and
recreated the db and all that.  but still, you need an update).