Switching from SQLite to PostgreSQL

On Heroku, when the size of the project begins to grow too much you have to set out to use external services for storing data. For instance, I use Amazon S3 for storing dynamic content that I upload from an application. But in this case I am going to talk how to switch from a SQLite database to a PostgreSQL DBMS without losing any data.

Installation and setting up the framework

In first place, you have to install PostgreSQL DBMS in your system in order to migrate the content from a SQLite data dump. After the installation, start the DBMS service and access through the terminal to the DBMS in order to create a new database. You might be able to access to the database using your computer user account.

$ brew install postgres
$ brew services start postgres
$ psql template1

Your prompt will be switched by the PostgreSQL terminal. From this environment you have to create a new database where the SQLite dump will be stored. Granting database access to your current user is required.

> create database my_database;
> grant all on database my_database to my_user;

Migrate data

For migrating the data first the content of the database have to be dumped to a JSON file.

$ python manage.py dumpdata --natural-foreign --exclude auth.permission --exclude contenttypes --indent 4 > data.json

Then the database configuration must be replaced by the reference to your PostgreSQL configuration in the framework you are using (e.g.: Django).

# yourproject/settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': os.environ.get('DB_NAME'),
        'USER': os.environ.get('DB_USER'),
        'PASSWORD': os.environ.get('DB_PASSWD'),
        'HOST': os.environ.get('DB_HOST'),
        'PORT': os.environ.get('DB_PORT'),
    }
}

Execute the migrations of your application model in order to create the required tables in the previously created PostgreSQL database.

$ python manage.py migrate

After that, import SQLite dump using your framework. The following command will read the dump file and will store the data into the PostgreSQL database.

$ python manage.py loaddata data.json

 Upload the PosgreSQL dump to Heroku

Finally, a PostgreSQL dump has to be created and uploaded to an storage service as Amazon S3 in order to be able to import the data from the Heroku PostgreSQL management tool.

$ pg_dump -U USERNAME DBNAME > mydb.pgsql
$ heroku pg:backups:restore 'https://s3.amazonaws.com/me/items/3H0q/mydb.pgsql' DATABASE_URL