How to connect to multiple PostgreSQL schemas from Django?

Question

In my GeoDjango project, I want to connect to an obsolete PostgreSQL / PostGIS database. It contains the following schemes:

• data // contain all geospatial data
• django // empty created by me
• public // system tables such as spatial_ref_sys

I want the Django tables shown in the screenshot to go into the django schema. I do not want to pollute the public scheme.

I want data models to be connected to the data schema. I already tried to generate models from old tables , but python manage.py inspectdb connects to the public schema.

---

To provide access to various schemas, I adapted approach 2 of this article , which reassigns individual search_path values ​​to a specific user database:

 -- user accessing django schema... CREATE ROLE django_user LOGIN PASSWORD 'secret'; ALTER ROLE django_user SET search_path TO django, public; -- user accessing data schema... CREATE ROLE data_user LOGIN PASSWORD 'secret'; ALTER ROLE data_user SET search_path TO data, public; 

Then I set up the database connections as follows:

 DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'NAME': 'multi_schema_db', 'USER': 'django_user', 'PASSWORD': 'secret', }, 'data': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'NAME': 'multi_schema_db', 'USER': 'data_user', 'PASSWORD': 'secret', }, } 

How can I actually configure that Django uses the django scheme while data models connect to the data scheme?

---

Indications

• How to use schemas in Django?
• How to specify schema name while running "syncdb" in django?
• Issue # 6148 - Add general database schema support

Answer 1

You need to use search_path :

 DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'OPTIONS' : { 'options': '-c search_path=django,public' }, 'NAME': 'multi_schema_db', 'USER': 'django_user', 'PASSWORD': 'secret', }, 'data': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'OPTIONS' : { 'options': '-c search_path=data,public' }, 'NAME': 'multi_schema_db', 'USER': 'data_user', 'PASSWORD': 'secret', }, } 

• Source: Accessing Multiple Postgres Schemas from Django

Answer 2

We have been using Django Tenant Schemas with great success. It allows you to access various schemes by identifying various tenants as owners of the schemes.

This will allow you to set up a schema based on each call. If a scheme needs to be set based on each URL, you can do this in middleware.