I was using SQLite3 before, and I need to migrate the data to PostgreSQL

[helianthus1]

Describe the bug

label-studio start --init -db postgresql --username defaultuser --password badpassword

I have been using the default SQLite3 before, and now I want to migrate the entire system to PostgreSQL. How can I do this? I used DBeaver to directly import the original SQLite3, but the following error message appeared

Screenshots

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/label_studio/core/utils/common.py", line 376, in get_latest_version
    response = requests.get(pypi_url, timeout=10).text
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/requests/api.py", line 73, in get
    return request("get", url, params=params, **kwargs)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/requests/api.py", line 59, in request
    return session.request(method=method, url=url, **kwargs)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/requests/sessions.py", line 589, in request
    resp = self.send(prep, **send_kwargs)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/requests/sessions.py", line 703, in send
    r = adapter.send(request, **kwargs)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/requests/adapters.py", line 519, in send
    raise ConnectionError(e, request=request)
requests.exceptions.ConnectionError: HTTPSConnectionPool(host='pypi.org', port=443): Max retries exceeded with url: /pypi/label-studio/json (Caused by NewConnectionError('<urllib3.connection.HTTPSConnection object at 0x7f4fbb6f9960>: Failed to establish a new connection: [Errno 101] Network is unreachable'))
Traceback (most recent call last):
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.DatatypeMismatch: column "is_superuser" is of type binary_double but expression is of type boolean
LINE 1: ...p6MqeNnxcZjdBKxvAM3cQnW+vLMvU0A4ZKkvzxX4=', NULL, false, '20...
                                                             ^
HINT:  You will need to rewrite or cast the expression.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/root/Downloads/Python-3.10.11/myenv/bin/label-studio", line 8, in <module>
    sys.exit(main())
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/label_studio/server.py", line 353, in main
    _init(input_args, config)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/label_studio/server.py", line 189, in _init
    user = _create_user(input_args, config)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/label_studio/server.py", line 161, in _create_user
    user = User.objects.create_user(email=username, password=password)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/label_studio/users/models.py", line 50, in create_user
    return self._create_user(email, password, **extra_fields)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/label_studio/users/models.py", line 43, in _create_user
    user.save(using=self._db)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/contrib/auth/base_user.py", line 67, in save
    super().save(*args, **kwargs)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/db/models/base.py", line 739, in save
    self.save_base(using=using, force_insert=force_insert,
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/db/models/base.py", line 776, in save_base
    updated = self._save_table(
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/db/models/base.py", line 881, in _save_table
    results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/db/models/base.py", line 919, in _do_insert
    return manager._insert(
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/db/models/query.py", line 1270, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
    cursor.execute(sql, params)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/sentry_sdk/utils.py", line 1860, in runner
    return sentry_patched_function(*args, **kwargs)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/sentry_sdk/integrations/django/__init__.py", line 653, in execute
    result = real_execute(self, sql, params)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in _execute
    with self.db.wrap_database_errors:
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/root/Downloads/Python-3.10.11/myenv/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "is_superuser" is of type binary_double but expression is of type boolean
LINE 1: ...p6MqeNnxcZjdBKxvAM3cQnW+vLMvU0A4ZKkvzxX4=', NULL, false, '20...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

Sentry is attempting to send 2 pending events
Waiting up to 2 seconds
Press Ctrl-C to quit

Environment (please complete the following information):

• OS: centOS7
• Label Studio Version 1.11.0

[helianthus1]

Is there any good method to recommend for converting the database?

[heidi-humansignal]

Hello,

Thanks for reaching out regarding migrating your Label Studio database from SQLite to PostgreSQL. Directly importing your SQLite file via tools like DBeaver can lead to data type mismatches (as in the “is_superuser” error you're seeing). Instead, we recommend using Django’s built‐in data export/import process. Here’s a suggested workflow:

1. Backup your current SQLite database by dumping your data:

   python manage.py dumpdata > datadump.json

2. Update your Label Studio configuration (or environment variables) to point to your PostgreSQL database. For example, your settings should reflect something like:

   { "DATABASE": { "ENGINE": "django.db.backends.postgresql", "NAME": "labelstudio", "USER": "labelstudio_user", "PASSWORD": "your_password", "HOST": "your_postgres_host", "PORT": "5432" } }

3. Create the new PostgreSQL tables by running the migrations:

   python manage.py migrate --run-syncdb

4. (Optional) Open a Django shell to clear any pre-existing content types if you run into issues:

   python manage.py shell>>> from django.contrib.contenttypes.models import ContentType>>> ContentType.objects.all().delete()>>> quit()

5. Load your previously dumped data into PostgreSQL:

   python manage.py loaddata datadump.json

6. Finally, start Label Studio and verify that everything is working correctly.
   This export–migrate–import process uses Django’s awareness of field types to correctly convert booleans (and other types) so that you won’t see errors like “column 'is_superuser' is of type binary_double but expression is of type boolean.”
   Please let me know if you need any further details or run into any issues.

Thank you,
Abu

Comment by Abubakar Saad
Workflow Run