How to link PostgreSQL, TimescaleDB database data?

Question

PostgreSQL

I decided to store some data with a timestamp in TmmescaleDB, since postgres starts to slow down terribly with a large number of rows.

I did everything according to the Django and Timescale dock.
I pulled out the image in the docker, launched it, created the database.
I wrote routers for databases, registered the data in the config.

settings.py

DATABASES = {
    'default': {
        'ENGINE': os.environ.get('SQL_ENGINE', 'django.db.backends.postgresql'),
        'NAME': os.environ.get('SQL_DATABASE', 'postgres'),
        'USER': os.environ.get('SQL_USER', 'postgres'),
        'PASSWORD': os.environ.get('SQL_PASSWORD', 'pass'),
        'HOST': os.environ.get('SQL_HOST', '127.0.0.1'),
        'PORT': os.environ.get('SQL_PORT', '5432'),
    },
    'data_db': {
        'ENGINE': 'timescale.db.backends.postgresql',
        'NAME': 'data_db',
        'USER': 'postgres',
        'PASSWORD': 'pass',
        'HOST': '127.0.0.1',
        'PORT': '5432'
    }
}

DATABASE_ROUTERS = ['detector_data.dbRouter.Router', 'client.dbRouter.Router']

detector_data.dbRouter

class Router:
    """
    A router to control all database operations on models in the
    auth application.
    """
    def db_for_read(self, model, **hints):
        """
        Attempts to read auth models go to data_db.
        """
        if model._meta.app_label == 'detector_data':
            return 'data_db'
        return None

    def db_for_write(self, model, **hints):
        """
        Attempts to write data models go to data_db.
        """
        if model._meta.app_label == 'detector_data':
            return 'data_db'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow relations if a model in the auth app is involved.
        """
        return True

    def allow_migrate(self, db, app_label, model=None, **hints):
        """
        Make sure the auth app only appears in the 'data_db'
        database.
        """
        if app_label == 'detector_data':
            return db == 'data_db'
        return None

client.dbRouter

class Router:
    """
    A router to control all database operations on models in the
    auth application.
    """
    def db_for_read(self, model, **hints):
        """
        Attempts to read auth models go to default.
        """
        if model._meta.app_label != 'detector_data':
            return 'default'
        return None

    def db_for_write(self, model, **hints):
        """
        Attempts to write data models go to default.
        """
        if model._meta.app_label != 'detector_data':
            return 'default'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow relations if a model in the auth app is involved.
        """
        return True

    def allow_migrate(self, db, app_label, model=None, **hints):
        """
        Make sure the auth app only appears in the 'default'
        database.
        """
        if app_label == 'detector_data':
            return db == 'default'
        return None

In general, everything is standard here.
I decided to rejoice, because the migrations in both databases worked, but here when creating an object of the DetectorData class

from django.db import models
from django.contrib.postgres.indexes import BrinIndex

from timescale.db.models.fields import TimescaleDateTimeField
from timescale.db.models.managers import TimescaleManager

from detector.models import Detector

class DetectorData(models.Model):
    detector = models.ForeignKey(
        Detector, 
        verbose_name='Привязанный датчик',
        on_delete=models.DO_NOTHING, 
        related_name='data'
    )
    first_temp = models.DecimalField('Первая температура', max_digits=4, decimal_places=2)
    second_temp = models.DecimalField('Вторая температура', max_digits=4, decimal_places=2)
    third_temp = models.DecimalField('Третья температура', max_digits=4, decimal_places=2)
    humidity = models.DecimalField('Влажность', max_digits=4, decimal_places=2)
    lightning = models.DecimalField('Освещенность', max_digits=4, decimal_places=2)
    pH = models.DecimalField('Кислотность', max_digits=4, decimal_places=2)
    timestamp = TimescaleDateTimeField('Время сбор данных', interval='1 day', auto_now_add=True)

    objects = models.Manager()
    timescale = TimescaleManager()

    def __str__(self):
        return f'Отчет в {self.timestamp} от {self.detector}'

    class Meta:
        verbose_name = 'Данные датчика'
        verbose_name_plural = 'Данные датчиков'
        indexes = [BrinIndex(fields=['timestamp'])]

    @classmethod
    def create_random(cls, detector, days=None):
        data = cls.objects.create(
            detector=detector,
            first_temp=round(uniform(0, 20), 2),
            second_temp=round(uniform(0, 20), 2),
            third_temp=round(uniform(0, 20), 2),
            humidity=round(uniform(0, 20), 2),
            lightning=round(uniform(0, 20), 2),
            pH=round(uniform(0, 20), 2),
        )

        if days:
            data.timestamp = datetime.now().date()+timedelta(days=days)
            data.save()

        return data

It appeared


I understand that it is written here that the table is not the same.
But what to do with it.
And also - how to make sure that the time-series models are in Timescale, and all the rest are in Postgres
For reference: the DetectorData model has a foreign key on the Detector (they are in different databases)

I would be grateful for any help!

Answer 1

the DetectorData model has a foreign key on the Detector (they are in different databases)

And who, in your opinion, will ensure the operation of this FK?
Apparently, you unexpectedly received an empty table in the database where TimescaleDB stands. Your FK refers to this table, and, of course, does not allow anything to be written to the referencing table.
Why are you sawing on two bases at all? TimescaleDB is just an extension to the same postgresql, not a separate DBMS. So add it to the used base directly.