Wagtail moving sqlite to postgres database

Question

The issue

I have an empty (migrated) postgres database to which I want to move the data from my sqlite database.

What I've tried

I export with

./manage.py dumpdata --exclude auth.permission --exclude contenttypes --natural-foreign

and consequtively load the data into the postgres db with ./manage.py loaddata.

The problem here is that wagtail requires the contenttypes and I get a runtime error

FooPage matching query does not exist.

at

/wagtail/wagtailcore/models.py:639 
return content_type.get_object_for_this_type(id=self.id)

Don't exclude contenttypes with dumpdata. Now the loaddata command fails with an IntegrityError: Key ... already exists.
I have tried to remove all ContentType model objects before loading in the data so that it doesn't whine about duplicate keys. While that works when using the sqlite db, it fails on the postgres db with an IntegrityError
```
Key (id)=(1) is still referenced from table "wagtailcore_page".
```

Used versions

django 1.11.9
wagtail 1.12.3
python 3.5

Related

Problems with contenttypes when loading a fixture in Django

Try using --natural-foreign for dumpdata, see documentation. — rafalmp
– rafalmp, Commented Jan 7, 2018 at 22:48
@rafalmp I've tried that as well, and I've updated the question accordingly — Joren
– Joren, Commented Jan 8, 2018 at 12:05
Too much time is wasted on Djangos dumpdata and loaddata. It is fiddly and hardly ever works. — allcaps
– allcaps, Commented Nov 28, 2019 at 22:40

allcaps · Accepted Answer · 2019-11-28 22:31:22Z

2

With pgloader you can execute command instructions to load data from SQLite into Postgres.

Install on OSX with Homebrew:

brew install pgloader

Create a script file. Eg migrate.script:

load database
     from sqlite:///path/to/db.sqlite3
     into postgresql:///yourpostgresdbname

 with include drop, create tables, create indexes, reset sequences

  set work_mem to '16MB', maintenance_work_mem to '512 MB';

Run it:

pgloader migrate.script

I did a testdrive with a SQLite database containing a minimal Wagtail project (some pages, revisions and images) and it worked perfectly.

answered Nov 28, 2019 at 22:31

allcaps

11.3k1 gold badge38 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sequence Over a year ago

This solution worked on Ubuntu / Debian like a breeze. Great solution.

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

I managed something when I moved my Wagtail site from sqlite to Postgres, so just adding for reference.

I used this website as a base, but ran into an error when I couldn't delete ContentType objects right away due to a foreign key issue, as follows:

django.db.utils.IntegrityError: insert or update on table "wagtailcore_page" violates foreign key constraint "wagtailcore_page_content_type_id_c28424df_fk_django_co"

DETAIL: Key (content_type_id)=(1) is not present in table "django_content_type".

Seemed like it was due to Wagtail page objects, so executed the following two lines in the Django shell first. Was then able to follow the rest of the instructions on that page, executing the ContentType deletion and the import.

from wagtail.core.models import Page
Page.objects.all().delete()

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 30, 2019 at 6:50

Ian Cameron

392 bronze badges

2 Comments

Joren Over a year ago

At the moment of loading in my data, there are no existing pages in the database, so this answer does not apply to my question.

Ian Cameron Over a year ago

@Joren Sorry it wasn't relevant..Yeah, I didn't think there would be any page data, either, since it was a fresh database and all I had run were the basic migrations. I think the pages were likely wagtail admin pages etc.

Collectives™ on Stack Overflow

Wagtail moving sqlite to postgres database

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related