Running PostgreSQL and psql in Docker

03 April 2023

Quick tip on how to set up a local instance of PostgreSQL using Docker.

This is useful if you want a separate instance for small projects/hacks, or don’t want to install postgres directly to your system.

Requirements:

• docker (or podman)
• docker-compose

Instructions:

Create the docker-compose.yml file.

# file: docker-compose.yml
version: '3'
services:
  postgres:
    image: docker.io/postgres
    environment:
      - POSTGRES_PASSWORD=mypassword
      # optionally, allow (insecure) passwordless login
      # - POSTGRES_HOST_AUTH_METHOD=trust
    volumes:
      - ./pgdata:/var/lib/postgresql/data
      - ./user_data:/user_data
    ports:
      - "5432:5432"

Start the docker compose stack with:

$ docker-compose up -d

For the psql CLI, use the following script. You can keep it in ~/.local/bin/psql.

#!/usr/bin/env bash
# file: ~/.local/bin/psql
if [ -t 0 ]; then
	# interactive
	exec docker-compose exec --user postgres postgres psql $@
else
	# non-interactive
	exec docker-compose exec --user postgres -T postgres psql $@
fi

Remember to chmod +x ~/.local/bin/psql.

Now you can either open it for interactive use

$ psql

psql (15.2 (Debian 15.2-1.pgdg110+1))
Type "help" for help.

postgres=#

or pipe commands directly into it

$ psql <<< 'create database mydb;'
CREATE DATABASE

$ psql < my_script.sql
[...]

Heads up: while my_script.sql does not need to be mounted in the container for you to be able to pipe its contents to the psql script, any files referenced inside my_script.sql need to be mounted.

As a convenience, the docker-compose.yml above already has a ./user_data folder mounted into the container, so you can reference them as /user_data/my_import_data.csv, for example, as long as they’re located in the ./user_data folder locally.

Bonus: adding extensions

In the example below, we install and enable PostGIS.

1. Add a Dockerfile to the same path as docker-compose.yml

   FROM docker.io/postgres
   RUN apt-get update && apt-get install -y postgis
   RUN echo "CREATE EXTENSION postgis;" > /docker-entrypoint-initdb.d/init.sql
   

2. Update docker-compose.yml to build the image from the current folder:

   -    image: docker.io/postgres
   +    build: .
   

The file /docker-entrypoint-initdb.d/init.sql is executed automatically during build time.