Scripting PostGIS setup on Amazon RDS Postgres

Question

I am trying to script the setup of PostGIS on my Amazon RDS Postgres instance. Here is the commands I am running:

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;

When I log into the server with the psql client and run each command individually, everything works great.

Result of test:

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)

When I drop all those commands into a .sql file and then try to run them all at once via a psql command...

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

...I get the following error when trying to test the PostGIS functions:

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.

Any idea why this exact sequence of commands works when running command direct on server, but not when run via command line as a .sql file?

NOTE: I am certain the db user I am using to run the commands is in the rds_superuser role

What does show search_path show for the server? Is tiger in there? — Mike T
What Mike T is getting at is that this is likely schema related, as if a schema is not in your search path the function won't be found from that context. — John Powell
So clearly when I am remoted in via the psql client, the schema is in the search path. But when I try and run the commands via a script using psql -f it is not. How can I add a schema to the search path for that case? — Adam Duro
@MikeT How can I go about checking what my search path is? And more importantly, if I need to adjust it, how can I do that? — Adam Duro
with psql: show search_path; can be modified with alter database ... DDL — Mike T

Adam Duro Adam Duro · Accepted Answer · 2014-10-22T02:26:51

It is not a problem with scripting the installation. It is indeed a problem with the search_path when connected to the Postgres DB as @MikeT suggested.

It appears that when running the install commands from inside the server, the search_path is automatically set for the running client session to include the tiger extension. So when you run the test command immediately after installing the extension, it is in the search_path and the test commands works.

However, when you disconnect the session, and then re-connect, it is no longer in the search_path. Since running the commands as a remote script ends the session as soon as the script is complete, the same problem exists. Next time you connect, tiger is no longer in the search_path and the test command does not work.

So it would appear that for this test to work post install script, you must manually add tiger to your search path: SET search_path=public,tiger;

Then run the command, and all is well.

If your app will require the tiger functions in queries, you will either have to lock tiger into your search path by altering the database configured search_path as outlined in this listserv response:

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

Or you will have to set it each time you make a connection to the DB.

Scripting PostGIS setup on Amazon RDS Postgres

1 Answers