Metadata-Version: 2.4
Name: postgres-dbman
Version: 0.1.0
Summary: Add your description here
Requires-Python: >=3.12
Description-Content-Type: text/markdown
Requires-Dist: docker>=7.1.0
Requires-Dist: pre-commit>=4.2.0
Requires-Dist: python-decouple>=3.8
Requires-Dist: ruff>=0.11.4
Requires-Dist: zstandard>=0.22.0

# 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:

```bash
# 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:

```bash
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:

```bash
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.

  ```bash
  # 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

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

- create a snapshot of a database

  ```bash
  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

  ```bash
  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

  ```bash
  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

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

- print database environment variables

  ```bash
  # 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
  ```bash
  uv run dbman.py destroy mydb
  # or using the short command
  uv run dbman.py del mydb
  ```

For detailed usage information, run:

```bash
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.
