Because Django does not support Postgres database schemas out of the box, in order to get this to work, use a database router.
I created a test database to try this out with, here’s how to reproduce it:
Create a test database with psql:
CREATE USER tester WITH PASSWORD 'lol so easy';
CREATE DATABASE multi_schema_db WITH OWNER tester;
CREATE SCHEMA samples AUTHORIZATION tester;
CREATE TABLE samples.my_samples (
id INTEGER NOT NULL PRIMARY KEY,
description CHAR(255) NOT NULL
);
Add the schemas to the settings as different database connections, remember to add HOST
to avoid the “Peer authentication failed” error.
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'OPTIONS': {
'options': '-c search_path=django,public'
},
'NAME': 'multi_schema_db',
'USER': 'tester',
'PASSWORD': 'lol so easy',
'HOST': 'localhost'
},
'samples': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'OPTIONS': {
'options': '-c search_path=samples,public'
},
'NAME': 'multi_schema_db',
'USER': 'tester',
'PASSWORD': 'lol so easy',
'HOST': 'localhost'
},
}
Next create the MySample
model:
from django.db import models
class MySample(models.Model):
description = models.CharField(max_length=255, null=False)
class Meta:
managed = False
db_table="my_samples"
Create a database router to direct all sample-related queries to the sample database:
from database_test.models import MySample
ROUTED_MODELS = [MySample]
class MyDBRouter(object):
def db_for_read(self, model, **hints):
if model in ROUTED_MODELS:
return 'samples'
return None
def db_for_write(self, model, **hints):
if model in ROUTED_MODELS:
return 'samples'
return None
Basically, the router will route all the models specified in ROUTED_MODELS to the database connection samples
and return None for all the other models. This will route them to the default
database connection.
Finally add the router to your settings.py
DATABASE_ROUTERS = ('database_test.db_router.MyDBRouter',)
And now when doing a query for the MySample
model, it will fetch data from the samples
schema.