While surfing on the internet and brainstorming for a day, I couldn’t find any perfect and straightforward guide to make the actual migration of the database. As we all know, the database is a critical part of the migration and needs to be done with care or all of your important data is gone. So, I had to do the migration but with care so did some experiments on local, tried lots of commands from the internet and did some good amount of experiments and boom the transfer had been made and my database was running from the docker container.
The time had come when the actual migration was to be done so just made a small snippet of the commands and did the migration without any data loss. A successful migration was made and so did the claps came.
After seeing me struggle the whole day I thought why not make things simpler for the next person like me struggling with the same and things ended up with me writing this blog.
1. First things first you need to put your app on Maintenance Mode to temporarily disable access to your Heroku app(for example, to perform a large migration). There are tons of guides for this or you can check out the Heroku devcenter here. You can even customize the maintenance page by changing the MAINTENANCE_PAGE_URL environment variable. If you have multiple apps, use the -an app name tag for the same for example
heroku maintenance:on -a appname2. Generate your Heroku database dump download URL
heroku pgbackups:url3. SSH into your instance and install docker(Refer Here). Install curl if not installed
apt-get update && apt-get install curl4. Use git clone and clone this repository for the docker-compose file
git clone https://github.com/bl4rr0w/postgres-heroku-to-docker-aws.git
5. Change the folder to Postgres-Heroku-to-docker-aws and run. Change the password to something secure before starting the container.
docker-compose up -d
At this point, our Postgres service is up and is ready to accept connections.
6. Now we need to download the dump from Heroku. Use the url from Step 2.
curl -o latest.dump [PASTE THE OUTPUT OF STEP 2 HERE]If you want to confirm if the file has been downloaded, use the ls command.
7. We now need to find the volume to confirm we are going right. Run the command
docker inspect -f '{{ json .Mounts }}' | python -m json.tool Then, look at the volume paths under the key Destination.
You should get the following:
As we linked-to in our folder from Step 5 that would make it very much easier. As a root user just use the following command if you want to make changes into the volume.
cd /home/< user >/postgres-heroku-to-docker-aws/db-data
8. Now we need to copy the latest.dump to the Postgres volume that we created through our compose file in Step 5.
docker cp latest.dump postgres_db_1:/var/lib/postgresql/datawhere postgres_db_1 is the name of the service. This will copy our latest.dump to the volume which makes it easier to restore.
You can now delete the old latest.dump to save some space as we have already copied it to the volume.
9. We need to create a database and user to restore the database. Follow me.
9.1 You need to get into the instance to perform sql operation to do so just use the following command.
docker exec-it postgres_db_1 bash where postgres_db_1is the service name.
9.2 Now you need to change the user to postgres and then enter psql.
Use the following commands
root@8f2771dd82ed:/# su postgres postgres@8f2771dd82ed:/$ psql
9.3 We can now execute the sql commands directly.
postgres=# help You are using psql, the command-line interface to PostgreSQL. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit
9.4 Create database, make a new user and grant all access to the user with superuser access.
CREATE DATABASE yourdbname; CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass'; GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser; ALTER USER youruser WITH SUPERUSER;
9.5 Our database is created along with the user and we are ready to restore the database from the dump
10. We now make the database restore. Use the following command.
docker exec postgres_db_1 pg_restore -U < user > -d < database name > /var/lib/postgresql/data/latest.dump
11. Wait for the process to complete and Voila!
Heroku and AWS both are excellent platforms. However, you need to understand what kind of feature your organization needs and how much you are ready to pay for them. It would help if you also ascertained the skills sets (especially DevOps) in your team before you make a choice. The right platform is one which helps you achieve your business goal and at the same time, keeps your developers productive.
P.S. This is a noob-friendly guide so has all the basic information. Improvements Appreciated!
