scripts related to PostgreSQL

postgres.md

Scripts to make working with multiple pg dbs

Dump multiple database on same db instance

dump.sh

#!/usr/bin/env bash

DB_USER="user"
DB_HOST="localhost"
DB_PASSWORD="dbpswd123"

databases=("auth" "page" "notification" "chat")

export PGPASSWORD=$DB_PASSWORD

for db in "${databases[@]}"; do
    echo "Dumping database: $db"
    pg_dump --no-owner --no-privileges --host=$DB_HOST -x -U $DB_USER $db > "$db.dump"
    
    if [ $? -eq 0 ]; then
        echo "Database $db dumped successfully."
    else
        echo "Failed to dump database $db."
    fi
done

unset PGPASSWORD

Restore db from previously extracted dump files from above script

#!/usr/bin/env bash

DB_USER="user"
DB_HOST="localhost"
DB_PASSWORD="dbpswd123"

databases=("auth" "page" "notification" "chat")

export PGPASSWORD=$DB_PASSWORD

for db in "${databases[@]}"; do
    echo "Restoring database: $db"
    
    psql -U $DB_USER -h $DB_HOST -p 5432 -d $db < "$db.dump"    
    
    if [ $? -eq 0 ]; then
        echo "Database $db restored successfully."
    else
        echo "Failed to restore database $db."
    fi
done

unset PGPASSWORD