How to connect to multiple PostgreSQL schemas from Django?

Question

In my GeoDjango project I want to connect to a legacy PostgreSQL/PostGIS database. It contains the following schemas:

• data // contains all the 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 schema.

I want the "data" models to connect to the data schema. I already tried to generate models from the legacy tables but python manage.py inspectdb connects to the public schema.

---

In order to provide access to the different schemas I adapted the approach 2 of this article which preassigns individual search_path values to specific database users:

-- 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 configured 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 schema while "data" models connect to the data schema?

---

Readings

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

Answer 1

You have to leverage the 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

If you don't need to manage the tables through migrations, you could use escaped quotes for the db_table attribute of your model:

class SomeModel(models.Model):
    field1 = models.AutoField(primary_key=True)  
    class Meta():
        managed=False
        db_table=u'"schema\".\"table"'