Re: Auto vacuum not running -- Could not bind socket for statistics collector

Tim Schäfer <ts+ml@xxxxxxxx> · Wed, 3 Dec 2014 16:39:47 +0100 (CET)

Hi,

on the netstat question: it bound the default postgres port (5432) sucessfully
on both interfaces (lo and
eth0) it seems:

postgres@ixion41:~/data> netstat -na | grep postg
unix 2 [ ACC ] STREAM LISTENING 21156728
/var/run/postgresql/.s.PGSQL.5432
postgres@ixion41:~/data> netstat -na | grep 5432
tcp 0 0 192.168.185.41:5432 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
tcp 0 0 192.168.185.41:5432 192.168.185.42:40013 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.38:34332 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:40036 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.38:34285 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.35:49972 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.39:40494 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:40035 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:39976 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:40042 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.35:49975 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:40041 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:40023 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.34:50950 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.38:34339 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:39995 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.34:50922 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:40009 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.38:34343 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:40048 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.34:50962 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.38:34320 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.35:49966 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.36:58217 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.35:49910 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.39:40563 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.39:40566 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.35:49937 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.34:50891 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.39:40549 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.36:58215 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.40:59597 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.40:59590 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.38:34335 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.36:58212 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.35:49963 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.35:49950 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.39:40540 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.36:58167 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.36:58188 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:40032 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:40020 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.40:59587 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.40:59575 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:40046 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.40:59594 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.38:34345 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:40027 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.39:40559 ESTABLISHED
tcp 0 846 192.168.185.41:5432 192.168.185.36:58221 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.34:50947 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:39960 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.36:58196 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.34:50954 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.42:40024 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.35:49969 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.34:50961 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.39:40522 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.36:58191 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.38:34294 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.40:59584 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.40:59581 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.34:50958 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.40:59599 ESTABLISHED
tcp 0 0 192.168.185.41:5432 192.168.185.39:40557 ESTABLISHED
unix 2 [ ACC ] STREAM LISTENING 21156730 /tmp/.s.PGSQL.5432
unix 2 [ ACC ] STREAM LISTENING 21156728
/var/run/postgresql/.s.PGSQL.5432
unix 3 [ ] STREAM CONNECTED 21244990 /tmp/.s.PGSQL.5432

All the ESTABLISHED connections are the clients writing to it (slowly).

    Also you say you are using pg_ctl to start the program.
    What user are you doing that as?

As user 'postgres'. If I try it as root, it gives me the following message:

# pg_ctl restart
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.

    Also any other changes in the Connections portion of postgresql.conf.

The only change in that section besides 'listen_addresses' is 'max_connections =
140'. (The default value on SuSE 12.3 was 100.)

I also noticed the following, but it is somehow expected I guess:
vplg=# show autovacuum;
autovacuum
------------
on
(1 row)

vplg=# show track_counts;
track_counts
--------------
off
(1 row)

Here are the relevant parts from my postgresql.conf:

postgres@dbsrv:~/data> grep autovac postgresql.conf
autovacuum = on # Enable autovacuum subprocess? 'on'
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
autovacuum_max_workers = 5 # max number of autovacuum subprocesses
autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
#autovacuum_analyze_threshold = 50 # min number of row updates before
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
#autovacuum_multixact_freeze_max_age = 400000000 # maximum Multixact age
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use

postgres@dbsrv:~/data> grep track postgresql.conf
track_activities = on
track_counts = on
#track_io_timing = off
#track_functions = none # none, pl, all
#track_activity_query_size = 1024 # (change requires restart)

Thanks & greetings,

--
Tim

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general