kamal db:pull, db:push etc (for pg and more?)

[koenhandekyn]

i understand it's not the core of kamal maybe, it still is strongly related to having a full alternative for heroku, i.e. the ability to easily pull and push databases from environments for debugging reasons for example.

below is a script that provides us with the same convenience for our setup. it reads the database credentials for the kamal secrets but for the rest it's kind of kamal agnostics. it provides pull, push, copy, backup, rename functionality for your kamal environments.

there's also a 'console' command that opens a psql straight to the remote db, which is actually faster then launching the rails db console on the host and it's also more convenient for the case where you want to save/load e.g. from local CSV files.

first of all, feel free to use it as is or as inspiration, to anyone interested.

i'm curious where @dhh thinks this kind of functionality would ideally be made shareable? for us, these scripts where kind of one of the key missing parts to comfortably migrate away from heroku to hetzner, and i'm assuming that many others would be making the jump more easily if they have these available.

currently it's PG specific, but conceptually at least, pulling and/or pushing a database from local to a destination is useful for independent.

the script is here: #1164 (comment)

[sasharevzin]

This is amazing. If it can't be part of Kamal, perhaps Kamal can introduce some kind of plugins or add-ons, similar to what Dokku does? Oh wait, @dhh already mentioned hooks here

[dhh]

I think it's great to build stuff on top of Kamal, and share secrets with it, but I'm not sure what form any additional integration would take. It's totally fine just to shell out to Kamal to drive it from secondary scripts.

[koenhandekyn]

i agree that here there is no deep integration, but as said, to make the switch from heroku to hetzner, i was missing the db:pull, db:push, pg:backups, etc to give me operational confidence.

developing them wasn't that hard, 'integration' is indeed minimal, but i did spend some time on it and it did take quite some iterations to get them to some basic minimal level.

so my question really is about how and where we can easily share those things 'on-top-of' kamal, even if the integration is low. i don't mind sharing it at all, and i'm guessing that quite some kamal users would be very happy to get db:push, db:pull commands for there apps they moved, but currently the best i could come up with was to throw them here in the discussions :)

though maybe it's just extra gems indeed that install the (client side) scripts, and the gems can be discovered maybe simply by naming convention - e.g kamal-ext-xxx and google/gpt takes care of discoverability :)

i might give that a try :)

[koenhandekyn]

i was working on another gem that is a very lightweight solution for doing backups for postgres when e.g. running with kamal on hetzner. it basically calls into pg_dump and uses active storage as an abstraction to store the backup wherever you see fit, and in our case it's S3. (some older gems hard link to S3 but with active storage we can easily be more loose)

i've added the script as part of that gem, it's called pgdb now and provides the below.
the script kind of goes hand in hand with the backups anyhow.

PR's welcome there :)

so gem install ops_backups is enough to bring the pgdb script, it's not yet written in ruby so it assumes bash/zsh underlying ...

Usage: pgdb <command> <environment> [<additional_args>]

Available commands:
  console <environment>                   - Opens a database console for the specified environment.
  create <environment>                    - Creates a new database for the specified environment.
  revert <environment> <backup_db_name>   - Reverts the database to a specified backup.
  backup <environment>                    - Creates a clone of the remote database as a backup on the server.
  backup:local <environment>              - Backs up the remote database to a local dump file.
  drop <environment>                      - Drops the database of the specified environment after confirmation.
  pull <environment|url> [local_db_name]  - Pulls the remote database to a local database with an optional custom name (optional).
  push <environment> <local_db_name>      - Pushes a local database to the remote environment, creating a backup of the remote.
  rename <environment> [new_db_name]      - Renames the main database of the specified environment, optionally using a new name (optional).
  copy <source_env> <target_env>          - Copies a database from one environment to another, with backup of the target database.
  list <environment>                      - Lists databases for the base URL associated with the environment.

set EXCLUDE_TABLE_DATA to exclude data from specific tables on export
e.g. EXCLUDE_TABLE_DATA=versions   bin/pgdb pull staging

https://github.com/koenhandekyn/ops_backups

[koenhandekyn]

actually, reflecting on this with @kennethgeerts, we concluded that kamal would just need something like rake has, a means to list contributed commands that are 'provided' by the gems in the project. or similar to rails that lists the available generators, provided by installed gems.