postgres-dbman 0.1.0

Add your description here

DBMan

DBMan is an CLI tool to manage PostgreSQL databases running in separate Docker containers. The content of the databases is obtained from pg_dumps of production databases.

It will assign each database to a unique ZFS dataset inside a predefined ZFS dataset. It will allow snapshotting the dataset and restoring the dataset from a snapshot.

Configuration

DBMan uses environment variables for configuration. Copy .env.example to .env and modify the values as needed:

# ZFS configuration
DBMAN_ZFS_DATASET=big/docker-postgres
DBMAN_ZFS_MOUNT_POINT=/mnt/big/docker-postgres
DBMAN_ZFS_COMPRESSION=lz4  # Default compression algorithm for new databases

# Docker configuration
DBMAN_DOCKER_IMAGE=postgres:latest

# PostgreSQL configuration
DBMAN_POSTGRES_USER=postgres
DBMAN_POSTGRES_PASSWORD=postgres

# Database configuration
DBMAN_SQLITE_DB=~/.dbman/dbman.db

You can also override the ZFS compression algorithm using the -c or --compression command line option:

uv run dbman.py -c zstd create mydb dump.sql

Installation

1. You need uv to run dbman. More info about installing it can be found here https://docs.astral.sh/uv/getting-started/installation/

2. Copy .env.example to .env and configure your environment variables.

3. Test it:

uv run dbman.py

Usage

The tool can be used to:

• create new databases from pg_dumps - it will automatically create a new Docker container with the data from the dump. It will allow naming the container and the database. It will automatically assign a port to the container.

  # Create a database with default compression (inherited from parent ZFS dataset)
  uv run dbman.py create mydb dump.sql
  # or using the short command
  uv run dbman.py c mydb dump.sql
  
  # Create a database with specific compression
  uv run dbman.py create -c zstd mydb dump.sql
  # or using the short command
  uv run dbman.py c -c zstd mydb dump.sql
  

• list all databases and their statuses

  uv run dbman.py list
  # or using the short command
  uv run dbman.py ls
  

• create a snapshot of a database

  uv run dbman.py snapshot mydb mysnapshot
  # or using the short command
  uv run dbman.py snap mydb mysnapshot
  

• restore a database from a snapshot

  uv run dbman.py restore mydb mysnapshot
  # or using the short command
  uv run dbman.py rest mydb mysnapshot
  

• list all snapshots of a database

  uv run dbman.py list-snapshots mydb
  # or using the short command
  uv run dbman.py lss mydb
  

• start a subshell with database environment variables

  uv run dbman.py shell mydb
  # or using the short command
  uv run dbman.py sh mydb
  

• print database environment variables

  # Print variables
  uv run dbman.py env mydb
  # or using the short command
  uv run dbman.py e mydb
  
  # Export variables to the current shell
  eval $(uv run dbman.py env --export mydb)
  
  # Unset variables from the current shell
  eval $(uv run dbman.py env --clean mydb)
  

• destroy a database

  uv run dbman.py destroy mydb
  # or using the short command
  uv run dbman.py del mydb
  

For detailed usage information, run:

uv run dbman.py --help

Note

This tool was also used as a proof-of-concept in creating code by using AI. I started with a docstring in dbman.py and asked Cursor to create an implementation for me. It created a functional ~250 lines of code which mostly did what I wanted. When I tweaked and improved it, I also extensively used AI to do the modifications, rather than coding it myself.