3

How can I tell manage.py to operate in a specific PostgreSQL schema?

My database is separated into multiple schema, one per client/deployment, to keep data separate.

However, it seems that Django does not really support schemas very well. How can I generate my models, etc for a specific schema with manage.py?

Goro
  • 9,919
  • 22
  • 74
  • 108

2 Answers2

4

You can assign a default schema to a database user or - to be precise - you assign a schema search path to a user. If that path only contains a single schema, you effectively set a default schema for the user.

Any unqualified object name (table, view, function, ...) is then looked for along the path. Unqualified CREATE statements will create the object in the first schema of the search path.

So all you need to do is to change the user that Djange uses to connect to the database:

alter user django_user set search_path to the_one_schema;
commit;

After that, e.g. a create table foo will create the table in the schema the_one_schema.

You need to log off and then log on again to make the change "active".

2

Here's what worked in the end:

I wrote a new manage.py command that was a wrapper for the commands I wanted to be schema-aware (syncdb, migrate, changepassword), and select the schema manually by:

from django.db import connection
cursor = connection.cursor()
cursor.execute('SET search_path TO ' + my_little_schema)
Goro
  • 9,919
  • 22
  • 74
  • 108
  • I encountered the exact same problem. I would love if you post more details on the wrapper tool that you wrote. – Agniva De Sarker Jul 03 '13 at 06:29
  • @AgnivaDeSarker The command wrapper was mostly based on the command wrapper in https://github.com/bcarneiro/django-tenant-schemas, it does essentially the same thing. – Goro Jul 03 '13 at 17:03
  • 1
    You should use cursor.execute('SET search_path=%s', (schema,)) instead because it is safer (SQL Injection...). – Matt3o12 Aug 08 '14 at 19:09