Postgresql Export Using URL

pg_export.sh

#!/bin/bash

CONNECTION_URL=""
BACKUP_DIR="bks"
BACKUP_FILE="$BACKUP_DIR/backup_$(date +'%Y%m%d%H%M%S').sql"

# Function to parse connection URL
parse_url() {
  local url="$1"
  local proto="$(echo $url | grep '://' | sed -e's,^\(.*://\).*,\1,g')"
  local url_no_proto="$(echo ${url/$proto/})"
  local userpass="$(echo $url_no_proto | grep '@' | cut -d@ -f1)"
  local user="$(echo $userpass | grep ':' | cut -d: -f1)"
  local pass="$(echo $userpass | grep ':' | cut -d: -f2)"
  local hostportdb="$(echo $url_no_proto | sed -e s,$userpass@,,g)"
  local hostport="$(echo $hostportdb | grep '/' | cut -d/ -f1)"
  local db="$(echo $hostportdb | grep '/' | cut -d/ -f2-)"
  local host="$(echo $hostport | grep ':' | cut -d: -f1)"
  local port="$(echo $hostport | grep ':' | cut -d: -f2)"

  echo "$user" "$pass" "$host" "$port" "$db"
}


# Parse the connection URL into components
read USER PASSWORD HOST PORT DBNAME <<< $(parse_url $CONNECTION_URL)

# Export password to avoid prompting
export PGPASSWORD=$PASSWORD

# Check if backup directory exists, if not, create it
if [ ! -d "$BACKUP_DIR" ]; then
  mkdir -p "$BACKUP_DIR"
fi

# Perform the backup using pg_dump
pg_dump -h $HOST -p $PORT -U $USER -d $DBNAME -f $BACKUP_FILE --format=custom --disable-triggers

# Verify if the backup was successful
if [ $? -eq 0 ]; then
  echo "Backup successful! File created: $BACKUP_FILE"
else
  echo "Backup failed!" >&2
  exit 1
fi

# Unset the password environment variable
unset PGPASSWORD