postgres-dbman 0.2.1

A CLI tool to manage PostgreSQL databases running in separate Docker containers on top of ZFS

DBMan

DBMan is a 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 assigns each database to a unique ZFS dataset inside a predefined ZFS dataset. Database metadata is stored in ZFS properties. You can snapshot datasets, restore from snapshots, and clone or export/import databases between hosts.

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
DBMAN_BIND_IP=127.0.0.1  # One or more host IPs to bind the database port to (comma-separated, default: localhost only)

# PostgreSQL configuration
DBMAN_POSTGRES_USER=postgres
DBMAN_POSTGRES_PASSWORD=postgres

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
  

• start, stop, or restart a database

  uv run dbman.py start mydb
  uv run dbman.py stop mydb
  uv run dbman.py restart mydb
  

• clone a database from a snapshot

  uv run dbman.py clone mydb mysnapshot newdb
  

• export a database to a ZFS send file (for transport between hosts)

  uv run dbman.py export mydb output.zfs
  # or: uv run dbman.py exp mydb -s snapshotname output.zfs
  

• import a database from a ZFS send file

  uv run dbman.py import output.zfs newdb
  # or: uv run dbman.py imp output.zfs
  

• list orphaned mountpoints (cleanup)

  uv run dbman.py cleanup
  

• list or delete databases older than a given number of days

  uv run dbman.py delete_older_than 30
  uv run dbman.py delete_older_than 30 --do-it
  # or: uv run dbman.py dot 30
  

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.