Metadata-Version: 2.4
Name: postgres-dbman
Version: 0.2.0
Summary: A CLI tool to manage PostgreSQL databases running in separate Docker containers on top of ZFS
Author-email: Beda Kosata <beda@kosata.me>
License: WTFPL
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: System Administrators
Classifier: Operating System :: POSIX :: Linux
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.12
Requires-Python: >=3.12
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: docker>=7.1.0
Requires-Dist: pre-commit>=4.5.1
Requires-Dist: python-decouple>=3.8
Requires-Dist: ruff>=0.15.6
Requires-Dist: zstandard>=0.25.0
Dynamic: license-file

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

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

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

- start, stop, or restart a database

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

- clone a database from a snapshot

  ```bash
  uv run dbman.py clone mydb mysnapshot newdb
  ```

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

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

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

- list orphaned mountpoints (cleanup)

  ```bash
  uv run dbman.py cleanup
  ```

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

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

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