Recently I had a problem with accessing an ElephantSQL database from my location. I hosted the instance in Hong Kong and the connection was inconsistent since past month. Most of the days it didn’t work, while some days it did. I tried changing the DNS and also restarting the router for a new dynamic IP to see if I was being blocked. Both didn’t work. However, connecting through a VPN worked.
Since it wasn’t convenient to run a VPN 24/7, I contacted ElephantSQL support, but they weren’t able to help. They had not blocked my connections; they were just not reaching the target server. I did a trace-route to the server address, and it seems to go through China, so my guess was it was being dropped. So I had to move.
I already had some hobby projects running on Heroku, and their PostgreSQL package was also had more storage and features. So, it was a straightforward decision. Moving to Heroku was pretty easy with a little effort. Here’s how I achieved it.
First, back up your database and download the file to your storage. ElephantSQL has an easy to use a backup option in the sidebar that lets you download your database in few clicks. However, ElephantSQL compresses the sql file in LZO format. Windows/Linux installation won’t be able to decompress LZO files by default. So you’ll have to download an additional tool to extract the SQL file within.
Requirements:
- Heroku CLI application installed
- PostgreSQL installed
- LZOP for lzo file decompression
You need to have PostgreSQL install in order to perform the import to the Heroku instance. The following tutorial targets Ubuntu Linux, but it should work on Windows and Mac operating systems
For LZOP, in Debian based Linux, we require lzop package for extracting lzo files. Install it with the following command:
sudo apt install lzop
In Windows, you can install Lzop for Windows to extract the file
Then, to extract the file you use:
lzop -d [filename.sql.lzo]
The extraction tool will create the sql file in your current directory. Once you have exported it to a sql file, we can move it to Heroku.
Once you’ve setup an app and a database on Heroku, you’re ready to import the database from ElephantSQL. You will need the heroku-cli to perform the import. As mentioned before, the command depends on the postgres installation on your local computer to perform the import to the remote database. Heroku has a command that helps you import postgres database files. Once you’re in the directory where you’ve stored the sql file. Run the following command:
heroku pg:psql --app app-name < db.sql
The app-name should refer to your app instance on Heroku and the db.sql is the file you extracted from ElephantSQL export. Now the application will start importing your database to your Heroku instance. As the database name are different, you may see some errors, but your tables and data will get imported. You can check this with a database management application like DBeaver or pgAdmin.
