Database stuck in a state where vacuum and vacuumdb is not working
Hello.
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
Thank you!
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 transactionsStopped 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