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)
Database synced with last 3 days of messages
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

⚡ Real-Time Database Backups

📦 Daily Database Backups