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
