6

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.

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
green
  • 226
  • 2
  • 12

2 Answers2

4

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).

antiplex
  • 938
  • 2
  • 12
  • 17
0

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

Changaco
  • 790
  • 5
  • 12