posts /

Postgis in multiple schema

Restoring the very same dump into multiple schemas is not that easy when using postgis. Typically a pg_dump contains public.geometry when creating the tables which is not reachable as postgis will be in a different schema at least on the second import.

First create a different schema for the postgis extension

create schema postgis;
create extension postgis with schema postgis;

Then set the search path for non schema qualified postgis functions to be found:

alter database joineddb SET search_path TO public,postgis;

Then feed your psql dumps into psql using sed. This import will end up in the public schema:

zcat "dump-with-postgis-new.sql.gz"  \
    | sed -e 's/public.geometry/postgis.geometry/ig'\
    | psql -f - joineddb

Then connect to the database and rename schema:

alter schema public rename to new;
create schema public;

And import the second file:

zcat "dump-with-postgis-old.sql.gz"  \
    | sed -e 's/public.geometry/postgis.geometry/ig'\
    | psql -f - joineddb

Now you have both dumps in the database for comparison.