Re: Database stuck in a state where vacuum and vacuumdb is not working

On Mon, 2022-01-10 at 13:19 +0000, jonathaan67 wrote:
> My database server stuck in a state where vacuum and vacuumdb is not working. Yesterday saw this error:
>
> > HINT: To avoid a database shutdown, execute a full-database VACUUM in "production_services"
> > WARNING: database "production_services" must be vacuumed within 10059807 transactions
>
> Stopped all services using database and tried to launch vacuumdb and it immediately failed with this error message:
>
> > vacuumdb: error: processing of database "production_services" failed: ERROR:  invalid page in block 38919 of relation base/31893/272925691
>
> I continued with manually executing vacuum on:
>
> > SET zero_damaged_pages=on;
> > VACUUM(FULL, VERBOSE, ANALYZE) queue;
>
> It was running good for some time until connection was terminated and I stopped/started
> server and after that nothing is working. vacuumdb is giving same error about invalid page,
> but manually running vacuum sql command with zero_damaged_pages is giving error:
>
> > ERROR:  database is not accepting commands to avoid wraparound data loss in database "production_services"
> > HINT:  Stop the postmaster and vacuum that database in single-user mode.
> > You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
> > SQL state: 54000
>  
> And postgre log is filling with this error now:
>
> > WARNING:  database "production_services" must be vacuumed within 999999 transactions
>
> So vacuumdb and vacuum with zero_damaged_pages=on is not working anymore.
>
> How can I get out of this state and force database to accept incoming command for vacuum and
> zeroing damaged pages on vacuum?  Is there any way to make vacuumdb command line do "zero_damaged_pages=on"?
> Some data loss on that table is acceptable and its not a problem.
>
> Running "PostgreSQL 13.2, compiled by Visual C++ build 1914, 64-bit" on Windows 10, Database size is over 1,5 TB,
> damaged table ~350 GB

You should use single-user mode, like the error message recommends.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com