Autovacuum is not running on Openshift Online Postgres cartridge

Question

I have Postgres 9.2 on my Openshift Online cartridge. Using Pgadmin3, I have enabled (by ticking the box) the autovuum setting for postgresql.conf. However, the autovacuum does not seem to be running.

Here is what I have:

1. ps -ef | grep -i vacuum No autovacuum process is shown.

2. Using psql console, show autovacuum, says that its value is ON

3. Using psql console, SELECT schemaname, relname, last_vacuum, last_autovacuum from FROM pg_stat_user_tables; gives no value in last_vacuum and last_autovacuum column even though I did a manual Vacuum via Maintenance function using pgadmin3.

4. The properties tab on the db in pgAdminIII says AUTOVACUUM value of 'not running'

What do I miss?

EDIT
I also cannot access the postgresql.conf on Openshift Online when trying to find the file on the server - hoping to manually edit the file instead of using pgAdminIII.
-- Found this https://www.openshift.com/forums/openshift/how-do-i-set-maxpreparedtransactions-on-my-postgresql-cartridge I am now able to view/edit my postgresql.conf. Apparently the autovacuum is on already so the conf has the right setting.

When issue pg_ctl restart -m fast I got

LOG:  could not bind socket for statistics collector: Permission denied 
LOG:  trying another address for the statistics collector 
LOG:  could not bind socket for statistics collector: Permission denied 
LOG: trying another address for the statistics collector 
LOG:  could not bind socket for statistics collector: Cannot assign requested address LOG:  trying another address for the statistics collector 
LOG:  could not bind socket for statistics collector: Cannot assign requested address LOG:  disabling statistics collector for lack of working socket 
WARNING:  autovacuum not started because of misconfiguration 
HINT:  Enable the "track_counts" option. 
LOG:  database system was shut down at 2014-04-22 09:58:19 GMT 
LOG:  database system is ready to accept connections

Though track_counts is already set to on in postgresql.conf

Sorry for being so stupid but any help or pointers are much appreciated. Thank you in advance.

Beka

Make sure you have no sessions in the state <idle> in transaction. They will prevent auto vacuum from cleaning up old values – Audraaudras

Thank you for prompt response but how do I check that. Not quite sure I understand what the transaction this is. thank you. – Beka

As a superuser: select * from pg_stat_activity – Audraaudras

There is no sessions with <idle> stage – Beka

bugzilla.redhat.com/show_bug.cgi?id=849428 – Gurevich

Answer 1

i ran into a similar issue and found a helpful hint in this discussion:

... for some insane reason, openshit disabled localhost, and autovacuum only connects to localhost, I suppose it makes sense that they wouldn't want to be trying to vacuum a remote db... but openshit breaks autovacuum.

one solution i've found (and that i'll probably use) is to manually add a cronjob that does a forced vacuum. here is a batch-script that looks promising but be careful with the side-effects that a forced vacuum might involve (depending on you app of course).

Respondent

i suffering from same problem but it does'nt solve my issue – Cosine

Answer 2

Patching postgres to use the OPENSHIFT_PG_HOST environment variable instead of localhost seems to solve the problem: pgstat.patch.

Philous