title: Postgres import to RDS: could not execute query: ERROR: type "earth" does not exist
body: <p>Having trouble doing a pg_restore into an RDS instance.</p>
  
  <p>Source DB: A dockerized PG9.4 instance.</p>
  
  <pre><code>pg_dump -Fc -h localhost -p 5432 -U user --no-synchronized-snapshot -Fd --jobs 2 dbname --file="/pgdump/dbname_fd"
  </code></pre>
  
  <p>Destination DB: RDS PG9.6 instance:</p>
  
  <pre><code>#drop and recreate databases with proper ownership
  export PGPASSWORD=''
  psql -h $pgHost -U dbname postgres -c 'DROP DATABASE dbname;'
  export PGPASSWORD=''
  psql -h $pgHost -U rdspostgresmaster postgres -c 'create database dbname;'
  psql -h $pgHost -U rdspostgresmaster postgres -c 'alter database dbname owner to dbname;'
  
  # Drop extensions
  psql -h $pgHost -U rdspostgresmaster postgres -c 'DROP EXTENSION IF EXISTS hstore;
  DROP EXTENSION IF EXISTS earthdistance;
  DROP EXTENSION IF EXISTS postgis;
  DROP EXTENSION IF EXISTS ltree;
  DROP EXTENSION IF EXISTS plpgsql;'
  
  # Install superuser extensions
  psql -h $pgHost -U rdspostgresmaster postgres -c 'CREATE EXTENSION IF NOT EXISTS hstore;'
  psql -h $pgHost -U rdspostgresmaster postgres -c 'CREATE EXTENSION IF NOT EXISTS earthdistance with cascade;;'
  psql -h $pgHost -U rdspostgresmaster postgres -c 'CREATE ROLE IF NOT EXISTS postgres;'
  psql -h $pgHost -U rdspostgresmaster postgres -c 'GRANT rds_superuser to postgres'
  psql -h $pgHost -U rdspostgresmaster postgres -c 'GRANT ALL ON SCHEMA public TO postgres;'
  
  # dbname permissions
  psql -h $pgHost -U rdspostgresmaster postgres -c 'grant rds_superuser to dbname'
  psql -h $pgHost -U rdspostgresmaster postgres -c 'GRANT postgres to dbname;'
  
  #connect as dbname user and add extensions
  export PGPASSWORD=''
  psql -h $pgHost -U dbowner dbname -c 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
  CREATE EXTENSION IF NOT EXISTS cube;
  CREATE EXTENSION IF NOT EXISTS earthdistance with cascade;
  CREATE EXTENSION IF NOT EXISTS ltree;
  CREATE EXTENSION IF NOT EXISTS plpgsql;'
  
  ###Restore
  export PGPASSWORD=''
  pg_restore -h $pgHost -U dbowner --jobs=4 -d dbname -Fd /pgdump/dbname_fd
  </code></pre>
  
  <p>Getting the following:</p>
  
  <pre><code>pg_restore: [archiver (db)] Error from TOC entry 4623; 1259 39684305 INDEX location_search_ll_to_earth_gist dbname
  pg_restore: [archiver (db)] could not execute query: ERROR:  type "earth" does not exist
  LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
                                                                ^
  QUERY:  SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
  CONTEXT:  SQL function "ll_to_earth" during inlining
  Command was: CREATE INDEX location_search_ll_to_earth_gist ON public.location_search USING gist (public.ll_to_earth((latitude)::double pr...
  </code></pre>
  
  <p>My other extensions seem to be working fine, but that "earth" type is giving trouble. I've tried installing the extension at various points in the process (commenting out either of the CREATE EXTENSION blocks, for instand), as either the RDS postgres master or as the dbowner user, getting this error both times. I've also tried to hone in my dump and restores to the public or the dbname schema. </p>
  
  <p>Is there something I have to do to make that extension work with that public schema?</p>