Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Wagtail moving sqlite to postgres database

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

like image 822
Joren Avatar asked Jan 07 '18 22:01

Joren


People also ask

How do I move SQLite database to Postgres?

Steps for Connecting SQLite to PostgreSQLStep 1: Create SQLite DB Dumpdata Backup. Step 2: Generate a Postgres DB and User. Step 3: Configure Settings.py. Step 4: Import Required Fixture via Loaddata from SQLite to PostgreSQL.

Can SQLite connect to PostgreSQL?

Using CData Sync, you can replicate SQLite data to PostgreSQL. To add a replication destination, navigate to the Connections tab. Click Add Connection.

Can you open SQLite in PostgreSQL?

No. pgAdmin is meant for Postgres, not for SQLite. The two are completely incompatible on that level of access.


1 Answers

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.

like image 101
allcaps Avatar answered Oct 13 '22 23:10

allcaps