问题描述
我在Windows上运行PotgreSQL 9.4，并不断收到错误消息，
I am running PotgreSQL 9.4 on Windows, and constantly get the error,
2015-06-15 09:35:36 EDT LOG could not rename temporary statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat": Permission denied
我还看到200-800k恒定写入global.stat和global.tmp。我看到其他用户也遇到相同的问题，但没有解决方案。
这是一个大型数据库服务器，具有300g数据和6,000个数据库。
I also see constant 200-800k writes to global.stat and global.tmp. I have seen other users with the same issue, but no solution.It is a big database server, with 300g of data, and 6,000 databases.
我尝试设置，
track_activities=off
在配置文件中，
对错误有任何帮助，还是减少了写操作？
Any help for the error, or reducing the write?
推荐答案
在获得最初的答案之后，我决定研究stats收集器的操作，尤其是研究pg_stat_tmp中文件的作用。结果，我已经在很大程度上重写了答案。
After my initial answer, I decided to research the operation of the stats collector and in particular what it is doing with the files in pg_stat_tmp. I've substantially re-written the answer as a result.
Postgresql包含收集有关其操作的统计信息和状态信息的功能。该功能在手册的中进行了说明。
Postgresql contains functionality to collect statistics and status information about its operation. The function is described in Section 27.2 of the manual.
此信息由统计信息收集程序整理。它通过 global.stat 文件可用于其他postgresql进程。第一次运行查询来访问事务中的此数据时，您所连接的后端将读取 global.stat 文件并缓存结果，直到使用该结果为止
This information is collated by the stats collector process. It is made available to the other postgresql processes via the global.stat file. The first time you run a query that accesses this data within a transaction, the backend which you are connected to will read the global.stat file and cache the result, using it until the end of the transaction.
为了使该文件保持最新状态，统计信息收集器进程会定期使用更新的信息对其进行重写。它通常每秒执行几次。过程如下：
To keep this file up to date, the stats collector process periodically re-writes it with updated information. It typically does this several times a second. The process is as follows:
- 创建一个新文件global.tmp
- 将数据写入该文件文件
- 将global.tmp重命名为global.stat，覆盖以前的global.stat
global.tmp 和 global.stats 文件被写入由配置参数。通常将其设置为 $ PGDATA / pg_stat_tmp 。
The global.tmp and global.stats files are written into the directory configured by the stats_temp_directory configuration parameter. Normally this is set to $PGDATA/pg_stat_tmp.
在关闭时，将stats文件写入文件 $ PGDATA / global / pgstat.stat ，以上tmp目录中的文件将被删除。然后再次启动数据库时，将读取并删除该文件。
On shutdown, the stats file is written into the file $PGDATA/global/pgstat.stat, and the files in the tmp dir above are removed. This file is then read and removed when the database is started up again.
通常，写入global.stats的数据量相对较少，写入数据不会产生那么多的I / O流量。但是，在某些情况下，它确实显得非常膨胀。当发生这种情况时，由于整个文件每秒被重写的次数超过一次，因此生成的负载量可能开始变得过大。
Normally, the amount of data written to the global.stats is relatively modest and writing it does not generate that much I/O traffic. However under some circumstances it does seem to get very bloated. When this happens the amount of load generated can start to get excessive as the entire file is rewritten more than once a second.
我曾经有过一次经历，那就是负载增长了相较于其他类似服务器，该系数是10或更大。这台机器确实有大量数据库（对于我们的应用程序至少为30-40个数据库，但没有像您说的那样拥有6000个数据库）。
I have had one experience where it grew by a factor or 10 or more, compared to other similar servers. This machine did have an unusually large number of databases (for our application at least - 30-40 databases - but nothing like the 6000 you say you have). It is possible that having a large number of databases exacerbates this.
下面的一些参考文献讨论了一种创建/删除大量表的模式，这些表导致这些文件中的文件膨胀，这有可能加剧这种情况。也许自动真空运行不足以消除相关的膨胀。您不妨考虑一下autovac设置。
Some of the references below talk about a pattern of creating / dropping lots of tables causing bloat in these files, and that perhaps autovacuum is not running aggressively enough to remove the associated bloat. You may wish to consider your autovac settings.
在检查了PostgreSQL源代码之后，我认为访问 global.stats 文件可能存在争用情况，这种情况可能随时发生，但由于大小过大而加剧了。文件。
After examining the postgresql source code I think there may be a race condition in accessing the global.stats file which could happen at any time, but is exacerbated by the size of the file.
Windows中的默认操作模式是，当另一个进程打开时，无法重命名或删除文件。这与Linux（或Unix）不同，在Linux（或Unix）中，可以在其他进程访问文件时重命名或删除文件。
The default mode of operation in Windows is that it is not possible to rename or remove a file while another process has it open. This is different to Linux (or Unix) where a file can be renamed or removed while other processes are accessing it.
在上面的序列中，您可以看到是否有一个后端进程在stats收集器重写文件的同时读取文件，那么后端进程在尝试重命名时可能仍会打开文件。这会导致您看到权限被拒绝错误。
In the sequence above you can see that if one of the backend processes is reading the file at the same time as the stats collector is rewriting it, then the backend process may still have the file open at the time the rename is attempted. That leads to the 'Permission Denied' error you are seeing.
自然地，当文件变得非常大时，读取它所花费的时间变得更加重要，因此统计收集器进程尝试在后端仍打开的情况下重命名的可能性增加。
Naturally when the file becomes very large, then the amount of time taken to read it becomes more significant, therefore the probability of the stats collector process attempting a rename while a backend still has it open increases.
但是，由于文件经常被重写，因此这些错误的影响是比较温和。这仅表示此特定更新失败，导致后端的统计信息略有过时。下一次更新可能会成功。
However, since the file is frequently being rewritten, the impact of these errors is relatively mild. It just means that this particular update fails, leading the the backends getting slightly out of date statistics. The next update will probably succeed.
请注意，Windows确实提供了一种文件打开模式，该模式允许通过其他进程打开或删除文件时将其删除或重命名。据我所知，Postgresql不使用这种模式。我找不到关于此的任何错误报告-似乎应该报告该错误。
Note that Windows does offer a file opening mode which does allow files to be deleted or renamed while they are opened by another process, however as far as I could tell, this mode is not used by Postgresql. I could not find any bug report on this - seems like it should be reported.
总而言之，这些错误是主要问题的副作用，这是过多的 global.stat 文件的大小。
In summary, these errors are a side effect of the main problem, which is the excessive size of the global.stat file.
据我所见， track_activites
此外，无论这些设置如何，看起来好像都在启动统计信息收集器进程，并且将影响统计信息收集器进程。继续重写文件。这些设置似乎只控制着新数据的收集。
In addition, it looks as though the stats collector process is started regardless of these settings, and will continue to re-write the file. The settings appear to control only the collection of fresh data.
我的结论是，一旦文件肿，它将保持不变并继续被重写，即使所有统计信息收集选项都关闭了。
My conclusion is that once the file has become bloated, it will remain so and continue to be re-written, even once all of the stats collection options are turned off.
一旦文件变得肿，似乎使数据库恢复到正常工作状态的最简单方法是使用以下步骤删除文件：
Once the file has become bloated, it seems that the easiest way to get the database back into a good working state is to remove the file, using the following steps:
-
停止数据库
Stop the database
停止数据库后，pg_stat_tmp目录为空，文件 $ PGDATA / global / pgstat.stat 。我们将此文件重命名为 pgstat.stat.old 。
When the DB is stopped, the pg_stat_tmp directory is empty and a file $PGDATA/global/pgstat.stat is written. We renamed this file to pgstat.stat.old.
启动数据库。它创建了一组新的pgstat文件。确认服务器运行正常后，您可以删除已重命名的旧文件。
Start the database. It creates a fresh set of pgstat files. After confirming the server was operating correctly you can remove the old file you have renamed.
这是我们使用的过程
不用说，手动操作Postgresql Data目录下的任何文件时要非常小心。
Needless to say be very careful when manually manipulating any files under the Postgresql Data directory.
此后，您可能希望监视服务器以查看文件是否再次膨胀。如果确实如此，那么这里还有一些其他想法可供考虑：
After this you may want to monitor the server to see if it the file becomes bloated again. If it does then here are some additional ideas to consider:
-
如上所述，我已经看到对此文件的一些引用变得肿如果autovacuum运行不够积极。您可能希望调整自动真空设置
As mentioned above I have seen some references to this file becoming bloated if autovacuum is not running aggressively enough. You may wish to tune the autovacuum settings
禁用track_xxx 选项不需要的href = http://www.postgresql.org/docs/current/static/runtime-config-statistics.html>第18.9.1节可能会有所帮助
Disabling any of the track_xxx options described in the Section 18.9.1 of the manual which are not required may help
可以将 pg_stats_tmp 目录放在tmpfs文件系统中（或Windows中可用的任何等效的基于RAM的文件系统中） 。这样做应避免将I / O当作这些文件的考虑。
It is possible to place the pg_stats_tmp directory in a tmpfs filesystem (or whatever equivalent RAM based filesystem is available in windows). Doing so should eliminate I/O as a concern for these files.
参考：
- Postgres stats collector showing high disk I/O
- Too much I/O generated by postgres stats collector process
- stats collector suddenly causing lots of IO
这篇关于PostgreSQL统计信息问题-无法重命名临时统计文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！