postgresql track counts e autovacuum não está funcionando

Question

Nas entradas do log de inicialização, o autovacuum não está funcionando. Eu consultar a tabela pg_stat_user_tables e as colunas last_vacuum e last_autovacuum estão vazias, apesar da consulta de vácuo que corri pouco antes. Conectar o pgadmin ao banco de dados dá a indicação de que o vácuo não está funcionando.

Estou usando o postgresql em duas VMs do Ubuntu Azure. Uma VM é configurada para ser a principal, a segunda é o banco de dados replicado por meio de streaming. Grosso modo descrito em link .

Tudo parece tão bom, exceto pelo autovacuum. Durante a inicialização, o seguinte erro é registrado:

LOG:  test message did not get through on socket for statistics collector
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 2017-01-19 14:07:13 UTC
DEBUG:  checkpoint record is at 38/F6000028

No postgresql.config eu uso as seguintes configurações:

track_counts = on  
autovacuum = on
log_autovacuum_min_duration = 200 
autovacuum_max_workers = 1  
autovacuum_naptime =960
autovacuum_vacuum_threshold = 128 
autovacuum_analyze_threshold = 256

Uma consulta (selecione * de pg_stat_user_tables) no banco de dados para encontrar o último vácuo (automático) fornece colunas vazias para o último vácuo (automático) em vez de um datetime. Fui logo antes de executar o VACUUM FULL VERBOSE; e isso me deu resultados de vácuo.

Se eu consultar as configurações de vácuo com:

select *
from pg_settings 
where name like 'autovacuum%'

Este é o resultado:

"autovacuum";"on"<br />
"autovacuum_analyze_scale_factor";"0.1"
"autovacuum_analyze_threshold";"256"
"autovacuum_freeze_max_age";"200000000"
"autovacuum_max_workers";"1"<br />
"autovacuum_multixact_freeze_max_age";"400000000"
"autovacuum_naptime";"960"<br />
"autovacuum_vacuum_cost_delay";"20"
"autovacuum_vacuum_cost_limit";"-1"
"autovacuum_vacuum_scale_factor";"0.2"
"autovacuum_vacuum_threshold";"128"
"autovacuum_work_mem";"-1"

Estes são os resultados 'track_':

"track_activities";"on"
"track_activity_query_size";"1024"
"track_commit_timestamp";"off"
"track_counts";"off"
"track_functions";"none"
"track_io_timing";"off"

O pg_hba.conf (sem as configurações de replicação e rede / usuário) é assim:

local   all             all                                     trust
host    all             all             localhost               trust
host    all             all             10.1.1.5/32             md5
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0 0.0.0.0         md5

o / etc / hosts:

127.0.0.1       localhost
127.0.1.1       ubuntu
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts

Este é o resultado de 'netstat -ant | grep 5432' Se for limpo e formatado.

User@Machine:/datadrive/log/postgresql/pg_log$ netstat -ant|grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN
tcp       39      0 InternIpMaster:5432           InternIpSlave:36338          ESTABLISHED
tcp        0      0 InternIpMaster:5432           IpJob:63814     TIME_WAIT
tcp        0      0 InternIpMaster:5432           IpJob:22192      TIME_WAIT
tcp        0      0 InternIpMaster:5432           IpJob:47729      TIME_WAIT
tcp        0      0 InternIpMaster:5432           IpJob:55663      TIME_WAIT
tcp6       0      0 :::5432                 :::*                    LISTEN

Eu não espero que o autovacuum precise funcionar ainda por causa do

Assim, durante a inicialização, as track_counts são desativadas no tempo de execução.

Eu tenho procurado por soluções alterando o iptables. Sem quaisquer regras de iptable, isso não funcionará. Eu me conectei ao host local como um host. Eu mudei as configurações de firewall no Azure. Eu abri o 5432 para acessar o vm de todos os ip's. Eu sou capaz de acessar o banco de dados de outros outros sistemas. Eu restaurei o conf para o padrão apenas com alterações de replicação. Eu reiniciei o serviço muitas vezes.

O que eu sinto falta?

    

Answer 1

Eu quero elaborar a resposta @Daniel deu e a solução para o meu problema.

Eu configurei o iptables para obter acesso ao postgresql assim:

sudo iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
sudo iptables -A INPUT -i lo -j ACCEPT
sudo iptables -A OUTPUT -o lo -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 22 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 443 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
sudo iptables -A INPUT -j DROP

Eu assumi que isso era muito. No entanto, quando eu usei sudo iptables --flush e reiniciei o servidor postgres, o erro desabilitando o coletor de estatísticas por falta de soquete de trabalho desapareceu.

Eu também usei iptraf para investigar o tráfego ( sudo apt-get install iptraf sudo iptraf ). Notei que um tráfego originado no endereço ip local (sub-rede) do servidor, mas em portas diferentes. Este é o tráfego na máquina escrava (sem o tráfego azul).

SubnetIpSlave:22
SubnetIpSlave:45622
SubnetIpSlave:44770
SubnetIpSlave:48948
SubnetIpMaster:5432

Eu suponho que esse tráfego é bloqueado pelo iptables pois ele não está passando pelo loopback. Por isso eu limpei o iptables. Este é o resultado:

sudo iptables -A INPUT -i lo -j ACCEPT
sudo iptables -A OUTPUT -o lo -j ACCEPT
sudo iptables -A INPUT -p icmp -j ACCEPT
sudo iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 22 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 443 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 5432 -j ACCEPT
sudo iptables -A INPUT -s 10.1.1.0/24 -j ACCEPT
sudo iptables -A INPUT -j DROP

Eu incluí a sub-rede. Eu acho que isso é o que faz o trabalho, para o SubnetIpSlave e SubnetIpMaster estão neste intervalo. Eu provavelmente tenho permissão para remover a regra ESTABLISHED, RELATED .

O log parece que deveria:

2017-01-24 09:19:38 UTC [1482-1] LOG:  database system was shut down in recovery at 2017-01-24 09:17:41 UTC
2017-01-24 09:19:38 UTC [1483-1] [unknown]@[unknown] LOG:  incomplete startup packet
2017-01-24 09:19:38 UTC [1482-2] LOG:  entering standby mode
2017-01-24 09:19:38 UTC [1482-3] DEBUG:  checkpoint record is at 5D/F2042CA8

Estou feliz;)

    

Answer 2

Você quer corrigir isso:

LOG: test message did not get through on socket for statistics collector
LOG: disabling statistics collector for lack of working socket

O coletor de estatísticas espera pacotes UDP do host local. Dado que localhost parece bem no seu /etc/hosts (especificamente, ele não resolve IPv6) a próxima explicação mais plausível é que há um firewall filtrando esses pacotes.

Relacionados: Problema na criação de soquetes do UDP resolvido com: Encontrou e resolveu o problema na criação de sockets UDP. Foi por causa do firewall do sistema operacional (iptables) restringindo a criação de sockets UDP.

    

Answer 3

De acordo com o seu link, You should now be able to ssh freely between your two servers as the postgres user. Então, você precisa configurar a relação de confiança do usuário postgres do mestre para o escravo e escravo para o mestre.

Você pode usar ssh-keygen para criar um par de chaves com senha em branco.

shui@shui:~$ ssh-keygen Generating public/private rsa key pair. Enter file in which to save the key (/home/shui/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/shui/.ssh/id_rsa. Your public key has been saved in /home/shui/.ssh/id_rsa.pub. The key fingerprint is: SHA256:mCyBHNLeEdCH2VqBjhtOC8njVLSXnjU7V9GbufK+hlE shui@shui The key's randomart image is: +---[RSA 2048]----+ |..++.*.. .. | | o.+B = .. | |.o+=.B o . + | |o+= *oooo . E | |o+.+.o+oS. . . | | .+ . o o . | | = | | . o | | oo. | +----[SHA256]-----+ Para mais informações, consulte este link .

Além disso, você precisa abrir a porta 5432 no NSG do Azure.