☰

postgresql 轨道计数和自动清理不起作用

tag-icon • tag-icon ubuntu azure postgresql vacuum
postgresql 轨道计数和自动清理不起作用

启动日志条目表明自动清理未运行。我查询了 pg_stat_user_tables 表，尽管我之前运行了清理查询，但 last_vacuum 和 last_autovacuum 列仍为空。将 pgadmin 连接到数据库时，发现清理未运行。

我在两台 Ubuntu Azure VM 上使用 postgresql。一台 VM 设置为主服务器，另一台 VM 是通过流式传输复制的数据库。大致描述如下https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps。

除了自动清理之外，一切似乎都正常。启动期间记录了以下错误：

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

在数据库中执行查询（从 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 规则，它将无法工作。我已将 localhost 作为主机连接。我已更改 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 本地（子网）地址，但位于不同的端口上。这是从属机器上的流量（没有 azure 流量）。

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。

相关内容