☰

postgresql 追蹤計數和 autovacuum 不起作用

tag-icon • tag-icon ubuntu azure postgresql vacuum
postgresql 追蹤計數和 autovacuum 不起作用

在啟動日誌中，條目表明 autovacuum 不起作用。我查詢 pg_stat_user_tables 表，儘管我之前運行了真空查詢，但 last_vacuum 和 last_autovacuum 列為空。將 pgadmin 連接到資料庫表示真空不工作。

我在兩個 Ubuntu Azure VM 上使用 postgresql。一台虛擬機被設定為主虛擬機，第二台虛擬機是透過串流的方式複製資料庫。大致描述於https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps。

除了 autovacuum 之外，一切似乎都很有效。啟動期間會記錄以下錯誤：

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

在 postgresql.config 中我使用以下設定：

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

在資料庫上尋找最後一個（自動）真空的查詢（select * from pg_stat_user_tables）會為最後一個（自動）真空提供空列，而不是日期時間。就在我運行 VACUUM FULL VERBOSE 之前；這給了我真空結果。

如果我使用以下命令查詢真空設定：

select *
from pg_settings 
where name like 'autovacuum%'

這是結果：

"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"

這些是“track_”結果：

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

pg_hba.conf（沒有複製和網路/用戶設定）如下所示：

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

/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

這是「netstat -ant|grep 5432」清理並格式化後的結果。

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

我不認為自動真空需要工作，因為

因此，在啟動期間，track_counts 在運行時被停用。

我一直在尋找改變 iptables 的解決方案。如果沒有任何 iptable 規則，它將無法運作。我已連接到本機主機作為主機。我已更改 Azure 中的防火牆設定。我打開 5432 從所有 IP 存取虛擬機器。我可以從其他系統存取資料庫。我已將conf重設為預設值，僅進行複製變更。我多次重啟服務。

我缺什麼？

答案1

你想解決這個問題：

日誌：測試訊息未在統計收集器的套接字上通過
日誌：停用統計收集器缺少工作插座

統計收集器期望來自本地主機的 UDP 封包。鑑於localhost您的情況看起來不錯/etc/hosts（特別是它無法解析為 IPv6），下一個更合理的解釋是有防火牆過濾這些封包。

有關的：建立UDP套接字時出現問題解決方法： 發現並解決了建立UDP套接字的問題。這是因為作業系統防火牆（iptables）限制建立 UDP 套接字。

答案2

我想詳細說明答案@丹尼爾給出了我的問題的解決方案。

我已經設定了 iptables 來存取 postgresql，如下所示：

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

我以為這已經足夠了。但是當我使用sudo iptables --flush並重新啟動 postgres 伺服器時出現錯誤由於缺少工作套接字而停用統計收集器失去了。

我還使用iptraf來調查流量（sudo apt-get install iptraf sudo iptraf）。我注意到流量源自伺服器的 ip 本機（子網路）位址，但位於不同的連接埠。這是從屬電腦上的流量（不含天藍色流量）。

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

我假設此流量被 iptables 阻止，因為它不透過環回。因此我清理了 iptables。這是結果：

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

我包括了子網。我認為這就是它起作用的原因，因為 SubnetIpSlave 和 SubnetIpMaster 都在這個範圍內。我可能被允許刪除已建立、相關規則。

日誌看起來應該是：

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

我很開心 ;）

答案3

根據您的鏈接，You should now be able to ssh freely between your two servers as the postgres user.因此，您需要為 postgres 用戶設定從主設備到從設備以及從設備到主設備的信任關係。

您可以使用ssh-keygen空白密碼建立一對密鑰。

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]-----+ 更多資訊請參考這個關聯。

此外，您需要在 Azure NSG 上開啟連接埠 5432。

相關內容