使用Google Cloud SQL重置pg_stat_statements [英] Reset pg_stat_statements with Google Cloud SQL

问题描述

我正在使用在Google Cloud SQL中运行的PostgreSQL实例。

I am using a PostgreSQL instance running in Google Cloud SQL.

我试图重置pg_stat_statements，因为我们对表进行了改进并添加了一些索引。不幸的是，当我尝试使用主要的 postgres 用户运行以下命令时：

I am trying to reset the pg_stat_statements because we made improvements to our tables and added some index. Unfortunately, when I try to run the following command using the main postgres user:

select pg_stat_reset();

我收到以下错误消息：

ERROR:  permission denied for function pg_stat_reset

的权限被拒绝托管服务和一些事情需要解决，但是能够重置pg_stat以便调整数据库很重要。基本上，如果您进行更改，则需要能够重置pg_stat，以使其更改前的输出不会受到数据的污染。即使在托管服务中，这种调整也是至关重要的。

I understand that it's a managed service and some things need to be walled off, but it's important to be able to reset pg_stat so you can tune the database. Basically if you make a change, you need to be able to reset pg_stat so that its output isn't polluted from data prior to the change. Even in a managed service, this kind of tuning is critical to be able to do.

任何人都知道如何将其与Google Cloud SQL配合使用吗？

Anyone know how I can get this to work with Google Cloud SQL?

谢谢。

推荐答案

我能够向GoogleCloud支持人员咨询此事，这里是他们获得的回报：

I was able to ask GoogleCloud Support about this and here's what they got back with:

pg_stat_reset（）需要超级用户特权。但是
CloudSQL不支持它。我们建议您使用pg_stat_statements_reset（）
函数代替重置统计信息。为此，要从Cloud Console创建
的用户（即默认PostgreSQL用户），或者应由默认
PostgreSQL用户通过执行以下命令授予对该功能的
EXECUTE权限命令：授予在
函数pg_stat_statements_reset（）上执行的权限；

pg_stat_reset() needs superuser privileges. But it is not supported in CloudSQL. We recommand that you use pg_stat_statements_reset() function instead to reset the statistics. To do this, either user has to be created from Cloud Console (i.e. Default PostgreSQL user), or EXECUTE permission on this function should be granted by a default PostgreSQL user by executing the following command: grant execute on function pg_stat_statements_reset() to ;

我能够使用postgres用户成功使用此命令。请注意，此功能必须在启用pg_stat_statement的数据库上运行。

I was able to use this command successfully using the postgres user. Note that this function must be run on the database where pg_stat_statement was enabled.

这篇关于使用Google Cloud SQL重置pg_stat_statements的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！