Adding PostgreSQL

- 4 mins read

Adding Ecto

When I generated a new Phoenix project for Minotaur, I ran the phx.new command with the --no-ecto switch since I didn’t want to worry about any database details until I had built out an initial concept. It is now time to introduce some form of persistence to the application as everything so far has been in-memory storage.

Adding Ecto to an existing project isn’t too complicated. I generate a new Phoenix app using the default included Ecto library with the PostgreSQL adapter and compare the differences with my application. Most of the changes are in the configuration files and it doesn’t take long to copy over the necessary additions for Ecto.

I haven’t started a local PostgreSQL server yet, but I run one of my unit tests to make sure the application compiles. The code compiles and the application startup crashes expectedly with the following error:

[error] Postgrex.Protocol (#PID<0.674.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (localhost:5432): connection refused - :econnrefused

I’m already running my local development with VS Code Dev Containers so I want to add the local development database to start up with the VS Code server. I add a Docker Compose file and update .devcontainer/devcontainer.json to have the Dev Container start with it instead of using a Dockerfile directly.

version: '3.8'
services:
  devcontainer:
    build:
      context: .
      dockerfile: Dockerfile
    volumes:
      - ../..:/workspaces:cached
    command: sleep infinity
    depends_on:
    - db

  db:
    image: postgres:16
    restart: unless-stopped
    volumes:
      - postgres-data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: postgres
      POSTGRES_USER: postgres
      POSTGRES_DB: minotaur_dev

volumes:
  postgres-data:

// devcontainer.json
{
-  "dockerFile": "Dockerfile",
-  "context": "..",
+  "dockerComposeFile": "docker-compose.yml",
+  "service": "devcontainer",
+  "workspaceFolder": "/workspaces/${localWorkspaceFolderBasename}",
}

I also update config/test.exs to have Ecto use the database hostname defined in the Compose file.

config :minotaur, Minotaur.Repo,
  username: "postgres",
  password: "postgres",
-  hostname: "localhost",
+  hostname: "db",
  database: "minotaur_test#{System.get_env("MIX_TEST_PARTITION")}",
  pool: Ecto.Adapters.SQL.Sandbox,
  pool_size: System.schedulers_online() * 2

The full test suite now runs and all tests pass, however, there are several errors being logged on a few tests:

(Postgrex.Error) FATAL 53300 (too_many_connections) sorry, too many clients already

I suspect this is due to having multiple instances of the application starting with my cluster tests so I run the suite again with those tests excluded mix test --exclude cluster_test:true The error logs are no longer showing. I run the suite again with only the cluster tests, and the logs are present. This confirms my hunch is correct.

My code isn’t doing anything with Ecto at this point other than creating connections at startup. I see the connection pool size in the test environment is set to System.schedulers_online() * 2 which evaluates to 40 in my local environment. My cluster tests are creating at most 2 nodes which each boot up an instance of the application while the test runner process is also starting its own instance. This means these tests are opening 120 simultaneous database connections which is more than the default 100 connections.

I could increase the max connections for the database or reduce the pool size for the clients. I opt to modify the connection pool in the configuration by adding a limit to the pool size so no more than 30 connections can be created per client.

config :minotaur, Minotaur.Repo,
  # …
  pool_size: min(30, System.schedulers_online() * 2)

This will cover my current use cases where 3 instances of the application can be started at once in the test environment. The full test suite now runs without any error logs.

Installing production PostgreSQL

I don’t have an official production environment at this stage, but I am treating my deployments to my personal dev VPS as a production-like environment. I now need a production instance of PostgreSQL for my deployed application. Considering all the work involved with database administration, a managed solution which encapsulates the bulk of these administration features within a platform is a great option for this solo project. However, I am also trying to minimize my infrastructure spend for this side project, so going for a self-managed option may be best for the short term.

I install PostgreSQL version 16 on my Debian server.

$ sudo apt install postgresql-16

I switch to the postgres user and open an interactive terminal for PostgreSQL via psql.

$ sudo -i -u postgres
$ psql

Next, I create a new database and a user which will be used by the Minotaur application.

CREATE DATABASE minotaur_production;
CREATE USER minotaur_application WITH PASSWORD '************';

I grant privileges for the application user to create tables and perform CRUD operations on future tables in the public schema for the created database.

\c minotaur_production
GRANT USAGE ON SCHEMA public TO minotaur_application;
GRANT CREATE ON SCHEMA public TO minotaur_application;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO minotaur_application;

That is all I have time for today. Next time I’ll configure my production application environment with the new database credentials and run some demo migrations to verify everything works as expected.