wip
atscan.net
1#!/bin/bash
2
3# === Configuration ===
4CONFIG_FILE="config.yaml" # Path to your config file
5SCHEMA_NAME="public" # Replace if your schema is different
6
7# Check if config file exists
8if [ ! -f "$CONFIG_FILE" ]; then
9 echo "Error: Config file not found at '$CONFIG_FILE'"
10 exit 1
11fi
12
13# Check if yq is installed
14if ! command -v yq &> /dev/null; then
15 echo "Error: 'yq' command not found. Please install yq (Go version by Mike Farah)."
16 echo "See: https://github.com/mikefarah/yq/"
17 exit 1
18fi
19
20echo "--- Reading connection info from '$CONFIG_FILE' ---"
21
22# === Extract Database Config using yq ===
23DB_TYPE=$(yq e '.database.type' "$CONFIG_FILE")
24DB_CONN_STRING=$(yq e '.database.path' "$CONFIG_FILE") # This is likely a URI
25
26if [ -z "$DB_TYPE" ] || [ -z "$DB_CONN_STRING" ]; then
27 echo "Error: Could not read database type or path from '$CONFIG_FILE'."
28 exit 1
29fi
30
31# === Parse the Connection String ===
32DB_USER=""
33DB_PASSWORD=""
34DB_HOST="localhost" # Default
35DB_PORT="5432" # Default
36DB_NAME=""
37
38# Use regex to parse the URI (handles postgres:// or postgresql://, optional password/port, and query parameters)
39if [[ "$DB_CONN_STRING" =~ ^(postgres|postgresql)://([^:]+)(:([^@]+))?@([^:/]+)(:([0-9]+))?/([^?]+)(\?.+)?$ ]]; then
40 DB_USER="${BASH_REMATCH[2]}"
41 DB_PASSWORD="${BASH_REMATCH[4]}" # Optional group
42 DB_HOST="${BASH_REMATCH[5]}"
43 DB_PORT="${BASH_REMATCH[7]:-$DB_PORT}" # Use extracted port or default
44 DB_NAME="${BASH_REMATCH[8]}" # Database name before the '?'
45else
46 echo "Error: Could not parse database connection string URI: $DB_CONN_STRING"
47 exit 1
48fi
49
50# Set PGPASSWORD environment variable if password was found
51if [ -n "$DB_PASSWORD" ]; then
52 export PGPASSWORD="$DB_PASSWORD"
53else
54 echo "Warning: No password found in connection string. Relying on ~/.pgpass or password prompt."
55 unset PGPASSWORD
56fi
57
58echo "--- Database Size Investigation ---"
59echo "Database: $DB_NAME"
60echo "Schema: $SCHEMA_NAME"
61echo "User: $DB_USER"
62echo "Host: $DB_HOST:$DB_PORT"
63echo "-----------------------------------"
64
65# === Table Sizes ===
66echo ""
67echo "## Table Sizes (Schema: $SCHEMA_NAME) ##"
68# Removed --tuples-only and --no-align, added -P footer=off
69psql -U "$DB_USER" -d "$DB_NAME" -h "$DB_HOST" -p "$DB_PORT" -X -q -P footer=off <<EOF
70SELECT
71 c.relname AS "Table Name",
72 pg_size_pretty(pg_total_relation_size(c.oid)) AS "Total Size",
73 pg_size_pretty(pg_relation_size(c.oid)) AS "Table Heap Size",
74 pg_size_pretty(pg_indexes_size(c.oid)) AS "Indexes Size"
75FROM
76 pg_class c
77LEFT JOIN
78 pg_namespace n ON n.oid = c.relnamespace
79WHERE
80 c.relkind = 'r' -- 'r' = ordinary table
81 AND n.nspname = '$SCHEMA_NAME'
82ORDER BY
83 pg_total_relation_size(c.oid) DESC;
84EOF
85
86if [ $? -ne 0 ]; then
87 echo "Error querying table sizes. Check connection details, permissions, and password."
88 unset PGPASSWORD
89 exit 1
90fi
91
92# === Index Sizes ===
93echo ""
94echo "## Index Sizes (Schema: $SCHEMA_NAME) ##"
95# Removed --tuples-only and --no-align, added -P footer=off
96psql -U "$DB_USER" -d "$DB_NAME" -h "$DB_HOST" -p "$DB_PORT" -X -q -P footer=off <<EOF
97SELECT
98 c.relname AS "Index Name",
99 i.indrelid::regclass AS "Table Name", -- Show associated table
100 pg_size_pretty(pg_relation_size(c.oid)) AS "Index Size"
101FROM
102 pg_class c
103LEFT JOIN
104 pg_index i ON i.indexrelid = c.oid
105LEFT JOIN
106 pg_namespace n ON n.oid = c.relnamespace
107WHERE
108 c.relkind = 'i' -- 'i' = index
109 AND n.nspname = '$SCHEMA_NAME'
110ORDER BY
111 pg_relation_size(c.oid) DESC;
112EOF
113
114if [ $? -ne 0 ]; then
115 echo "Error querying index sizes. Check connection details, permissions, and password."
116 unset PGPASSWORD
117 exit 1
118fi
119
120echo ""
121echo "-----------------------------------"
122echo "Investigation complete."
123
124# Unset the password variable for security
125unset PGPASSWORD