project local PostGres on GUIX with Clojure JDBC (2022)
Table of Contents
Intro
🖼1
There are several copy-pasted articles2 out there on setting up project-local postgres instances. Which one came first or whether they are both copies of another, I don’t know; but in any case, I found the goals good, and was enlightened by the concept. There are several technical errors, but they served to get the juices flowing.
I am using the Postgres software version I got from GUIX, which is postgres 14.4. I am also using pg_crypto, which comes with the postgres install but will need to be installed on the individual database as an extension. I am operating with Clojure which uses JDBC for PostGres connections, and in particular my apps are using HikariCP3 for connection pooling.
Why
The first major reason is what is captured in GUIX philosophy and in modern idiom: it’s desirable to avoid a centralize “root” database install and, for reasons of security and operational modularity, to have a database instance that can live within your project.
Our Goal
The goal is inspired by the afore-mentioned articles, though I don’t yet use either direnv or asdf4. I want to be able to just start up the database with command postgres and to run a command psql to get a prompt, if desired5.
The Strategy
PostGres includes a management utility pg_ctl6 that can be used to start and stop processes associated with a particular database directory and their associated configs! So we will make a shell script with only one piece of conditional logic: checking whether the database directory already exists. If it does not, then create it, set up a database matching our default (not very secure) credentials, and get it going. If it does exist, just start it back up again.
JDBC needs ports, not UNIX Sockets
One of the first issues I had was a decision made where both articles chose unix sockets, one for security purposes and the other for avoiding collisions. However, this was not a viable solution for me because JDBC (on which Hikari and my Clojure apps rely) does not support Unix Sockets. In order to keep my programs as happy as they’ve always been without changing their code, I need to ensure that I am using HTTP ports, instead.
The single shell script ./postgres
The whole process is completed with only a single, relatively short shell script. I change their $PGDATA to postgres_data instead of their given postgres because Linux doesn’t allow a file and directory to have the same name and I want, like the articles, to be able to just type . ./postgres7 to get the database going.
Note also my use of Port in what I’m sending to the config; this may need to change if you are running multiple instances. Like other settings, is probably a bridge I will cross later, but right now, all of my apps expect their disparate development databases to be on the default port.
#!/usr/bin/env bash
# execute this with `. postgres` so that exports are inherited by the current shell process, so `psql` will work
# PROJECT NAME used for dev table and pass
PROJECT_NAME="coolproject"
# Place the data directory inside the project directory
export PGDATA="$(pwd)/postgres_data"
# Place Postgres' Unix socket inside the data directory
export PGHOST="$PGDATA"
if [[ ! -d "$PGDATA" ]]; then
mkdir $PGDATA
initdb
# ...configure it to listen on the port my apps want and...
cat >> "$PGDATA/postgresql.conf" <<-EOF
listen_addresses = '*'
Port = 5432
log_directory = '$PGDATA'
log_filename = 'logfile.log'
unix_socket_directories = '$PGHOST'
EOF
# ...create a database using the name Postgres defaults to.
echo "CREATE DATABASE $USER;" | postgres --single -E postgres
echo "CREATE DATABASE $PROJECT_NAME;" | postgres --single -E postgres
echo "CREATE USER $PROJECT_NAME WITH encrypted password '$PROJECT_NAME';" | postgres --single -E postgres
echo "GRANT ALL PRIVILEGES ON DATABASE $PROJECT_NAME TO $PROJECT_NAME;" | postgres --single -E postgres
# The following line is because we use pgcrypto for UUIDs in our apps
echo "CREATE EXTENSION pgcrypto;" | postgres --single -E $PROJECT_NAME
# echo "CREATE DATABASE $USER;" | postgres --single -E postgres
fi
pg_ctl -D $PGDATA -l postgres.log start
# or, if you want to stop/restart/etc, use something like ↓
# pg_ctl -D $PGDATA -l postgres.log stop
# The command below will be run if you are calling psql without using ". " on this script to get the environment variables.
# psql -h $PGDATA
Footnotes
1 Image from a blog post also worth reading (though I’m not sure about recommending enums in your SQL) https://www.bevuta.com/en/blog/using-postgresql-enums-in-clojure/
2 Okay, maybe not entirely copy-pasted; but in the technical details (and in the technical errors) they are similar. From 2019 we have https://jamey.thesharps.us/2019/05/29/per-project-postgres/ and then from latter 2021 we have https://ylan.segal-family.com/blog/2021/07/23/per-project-postgres-with-asdf-and-direnv/
3 HikariCP, https://github.com/tomekw/hikari-cp, works great for pooling connections, as long as the DB is ready to take connections at run-time (some of our production apps, which do not yet have this local PostGres setup, have race-condition issues where the pool fails because the application server restarted after updates before the DB server did. I am working on a solution to this).
4 I suppose if I hit issues running two application databases simultaneously it may be time for me to look into one of those other technologies, starting with Direnv.
5 I actually will certainly want to be able to connect via emacs with M-x sql-postgres, but I’ll deal with that at some later time. It’s an improvement over terminal psql, but not a show-stopper for my projects if I don’t have it.
6 pg_ctl has so many options. See https://www.postgresql.org/docs/current/app-pg-ctl.html
7 Use the . ./postgres instead of just ./postgres so the dot space shell operator causes it to remember the environment exported in the script, such as $PGDATA$ which is needed for psql to run properly. See https://stackoverflow.com/questions/16011245/source-files-in-a-bash-script
