Running in Docker - wiltondb/wiltondb GitHub Wiki

On this page:

WiltonDB RPM and DEB packages are intended to be used with "full" Linux environment - with a separate postgres user and postgresql service controlled by systemd. For minimal images running in Docker container it may be easier to use vanilla Babelfish, for example based on this image.

Still it is possible to run WiltonDB in containers, examples below use Podman and may need minor modifications to run with Docker.

Running WiltonDB with systemd inside a container

When running on RHEL/Fedora host Podman allows a straightforward way to run systemd inside the container. Based on this example the following steps can be used to run WiltonDB with systemd on Rocky Linux 8 (expected to work the same way with other popular RHEL derivatives).

$ podman pull rockylinux:8
$ podman run -id --name rocky8 -p 1433:1433 -p 5432:5432 -v /path/to/empt/dir/on/host/:/var/lib/pgsql/ rockylinux:8 /lib/systemd/systemd
$ podman exec -it rocky8 bash
# cat /etc/redhat-release
Rocky Linux release 8.8 (Green Obsidian)
# dnf update
# dnf install 'dnf-command(copr)'
# dnf copr enable wiltondb/wiltondb
# dnf update
# dnf install wiltondb
# wiltondb-setup
WARNING: Note that either your data directory '/var/lib/pgsql/data' or
         the parent directory '/var/lib/pgsql'
         is a direct mountpoint.  This is usually a bad idea and your
         filesystem layout should ideally look like:
         /ROOT_OWNED_MOUNTPOINT/POSTGRES_OWNED_DIRECTORY/DATADIR.
         See the upstream documentation for more info:
         http://www.postgresql.org/docs/15/static/creating-cluster.html
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
 * Initializing WiltonDB
WARNING: DB superuser 'wilton' was created with password 'wilton', please change the password before allowing remote connections.
 * Initialized, use 'systemctl start postgresql' to start the server
# systemctl start postgresql
# su - postgres
$ psql
postgres=# alter user wilton with password 'strong_password_here';
ALTER ROLE
postgres=# alter system set listen_addresses = '*';
postgres=# exit
$ exit
# systemctl restart postgresql

Now WiltonDB is running in container with ports 1433 and 5432 exposed to host machine. Connecting from host with sqlcmd we can check the running version:

sqlcmd -S 127.0.0.1,1433 -U wilton -P foobar
1> select @@version
2> go
version                                                                                                                                                                                                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Babelfish for PostgreSQL with SQL Server Compatibility - 12.0.2000.8
Oct 22 2023 17:48:32
Copyright (c) Amazon Web Services
PostgreSQL 15.4 (EL 1:15.4.wiltondb3.3_2-2.el8) on x86_64-redhat-linux-gnu (Babelfish 3.3.0)                                        

(1 row affected)

Running WiltonDB in a container directly

It is possible to run WiltonDB in a container running /usr/bin/postgres directly without systemd. Though wiltondb-setup script won't work without systemd, so we need to copy existing DB cluster initialized with wiltondb-setup or to create a custom DB cluster with Babelfish (to do so some additional scripting is needed taking wiltondb-setup as a starting point). Here we assume that DB cluster with Babelfish extensions already exists in some directory on a host machine.

The following simplistic Dockerfile can be used to build a container with WiltonDB:

FROM rockylinux:8
RUN dnf update -y && \
    dnf install 'dnf-command(copr)' -y && \
    dnf copr enable wiltondb/wiltondb -y && \
    dnf update -y && \
    dnf install wiltondb -y

Lets build the image:

podman build -t rocky8_image .

And run the container mounting the DB cluster directory and exposing ports 1433 and 5432:

podman run -id \
    --name rocky8_container \
    -p 1433:1433 \
    -p 5432:5432 \
    -v /path/to/existing/db/cluster/on/host:/var/lib/pgsql/ \
    rocky8_image \
    su postgres -c '/usr/bin/postgres -D /var/lib/pgsql/data'

Now WiltonDB is running in container with ports 1433 and 5432 exposed to host machine.