如何在导出中排除PL/pgSQL函数?

问题描述：

我使用以下命令从服务器的数据库中转储一些结构，以便能够在本地硬盘上创建数据样本.

I use following command to dump some structures from server' database to be able to create sample of data on my local hard drive.

pg_dump -h myserver.com -U product_user -s -f ./data/base.structure.postgresql.sql -F p -v -T public.* -T first_product.* -T second_product.* -T another_product.locales mydatabase

我需要排除一些模式，否则它将因权限或其他错误而终止.即使我不公开架构，它也会转储该架构中的所有功能，如下所示:

I need to exclude some schemas otherwise it would ended up on permissions or other errors. Even that I exclude schema public, it dumps all functions in that schema, like this:

REVOKE ALL ON FUNCTION gin_extract_trgm(text, internal) FROM PUBLIC;
psql:./data/base.structure.postgresql.sql:8482: ERROR:  function gin_extract_trgm(text, internal) does not exist

我知道这来自PostgreSQL中的全文或相似性插件，但是我不使用它，也不需要在我的机器上使用它，所以我想排除这些功能.

I know this comes from the fulltext or similarity plugin in PostgreSQL, but I don't use it and don't need it on my machine, so I'd like to exclude these functions.

我该怎么办?

答

我需要排除一些模式

I need to exclude some schemas

pg_dump可以切换到排除模式:

pg_dump has a switch to exclude schemas:

pg_dump -N schema ...

我引用有关pg_dump的手册:

-N模式
--exclude-schema = schema

-N schema
--exclude-schema=schema

请勿转储任何与模式模式匹配的模式.根据与-n相同的规则来解释该模式. -N可以给不止一次排除与多个模式中的任何一个匹配的模式.
...

Do not dump any schemas matching the schema pattern. The pattern is interpreted according to the same rules as for -n. -N can be given more than once to exclude schemas matching any of several patterns.
...

使用 PostgreSQL 9.1 或更高版本，您可以使用新选项将扩展移动到单独的模式中，甚至可以预安装旧式模块.您可以使用(新样式)扩展名注册旧对象，然后使用新工具.对于fulltext和similarity，您可能是指 fuzzystrmatch 和 tsearch2 .示例:

With PostgreSQL 9.1 or later you have new options to move extensions into a separate schema - even pre-installed old-style modules. You can register old object with your (new-style) extension and then use the new tools. With fulltext and similarity you probably mean fuzzystrmatch and tsearch2. Example:

为扩展名fuzzystrmatch注册现有的旧式对象:

CREATE EXTENSION fuzzystrmatch SCHEMA public FROM unpackaged;

删除扩展名:

DROP EXTENSION fuzzystrmatch;

将其安装到另一个架构:

Install it to another schema:

CREATE EXTENSION fuzzystrmatch SCHEMA my_schema;

当然，如果正在使用扩展中的对象，则不能删除该扩展.
另外，如果安装到另一个架构，则需要对使用中的功能进行架构限定，或者将架构添加到

Of course, you cannot drop the extension, if objects from it are in use.
Also, if you install to another schema, you need to schema-qualify its functions in use or add the schema to the search_path.

如何在导出中排除PL/pgSQL函数?

相关推荐