如何从Django连接到多个PostgreSQL架构？

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

在我的GeoDjango项目中，我想连接到传统的PostgreSQL / PostGIS数据库。它包含以下模式：

data // contains all the geospatial data
data //包含所有地理空间数据
django // empty, created by me
django //空，由我创造
public // system tables such as spatial_ref_sys
public //系统表，例如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.

我希望屏幕截图中显示的Django表进入django架构。我不想污染公共架构。

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.

我希望“数据”模型连接到数据模式。我已经尝试从遗留表中生成模型，但python manage.py inspectdb连接到公共模式。

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:

为了提供对不同模式的访问，我采用了本文的方法2，该方法将各个search_path值预先分配给特定的数据库用户：

-- 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?

如何在“数据”模型连接到数据模式时实际配置Django使用django模式？

Readings

How to use schemas in Django?
如何在Django中使用模式？
How to specify schema name while running "syncdb" in django?
如何在django中运行“syncdb”时指定模式名称？
Issue #6148 - Add generic support for database schemas
问题＃6148 - 添加对数据库模式的通用支持

3 个解决方案

#1

You have to leverage the search_path:

你必须利用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
来源：从Django访问多个Postgres模式

#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:

如果您不需要通过迁移来管理表，则可以使用模型的db_table属性的转义引号：

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

#3

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.

我们使用Django Tenant 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.

这将允许您基于每个调用设置架构。如果需要基于每个url设置架构，则可以在中间件中执行此操作。

#1