K-SOCIAL Official Database

🔧 PostgreSQL Database Restoration Tutorial

This tutorial will guide you through the process of restoring a PostgreSQL database using the automated bash script provided below.

📋 Prerequisites

PostgreSQL 17 installed on your system
Access to the database server (localhost or remote)
Database credentials (username and password)
Downloaded backup file (.sql) from this repository
Sufficient permissions to create/drop databases

⚙️ Step 1: Create the Restoration Script

First, create a new bash script file on your system:

nano restore_db.sh

Copy and paste the complete script below into the file:

#!/bin/bash
# ==============================================================================
# CONFIGURATION (TO BE FILLED IN)
# ==============================================================================

# Database connection parameters
DB_HOST="localhost"          # Database host (use "localhost" for local database)
DB_PORT="5432"              # PostgreSQL port (default is 5432)
DB_NAME="DB_NAME"           # Name of the database to restore
DB_USER="YOUR_USER"         # PostgreSQL username
DB_PASS="YOUR_PASSWORD"     # PostgreSQL password

# Full path to the .sql backup file to restore
BCK_SQL_FILE="/YOUR/DB/FOLDER/k-db.DB_NAME.sql"

# Log file path for restoration process
LOG_FILE="/YOUR/LOG/FOLDER/log.txt"

# PostgreSQL binary paths (version 17)
PSQL_BIN="/usr/lib/postgresql/17/bin/psql"
DROPDB_BIN="/usr/lib/postgresql/17/bin/dropdb"
CREATEDB_BIN="/usr/lib/postgresql/17/bin/createdb"

# ==============================================================================
# RESTORATION LOGIC
# ==============================================================================

# Function to log messages with timestamp
log_msg() {
    echo "$(date +'%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

# Check if backup file exists
if [ ! -f "$BCK_SQL_FILE" ]; then
    log_msg "ERROR: Backup file $BCK_SQL_FILE does not exist!"
    exit 1
fi

# Create temporary password file for secure authentication
PGPASS_TEMP=$(mktemp)
echo "${DB_HOST}:${DB_PORT}:*:${DB_USER}:${DB_PASS}" > "$PGPASS_TEMP"
chmod 600 "$PGPASS_TEMP"
export PGPASSFILE="$PGPASS_TEMP"

# Ensure temporary file is deleted on script exit
trap 'rm -f "$PGPASS_TEMP"' EXIT

log_msg "--- STARTING DATABASE RESTORATION PROCEDURE: $DB_NAME ---"

# Phase 1: Drop existing database (complete cleanup)
log_msg "Phase 1: Dropping existing database '$DB_NAME'..."
$DROPDB_BIN -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" --if-exists --force "$DB_NAME" 2>>"$LOG_FILE"

# Phase 2: Create empty database
log_msg "Phase 2: Creating empty database '$DB_NAME'..."
$CREATEDB_BIN -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" "$DB_NAME" 2>>"$LOG_FILE"

if [ $? -eq 0 ]; then
    # Phase 3: Import SQL file data
    log_msg "Phase 3: Importing data from file $(basename "$BCK_SQL_FILE")..."
    
    # Execute import and capture any errors in the log
    $PSQL_BIN -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -f "$BCK_SQL_FILE" >> "$LOG_FILE" 2>&1
    
    if [ ${PIPESTATUS[0]} -eq 0 ]; then
        log_msg "✅ RESTORATION COMPLETED SUCCESSFULLY."
    else
        log_msg "❌ ERROR during SQL import. Check the log file."
    fi
else
    log_msg "❌ CRITICAL ERROR creating database. Procedure aborted."
    exit 1
fi

exit 0

🔐 Step 2: Configure Database Credentials

Edit the configuration section at the top of the script with your specific values:

DB_HOST: Enter your database server address (e.g., "localhost", "192.168.1.100", or "db.example.com")

DB_PORT: PostgreSQL port (default is 5432, change only if using a custom port)

DB_NAME: The name you want to give to the restored database

DB_USER: Your PostgreSQL username with sufficient privileges

DB_PASS: Your PostgreSQL password

BCK_SQL_FILE: Full path to the downloaded backup file (e.g., "/home/user/backups/k-db.2026-01-17_18-16-57.sql")

LOG_FILE: Path where the restoration log will be saved (e.g., "/var/log/db_restore.log")

Example Configuration:

DB_HOST="localhost"
DB_PORT="5432"
DB_NAME="ksocial_db"
DB_USER="postgres"
DB_PASS="MySecurePassword123"
BCK_SQL_FILE="/home/admin/backups/k-db.2026-01-17_18-16-57.sql"
LOG_FILE="/var/log/ksocial_restore.log"

⚠️ Security Warning: This script contains your database password in plain text. Ensure the file has restricted permissions (chmod 700) and is stored securely. Consider deleting or securing the script after use.

💾 Step 3: Make the Script Executable

Save the file and make it executable:

chmod 700 restore_db.sh

▶️ Step 4: Run the Restoration Script

Execute the script to begin the restoration process:

./restore_db.sh

📊 Step 5: Monitor the Process

The script will display progress messages in real-time and also log them to the specified log file. You can monitor the log in real-time using:

tail -f /YOUR/LOG/FOLDER/log.txt

✅ What the Script Does:

Validation: Checks if the backup file exists before proceeding
Secure Authentication: Creates a temporary password file for secure PostgreSQL authentication
Database Cleanup: Drops the existing database (if it exists) to ensure a clean restoration
Database Creation: Creates a new empty database with the specified name
Data Import: Imports all data, schemas, and structures from the backup file
Logging: Records all operations with timestamps for troubleshooting
Cleanup: Automatically removes the temporary password file on completion

⚠️ Important: This script will completely DROP and recreate the database. Ensure you have a backup of any existing data before running it!

💡 Tip: After successful restoration, verify the database integrity by connecting to it and checking your tables and data.

K-SOCIAL OFFICIAL DATABASE

📦 Database Backups