Migrating from SQLite to PostgreSQL

Using Django Using the ORM
1

I am aware problems with migrating from SQLite to PostgreSQL have been posted numerous times, but I hope my issue is sufficiently original.

I dumped the content of my SQLite file, excluding wagtailsearch.sqliteftsindexentry as per this suggestion:

python3 manage.py dumpdata --exclude=wagtailsearch.sqliteftsindexentry > datadump.json

switched the DB configuration in settings.py, created the DB tables in PostgreSQL:

python3 manage.py migrate --run-syncdb

removed the content types from PostgreSQL database (in the Python shell):

from wagtail.models import Page
Page.objects.all().delete()
from django.contrib.contenttypes.models import ContentType
ContentType.objects.all().delete()

and tried to load the JSON file:

python3 manage.py loaddata datadump.json

This results in the following error message:

django.db.utils.ProgrammingError: Problem installing fixture '/Users/jan/Sites/lms/datadump.json': Could not load wagtailsearch.IndexEntry(pk=32): syntax error in tsvector:

The error is located at the beginning of the body field value, and is not related to the actual field values (I experimented with deleting certain pages, and the error was consistently located at this position:

LINE 1: ...e" = 'Impressum', "title" = 'Impressum', "body" = 'Anbieter ...
                                                             ^

The same error message is shown if I dump the data in XML format. Any help in debugging this is greatly appreciated. Django 5.0.3 (with Wagtail 6.0.1), PostgreSQL 14.11.

2

Check the JSON file for any problematic characters or syntax issues, particularly in the “body” field of the entry with pk=32.

Or If there are special characters, escape or sanitize them before loading into PostgreSQL database to avoid any syntax errors

And Ensure your PostgreSQL database settings are compatible with the data being imported, especially regarding text encoding and full-text search configurations.

Or Try loading a smaller subset of the data to isolate the issue and identify if it’s specific to certain entries.

Consult Django and Wagtail documentation for migration and data import/export guidelines.

check one by one and you will fix the issue soon.

3

Thanks for the reply! I followed your suggestions, but gave up in the end. I kept getting new error messages pointing in different directions.

1 Like
5

What I would suggest you do is dump the original database as SQL insert statements, then apply that SQL to your new database.

This should provide you with more detailed error information for figuring out where the problem lies.