Setup Free PostgreSQL on Fly.io and Import Database

This story started when I was trying to find a platform that can deploy PostgreSQL for free. I usually use Google Cloud for everything, and it turns out to be expensive if I let the server run 24 hours 7 days a week. There are several options other than the platform that I wrote on the title which eventually led me to the awesome list on Github. But it back again to Fly.io which provides free allowances to host PostgreSQL, just to play with some queries and host a SQL workshop.

This article will guide you how to deploy PostgreSQL server and import a database dvdrental to it. At first, I don’t think that I need to write this article because the documention is quite straightforward, but I found some difficulties on how to understand the system and import the database for my laptop to the server on fly.io.

Step 1: Create account and Insert Credit Card

Go to https://fly.io/app/sign-in I signed in using my github account to make it fast. Then, insert the credit card for free allowances.

Free allowances consist of (from the docs):

• Up to 3 shared-cpu-1x 256mb VMs
• 3GB persistent volume storage (total)
• 160GB outbound data transfer

What does it mean is that you have 3 free instances with that very small specs, 3GB disk storage in total and 160GB traffic outbound which measures the traffic from the server fly.io to the internet. Note that for the inbound is free (like AWS).

Step 2: Install flyctl and authenticate

• install flyctl using curl -L https://fly.io/install.sh | sh for ubuntu. Complete documentation here
• Then authenticate using flyctl auth login, there you go, just follow the steps. It is quite easy.

Step 3: Create PostgreSQL

To create a PostgreSQL app, run this command flyctl postgres create Then just follow the prompts.

(base) andreas@Alexandre:~$ fly postgres create
? Choose an app name (leave blank to generate one): dvdrental
? Select Organization: Andreas Chandra (personal)
? Select regions: Singapore, Singapore (sin)
? Select configuration: Development - Single node, 1x shared CPU, 256MB RAM, 1GB disk

• I create a postgres app named dvdrental
• Select the organization, I selected my name
• then, select region. I chose Singapore because it close to me
• Then select machine type Development — Single node it is free.

After that the prompt will give you the message like this

Creating postgres cluster dvdrental in organization personal
Creating app...
Setting secrets...
Provisioning 1 of 1 machines with image flyio/postgres:14.4
Waiting for machine to start...
Machine d5683461b138e9 is created
==> Monitoring health checks  
    Waiting for d5683461b138e9 to become healthy (started, 3/3) Postgres cluster dvdrental created  
Username:    postgres  
Password:    <password>
Hostname:    dvdrental.internal  
Proxy port:  5432  
Postgres port:  5433
Save your credentials in a secure place -- you won't be able to see them again! 
Connect to postgres
Any app within the Andreas Chandra organization can connect to this Postgres using the following credentials:
For example: postgres://postgres:<password>@dvdrental.internal:5432  
Now that you've set up postgres, here's what you need to understand: https://fly.io/docs/reference/postgres-whats-next/

You need to save username, password, hostname and the ports to later connected in your IDE.

dvdrental app has created and seen in the dashboard

Here ths screenshot of the app

As you can see, the app dashboard gives you an information the server metrics in the score carads and detail info on the app. on the right sidebar, if give you an activity information. While on the sidebar menu, gives you options to monitor app using grafana / built-in charts, configurations, etc.

Just look at every sidebar menu to explore.

Step 4: Connect fly.io PostgreSQL from terminal

Run this command

flyctl postgres connect -a dvdrental

you can change dvdrental to your app name. That command will enter you to postgres bash.

type \l to list all the databases.

However, you can also ssh tunnel the app using this command

flyctl proxy 5432 -a dvdrental

Hence, this will give you an ability to connect SQL IDE (dbeaver, datagrip, etc) using localhost with the port 5432

Step 5: Import dvdrental database to the postgres

Download the database from here

Extract the zip file, then you will have .tar file

run this command

postgres=# CREATE DATABASE dvdrental;
CREATE DATABASE

Then restore

pg_restore -v -d postgres://postgres:<password>@localhost:5432/dvdrental < dvdrental.tar

To check import the imported database successful.

flyctl postgres connect -a dvdrental

then \l again, there… there is database dvdrental.

Last

Now, you have your own server for PostgreSQL. You can use it to master SQL for data analytics. I have sounded about this on Jakarta AI Research Discord Server. Next thing to do, I want to stress test on this free server using pg_bench. We will discuss it further on discord. If you are interested, please click the link, and chat with us.

Cheers 🍻