README.md

Postgres maintenance scripts

Note: for development use only. Please be sure not to use this with production data.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

migrate-postgresapp-to-docker.sh

Copy the contents of a db from postgres.app to a specific docker postgres instance

re-init postgres

This principally solves collations issues in template1 but can also solve any other number of problems.

migrate-postgresapp-to-docker.sh

#!/usr/bin/env bash
set -euo pipefail

# -------- CONFIG --------
DB_NAME="kindred_development"

# Docker
DOCKER_DB_CONTAINER="your-container-name"
DOCKER_DB_SERVICE="db"
DOCKER_DB_USER="your-user"

# Files
DUMP_FILE="your-db-from-postgresapp.dump"

# Postgres.app paths (Apple Silicon + Intel compatible)
PG_BIN="/Applications/Postgres.app/Contents/Versions/latest/bin"

# ------------------------

echo "🛑 Stopping Docker Postgres (freeing port 5432)..."
docker compose stop "$DOCKER_DB_SERVICE"

echo "🚀 Starting Postgres.app..."
open -a Postgres

echo "⏳ Waiting for Postgres.app to be ready..."
until "$PG_BIN/pg_isready" -h localhost -p 5432 >/dev/null 2>&1; do
  sleep 1
done

echo "📤 Dumping database from Postgres.app (no auth)..."
"$PG_BIN/pg_dump" \
  -h localhost \
  -p 5432 \
  -d "$DB_NAME" \
  --format=custom \
  --file="$DUMP_FILE"

echo "🛑 Stopping Postgres.app..."
osascript -e 'tell application "Postgres" to quit'

echo "🚀 Starting Docker Postgres..."
docker compose up -d "$DOCKER_DB_SERVICE"

echo "⏳ Waiting for Docker Postgres to be ready..."
until docker exec "$DOCKER_DB_CONTAINER" \
  pg_isready -U "$DOCKER_DB_USER" >/dev/null 2>&1; do
  sleep 1
done

echo "📥 Restoring dump into Docker Postgres..."
docker exec -i "$DOCKER_DB_CONTAINER" \
  pg_restore \
  -U "$DOCKER_DB_USER" \
  -d "$DB_NAME" \
  --clean \
  --if-exists \
  --no-owner \
  < "$DUMP_FILE"

echo "✅ Migration complete (Postgres.app → Docker)."

reinit-postgres.sh

#!/usr/bin/env bash
set -euo pipefail

# ---- CONFIG (adjust if needed) ----
COMPOSE_SERVICE_DB="db"
CONTAINER_NAME="your-container-name"
DB_USER="db-user"
DB_NAME="your-db"
VOLUME_NAME="your-app_postgres_data"
DUMP_FILE="your-db.dump"
# ----------------------------------

echo "🔐 Backing up database..."

docker exec "$CONTAINER_NAME" \
  pg_dump -U "$DB_USER" -d "$DB_NAME" \
  --format=custom \
  --file="/tmp/$DUMP_FILE"

docker cp "$CONTAINER_NAME:/tmp/$DUMP_FILE" "./$DUMP_FILE"

echo "🛑 Stopping containers..."
docker compose down

echo "💣 Removing Postgres volume (cluster re-init)..."
docker volume rm "$VOLUME_NAME"

echo "🚀 Starting fresh Postgres cluster..."
docker compose up -d "$COMPOSE_SERVICE_DB"

echo "⏳ Waiting for Postgres to be ready..."
until docker exec "$CONTAINER_NAME" pg_isready -U "$DB_USER" >/dev/null 2>&1; do
  sleep 1
done

echo "🧱 Creating database (if needed)..."
docker exec "$CONTAINER_NAME" \
  psql -U "$DB_USER" -d postgres \
  -c "CREATE DATABASE $DB_NAME;" || true

echo "📥 Restoring database..."
docker exec -i "$CONTAINER_NAME" \
  pg_restore -U "$DB_USER" \
  -d "$DB_NAME" \
  --clean \
  --if-exists \
  < "./$DUMP_FILE"

echo "✅ Done. Postgres cluster re-initialized and data restored."