0

We recently added Geodjango and PostGIS to the system. I see that PostGIS installs itself in the public schema. Now, we are using PostgreSQL schemas for client tenancy, so for example client A will have a schema tenant_A, so we usually set the search path manually:

SET search_path TO tenant_A;

But since PostGIS lives inside the public schema, I will now have to do this:

SET search_path TO tenant_A, public;

Would it ever make sense to simply move the PostGIS tables - or even only the geometry_columns table - into the individual client schemas? The advantage of that would be 1) clients' GIS data would have a greater degree of separation and 2) Tenant-specific maintenance (backup, migrations, etc) would be easier.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Goro
  • 9,919
  • 22
  • 74
  • 108

1 Answers1

1

That would hardly make sense, since the PostGIS objects are the same for all clients. If every client installs a separate instance, you waste a lot of disk space and also weaken cache efficiency.

If other objects live in the public schema that are of no interest to some clients, it might be an option to install PostGIS to a dedicated schema like postgis and include that in the search_path instead.

If you really want to separate clients rigorously, you'd have to create separate databases or even a db cluster per client.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • True. What about only the geometry_columns table... which can contain data that is different from client to client? – Goro Aug 22 '13 at 17:40
  • @Goro: Moving around a single table won't change the need to have the PostGIS schema in your `search_path`, though. You need it for functions and operators to begin with. BTW, [`geometry_columns`](http://postgis.net/docs/using_postgis_dbmanagement.html#geometry_columns) is a view since PostGIS 2.0. – Erwin Brandstetter Aug 22 '13 at 17:44