There are multiple ways we can run a postgres database on webapp.io. In this section we’ll cover running postgres with a Dockerfile.

The code for this project is listed in the repository below:


GitHub Repository

Postgres in webapp.io (with Docker)


Postgres & Docker Tutorial

This project will consist of three files in our repository:

  1. init.sql (what we will initialize our database with)
  2. Dockerfile (the dockerfile for the postgres database)
  3. Layerfile (the Layerfile for webapp.io)

Initialize the Database

We’re going to be using the root user for this postgres database. To do so, we’ll create a script to initialize a database called root.

init.sql
SELECT 'CREATE DATABASE root' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'root');
\gexec

Create the Dockerfile

After creating the script to initialize the root database in postgres, we’ll use the postgres:14-bullseye image from Docker. We’ll set the POSTGRES_USER as root, the POSTGRES_PASSWORD as postgres, and POSTGRES_DB as root.

FROM postgres:14-bullseye
ENV POSTGRES_USER root
ENV POSTGRES_DB root
ENV POSTGRES_PASSWORD postgres
COPY init.sql /docker-entrypoint-initdb.d/
EXPOSE 5432

Create the Layerfile

Once we’ve created the Dockerfile, we can create the Layerfile which will install Docker, build the Docker image, and run the docker image.

Layerfile
FROM vm/ubuntu:18.04

# install the latest version of Docker, as in the official Docker installation tutorial.
RUN apt-get update && \
    apt-get install ca-certificates curl gnupg lsb-release && \
    sudo mkdir -p /etc/apt/keyrings && \
    curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /etc/apt/keyrings/docker.gpg && \
    echo \
    "deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" |\
    sudo tee /etc/apt/sources.list.d/docker.list > /dev/null && \
    apt-get update && \
    apt-get install docker-ce docker-ce-cli containerd.io

COPY . .

RUN sudo apt-get install postgresql-client-common
RUN sudo apt-get install postgresql-client

RUN docker build -t image .
RUN docker run -d -t -p 5432:5432 image

The last step is to push this code to your repository. If you’ve connected your repository to webapp.io, webapp.io will get notified of the new commit and run the instructions in the Layerfile.

Entering the Database

To enter the database, click the button “Connect to a debugging terminal” on the Layerfile Runner page, and type in the command psql -h localhost -p 5432 to enter the postgres shell.

If you’re prompted to enter a password, enter in postgres.