Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux