Migrating your database can be quite terrifying as it can contain sensitive material, such as users’ profiles or more.
I was recently challenged by this exact problem and initially laid it to the side for a couple of weeks as I was quite anxious as to how I should approach this problem. Eventually however as I was getting closer and closer to crossing my current Heroku database plans limit and I had to get a crack on and migrate the database or else it’s going to cost me a lot of money!
Now I had already previously migrated my Django web server over from Heroku to CapRover, but I was still using the same backend (Heroku) database. After much messing around I have eventually found a good quick method to migrate the Postgres Database from Heroku to CapRover.
Click here to read how to migrate Django webserver from Heroku to CapRover.
Downloading The Database From Heroku
The first thing we should do is go to Heroku and download a dump of our database so that we can restore this later on our own server.
Head on over to your database, then under ‘Durability‘ click on ‘Create Manual Backup‘. Once it’s downloaded, rename it to something legible such as ‘Dump‘ and place it somewhere easily accessible such as your Desktop.
Create a Postgres Database on CapRover
Before I start, one thing is important to know. Your Postgres database must be the same version on CapRover as it is on Heroku. If your Heroku database is version 11.6, and your CapRover database is version 9.6, make sure you upgrade your CapRover database to version 11.6 also.
If you already have a Postgres database app on CapRover, ignore this part. Else, follow along.
Create a new database Postgres Database, using the 1-click-app wizard on your CapRover instance, and fill in your desired Postgres database username, password, and database name. For more information check out this article and scroll to the section ‘Setting up Postgres on CapRover’.
Setup PgAdmin on CapRover
We will use a free tool called PgAdmin to restore our ‘Dump’ from Heroku onto our Postgres database.
Head on over to Apps > One-Click-Apps/Databases > Pgadmin4, and create a new app. Once its created, head on over to the apps URL.
You should be greeted with this message once you visit the apps URL.
This is actually a big problem because it means that anyone who has access to your apps URL will have control over your Databases once they are added to the pgAdmin4 browser. 🙁
A quick solution to this is to go back to the app on CapRover and click on ‘Edit HTTP Basic Auth’. Provide a safe username and password, then Save & Update. This will at least prevent any random stranger from being able to access your pgAdmin dashboard. Furthermore, for additional safety, you can go to the ‘app config’ tab and when you are done using pgAdmin4, change the instance count to 0, this will put this container offline. Remember to change the instance count back to 1, when you want to use it again, however.
Connecting PgAdmin4 to our Postgres Database
Open up PgAdmin4 again and head on over to the server browser. From the server browser, right mouse click on servers>Create>Server. You should see something similar to below.
From here, add a Name to the server (e.g. myDatabase ). Then under Connection add the following:
- Hostname/address = srv-captain–<MY_POSTGRES_APP>
- Port = 5432
- Maintenance database = <YOUR_DEFAULT_POSTGRES_DATABASE>
- username = <YOUR_POSTGRES_USERNAME>
- password = <YOUR_POSTGRES_PASSWORD>
So for example, entering the data provided from the previous screenshot where I showed the parameters I used for my testing database I would have entered the following:
- Hostname/address = srv-captain–testing-db
- Port = 5432
- Maintenance database=default
- username = testing-username
- password = testing-password
then click save. You should get a green ‘server connected’ message.
Getting our Heroku Dump Onto PgAdmin4
Now that we have a program that is able to restore our Heroku dump we need to be able to provide pgAdmin4 the dump.
Assuming you have SSH access to your server follow along with me; Basically what we will do is:
- transfer the dump from our local machine to our server with scp.
- transfer the dump from our server to our pgAdmin4 docker container.
- from pgAdmin4 select the dump and restore.
Let’s get started.
first, run this command in the terminal to transfer the dump from your local machine to your remote server.
replace the <> with the info, please take note of the spaces between commands. If you are on DigitalOcean your username will probably be ‘root’ without the single quotes.
scp <PATH_TO_DUMP_ON_LOCAL_MACHINE> <USERNAME_REMOTE_MACHINE>@<REMOTE_MACHINE_IP>:/home
‘dump’ should now be on your server, in the directory ‘home’, next lets SSH into the server with the following command.
Once SSH’d into your sever we have to copy the dump file from the server to the pgAdmin4 container. First, we must find the container ID. To do this, run the following command.
Once you located the correct container ID matching pgAdmin4 (it should look something similar to this: 47ee4eeecbda (in my case) ) we can copy dump over from the server storage to the container storage with the following command.
docker cp home/dump <CONTAINER_ID>:/dump
That’s it, we have transferred the dump onto the pgadmin4 container, don’t worry, if you did it correctly then there won’t be any message printed.
if you get an error, make sure you are in the right directory, to begin with, when executing the command ‘ls’ you should see ‘home’ among the many directories printed.
Restoring on PgAdmin4
Now that the dump is located in the pgAdmin4 container we can go back to our pgAdmin4 app and restore.
Head over back to pgAdmin4, find your server, then right mouse click on your database and click restore, you should see something similar:
Next click on the 3 dots right of ‘Filename’, then select ‘dump’ (if you don’t see it click the little box at the bottom saying “Show hidden files and folders?”). Click Select, then click Restore.
pgAdmin4 should now successfully restore your database from Heroku onto your Postgres instance on CapRover :).