Apache Superset: Migrate from sqlite to Postgres

Question

When I migrate from sqlite to postgres, I cannot make any writes to the new database. The log shows the following error:

Unique key conflict id= 10, already exist in table ab_user

Answer 1

Two potential scenarios:

Scenario A: You are starting from scratch and want to use Postgres instead of sqlite.

1. Install postgres
2. Add postgres connection string to superset_config.py file
3. Run superset db upgrade this will create all the tables on Postgres
4. Run superset init
5. Launch Superset

Scenario B: You have an already populated sqliteDB and want to migrate it to Postgres.

1. Install postgres
2. Add postgres connection string to superset_config.py file
3. Copy all tables from sqlite to postgres, many ways to do this. My preferred way is to do is to use ruby
4. After you have copied your data run superset db upgrade and superset init
5. After you have done this you will need to update key sequences on Postgres on table information_schema.sequences otherwise you will hit unique key conflicts errors.
6. Launch Superset

In both scenarios you should see the message below after you have run superset db upgrade this means you have configured your superset_config.py properly.:

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.

Make sure superset is looking at the config file by setting the environment variables:

export PYTHONPATH=/home/local_settings/:$PYTHONPATH
OR
export SUPERSET_CONFIG_PATH=/home/local_settings/