Last active
June 5, 2025 22:54
-
-
Save lbrito1/27019df79120bbd2aba4d223b7b14199 to your computer and use it in GitHub Desktop.
db_restore.sh
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash | |
| # Check if the required arguments are provided | |
| if [ $# -ne 3 ]; then | |
| echo "Usage: $0 <CONNECTION_STRING> <DB_NAME> <IMPORT_FILE>" | |
| echo "Example: $0 'postgresql://postgres:my_password@myhost:5432' 'frdm' 'path/to/import.sql'" | |
| echo "Note: The DB_NAME argument defines the target database for operations (drop, create, import)." | |
| exit 1 | |
| fi | |
| # Assign command-line arguments to variables | |
| CONNECTION_STRING="$1" | |
| DB_NAME="$2" # This is the mandatory DB_NAME argument for operations | |
| IMPORT_FILE="$3" | |
| # --- Parse the Connection String to set environment variables --- | |
| # This ensures psql and createdb can connect using these details. | |
| # Extract everything after the protocol (e.g., 'user:pass@host:port/optional_db') | |
| HOST_PART=$(echo "$CONNECTION_STRING" | sed -E 's/^[a-z]+:\/\///') | |
| # Extract user:password part (if present) | |
| if [[ "$HOST_PART" =~ ^([^:]+)(:([^@]+))?@ ]]; then | |
| export PGUSER="${BASH_REMATCH[1]}" | |
| export PGPASSWORD="${BASH_REMATCH[3]}" # Will be empty if no password | |
| HOST_PART="${HOST_PART#*@}" # Remove user:pass@ | |
| fi | |
| # Extract host:port (and optional database name from URI, though we'll use the DB_NAME argument) | |
| if [[ "$HOST_PART" =~ ^([^:]+)(:([0-9]+))?(\/(.*))?$ ]]; then | |
| export PGHOST="${BASH_REMATCH[1]}" | |
| export PGPORT="${BASH_REMATCH[3]}" # Will be empty if no port | |
| # The database name from the URI path is in ${BASH_REMATCH[5]}, but we're ignoring it for operations. | |
| fi | |
| # --- End Connection String Parsing --- | |
| # ANSI color codes | |
| GREEN='\033[0;32m' # Green color | |
| NC='\033[0m' # No color | |
| echo "Step 0: Drop old database '$DB_NAME'..." | |
| # Connect to the default 'postgres' database to drop the target database | |
| # psql will use PGUSER, PGPASSWORD, PGHOST, PGPORT from environment variables | |
| psql -d postgres -c "DROP DATABASE IF EXISTS \"$DB_NAME\";" | |
| echo "Step 1: Creating the new database '$DB_NAME'..." | |
| # createdb will use PGUSER, PGPASSWORD, PGHOST, PGPORT from environment variables | |
| createdb "$DB_NAME" | |
| # Check if database creation was successful | |
| if [ $? -ne 0 ]; then | |
| echo "Error: Failed to create database $DB_NAME." | |
| exit 1 | |
| fi | |
| echo "Step 2: Creating the 'blazer' role and granting privileges..." | |
| # Connect to the newly created database to set up roles and privileges | |
| psql -d "$DB_NAME" -c "DO \$\$ BEGIN IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'blazer') THEN CREATE ROLE blazer LOGIN PASSWORD 'change-me-later'; END IF; END \$\$;" | |
| psql -d "$DB_NAME" -c "GRANT CONNECT ON DATABASE \"$DB_NAME\" TO blazer;" | |
| psql -d "$DB_NAME" -c "GRANT USAGE ON SCHEMA public TO blazer;" | |
| psql -d "$DB_NAME" -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO blazer;" | |
| psql -d "$DB_NAME" -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO blazer;" | |
| echo "Step 3: Importing data from $IMPORT_FILE with FK constraints temporarily disabled..." | |
| # Connect to the target database for the import | |
| psql -d "$DB_NAME" -c "SET session_replication_role = replica;" -f "$IMPORT_FILE" | |
| # Check if the import command failed | |
| if [ $? -ne 0 ]; then | |
| echo "Error: psql command failed during import." | |
| exit 1 | |
| fi | |
| echo -e "${GREEN}Import and role creation completed successfully.${NC}" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment