Menu
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:
ps -ef | grep -i vacuum
No autovacuum process is shown.
Using psql console, show autovacuum, says that its value is ON
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.
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.
843
You can start the vacuum manually also. By running psql command vacuum full analyze verbose . It will take some time. Beware that this exclusively locks the tables and therefore might not be recommended for large productive databases.
This is on by default; however, track_counts must also be enabled for autovacuum to work. This parameter can only be set in the postgresql. conf file or on the server command line; however, autovacuuming can be disabled for individual tables by changing table storage parameters.
The Autovacuum Daemon. PostgreSQL has an optional but highly recommended feature called autovacuum, whose purpose is to automate the execution of VACUUM and ANALYZE commands. When enabled, autovacuum checks for tables that have had a large number of inserted, updated or deleted tuples.
Autovacuum may be disabled on certain tables We can re-enable it by running: ALTER TABLE my_table SET (autovacuum_enabled = true);
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).
136
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
