postgresql - 通过脚本安装 postgis 扩展

Question

我正在尝试将 GIS 扩展程序自动安装到 Postgres (AWS RDS) 中。下面的脚本是使用 AWS doco here 创建的.它在等待某个命令的用户输入时卡住。

脚本.sql

select current_user;

create extension postgis;
create extension fuzzystrmatch;
create extension postgis_tiger_geocoder;
create extension postgis_topology;
\dn

alter schema tiger owner to rds_superuser;
alter schema tiger_data owner to rds_superuser;
alter schema topology owner to rds_superuser;
\dn

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;

SET search_path=public,tiger;

select na.address, na.streetname, na.streettypeabbrev, na.zip
from normalize_address('1 Devonshire Place, Boston, MA 02109') as na;

select topology.createtopology('my_new_topo',26986,0.5);

这些命令在手动运行时都可以正常工作，但是有一个命令需要用户输入，我不知道如何让它自动化。

导致问题的命令是:

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;

执行时，我不会像其他命令那样返回到 psql shell。我得到如下输出:

                                exec
--------------------------------------------------------------------
 ALTER TABLE tiger.loader_platform OWNER TO rds_superuser;
 ALTER TABLE tiger.pagc_rules OWNER TO rds_superuser;
 ALTER TABLE tiger.zip_lookup_base OWNER TO rds_superuser;
 ALTER TABLE topology.topology OWNER TO rds_superuser;
 ...
 ALTER TABLE tiger.pagc_lex OWNER TO rds_superuser;
 ALTER TABLE tiger.zip_lookup_all OWNER TO rds_superuser;
 ALTER TABLE tiger.featnames_gid_seq OWNER TO rds_superuser;
 ALTER TABLE tiger.bg_gid_seq OWNER TO rds_superuser;
:

它要求我按 q，然后它退出并继续执行脚本。

我正在运行的命令是:psql -h myrdsinstance.rds.amazonaws.com -U myusername -d mydbname -a -f script.sql

Answer 1

看起来寻呼机启动了。尝试在 psql 中禁用它

\pset pager off

要在没有寻呼机支持的情况下启动 psql，请取消设置 PAGER 环境变量，如下所示:

PAGER= psql