Django and postgresql schemas

Question

I've been trying to solve this one all week, help very much appreciated.

I have various schemas in a postgres db and I would like to be able to map to them from within the same or across different django apps.

Some of the schemas are :

samples

excavation

geophysics

...

I have tried the recommended way, but I'm not getting any data to display from the schemas, I can only connect to the public schema with managed tables. Here is the database connections from the settings.py file.

DATABASES = {

'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'OPTIONS': {
            'options': '-c search_path=django,public'
        },
        'NAME': 'gygaia',
        'USER': 'appuser',
        'PASSWORD': 'secret',
},

'samples': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'OPTIONS': {
            'options': '-c search_path=samples,public'
        },
        'NAME': 'gygaia',
        'USER': 'appuser',
        'PASSWORD': 'secret',
},
}

source: https://www.amvtek.com/blog/posts/2014/Jun/13/accessing-multiple-postgres-schemas-from-django/

In the model.py I add:

    from django.db import models

    # Create your models here.
    class Storage(models.Model):
        #id = models.IntegerField(default=0)
        storage_id = models.AutoField(primary_key=True)
        store_name = models.CharField(max_length=200, default='')
        address_1 = models.CharField(max_length=200, default='')
        address_2 = models.CharField(max_length=200, default='')
        region = models.CharField(max_length=200, default='')
        city = models.CharField(max_length=200, default='')
        zip = models.CharField(max_length=200, default='')
        country = models.CharField(max_length=200, default="Turkey")
        user = models.CharField(max_length=200, default="Gygaia")
        datestamp = models.DateTimeField(auto_now=True)

    class Meta():
        managed=False
        db_table = 'samples\".\"store'

I don't want to restrict schemas to users, and the database was created a few years ago so I'm not allowed to bring it all under one schema. I know there are various solutions posted on stackoverflow and other coreners of the internet, I have tried these, but I'm unable to get this to work. Any ideas how to solve thos one??

Answer 1

search_path is the key here. This is a Postgres setting that decides how your database will be traversed when trying to find a matching table (source).

You do not need to define multiple routers or explicitly specify db_table or anything like that unless you have tables with the same name in multiple schemas, which you should avoid as it simplifies this process.

Therefore, to make it so that your PostgreSQL connection will always traverse all of the schemas you desire when you are accessing your database, you just need to permanently modify its search_path (source):

ALTER DATABASE <database_name> SET search_path TO schema1,schema2;
-- After running this command, you will have to close your current connection and
-- reconnect for the changes to be reflected in your session

Then, you will be able to define only the default Django DATABASES setting as usual and it will look through all of the given schemas to find the appropriate tables.

Note, however, that you will likely still need to use db_table to create new tables in anything but schema1.

Answer 2

First create tables in postgres using schemas and then access these tables in django using command python manage.py inspectdb > models.py then migrate back.

Answer 3

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.

Answer 4

I also consulted that source, but I could not solve it like you, but by performing tests I achieved the following.

If we have for example, the schemas foo and bar, writing in the Meta:

class MySample1 (models.Model):
     description = models.CharField (max_length = 255, null = False)
     class Goal:
         managed = True
         db_table = 'fo\".\"my_samples1'

class MySample2 (models.Model):
     description = models.CharField (max_length = 255, null = False)
     class Goal:
         managed = True
         db_table = 'bar\".\"my_samples2'

Then we can redirect each model to the scheme we want provided we have the variable managed in True. The limitation is that we have to name the table ourselves.