Creating a PostgreSQL gitpod demo

Martijn Pronk
0 0
Read Time:3 Minute, 10 Second

In this blog I will demo how to create a GitPod workspace containing a PostgreSQL database and database tools in VSCode. Lucas Jellema recently created a GitPod workspace with an Oracle Database, and as we’re also increasingly interested in PostgreSQL I wanted to recreate this experiment but now with PostgreSQL.

First, I started with an empty repository and created a README.md file.

Finding the docker image

My next step was to search the Docker Hub for a convenient ready to use image of a PostgreSQL database:

First hit seems reasonable, looking at the information we can see the latest version maps to 15.1. The Gitpod should start with downloading this docker image version. After download the container should be started, possibly be configured and VSCode needs configuration. To start this, I created a basic .gitpod.yml file containing a small set of statements. The first step is to pull the image, then start the container. As I want to access this database in the gitpod environment, I add a port forwarding option to the docker command line. Additionally a password is given (this is required for this image). No persistent volume is given, this is a throwaway environment.

In my experiments I wanted to add a test user and test database when initializing the database but I found this not working. The issue was that right after starting the container the database takes a couple of seconds to initialize. This meant the command to add users to the database hit a not started database and wouldn’t continue its steps. A small wait of 5 seconds was enough for the database to be fully started. The command ‘docker exec -i -u postgres postgresDB15 psql‘ executes the program sql as user postgres inside the container. As the postgres user is the owner of the postgres software, it is permitted to connect as the database’s superuser without authentication.

After I started the environment a terminal is made available to connect as the superuser to the database. Additionally some extensions are installed in the VSCode environment to allow VSCode to interact with the database. In the pre-supplies VSCode settings file, a database connection is pre-setup. Only the database password needs to be supplied.

tasks:
  - init: |
      docker pull postgres:15.1
      # open the readme.md document in VS code; note: thanks to the configuration in .vscode/settings.json, the document opens in markdown preview mode
      gp open README.md
      docker run -d -p 5432:5432 --name postgresDB15 -e POSTGRES_PASSWORD=welkom01 postgres:15.1 &&
      sleep 5s &&
      docker exec -i -u postgres postgresDB15 psql < scripts/create_user_db.sql &&
      gp sync-done database-running
  - name: start container and run a session as postgres owner
    init: gp sync-await database-running
    command: |
      docker start postgresDB15 &&
      sleep 1s &&
      echo "Postgres is running at localhost port 5432, starting session" && 
      docker exec -it -u postgres postgresDB15 psql 
ports:
  - port: 5432
    onOpen: ignore
    visibility: private

vscode:
  extensions:
    - mtxr.sqltools
    - mtxr.sqltools-driver-pg

After some tries (most tries failed because of the lack of sleep between creation of the database and the first statements against it) the yaml file above is the working implementation. In the scripts directory of the repository is a small demo file created.

Demo time

When I run this small demo it creates 2 tables in the testdb database, as demonstrated in the terminal window. Using the SQL tools extension we can navigate to the database and see the contents of our table:

This was a small introduction, next steps could be to install a PostgreSQL capable tool locally and access it through the forwarded port

Use this link to view the gitpod working: https://gitpod.io/#https://github.com/martijnpronkAMIS/gitpod-postgres-database

About Post Author

Martijn Pronk

Martijn is an Oracle Consultant at AMIS. His focus is on Oracle databases (RAC and non-RAC) and Weblogic infrastructure. Using Puppet (including the Enterprise modules) and other opensource technologies. In my spare time I like to tinker with Raspberry Pi's, FreeBSD, Sun and SGI hardware and software.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next Post

Overhead of Service Mesh–measuring and comparing Istio, Linkerd, Kuma and Consul

A Service Mesh can provide many facilities regarding traffic management between microservices – from simple routing and load balancing, producing telemetry to applying complex routing logic and security policies. This layer of logic that is applied on top of what services are doing themselves is not free: additional latency is […]
%d bloggers like this: