Rootless podman with some common SQL database systems

Karl Levik
Karl Levik Published: Updated:
Tumbleweed

Tumbleweeds are rootless during part of their lifecycle.
(Denise Rowlands - CC BY-NC 2.0)

Several major database systems have become available as docker images, so it's now easier than ever to play around with new versions of your favourite system or even try out some of the other ones just for fun.

There are obviously more than one way to pull images, create and start containers, but below are some quick notes just to get started. (Hopefully, I will have time for a more in-depth investigation in the near future.) Also note: I'm using podman instead of docker, but the same or very similar commands should work with docker as well.

Environment and podman version

I'm running this on Fedora 35 with podman version 3.4.4 with no other modifications than what is described in the next section. Your mileage may vary on other platforms such as RHEL, CentOS and so on.

Rootless podman

Before we start, it's worth noting that podman can be configured to run rootless, i.e. as a non-privileged user (or perhaps "lesser-privileged" is more accurate). In other words, you won't necessarily need to use sudo if you first make a few configuration changes as root (man 8 usermod):

sudo usermod --add-subuids 200000-201000 --add-subgids 200000-201000 $YOUR_USER

Also make sure that /proc/sys/user/max_user_namespaces contains a number greater than 0 as rootless podman depends on user namespaces (man 7 namespaces and man 7 user_namespaces):

cat /proc/sys/user/max_user_namespaces

If not, set it to something like:

sudo sysctl user.max_user_namespaces=15000

Making docker run in rootless mode is more involved, which is one of the reasons why I'm using podman.

Prepare example database files

There are a small handful of example databases which are hosted / curatated / developed by MySQL including the well-known Sakila database. This database also appears to work with MariaDB. However, Sakila has been ported to several other database systems, including PostgreSQL and IBM Db2. Here is one of these ports which conveniently includes the MySQL version as well, so let's just download this:

git clone https://github.com/jOOQ/sakila

The sakila schema file for Db2 provided by JOOQ does not quite work, at least not with the db2 client. This is due to an issue in the triggers as the END in the procedural code needs a different delimiter / terminator than the regular DDL. However, since the body of the triggers are only one line each, we can workaround the issue by simply removing all lines starting with BEGIN and END:

sed -i '/^BEGIN/d;/^END/d' sakila/db2-sakila-db/db2-sakila-schema.sql

Another issue is that some of the *-schema.sql files do create the database for us whereas others don't. But we'll tackle that below.

MariaDB

Pull the image and create the container, then start it:

podman create -p 127.0.0.1:3306:3306 --name mariadb107 \
  -v $(pwd)/sakila/mysql-sakila-db:/mnt/sakila:z \
  -e MARIADB_ROOT_PASSWORD=mypass \
  docker.io/library/mariadb:10.7
podman start mariadb107

Note that we have mounted the example database directory from the host as /mnt/sakila in the container. The :z may be necessary if you have SELinux.

You can then connect using a local client if you have that:

mariadb -uroot -pmypass

If you don't have a local client, you can log in to the container and run it from there:

podman exec -it mariadb107 mariadb -pmypass

Some example commands and queries, mostly self-explanatory:

help
help contents
status
SHOW AUTHORS;
SHOW PLUGINS;
SHOW DATABASES;
USE mysql;
SHOW TABLES;
desc user;

Find more details on the MariaDB dockerhub page.

To import the example database using the container's client:

podman exec -it mariadb107 bash -c \
  'mariadb -pmypass < /mnt/sakila/mysql-sakila-schema.sql'
podman exec -it mariadb107 bash -c \
  'mariadb -pmypass sakila < /mnt/sakila/mysql-sakila-insert-data.sql'

To start the client and connect directly to the newly created sakila database:

podman exec -it mariadb107 mariadb -pmypass sakila

See the next to last section for some example queries.

Note that MariaDB Foundation also has a couple of other MariaDB container repositories. One allows you to get the most recent development images for each supported release series (10.3, 10.4, 10.5, 10.6 etc), plus the current development series (currently 10.8): quay.io/mariadb-foundation/mariadb-devel. Find the availale tags on the mariadb-devel quay.io page.

Another repo - quay.io/mariadb-foundation/mariadb-debug - exists for debug releases. Tags are on the mariadb-debug quay.io page.

MySQL

The commands for MySQL are very similar to the above commands for MariaDB.

Pull the image and create the container, then start it:

podman create -p 127.0.0.1:3306:3306 --name mysql8027 \
  -v $(pwd)/sakila/mysql-sakila-db:/mnt/sakila:z \
  -e MYSQL_ROOT_PASSWORD=mypass \
  docker.io/library/mysql:8.0.27
podman start mysql8027

You can then connect using a local mysql client if you have that (or a mariadb client might also work):

mysql -uroot -pmypass

If you don't have a local client:

podman exec -it mysql8027 mysql -pmypass

Most of the example commands and queries shown above for MariaDB also work for MySQL.

To import the example database using the container's client:

podman exec -it mysql8027 bash -c \
  'mysql -pmypass < /mnt/sakila/mysql-sakila-schema.sql'
podman exec -it mysql8027 bash -c \
  'mysql -pmypass sakila < /mnt/sakila/mysql-sakila-insert-data.sql'

To start the client and connect directly to the newly created sakila database:

podman exec -it mysql8027 mysql -pmypass sakila

See the next to last section for some example queries.

More details can be found on the official MySQL dockerhub page.

PostgreSQL

podman create -p 127.0.0.1:5432:5432 --name pg141 \
  -e POSTGRES_PASSWORD=mypass \
  -v $(pwd)/sakila/postgres-sakila-db:/mnt/sakila:z \
  docker.io/library/postgres:14.1
podman start pg141

If you have the psql client installed locally:

psql -U postgres -h 127.0.0.1 -W

... and then enter your password. However, for convenience, you can create a file ~/.pgpass (and do chmod 0600 ~/.pgpass). Inside the file, place a line:

127.0.0.1:5432:*:postgres:mypass

Now you can connect without having to enter your password:

psql -U postgres -h 127.0.0.1

But if you don't the psql client locally, it's available in the container:

podman exec -it -u postgres pg141 psql

A few example commands:

help
\?
\conninfo
\copyright
\l
\c postgres
\d

Execute below commands to create a sakila database and import the example schema and data using the container's client:

podman exec -it -u postgres pg141 createdb sakila
podman exec -it -u postgres -e PGOPTIONS='--client-min-messages=warning' pg141 \
  psql sakila -qf /mnt/sakila/postgres-sakila-schema.sql
podman exec -it -u postgres -e PGOPTIONS='--client-min-messages=warning' pg141 \
  psql sakila -qf /mnt/sakila/postgres-sakila-insert-data.sql

The PGOPTIONS parameter and the -q option helps to quiet messages from executing statements in the .sql files.

Let's start psql in interactive mode and run some queries against the newly
created sakila tables:

podman exec -it -u postgres pg141 psql sakila

See the next to last section for some example queries.

More details can be found on the official PostgreSQL dockerhub page.

IBM Db2

This is a proprietary product, so it wouldn't normally be on my radar, but they have a community edition which is free to use and available as a docker image, so I thought why not? It's somehow one of the most popular database systems out there, so I thought it would be fun to just see what it's like.

The Db2 container image also comes with its own sample database - see below for installation details.

First, let's make a directory for data and an environment file with parameters:

mkdir -p ${HOME}/podman-data/db2
cat > db2_env_list.txt << EOF
LICENSE=accept
DB2INSTANCE=db2inst1
DB2INST1_PASSWORD=password
DBNAME=testdb
BLU=false
ENABLE_ORACLE_COMPATIBILITY=false
UPDATEAVAIL=NO
TO_CREATE_SAMPLEDB=false
REPODB=false
IS_OSXFS=false
PERSISTENT_HOME=true
HADR_ENABLED=false
ETCD_ENDPOINT=
ETCD_USERNAME=
ETCD_PASSWORD=
EOF

Then, create and start the container:

podman create -h db2 --name db2 --restart=always \
  --privileged=true -p 50000:50000 --env-file db2_env_list.txt \
  -v ${HOME}/podman-data/db2:/database:z \
  -v $(pwd)/sakila/db2-sakila-db:/mnt/sakila:z \
  docker.io/ibmcom/db2:latest
podman start db2

Now wait a few seconds to allow the container to start up properly.

With all that out of the way, we're ready to kick the tyres:

# Log in to the container:
podman exec -it -u db2inst1 db2 bash

# List the Db2 instances:
db2ilist

# Show current instance:
echo $DB2INSTANCE

# Create / re-create a sample database:
db2sampl -force -sql

# Show databases in the current instance:
db2 list db directory

# Connect to the sample database:
db2 connect to sample

# Show tables in current database:
db2 list tables

# Example queries:
db2 "SELECT * FROM department"
db2 "SELECT * FROM employee fetch first 10 rows only"
db2 "DESCRIBE OUTPUT SELECT * FROM department"

# Disconnect:
db2 connect reset

(Note: db2 is the command-line client for Db2. It can be started in interactive mode - simply enter db2 - but doesn't provide convenient access to command history with arrow-up and arrow-down keys like in the mariadb, mysql and psql clients, so therefore I prefer not to use interactive mode.)

Finally, let's try to import the Sakila database:

db2 "CREATE DATABASE sakila"
db2 connect to sakila
db2 -tsf /mnt/sakila/db2-sakila-schema.sql
db2 +o -tsf /mnt/sakila/db2-sakila-insert-data.sql

(The +o option prevents messages to standard output - useful if you you expect a lot of output and don't care to verify the success of every statement.)

More info:

Sakila queries

The beauty of SQL is of course that the same queries can run on any SQL compliant database system. At least if we're using standards compliant SQL queries. Below are a couple to get started looking at the sakila database.

SELECT f.title,  f.release_year, l.name
FROM film f
  JOIN language l ON l.language_id = f.language_id
WHERE title LIKE '%SUN%';

SELECT a.first_name, a.last_name, f.title
FROM actor a
  JOIN film_actor fa ON a.actor_id = fa.actor_id
  JOIN film f ON f.film_id = fa.film_id
WHERE f.title IN ('STALLION SUNDANCE', 'SUNDANCE INVASION');

Okay, there is a lot more to see in sakila, but writing those queries was not the point of this article! I'll leave that as an exercise for the reader 😄

End-of-file

I've stepped outside my comfort zone somewhat here to explore podman and a few database systems, some with which I don't work on a daily basis. So, if you spot any errors or anything else less than perfect, then please do get it touch and let me know! DM or tag me on Twitter - see link in footer.