How to Get Database Sizes Owned by a Specific User in PostgreSQL Using a Bash Script

Managing PostgreSQL databases often involves monitoring their sizes to ensure optimal performance and resource allocation. If you want to automate the process of checking the sizes of all databases owned by a specific user, a Bash script can be a great solution. This post walks you through creating a script that retrieves and records the sizes of all databases owned by a given user.

Requirements

Before diving into the script, make sure you have:

1. A sudoer account: The script uses sudo to execute PostgreSQL commands as the postgres system user.
2. PostgreSQL installed: Ensure psql is available on your system.
3. The correct credentials: Access to the PostgreSQL database user and proper permissions.

The Bash Script

Here's the script, which works for any specified PostgreSQL user. It includes configurable options for the PostgreSQL host, port, and user.

#!/bin/bash

# Configuration
PG_HOST="localhost"         # PostgreSQL server host
PG_PORT="5432"              # PostgreSQL server port
PG_USER="postgres"          # PostgreSQL database superuser
TARGET_USER="your_username" # Replace with the target database owner
PG_DATABASE="postgres"      # Database to connect for the initial query
OUTPUT_FILE="database_sizes.txt" # File to store the database sizes

# Clear the output file
> "$OUTPUT_FILE"

# Function to check database size
check_db_size() {
    local db_name="$1"
    size=$(sudo -u postgres psql -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -t -A -d "$PG_DATABASE" -c \
        "SELECT pg_size_pretty(pg_database_size('$db_name'));")
    echo "$db_name: $size" >> "$OUTPUT_FILE"
}

# Get a list of databases owned by the target user
db_list=$(sudo -u postgres psql -h "$PG_HOST" -p "$PG_PORT" -U "$PG_USER" -t -A -d "$PG_DATABASE" -c \
    "SELECT datname FROM pg_database WHERE datdba = (SELECT oid FROM pg_roles WHERE rolname = '$TARGET_USER');")

# Iterate over the database list and check their sizes
for db in $db_list; do
check_db_size "$db"
done

echo "Database sizes recorded in $OUTPUT_FILE"

Key Features

Configurable Variables:

• PG_HOST: Specify the PostgreSQL server hostname.
• PG_PORT: Define the port for the PostgreSQL server.
• PG_USER: The PostgreSQL superuser for querying database metadata.
• TARGET_USER: The PostgreSQL user whose databases you want to analyze.
• PG_DATABASE: The database to connect to for querying metadata.
• OUTPUT_FILE: The file to store database sizes.

PostgreSQL Query Logic:

1. The script retrieves the oid of the TARGET_USER from the pg_roles system catalog.
2. It fetches the names of all databases owned by the user from the pg_database catalog.
3. For each database, it calculates the size using the pg_database_size function and records the output in OUTPUT_FILE.

How It Works

Step 1: Fetch Database List

The script uses the following SQL to get databases owned by the specified user:

SELECT datname FROM pg_database WHERE datdba = (SELECT oid FROM pg_roles WHERE rolname = '$TARGET_USER');

Step 2: Calculate Database Sizes

For each database, it runs the following query:

SELECT pg_size_pretty(pg_database_size('$db_name'));

This outputs a human-readable size (e.g., 25 MB, 1 GB).

Example Output

After running the script, the database_sizes.txt file will contain the sizes of all databases owned by the specified user:

database1: 25 MB
database2: 56 MB
database3: 1.2 GB

Running the Script

1. Save the script to a file, e.g., check_db_sizes.sh.
2. Make it executable:

   chmod +x check_db_sizes.sh
   

3. Run the script as a sudoer:

   sudo ./check_db_sizes.sh
   

Conclusion

This script simplifies monitoring database sizes owned by a specific user in PostgreSQL. By customizing the variables, you can adapt it to various setups and automate database size checks. This approach is handy for database administrators who need quick insights into storage usage.

Feel free to share your experiences or enhancements in the comments below!