pg db scripts equievalents for pg:push, pg:pull, pg:backup, pg:restore

[koenhandekyn]

our current scripts below

the bin/db script is a generic script that works with db urls

the bin/kdb script fetches the db urls from the kamal secret files and understand the kamal destinations to give shorthands like

• bin/kdb pull development
• bin/kdb push development db_xyz
• bin/kdb backup development
• etc

for us that is one of the key things missing in comparison with heroku. with the below scripts we are kind of happy. they are not yet generalised at all and work for our use case only. but they might help others.a

---

/bin/db

#!/bin/bash

# Function to rename a PostgreSQL database
db_create() {
  local db_url="$1"
  local new_db_name="$2"

  if [[ -z "$new_db_name" || -z "$db_url" ]]; then
    echo "Usage: create <db_url> <new_db_name>"
    exit 1
  fi

  # Run the CREATE DATABASE command to rename the database
  psql "$db_url" -c "CREATE DATABASE \"$new_db_name\";"
}

# Function to rename a PostgreSQL database
db_rename() {
  local db_url="$1"
  local old_db_name="$2"
  local new_db_name="$3"

  if [[ -z "$old_db_name" || -z "$new_db_name" || -z "$db_url" ]]; then
    echo "Usage: rename <db_url> <old_db_name> <new_db_name>"
    exit 1
  fi

  # Run the ALTER DATABASE command to rename the database
  psql "$db_url" -c "ALTER DATABASE \"$old_db_name\" RENAME TO \"$new_db_name\";"
}

# Function for "pull" subcommand
db_pull() {
  local db_url="$1"
  local db_name="$2"
  local dump_file="${db_name}.dump"

  # Step 1: Dump the remote database
  echo "Dumping database..."
  pg_dump --no-owner --exclude-table-data=versions -v -Fc -f "$dump_file" "$db_url"

  # Step 2: Check if local database exists, prompt for confirmation, and drop if confirmed
  if psql -lqt | cut -d \| -f 1 | grep -qw "$db_name"; then
    read -p "Database $db_name already exists. Do you want to drop it and recreate? (y/n): " confirm
    if [[ "$confirm" != "y" ]]; then
      echo "Aborting..."
      exit 1
    fi
    echo "Dropping existing database..."
    dropdb "$db_name"
  fi

  # Step 3: Create a new local database
  echo "Creating local database..."
  createdb "$db_name"

  # Step 4: Restore the dump to the new local database
  echo "Restoring database..."
  pg_restore --no-owner --no-comments -v -d "postgres://localhost/$db_name" "$dump_file"

  echo "Database pull completed: $db_name"
}


# Function for "push" subcommand
db_push() {
  local local_db_name="$1"
  local remote_db_url="$2"
  local dump_file="${local_db_name}.dump"

  # Step 1: Dump the local database
  echo "Dumping local database..."
  echo pg_dump --no-owner --exclude-table-data=versions -v -Fc -f $dump_file postgres://localhost/$local_db_name
  pg_dump --no-owner --exclude-table-data=versions -v -Fc -f $dump_file postgres://localhost/$local_db_name

  # Step 4: Restore the dump to the new remote database
  echo "Pushing dump to remote database..."
  echo pg_restore --no-owner --no-comments -v -d $remote_db_url $dump_file
  pg_restore --no-owner --no-comments -v -d $remote_db_url $dump_file

  echo "Database push completed: $local_db_name"
}

# Function for "copy" subcommand
db_copy() {
  local source_db_url="$1"
  local target_db_url="$2"
  local current_date=$(date +%Y%m%d)
  # local uuid=$(uuidgen)
  local timestamp=$(date +%H%M%S)
  local source_db_name="${source_db_url##*/}"
  local target_db_name="${target_db_url##*/}"
  local dump_file="${source_db_name}-${target_db_name}-${current_date}-${timestamp}.dump"

  # Step 1: Dump the source database
  echo "Dumping source database..."
  pg_dump --no-owner --exclude-table-data=versions -v -Fc -f "$dump_file" "$source_db_url"

  # stop if the dump failed
  if [[ $? -ne 0 ]]; then
    echo "Dump failed. Aborting..."
    exit 1
  fi

  # Step 2: Create the target database (and backup if needed)
  local backup_db_name="${target_db_name}_${current_date}_${timestamp}"
  local source_base_url="${source_db_url%/*}"
  local target_base_url="${target_db_url%/*}"
  echo "Renaming current target database from $target_db_name to $backup_db_name..."
  psql "$target_base_url" -c "ALTER DATABASE \"$target_db_name\" RENAME TO \"$backup_db_name\";"
  echo "Creating new target database: $target_db_name..."
  psql "$target_base_url" -c "CREATE DATABASE \"$target_db_name\";"

  # Step 3: Restore the dump to the target database
  echo "Restoring dump to target database..."
  pg_restore --no-owner --no-comments -v -d "$target_db_url" "$dump_file"

  echo "Database copy completed: $source_db_name to $target_db_name"
}

# Main entry point for the script
if [[ "$1" == "pull" ]]; then
  if [[ -z "$2" || -z "$3" ]]; then
    echo "Usage: db pull <remote_db_url> <local_db_name>"
    exit 1
  fi
  db_pull "$2" "$3"
elif [[ "$1" == "push" ]]; then
  if [[ -z "$2" || -z "$3" ]]; then
    echo "Usage: db push <local_db_name> <remote_db_url>"
    exit 1
  fi
  db_push "$2" "$3"
elif [[ "$1" == "rename" ]]; then
  if [[ -z "$2" || -z "$3" || -z "$4" ]]; then
    echo "Usage: db rename <db_url> <old_db_name> <new_db_name>"
    exit 1
  fi
  db_rename "$2" "$3" "$4"
elif [[ "$1" == "create" ]]; then
  if [[ -z "$2" || -z "$3" ]]; then
    echo "Usage: db create <db_url> <new_db_name>"
    exit 1
  fi
  db_create "$2" "$3"
elif [[ "$1" == "copy" ]]; then
  if [[ -z "$2" || -z "$3" ]]; then
    echo "Usage: db copy <db_url> <db_url>"
    exit 1
  fi
  db_copy "$2" "$3"
else
  echo "Usage: db <command> [options]"
  echo "Commands: pull, push, rename, create"
  echo " - pull <remote_db_url> <local_db_name>"
  echo " - push <local_db_name> <remote_db_url>"
  echo " - rename <db_url> <old_db_name> <new_db_name>"
  echo " - create <db_url> <new_db_name>"
  exit 1
fi

bin/kdb

#!/bin/bash

# Function to extract DATABASE_URL from the secrets file
fetch_db_url() {
  local env="$1"
  local secrets_file=".kamal/secrets.${env}"

  if [[ ! -f "$secrets_file" ]]; then
    echo "Secrets file not found: $secrets_file"
    exit 1
  fi

  # Extract DATABASE_URL from the secrets file
  local db_url
  db_url=$(awk -F '=' '/^DATABASE_URL=/{print $2}' "$secrets_file")

  if [[ -z "$db_url" ]]; then
    echo "DATABASE_URL not found in $secrets_file"
    exit 1
  fi

  echo "$db_url"
}

command="$1"
env="$2"
current_date=$(date +%Y%m%d)
timestamp=$(date +%H%M%S)

# Fetch the DATABASE_URL from the secrets file
db_url=$(fetch_db_url "$env")
# Extract everything before the last '/'
base_url="${db_url%/*}"
# Extract the database name after the last '/'
db_name="${db_url##*/}"

case "$command" in
  console)
    psql "$db_url"
    ;;

  copy)
    if [[ $# -lt 3 ]]; then
      echo "Usage: kdb copy <source_environment> <target_environment>"
      exit 1
    fi

    kamal app stop -d "$2"
    kamal app stop -d "$3"

    # Fetch the source and target DATABASE_URLs
    source_db_url=$db_url
    target_db_url=$(fetch_db_url "$3")

    source_base_url="${source_db_url%/*}"
    target_base_url="${target_db_url%/*}"

    echo "Source base URL: $source_base_url"
    echo "Target base URL: $target_base_url"

    # if source and target base are the same, copy on the server, else copy with bin/db
    if [[ "$source_base_url" == "$target_base_url" ]]; then
      echo "Renaming current target database from $3 to $3_backup_${current_date}_${timestamp}"
      backup_db_name="$3_${current_date}_${timestamp}"
      ./bin/db rename "$base_url" "$3" "$backup_db_name"
      echo "Copying database from $2 to $3"
      psql "$base_url" -c "CREATE DATABASE \"$3\" WITH TEMPLATE \"$2\";"
    else
      # Copy the source DB to the target DB
      ./bin/db copy "$source_db_url" "${target_db_url}"
    fi

    kamal app boot -d "$2"
    kamal app boot -d "$3"
    ;;

  pull)
    if [[ $# -lt 3 ]]; then
      echo "Usage: kdb pull <environment> <local_db_name:optional>"
      exit 1
    fi
    local_db_name="${env}_${current_date}_${timestamp}"
    echo "./bin/db pull \"$db_url\" \"$local_db_name\""
    ./bin/db pull "$db_url" "$local_db_name"
    ;;

  push)
    if [[ $# -lt 3 ]]; then
      echo "Usage: kdb push <environment> <local_db_name>"
      exit 1
    fi
    local_db_name="$3"
    backup_db_name="${db_name}_${current_date}_${timestamp}"

    echo "Base URL: $base_url"
    echo "Renaming current DB from $db_name to $backup_db_name"
    ./bin/db rename "$base_url" "$db_name" "$backup_db_name"

    echo "Creating new DB: ${env}"
    ./bin/db create "$base_url" "${env}"

    echo "Pushing local DB $local_db_name to remote DB ${env}"
    ./bin/db push "$local_db_name" "${base_url}/${env}"
    ;;

  rename)
    echo "Usage: kdb rename <environment> <new_db_name:optional>"
    # if $3 present, use it as new_db_name else append current_date to db_name
    if [[ -n "$3" ]]; then
      new_db_name="$3"
    else
      new_db_name="${db_name}_${current_date}"
    fi

    echo "./bin/db rename \"$base_url\" \"$db_name\" \"$new_db_name\""
    ./bin/db rename "$base_url" "$db_name" "$new_db_name"
    ;;

  list)
    echo "Listing databases for $base_url"
    echo
    psql "$base_url" -c "\l" | grep "$db_name"
    ;;

  drop)
    echo "Dropping database $db_name"
    # ask for confirmation
    read -p "Are you sure you want to drop the database $db_name? (y/n): " -n 1 -r
    # abort if not confirmed
    if [[ ! $REPLY =~ ^[Yy]$ ]]; then
    echo
      echo "Aborted!"
      exit 1
    fi
    psql "$base_url" -c "DROP DATABASE \"$db_name\";"
    ;;

  backup:local)
    echo "Backing up database $db_name"
    local_db_name="${env}_${current_date}_${timestamp}"
    ./bin/db pull "$db_url" "$local_db_name"
    echo "Database backed up to $local_db_name.dump"
    ;;

  backup:local:sql)
    echo "Backing up database $db_name as SQL dump"
    local_db_name="${env}_${current_date}_${timestamp}"
    pg_dump -v "$db_url" > "${local_db_name}.sql"
    echo "Database backed up to ${local_db_name}.sql"
    ;;

  backup)
    echo "Backing up database $db_name on the remote"
    kamal app stop -d "$env"
    # make a backup on the remote db server using create db with template
    backup_db_name="${db_name}_${current_date}_${timestamp}"
    psql "$base_url" -c "CREATE DATABASE \"$backup_db_name\" WITH TEMPLATE \"$db_name\";"
    kamal app boot -d "$env"
    # check if the backup db was created
    if psql "$base_url" -lqt | cut -d \| -f 1 | grep -qw "$backup_db_name"; then
      echo "Database copied as $backup_db_name"
    fi
    ;;

  revert)
    echo "Reverting database $db_name from $3"
    # ask for confirmation
    read -p "Are you sure you want to revert the database $db_name to $3? (y/n): " -n 1 -r
    # abort if not confirmed
    echo
    if [[ ! $REPLY =~ ^[Yy]$ ]]; then
      echo "Aborted!"
      exit 1
    fi
    # check if the chosen db exists
    if ! psql "$base_url" -lqt | cut -d \| -f 1 | grep -qw "$3"; then
      echo "Database $3 does not exist, aborting the revert!"
      exit 1
    fi
    echo "Stopping the app"
    kamal app stop -d "$env"
    # make a backup on the remote db server using create db with template
    backup_db_name="${db_name}_${current_date}_${timestamp}"
    psql "$base_url" -c "CREATE DATABASE \"$backup_db_name\" WITH TEMPLATE \"$db_name\";"
    echo "Database copied as $backup_db_name"
    # drop the current db
    echo "Dropping database $db_name"
    psql "$base_url" -c "DROP DATABASE \"$db_name\";"
    # rename the chosen db to the original db name
    echo "Renaming database $3 to $db_name"
    psql "$base_url" -c "ALTER DATABASE \"$3\" RENAME TO \"$db_name\";"
    echo "Booting the app"
    kamal app boot -d "$env"
    ;;

  create)
    echo "Creating database $env"
    psql "$base_url" -c "CREATE DATABASE \"$env\";"
    ;;

  *)
    echo "Unknown command: $command"
    echo "Usage: kdb <pull|push|rename|copy|list> <environment>"
    exit 1
    ;;
esac