On Thu, May 7, 2020 at 4:18 PM github kran <githubkran@xxxxxxxxx> wrote:
On Thu, May 7, 2020 at 1:33 PM Michael Lewis <mlewis@xxxxxxxxxxx> wrote:It is trying to do a vacuum freeze. Do you have autovacuum turned off? Any settings changed from default related to autovacuum?Read 24.1.5. Preventing Transaction ID Wraparound FailuresThese may also be of help-
https://info.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql
https://www.2ndquadrant.com/en/blog/managing-freezing/Note that you need to ensure the server gets caught up, or you risk being locked out to prevent data corruption.Thanks Mike.1) We haven't changed anything related to autovacuum except a work_mem parameter which was increased to 4 GB which I believe is not related to autovacuum2) The vacuum was not turned off and few parameters we had on vacuum areautovacuum_analyze_scale_factor = 0.02 and autovacuum_vacuum_scale_factor = 0.053) The database curently we are running is 2 years old for now and we have around close to 40 partitions and the datfrozenxid on the table is 343 million whereas the default is 200 million. I would try doing a manual auto vacuum on those tableswhere the autovacuum_freeze_max_age > 200 million. Do you think It's a right thing to do ?.I will also go through this documents.
Few more things 5/7 - 8:40 PM CDT
1) I see there are 8 Vacuum workers ( Not sure what changed) running in the background and the concern I have is all of these vacuum processes are running with wrap around and while they are running
I can't either DROP or ALTER any other tables ( REMOVE Inheritance for any of old tables where the WRITES are not getting written to). Any of the ALTER TABLE OR DROP TABLE DDL's arer not getting exeucted even I WAITED FOR SEVERAL MINUTES , so I have terminated those queries as I didn't have luck.
2) The VACUUM Process wrap around is running for last 1 day and several hrs on other tables.
3) Can I increase the
autovacuum_freeze_max_age on the tables on production system ?
Thanks
