Setting up a local dev postgres setup with podman/docker

postgres-dev-arch-linux-podman.md

PostgreSQL dev setup on Arch Linux using podman

Introduction

This document summarises my learnings from setting up a postgres container on arch linux for dev - no prod. I will also touch on using psql and vim-dadbod-ui to interface with the DB. There's also dbeaver, which is the shotgun epquivalent of DB tools (= fire and forget, easy to use), which I won't cover here for that reason. My goal was specifically to set up a local dev env with the limited knowledge I have of docker containers and DBMS in general.

Installing PostgreSQL

I did not want to install postgres on my system directly, although I could run it on demand and kill when done, it seemed cleaner to have a layer of separation. Instead I only installed postgresql-libs (aka libpq) solely for psql, which is essentially a postgres read-eval-print-loop (REPL).

Installing docker/podman

I decided to run postgres in a container instead. This is often done with docker and since knowing it well is a common job requirement, I decided... not to use it. I installed podman instead, which can be a drop-in replacement and completely FOSS.

I installed these arch packages:

• podman
• podman-docker to mimic docker's CLI
• podman-compose to run docker-compose.yml (ended up not needing this)

I will specify commands using the docker executable... to signify my enhanced state of employability. But don't worry, it's just good old podman under the hood.

---

Now that I had "docker" installed, I added docker.io as an unqualified search registry so podman pull postgres can resolve postgres via docker hub. Using unqualified names is not recommended, but very comfortable.

File: /etc/containers/registries.conf.d/10-unqualified-search-registries.conf:

unqualified-search-registries = ["docker.io"]

Installing postgres for real

After roughly two hours of confusion, I had learned enough about docker to be able to install postgres. The magic incantation is (line-wrapped for your pleasure):

docker run\
  -v ~/projects/web/pgsql-test/.pgdata:/var/lib/postgresql/data\
  -p 127.0.0.1:5432:5432\
  -e POSTGRES_PASSWORD="some password"\
  --name pg\
  "postgres:16.6-alpine3.20"

Arguments:

• -v localdir:remotedir defines a volume, mapping postgres' temporary data in /var to a directory on the host FS. Without this volume, data would not persist when the DB shuts down.
• -p ip:port publishes a container's port so it's accessible from the host. 5432 is of course postgres' default port. There's also -P, it will publish all exposed ports.
• e POSTGRES_PASSWORD="..." sets an environment variable, specifically the password for DB access, which is mandatory.
• --name pg gives the container a (short) name to refer to it.
• "postgres:16.6-alpine3.20" is the image to run. Docker pulls images from docker hub and so does podman with my config.
  • I installed postgres 16 to match my local version of psql (check with psql --version)
  • The default version of postgres uses debian. Alpine is much smaller and for that reason often used for devops.

When the container has been terminated, it can be started again via docker start -a pg. -a means that the container will run in the foreground ('attached'), so I can see stdout and stderr.

Interacting with the DBMS

psql

To verify that it's working, you can use psql on the host machine:

psql postgresql://postgres:PASSWORD@localhost:5432/postgres
#    PROTOCOL     USER     PASSWORD IP        PORT DATABASE

Some meta commands:

• \? lists all commands
• \q quits
• \l lists all databases

\l should print something like this if the container is running and properly set up:

   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres

You can run SQL queries either in the REPL by just entering them (don't forget the semicolon!), or via the command line parameter -c "{QUERY}"

dadbod.vim

I added dadbod-ui to my lazy vim setup:

-- suggested config:
{
  'kristijanhusak/vim-dadbod-ui',
  dependencies = {
    { 'tpope/vim-dadbod', lazy = true },
    { 'kristijanhusak/vim-dadbod-completion', ft = { 'sql', 'mysql', 'plsql' }, lazy = true },
  },
  cmd = {
    'DBUI',
    'DBUIToggle',
    'DBUIAddConnection',
    'DBUIFindBuffer',
  },
  init = function()
    vim.g.db_ui_use_nerd_fonts = 1
  end,
}

Now <leader>D opens dadbod-ui, where I first added my postgres db using the same URI I use for psql (but this is a one-time setup). Then I wrote a chunkier SQL query so I have some data to play with:

create table if not exists author (
  id serial primary key,
  name varchar(40) unique not null
);

create table if not exists book (
  id int generated always as identity,
  title varchar(40) unique not null,
  author int not null,
  year smallint,
  primary key(id),
  constraint fk_book_author
    foreign key(author)
    references author(id)
);

-- for subsequent runs
truncate book cascade;
truncate author cascade;

-- Put some meat on the bone
DO $$declare authorid int;
begin
  insert into author (
    name
  ) values ('Franz Kafka')
  returning id into authorid;

  insert into book (title, author, year)
  values 
    ('Der Prozeß', authorid, 1925),
    ('Das Schloß', authorid, 1926),
    ('Amerika', authorid, 1927);

  insert into author (
    name
  ) values ('Charles Dickens')
  returning id into authorid;

  insert into book (title, author, year)
  values 
    ('Oliver Twist', authorid, 1838),
    ('A Christmas Carol', authorid, 1843),
    ('Great Expectations', authorid, 1861),
    ('Hard Times', authorid, 1854),
    ('A Tale of Two Cities', authorid, 1850);
end$$;

select a.id, a.name, b.title, b.year 
  from book b 
  left join author a on b.author = a.id
  order by b.year ;

Then I press <leader>S to run the whole query and the output shows up in a new pane.

[truncated]
 id |      name       |        title         | year 
----+-----------------+----------------------+------
  4 | Charles Dickens | Oliver Twist         | 1838
  4 | Charles Dickens | A Christmas Carol    | 1843
  4 | Charles Dickens | A Tale of Two Cities | 1850
  4 | Charles Dickens | Hard Times           | 1854
  4 | Charles Dickens | Great Expectations   | 1861
  3 | Franz Kafka     | Der Prozeß           | 1925
  3 | Franz Kafka     | Das Schloß           | 1926
  3 | Franz Kafka     | Amerika              | 1927
(8 rows)

While in visual mode, only the selected part of the query will run via <leader>S. I can also persist this temporary query for later reuse via <leader>W.

The original vim-dadbod is of course also available and can be used to run a query via :DB {URI} {QUERY}.