Migrating your PostgreSQL database from one hosting platform to another (e.g Heroku to Supabase)

Hello, welcome to the devmaesters website. In this post I am going to show you how you can migrate your postgreSQL database from one hosting platform to another. The database platforms I am going to be using are Heroku and supabase.

Heroku
Heroku is an awesome platform for web hosting and one of the most enjoyable features it provides is a PostgreSQL database service that offers a storage capacity of 1GB on the free tier plan. I know that many developers have taken advantage of this free service at one time or another as its awesome to use expecially when your're getting started in software development.

Supabase
Supabase is an open source Firebase alternative. Start your project with a Postgres Database, Authentication, instant APIs, Realtime subscriptions and Storage. This is relatively new web hosting platform that gives you access to many awesome features and tools to aid in your web developent projects. For the sake of this post I am going to limit to the free postgreSQL database that they offer, you can check their official website @ supabase to learn more about the services they offer.

What supabase offers is 1 GB of free database storage split across two database instances (i.e each database gets a maximum of 500MB) and also an awesome table editor to view and edit all of your database content.

In this post I am going to be migrating my database with all its content from Heroku to Supabase. I am going to be using one of my django projects called Django CRUD blog that I created to show basic create, read, update and delete functionalities on the django web framework. This project is currently hosted on heroku with a posgreSQL database attached to it.

So please follow the steps here and if there are any questions then feel free to leave a comment down below.

Step 1

Password => password for your database
User => username for your database
Port => port for your database(default used by poasgres is 5432)
Name => name associated with your database
Host => the host credential for your database
Engine => connection engine for your database(default using psycopg2 is django.db.backends.postgresql_psycopg2)

Step 2

For the next step you need to access your code locally for that particular project. So open your project in a code editor and go to the projects settings.py file and find this line for your database connection.

if os.environ['ENVIRONMENT'] == "PRODUCTION":
    DATABASES['default'] = dj_database_url.config(conn_max_age=600, ssl_require=True)
else:
    DATABASES = {
        'default': {
            'ENGINE': os.environ['ENGINE'],
            'HOST': os.environ['HOST'],
            'NAME': os.environ['NAME'],
            'USER': os.environ['USER'],
            'PASSWORD': os.environ['PASSWORD'],
            'PORT': os.environ['PORT'],

        }
    }

Mine looks like that because;

I am using an if else statement to check if I am in a production or developent environment
I am getting all the values from an env file so that I can work with a 2 different postgreSQL databases one for development and one for production. Check out my adding enviroment variables to django post to learn how its done.

If you are not using enviroment variables then yours will look like this;

DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'HOST': 'your host', #e.g localhost
            'NAME': 'database name',
            'USER': 'username', #e.g postgres
            'PASSWORD': 'database password',
            'PORT': '5432',

        }
    }

Step 3

Next, replace all the values in your database connection configuration found in step 2 with that of the respective database credentials you got in step 1.

Step 4

Next start your project by running python manage.py runserver and confirm that the content on your frontend is the same as the content available in the online database (heroku). Note that you must be connected to the internet for this to work as your database content is being fetched online from heroku(or your database hosting platform) also your project migration state must be up to date with that of the hosted database. If everything at this step works successfully then move on to the next step.

Step 5

Go to your code editor terminal and execute the code below to create a json backup of all the data in your database.

python manage.py dumpdata > datadump.json

If it works successfully you should see a new datadump.json file in your project directory. Next stop your development server.

Step 6

Login to the new platform within which you want to migrate your database to and get the new databse credentials like in step 1. Checkout this post Hosting PostgreSQL database on Supabase for free to learn how you can host a free postgreSQL database on superbase.

Step 7

Go to your settings.py and replace the database configurations with the new ones you got in step 6 (i.e replace the heroku database configurations in settings.py with the database credentials provided by the new platform).

Step 8

The next step is migrate all your models and settings to the new database. Please before starting this step ensure that you are connected to the internet and stop your development server if you have one running.

Execute the commands below in your terminal

python manage.py makemigrations

and

python manage.py migrate

Give it a minute to sync migrate all your models to the new database.

Step 9

After step 8 has executed successfully the next step is to transfer all our data from the former database to the new one using the datadump.json file we created in step 5.

Execute the command below in your terminal to import the data in the datadump.json file into the new database

python manage.py loaddata datadump.json

Give it a minute to finish importing and that's it. If you check the tables in the new database you will see all the former database models and data. If you followed this tutorial using supabase postgreSQL database as your new database then you can check your table editor to see all the new data.

Conclusion

We have been able to successfully migrate our database from one platform(heroku) to another (superbase). The method explained in this tutorial works for all postgreSQL database providers. I would love to know your thoughts on the post so feel free to leave a comment down below. As always thanks for reading and please do subscribe to my youTube channel to get access to video tutorials on various programming topics.