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

@falsetru The documentation for automatic database router does not mention how to deal with multiple schemas in the one database. — JJD
– JJD, Commented Aug 5, 2015 at 17:07
@JJD Thank you for the response! I think you should mark it the right answer if it solved your problem, I neglected the answers on this question because it has no marked right answer and no up votes as well. — Abz Rockers
– Abz Rockers, Commented Jan 21, 2017 at 7:04

JJD · Accepted Answer · 2016-09-04 22:59:19Z

11

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

answered Sep 4, 2016 at 22:59

JJD

52.7k63 gold badges218 silver badges352 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JJD · Accepted Answer · 2018-04-09 21:39:07Z

7

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"'

edited Apr 9, 2018 at 21:39

JJD

52.7k63 gold badges218 silver badges352 bronze badges

answered Apr 3, 2018 at 14:59

wbloos

2713 silver badges4 bronze badges

4 Comments

Mathuv Over a year ago

This worked for me. You can simply use without escaped quotes too. db_table='schema"."table'

ladorm Over a year ago

This is great, and as far as I can tell, its working with migrations too!

Ladenkov Vladislav Over a year ago

did not work for me nor with quotes neither without

sɪʒɪhɪŋ βɪstɦa kxɐll Over a year ago

It worked for me without managed=False and creating only the schema in the database before running any migrations.

Incognos · Accepted Answer · 2015-08-05 17:46:27Z

2

We use Django Tenant Schemas with great success. It allows you to access different schemas by delineating different tenants as the owners of the schemas.

This will allow you to set the schema on a per call basis. If the schema needs to be set on a per url basis, you can do that in middleware.

edited Aug 5, 2015 at 17:46

answered Jul 30, 2015 at 14:12

Incognos

2,0011 gold badge18 silver badges28 bronze badges

Comments

agas0077 · Accepted Answer · 2025-02-11 15:43:24Z

For me this article helped a lot: https://erthalion.info/2014/03/08/django-with-schemas/

Basically it suggest setting search_path not via DATABASES...OPTIONS, but using connection_created signal.

In my case, I created signal.py in my core app an put this code inside. This work both for migrations and basic usage.

from django.conf import settings
from django.db.backends.signals import connection_created
from django.dispatch import receiver

@receiver(connection_created)
def setup_connection(sender, connection, **kwargs):
    # Чтобы грузить данные приложения в конкретную схему.
    if connection.alias == "default":
        cursor = connection.cursor()
        cursor.execute(f'SET search_path="{settings.SEARCH_PATH}"')

Collectives™ on Stack Overflow

How to connect to multiple PostgreSQL schemas from Django?

Readings

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Readings

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related