Autovacuum does not work on Openshift Online Postgres

Question

I have Postgres 9.2 on my Openshift Online cartridge. Using Pgadmin3, I turned on (by ticking) the autovuum setting for postgresql.conf. However, the car vacuum does not seem to work.

Here is what I have:

• ps -ef | grep -i vacuum ps -ef | grep -i vacuum The auto- ps -ef | grep -i vacuum process is not shown.

• Using the psql console, show autovacuum says its value is ON

• Using the psql console, SELECT schemaname, relname, last_vacuum, last_autovacuum from FROM pg_stat_user_tables; does not give values ​​in the last_vacuum and last_autovacuum columns, even if I performed the manual function “Vacuum through service” using pgadmin3.

• The properties tab on db in pgAdminIII says the AUTOVACUUM value is not working

What am I missing?

EDIT
I also cannot access postgresql.conf on Openshift Online when I try to find the file on the server - hoping to manually edit the file, rather than use pgAdminIII.
- Found this https://www.openshift.com/forums/openshift/how-do-i-set-maxpreparedtransactions-on-my-postgresql-cartridge Now I can view / edit my postgresql.conf. Apparently auto-vacuum is already on, so conf has the correct setting.

When releasing 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 

Although track_counts already included in postgresql.conf

Sorry to be so stupid, but any help or pointers are greatly appreciated. Thank you in advance.

Answer 1

i ran into a similar problem and found a useful hint for this discussion :

... for some crazy reason, openshit disconnects localhost, and autovacuum only connects to localhost, I suppose it makes sense that they don’t want to vacuum the remote db ... but openshit breaks autovacuum.

one solution I found (and probably will use) is to manually add a cronjob, which makes a forced vacuum. here is a batch script that looks promising, but beware of side effects that may be related to forced vacuum (depending on your application of course).

Answer 2

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