Tag: database

Find (and Safely Drop) Unused Indexes in PostgreSQL

A quick query to find PostgreSQL indexes nobody uses, the four checks to run before dropping one, and how to drop it without blocking writes.

Indexes speed up reads, but they aren’t free. Every INSERT, UPDATE and DELETE has to maintain them, they take up disk, and they compete for memory in shared buffers. On a database that’s been around a few years, it’s common to find indexes that nobody has used in months. Here’s how to find them, and what to check before you drop one.

The query

PostgreSQL tracks how often each index is scanned in pg_stat_user_indexes. This query lists indexes with zero scans. It skips primary keys, unique indexes, and indexes that back a constraint, because those enforce rules even if no query reads them.

SELECT s.schemaname,
       s.relname        AS table_name,
       s.indexrelname   AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT EXISTS (
        SELECT 1 FROM pg_constraint c
        WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

The biggest unused indexes come first, so the top of the list is where the payoff is.

Before you drop anything, check these four things

  1. When were stats last reset? A zero only means something if the counters have been collecting for a while. Run SELECT stats_reset FROM pg_stat_database WHERE datname = current_database(); and aim for at least a few weeks of history, ideally covering a month-end.
  2. Do you have read replicas? Scan counts are tracked per server. An index used only by reporting queries on a replica will show zero on the primary. Run the query on every replica too.
  3. Are there rare jobs? Quarterly reports and yearly archive jobs may depend on an index that looks idle most of the time.
  4. Is the index new? A recently created index will naturally show low counts.

On PostgreSQL 16 and later, pg_stat_user_indexes also has a last_idx_scan column, which tells you when the index was last used, not just how many times.

Dropping safely

First save the definition, so you can recreate the index if something slows down:

SELECT pg_get_indexdef('schema_name.index_name'::regclass);

Then drop it without blocking writes to the table:

DROP INDEX CONCURRENTLY schema_name.index_name;

CONCURRENTLY can’t run inside a transaction block. If it fails partway through, check for a leftover invalid index and drop it again.

Wrap-up

Run the query once a quarter, verify across all replicas, and keep the saved definitions somewhere handy. Removing dead indexes is one of the cheapest write-performance wins you’ll find.

Azure PostgreSQL Flexible Server: Inactive Replication Slots Eating Your Storage (And How to Fix It)

Inactive replication slots in Azure Database for PostgreSQL Flexible Server can silently fill your disk with WAL files. Here’s how to spot, drop, and prevent them.

The Problem: WAL Explosion from Orphaned Slots

Replication slots ensure WAL retention so consumers (CDC tools, read replicas) don’t miss changes. Inactive slots—created by stopped CDC jobs, deleted replicas, or failed experiments—pin old WAL indefinitely, consuming storage until it fills. Azure Flexible Server has safeguards like auto-grow, but slots can still cause outages.

Spot the Culprits

Run these to identify storage hogs:

-- WAL retained by each slot (biggest first)
SELECT slot_name, plugin, slot_type, active, 
       pg_size_pretty(pg_wal_lsn_diff(restart_lsn, '0/0')) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(restart_lsn, '0/0') DESC;

-- Current lag relative to WAL head
SELECT slot_name, 
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_size
FROM pg_replication_slots;

-- Inactive slots only
SELECT * FROM pg_replication_slots WHERE NOT active;

-- Check active physical replication (HA/replicas)
SELECT pid, state, sent_lsn, replay_lsn, write_lag 
FROM pg_stat_replication;

Focus on inactive logical slots (slot_type='logical', active=false).

Clean Them Up Safely

Drop one by one—never active or Azure HA slots like azure_standby:

SELECT pg_drop_replication_slot('your_inactive_slot_name');

Storage recovers as WAL checkpoints recycle old segments (minutes to hours). Verify with Azure Metrics > Disk Used.

Prevention: Best Practices

PracticeActionWhy
Monitor slotsAlert on inactive slots >24h or WAL >20% diskCatches issues early ​
Limit WAL retentionSet max_slot_wal_keep_size = '20GB' (PG13+)Auto-invalidates lagging slots 
Config for CDC/replicaswal_level=logical, max_replication_slots >= replicas + CDC + 4 (HA)Reserves space 
Cleanup workflowDrop slot before stopping CDC job/replicaNo orphans 
Azure limitsCheck portal Server Parameters; monitor replicasHA needs ~4 slots 

Real-World Traps

  • CDC tools (Debezium, DMS, Fivetran) create per-task slots; drop on job stop.
  • Deleted read replicas leave slots; check Azure portal first.
  • HA failover recreates azure_standby; ignore it.​

Automating SQL Server User Removal with PowerShell and dbatools

Introduction

When an employee leaves or a service account is retired, it’s essential to remove their access cleanly and consistently from SQL Server.
Manually revoking access across multiple databases can be error-prone and time-consuming — especially in large environments.

In this post, we’ll look at how to use the dbatools PowerShell module to automatically remove a user from all databases (except system ones) and drop the server-level login, with full logging for audit purposes.

Prerequisites

  • Install dbatools (if not already installed): Install-Module dbatools -Scope CurrentUser -Force
  • Ensure you have sysadmin rights on the SQL instance.
  • Have the login name ready (domain or SQL account).

The PowerShell Script

<#
.SYNOPSIS
Removes a SQL Server login and its users from all user databases.
Works for both domain and SQL logins, with logging.
#>

param(
    [Parameter(Mandatory = $true)]
    [string]$SqlInstance,
    [Parameter(Mandatory = $true)]
    [string]$Login,
    [string]$LogFile = "$(Join-Path $PSScriptRoot ("UserRemovalLog_{0:yyyyMMdd_HHmmss}.txt" -f (Get-Date)))"
)

if (-not (Get-Module -ListAvailable -Name dbatools)) {
    Write-Error "Please install dbatools using: Install-Module dbatools -Scope CurrentUser -Force"
    exit 1
}

function Write-Log {
    param([string]$Message, [string]$Color = "White")
    $timestamp = (Get-Date).ToString("yyyy-MM-dd HH:mm:ss")
    $logEntry = "[$timestamp] $Message"
    Write-Host $logEntry -ForegroundColor $Color
    Add-Content -Path $LogFile -Value $logEntry
}

Write-Log "=== Starting cleanup for login: $Login on instance: $SqlInstance ===" "Cyan"

$UserDatabases = Get-DbaDatabase -SqlInstance $SqlInstance | Where-Object { -not $_.IsSystemObject }

foreach ($db in $UserDatabases) {
    try {
        $dbName = $db.Name
        $user = Get-DbaDbUser -SqlInstance $SqlInstance -Database $dbName -User $Login -ErrorAction SilentlyContinue
        if ($user) {
            Write-Log "Removing user [$Login] from [$dbName]" "Red"
            Remove-DbaDbUser -SqlInstance $SqlInstance -Database $dbName -User $Login -Confirm:$false -ErrorAction Stop
            Write-Log "✅ Removed from [$dbName]" "Green"
        }
        else {
            Write-Log "User [$Login] not found in [$dbName]" "DarkGray"
        }
    }
    catch {
        Write-Log "⚠️ Failed in [$dbName]: $_" "Yellow"
    }
}

try {
    $loginObj = Get-DbaLogin -SqlInstance $SqlInstance -Login $Login -ErrorAction SilentlyContinue
    if ($loginObj) {
        $loginType = $loginObj.LoginType
        Write-Log "Removing server-level login [$Login] ($loginType)" "Red"
        Remove-DbaLogin -SqlInstance $SqlInstance -Login $Login -Confirm:$false -ErrorAction Stop
        Write-Log "✅ Server-level login removed" "Green"
    }
    else {
        Write-Log "No server-level login [$Login] found" "DarkGray"
    }
}
catch {
    Write-Log "⚠️ Failed to remove login [$Login]: $_" "Yellow"
}

Write-Log "=== Completed cleanup for [$Login] on [$SqlInstance] ===" "Cyan"
Write-Log "Log file saved to: $LogFile" "Gray"

How It Works

  • Get-DbaDatabase lists all user databases.
  • Get-DbaDbUser / Remove-DbaDbUser checks for and removes the user from each DB.
  • Get-DbaLogin / Remove-DbaLogin cleans up the login from the instance.
  • All actions are written to a timestamped .txt log for compliance or auditing.

Example Usage

.\Remove-DbUserFromAllDatabases.ps1 -SqlInstance "SQLPROD01" -Login "Contoso\User123"

You can also specify a custom log path:

.\Remove-DbUserFromAllDatabases.ps1 -SqlInstance "SQLPROD01" -Login "appuser" -LogFile "C:\Logs\UserCleanup.txt"

Key Takeaways

  • Fully automated and non-interactive — perfect for offboarding workflows.
  • Handles both Windows and SQL logins gracefully.
  • Creates a detailed audit log for every action taken.
  • Safe to re-run — it skips users or logins that don’t exist.

Automate SQL Server Database Role Assignment with PowerShell and dbatools

Introduction

As a SQL Server DBA, one of the most repetitive administrative tasks is granting user access to multiple databases — especially in environments with dozens or even hundreds of databases.
Instead of manually connecting to each database and assigning roles, you can automate the process with the dbatools PowerShell module.

In this post, we’ll walk through how to automatically grant a user db_datareader and db_datawriter roles across all user databases, while excluding system databases.

Prerequisites

Before running the script:

  • Install the dbatools PowerShell module: Install-Module dbatools -Scope CurrentUser -Force
  • Ensure your account has sufficient permissions (sysadmin or equivalent).
  • Know the SQL instance name and the login you want to grant permissions to.

The PowerShell Script

# Requires dbatools
# Install-Module dbatools -Scope CurrentUser -Force

$SqlInstance = "MyServer\MyInstance"  # Replace with your SQL Server instance
$Login = "MyDomain\MyUser"            # Replace with your Windows or SQL login

# Get all user databases (excluding system DBs)
$UserDatabases = Get-DbaDatabase -SqlInstance $SqlInstance | Where-Object { -not $_.IsSystemObject }

foreach ($db in $UserDatabases) {
    Write-Host "Processing database: $($db.Name)" -ForegroundColor Cyan

    try {
        # Create the user if not already present
        $user = Get-DbaDbUser -SqlInstance $SqlInstance -Database $db.Name -User $Login -ErrorAction SilentlyContinue
        if (-not $user) {
            New-DbaDbUser -SqlInstance $SqlInstance -Database $db.Name -Login $Login -Username $Login -Confirm:$false | Out-Null
        }

        # Grant roles
        Add-DbaDbRoleMember -SqlInstance $SqlInstance -Database $db.Name -Role db_datareader -User $Login -Confirm:$false -ErrorAction Stop
        Add-DbaDbRoleMember -SqlInstance $SqlInstance -Database $db.Name -Role db_datawriter -User $Login -Confirm:$false -ErrorAction Stop

        Write-Host "✅ Granted db_datareader and db_datawriter in $($db.Name)" -ForegroundColor Green
    }
    catch {
        Write-Warning "Failed to process $($db.Name): $_"
    }
}

Write-Host "Completed assigning roles for $Login on all user databases." -ForegroundColor Green

Explanation

  • Get-DbaDatabase retrieves all databases and filters out system ones.
  • New-DbaDbUser ensures the login exists as a user in each DB.
  • Add-DbaDbRoleMember grants the necessary roles.
  • The script is non-interactive (-Confirm:$false), making it perfect for automation or CI/CD pipelines.

Example Usage

.\Grant-DbRoles.ps1 -SqlInstance "SQL01" -Login "Contoso\User123"

Key Takeaways

  • Save hours by automating repetitive access management tasks.
  • dbatools provides robust error handling and clean PowerShell syntax.
  • Works seamlessly with both Windows and SQL logins.
  • Ideal for onboarding new users or service accounts.

How to Extract and Replicate PostgreSQL Permissions When Migrating to a New Instance

Migrating a PostgreSQL database isn’t just about moving data—getting the right roles and permissions in place is critical for security and proper application function. This post demonstrates how to extract roles and permissions from your source instance and apply them to a new PostgreSQL environment.

Understanding Permissions in PostgreSQL

PostgreSQL controls access via roles. A role can represent either a database user or a group, and roles are granted permissions (privileges) on objects (tables, databases, schemas, etc.) using GRANT and REVOKE commands. These permissions can be viewed and managed at various levels:

  • Database: Control who can connect
  • Schema: Control access to groups of tables, functions, etc.
  • Objects: Control what actions (SELECT, INSERT, UPDATE, etc.) users can perform on tables, functions, or sequences.

You can view permissions using PostgreSQL’s psql meta-commands:

  • \l+ — Show database privileges
  • \dn+ — Show schema privileges
  • \dp+ — Show table and other object privileges

Step 1: Extract Roles from the Source Instance

To extract roles (users and groups) from your current PostgreSQL server:

pg_dumpall --roles-only > roles.sql

Note:

  • This command will export all roles (but not their passwords in managed services like Azure Database for PostgreSQL).
  • In cloud managed systems, you might not have the ability to extract passwords; you’ll need to set them manually on the target instance.

Step 2: Extract Role & Object Permissions

Extracting object-level permissions (like all GRANT and REVOKE statements) can be done while dumping database schema:

pg_dump -h <source_server> -U <username> -d <dbname> -s > db_schema.sql

Next, filter permission statements:

If you’re working in PowerShell (Windows), run:

pg_dump -h "psql-prod-01.postgres.database.azure.com" -U pgadmin -d "prod_db" -s | Select-String -Pattern "^(GRANT|REVOKE|ALTER DEFAULT PRIVILEGES)" -Path "C:\Path\to\db_schema.sql" | ForEach-Object { $_.Line } > C:\Path\to\permissions.sql

If you’re working on Mac/Linux, run:

pg_dump -h "psql-prod-01.postgres.database.azure.com" -U pgadmin -d "prod_db" -s | grep -E '^(GRANT|REVOKE|ALTER DEFAULT PRIVILEGES)' > perms.sql

This extracts all lines related to granting or revoking privileges and puts them in perms.sql.

Step 3: Prepare and Edit Scripts

  • Review the extracted roles.sql and `permissions.sql:
    • Remove any references to unsupported roles (like postgres superuser in cloud environments).
    • Plan to set user passwords manually if they weren’t included.

Step 4: Copy Roles and Permissions to the New Instance

  1. Recreate roles:textpsql -h <target_server> -U <admin_user> -f roles.sql
    • Remember to set or update passwords for each user after creation.
  2. Apply object-level permissions:textpsql -h <target_server> -U <admin_user> -d <target_db> -f perms.sql

Step 5: Validate Permissions

Connect as each role or user to ensure operations work as expected:

  • Use \dp tablename in psql to check table permissions.
  • Use the information_schema views (e.g., role_table_grants) to query permissions programmatically:SELECT grantee, privilege_type, table_name FROM information_schema.role_table_grants;