Question: Multiple Schema migrates

[WilliamHH]

Thank you for this work ... I'm testing in an app at the moment with good success so far.
QUESTION: Is it possible to do multi schema updates at the same time for POSTGRESQL... e.g. TENANT_NAME=*?

[mik3y]

Hey there, thanks for sharing your feedback.

Is it possible to do multi schema updates at the same time for POSTGRESQL... e.g. TENANT_NAME=*?

No, there's no support for wildcard / "all tenant" operations. (The library isn't aware of how many tenants you have.) You would have to script this.

[WilliamHH]

Thank you in turn. Okay … understand. I’ll then explore a little … I have a “whitelist” of tenants in public schema (in a table separate from the Django project) which I can query for tenant listing. I tried making this list from the schema names at run time but could not get it to work. Then 2nd option with wildcard aside perhaps : TENANT_NAME= T1, T2, T3 etc ? I doubt if I’ll ever have more than 30 or so Tenants. Kind Regards William On 13 Aug 2021, at 17:15, mike w ***@***.***> wrote:  Hey there, thanks for sharing your feedback. Is it possible to do multi schema updates at the same time for POSTGRESQL... e.g. TENANT_NAME=*? No, there's no support for wildcard / "all tenant" operations. (The library isn't aware of how many tenants you have.) You would have to script this. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#31 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ABZTD27KHGJD53U55APML3TT4UZJVANCNFSM5CAPTGJA>.

[WilliamHH]

Approx one year later and now working [a hobby project] to find a method for multi-schema migrations. With step assistance from various forums, I think the following script may assist db-multitenant users. I've done some basic testing but am not yet fully confident given my limited experience. Your trained eye will be appreciated.

commands/all_tenant_migrate.py

"""
A command to assist db-multitenant in mass migration of model changes.
Not tested to use when initially setting up a fresh Schema.

COMMAND : python manage.py all_tenant_migrate
"""

from django.conf import settings
from django.core.management.base import BaseCommand
from subprocess import Popen
from sys import stdout, stdin, stderr
import os
import psycopg2
import signal
import time

class Command(BaseCommand):

help = 'Run single makemigrations and migrate in sequence for all tenant schemas'

def handle(self, *args, **kwargs):

	# get all tenant names from schema list in database  
	conn = psycopg2.connect(
	host=settings.GET_SECRET('DATABASE_HOST'),
	database=settings.GET_SECRET('DATABASE_NAME'),
	user=settings.GET_SECRET('DATABASE_USER'),
	password=settings.GET_SECRET('DB_USER_PASSWORD'),)


	cursor = conn.cursor()
	cursor.execute("SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('pg_toast','pg_catalog','information_schema', 'public');")
	tenants =[]
	tenants = cursor.fetchall() #values_list('tenant_name',flat=True)
	tenant_list = [list[0] for list in tenants]

	conn.commit()
	cursor.close()
	conn.close()	

	first_schema = tenant_list[0] # set a schema to run the leading 'makemigrations'
	
	commands = [f'TENANT_NAME={first_schema} python manage.py makemigrations ',]
	for schema in tenant_list:
		commands += f'TENANT_NAME={schema} python manage.py migrate',

	# OPTIONAL : commands.append('python manage.py runserver')

	proc_list = []

	for command in commands:
	    print("$ " + command)
	    proc = Popen(command, shell=True, stdin=stdin,
	                 stdout=stdout, stderr=stderr)
	    proc_list.append(proc)
	    time.sleep(2)
	
	for proc in proc_list:
	    os.kill(proc.pid, signal.SIGINT)