Autovacuum is not running or working

Question

We're currently using Postgres 9.4.14 hosting Zabbix 2.4 database. For a while now, the following message started to happens:

< 2017-11-23 15:15:20.037 -03 >HINT:  Close open transactions soon to avoid wraparound problems.
< 2017-11-23 15:15:20.048 -03 >WARNING:  oldest xmin is far in the past
< 2017-11-23 15:15:20.048 -03 >HINT:  Close open transactions soon to avoid wraparound problems.
< 2017-11-23 15:15:20.059 -03 >WARNING:  oldest xmin is far in the past

We restarted the datase and after startup the same message start to be happen:

< 2017-11-22 10:25:04.262 -03 >LOG:  database system was shut down at 2017-11-22 10:00:04 -03
< 2017-11-22 10:25:04.387 -03 >LOG:  MultiXact member wraparound protections are now enabled
< 2017-11-22 10:25:04.389 -03 >LOG:  autovacuum launcher started
< 2017-11-22 10:25:04.389 -03 >LOG:  database system is ready to accept connections
< 2017-11-22 10:25:15.910 -03 >WARNING:  there is no transaction in progress
...
< 2017-11-22 10:25:20.259 -03 >WARNING:  oldest xmin is far in the past
< 2017-11-22 10:25:20.259 -03 >HINT:  Close open transactions soon to avoid wraparound problems.

Vacuum daemon is running:

postgres@bchlnx361:pg_log $ ps -fea | grep vacuum
postgres   409   403  0 Nov22 ?        00:00:02 postgres: autovacuum launcher process  

and there are my postgresql.conf settings..

maintenance_work_mem = 2280MB           
autovacuum_work_mem = -1               
autovacuum = on                   
log_autovacuum_min_duration = 0         
autovacuum_max_workers = 2              
autovacuum_naptime = 60                 
autovacuum_vacuum_threshold = 5000      
autovacuum_analyze_threshold = 50000    
autovacuum_vacuum_scale_factor = 0.01   
autovacuum_analyze_scale_factor = 0.05  
autovacuum_freeze_max_age = 1000000000 
autovacuum_multixact_freeze_max_age = 400000000 
autovacuum_vacuum_cost_delay = 20ms     
autovacuum_vacuum_cost_limit = -1  

But tables are'nt being vacuumed, when I've checked on pg_stat_user_tables, all rows has last_Autovacuum with null values, and some tables really need to be vacuumed:

zabbix_db=# select tuple_count, dead_tuple_count from pgstattuple('public.triggers');
 tuple_count | dead_tuple_count 
-------------+------------------
       73657 |          5161183
(1 row)

Someone could help as?

Regards,

Answer 1

We finally found the problem!! This database had a Standby Read-Only database but We broked the replication, removed all Standby parameters but We forgot to remove the replication slot. Yestarday we tried to remove the replication slot but We couldn't because the database was not in standby mode:

zabbix_db=# select pg_drop_replication_slot('standby2');
ERROR:  replication slots can only be used if wal_level >= archive

We had to

1. Shutdown the database,
2. Change wal_level to archive Startup
3. Run again the pg_drop_replication (now it works)
4. Shudown
5. Disable wal_level
6. Startup

And.. the autovacuum comes back!!

postgres 19381 19089  1 13:07 ?        00:09:59 postgres: autovacuum worker process   zabbix_db
postgres 19411 19089  2 13:07 ?        00:11:20 postgres: autovacuum worker process   zabbix_db
postgres 19430 19089  1 13:07 ?        00:09:51 postgres: autovacuum worker process   zabbix_db