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

From: jonathaan67 <jonathaan67(at)protonmail(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Database stuck in a state where vacuum and vacuumdb is not working
Date: 2022-01-10 13:19:44
Message-ID: N-XsRTLnyEkHO3ofTAoLKSCI5Lzv7qqiYeBuZY281kVHfVCW0qo2yt8FuV6ixKIC-x0fMUO4IO0orTzaIXrFrvHvrgONC7WLQmSTrBrDAnM=@protonmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2022-01-10 13:29:22 Re: Database stuck in a state where vacuum and vacuumdb is not working
Previous Message Markur Sens 2022-01-10 10:16:23 CI/CD Boilerplate for extension binaries without source code ?