PostGIS scripting on Amazon RDS Postgres

Question

I am trying a script to configure PostGIS on my Amazon RDS Postgres instance. Here are the commands that I run:

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 in to the server with the psql client and run each command separately, everything works fine.

Test result:

 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 of these commands into a .sql file and then try to run them all at once with the 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 

... When trying to test PostGIS functions, the following error occurs:

 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 a command directly on the server, but not when running through the command line as a .sql file?

NOTE. I am sure that the db user that I use to run commands is in the role of rds_superuser

Answer 1

This is not a problem with writing the installation. This is really a search_packer problem when connecting to Postgres DB, as @MikeT suggested.

It seems that when you run installation commands from inside the server, the search path is automatically set to start a client session that includes the tiger extension. Therefore, when you run the test command immediately after installing the extension, it is in the search_path and the test commands work.

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

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

Then run the command, and all is well.

If your application needs tiger functions in requests, you will either have to block the tiger in your search path by changing the configured search path in the database, as indicated in this mailing reply:

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

Or you will have to install it every time you connect to the database.