主页 / dba / 问题 / 145280
Accepted
jpmc26
Asked: 2016-07-29 14:34:48 +0800 CST

将 search_path 重置为全局集群默认值

  • 772

假设我像这样永久更改我的数据库search_path：

ALTER DATABASE my_db SET search_path TO "$user",public,other_schema;

如何将其重置为该集群上原始、未更改的数据库所具有的默认值？

这通常是"$user",public，但可以从配置文件中更改。有一种方法可以读取来自配置文件的参数值（因此我可以在最坏的情况下构建动态查询），但我没有任何运气找到任何类似的东西。

postgresql configuration

1 个回答

  1. Best Answer

    出厂默认值

    如何将其重置为该集群上原始、未更改的数据库所具有的默认值？

    您可以从 中读取boot_val，pg_settings但这实际上是已编译的出厂默认设置，而不是postgresql.conf.

    SELECT boot_val
FROM   pg_settings
WHERE  name LIKE 'search_path';

    你没有忽略简单RESET吗？

    要重置设置（删除它），您可以简单地：

    ALTER DATABASE my_db RESET search_path;

    但这并不一定会给您提供“该集群上的原始、未更改的数据库”。它只是删除了数据库的自定义设置。有几种方法可以设置搜索路径：

    它不会改变search_path你当前会话的。效果（如果有）在您的下一个会话中可见。

    有关的：

    连接至template1

    您可以从连接中获取当前设置template1，就像@Abelisto 建议的那样。但您可能希望留在当前会话中。您可以为此使用 dblink：

    SELECT * FROM dblink('hostaddr=127.0.0.1 dbname=template1 user=postgres password=you_wish'
                         , $$SELECT boot_val, source FROM pg_settings
                             WHERE name LIKE 'search_path'$$
                          ) AS t(search_path text, source text);

search_path    | source
---------------+---------
"$user",public | default

    这有几个挑战：

    • 您需要安装附加模块dblink。这很简单：

    • 即使函数中止，UDF 中的持久插入也是如此

    • 您需要连接到template1. 您需要提供用户密码。

    • 即使在template1设置中也可能源于对角色或数据库（或另一个实例）的设置（即使它们可能没有）。所以检查列source。如果它说'default'，你明白了。如果它显示“数据库”或“用户”，您将返回到第 1 格...

    读取实际设置postgresql.conf

    .. 使用pg_read_file()，但这也受到一些重要的限制。手册：

    这些功能的使用仅限于超级用户。

    和：

    只能访问数据库集群目录中的log_directory文件。

    大胆强调我的。
    如果您的配置文件在其他地方（例如在 Debian 和朋友的默认安装中），您必须在文件系统中的数据或日志目录中创建指向实际配置文件的符号链接。

    SELECT substring(txt, $$\nsearch_path\s*=\s*'([^']+)'$$) AS search_path
FROM   pg_read_file((SELECT setting FROM pg_settings WHERE name = 'config_file')
                  , 0, 10000000) AS txt;  -- arbitrary 10 MB max.

search_path
------------
public

    请注意我如何动态获取配置文件的路径：

    SELECT setting FROM pg_settings WHERE name = 'config_file'

    然后我使用带有 substring() 的正则表达式来提取设置：

    \nsearch_path\s*=\s*'([^']+)'

    那应该可行，不过，我没有花太多时间来使正则表达式防弹。可能会被第一个匹配的注释设置所迷惑。你可能想测试更多。

    最后，从 Postgres 9.4 开始，还有一个ALTER SYSTEM命令可以覆盖postgresql.auto.conf文件中的设置。你也必须检查一下。

    • 4

相关问题