postgresql - 在 Amazon RDS Postgres 上编写 PostGIS 设置脚本

Question

我正在尝试在我的 Amazon RDS Postgres 实例上编写 PostGIS 设置脚本。这是我正在运行的命令:

create extension postgis;
create extension fuzzystrmatch;
create extension postgis_tiger_geocoder;
create extension postgis_topology;

CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser')
  FROM (
    SELECT nspname, relname
    FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
    WHERE nspname in ('tiger','topology') AND
    relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;

alter schema tiger owner to rds_superuser;
alter schema topology owner to rds_superuser;

当我使用 psql 客户端登录服务器并单独运行每个命令时，一切正常。

测试结果:

dbname=> select na.address, na.streetname, na.streettypeabbrev, na.zip from normalize_address('1 Devonshire Place, Boston, MA 02109') as na;
 address | streetname | streettypeabbrev |  zip  
---------+------------+------------------+-------
       1 | Devonshire | Pl               | 02109
(1 row)

当我将所有这些命令放到一个 .sql 文件中，然后尝试通过 psql 命令一次运行它们时...

PGPASSWORD='****' psql -h us-west-2-stg-db-1.***.rds.amazonaws.com -U dbuser -d dbname -f setup_postgis.sql -o setup_postgis.log

...我在尝试测试 PostGIS 功能时遇到以下错误:

dbname=> select na.address, na.streetname, na.streettypeabbrev, na.zip from normalize_address('1 Devonshire Place, Boston, MA 02109') as na;
ERROR:  function normalize_address(unknown) does not exist
LINE 2: from normalize_address('1 Devonshire Place, Boston, MA 02109...
             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

知道为什么当直接在服务器上运行命令时，这个确切的命令序列有效，但当通过命令行作为 .sql 文件运行时却无效吗？

注意:我确定我用来运行命令的数据库用户是 rds_superuser 角色

Answer 1

编写安装脚本不是问题。正如@MikeT 建议的那样，当连接到 Postgres 数据库时，search_path 确实存在问题。

看起来，当从服务器内部运行安装命令时，search_path 会自动设置为正在运行的客户端 session 以包含 tiger 扩展。因此，当您在安装扩展后立即运行测试命令时，它位于 search_path 中并且测试命令有效。

但是，当您断开 session ，然后重新连接时，它不再位于 search_path 中。由于作为远程脚本运行命令会在脚本完成后立即结束 session ，因此存在相同的问题。下次连接时，tiger 不再在 search_path 中，测试命令不起作用。

因此看来要使此测试在安装脚本后起作用，您必须手动将 tiger 添加到您的搜索路径:SET search_path=public,tiger;

然后运行命令，一切正常。

如果您的应用程序在查询中需要 tiger 函数，您将不得不通过更改数据库配置的 search_path 来将 tiger 锁定到您的搜索路径，如此列表服务响应中所述:

http://lists.osgeo.org/pipermail//postgis-users/2012-November/035701.html

或者每次连接到数据库时都必须设置它。