Create aurora postgresql cluster for query/execution plans auto generated. fine tune log_min_duration and sample_rate otherwise it may fill up the logs too quick related to load.

PrepareAuroraPostgreSql.ps1

# This script creates an Aurora PostgreSQL DB cluster and instance, applies a custom parameter group to enable auto explain for all queries/execution plans, and creates a database and user.
# It uses the AWS Tools for PowerShell module and the `aws` CLI to interact with Amazon RDS, and the `psql` command-line tool to execute SQL commands.
# Assumes that `psql` is installed and in the system PATH, and that AWS credentials are set as environment variables (from https://********.awsapps.com/start/#/?tab=accounts).
# Use the following command to run the script
# Use configured -SubnetGroupName which should allow Base monitor to connect to the Aurora PostgreSQL cluster
# .\PrepareAuroraPostgreSql.ps1 -SubnetGroupName XXXXXXX

param (
    [Parameter(Mandatory=$true, HelpMessage="Enter the name of the subnet group.")]
    [ValidateNotNullOrEmpty()]
    [string]$SubnetGroupName
)

$psqlPath = "C:\tools\postgresql-16.6-2\pgsql\bin\psql.exe"  # Adjust the path to your psql.exe

if (-not (Test-Path $psqlPath)) {
    Write-Output "Error: psql.exe not found at path $psqlPath."
    exit
}

function Get-Timestamp {
    return (Get-Date).ToString("yyyy-MM-dd HH:mm:ss")
}


# Check if AWS Tools for PowerShell is installed
if (-not (Get-Module -ListAvailable -Name AWSPowerShell.NetCore)) {
    Install-Module -Name AWSPowerShell.NetCore -Force -Scope CurrentUser
}

# Import the AWS module
Import-Module AWSPowerShell.NetCore

# Set AWS credentials and region
$awsAccessKey = $Env:AWS_ACCESS_KEY_ID
$awsSecretKey = $Env:AWS_SECRET_ACCESS_KEY
$awsSessionToken = $Env:AWS_SESSION_TOKEN
$region = "eu-west-2"

Initialize-AWSDefaultConfiguration -AccessKey $awsAccessKey -SecretKey $awsSecretKey -SessionToken $awsSessionToken -Region $region

# Create Aurora PostgreSQL DB cluster
$clusterIdentifier = "testdelete-cluster"
$dbInstanceIdentifier = "ozkantest-instance"
$dbName = "testdelete_db"
$masterUsername = "ozkantest_user"
$masterUserPassword = "ozkantest_password"
# $subnetGroupName="default-vpc-5d7d8334"

#Remove-RDSDBInstance -DBInstanceIdentifier $dbInstanceIdentifier -SkipFinalSnapshot $true -Force
#Remove-RDSDBCluster -DBClusterIdentifier $clusterIdentifier -SkipFinalSnapshot $true -Force
#Start-Sleep -Seconds 300

New-RDSDBCluster -DBClusterIdentifier $clusterIdentifier -Engine "aurora-postgresql" -MasterUsername $masterUsername -MasterUserPassword $masterUserPassword -DatabaseName $dbName  -DBSubnetGroupName $subnetGroupName

# Wait for the cluster to be available
$clusterStatus = ""
while ($clusterStatus -ne "available") {
    Start-Sleep -Seconds 30  # Adjust sleep time as needed
    $clusterStatus = (Get-RDSDBCluster -DBClusterIdentifier $clusterIdentifier).Status
    Write-Output "$(Get-Timestamp) - Current cluster status: $clusterStatus"
}

# Create a DB instance in the cluster
New-RDSDBInstance -DBInstanceIdentifier $dbInstanceIdentifier -DBClusterIdentifier $clusterIdentifier -Engine "aurora-postgresql" -DBInstanceClass "db.t3.medium" -PubliclyAccessible $true -DBSubnetGroupName $subnetGroupName

# Wait for the instance to be available
$dbInstanceStatus = ""
while ($dbInstanceStatus -ne "available") {
    Start-Sleep -Seconds 30  # Adjust sleep time as needed
    $dbInstanceStatus = (Get-RDSDBInstance -DBInstanceIdentifier $dbInstanceIdentifier).DBInstanceStatus
    Write-Output "$(Get-Timestamp) - Current DB instance status: $dbInstanceStatus"
}

# Retrieve the DB Instance Endpoint
$dbInstance = Get-RDSDBInstance -DBInstanceIdentifier $dbInstanceIdentifier
$endpoint = $dbInstance.Endpoint.Address

Write-Output "DB Endpoint: $endpoint"

# Define the SQL commands without ALTER SYSTEM
$sqlCommands = @"
CREATE USER redgatemonitor WITH PASSWORD '$masterUserPassword';
GRANT pg_monitor TO redgatemonitor;
GRANT ALL PRIVILEGES ON DATABASE redgatemonitor TO redgatemonitor;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS log_fdw;
CREATE SERVER sqlmonitor_file_server FOREIGN DATA WRAPPER log_fdw;
GRANT EXECUTE ON FUNCTION list_postgres_log_files() TO redgatemonitor;
GRANT EXECUTE ON FUNCTION create_foreign_table_for_log_file(text, text, text) TO redgatemonitor;
GRANT USAGE ON FOREIGN SERVER sqlmonitor_file_server TO redgatemonitor;

"@

# Set the password environment variable for psql
$env:PGPASSWORD = $masterUserPassword

# Define the SQL command for creating the database
$sqlCreateDatabase = "CREATE DATABASE redgatemonitor;"

# Execute the SQL command using psql
$psqlCommandCreateDatabase = "$psqlPath -h $endpoint -U $masterUsername -d $dbName -c `"$sqlCreateDatabase`""
Write-Output $psqlCommandCreateDatabase
Invoke-Expression $psqlCommandCreateDatabase

# Execute the remaining SQL commands
$psqlCommand = "$psqlPath -h $endpoint -U $masterUsername -d redgatemonitor -c `"$sqlCommands`""
Write-Output $psqlCommand
Invoke-Expression $psqlCommand
Write-Output "SQL commands executed."

$customParameterGroupName = "auto-explain-everything-aurora-postgresql15"
$parameterGroupExists = aws rds describe-db-cluster-parameter-groups --db-cluster-parameter-group-name $customParameterGroupName

if (-not $parameterGroupExists) {
    aws rds create-db-cluster-parameter-group `
        --db-cluster-parameter-group-name $customParameterGroupName `
        --db-parameter-group-family aurora-postgresql15 `
        --description "Enable auto explain for everything query/execution plans group for Aurora PostgreSQL"
}

# Modify the parameters in the custom parameter group in multiple requests
$parameters1 = @(
    "ParameterName=shared_preload_libraries,ParameterValue='pg_stat_statements,auto_explain',ApplyMethod=pending-reboot"
    "ParameterName=log_destination,ParameterValue='csvlog',ApplyMethod=pending-reboot"
    "ParameterName=auto_explain.log_format,ParameterValue=json,ApplyMethod=immediate"
)

$parameters2 = @(
    "ParameterName=auto_explain.log_min_duration,ParameterValue=0,ApplyMethod=immediate"
    "ParameterName=auto_explain.sample_rate,ParameterValue=1.0,ApplyMethod=immediate"
    "ParameterName=auto_explain.log_verbose,ParameterValue=true,ApplyMethod=immediate"
    "ParameterName=auto_explain.log_nested_statements,ParameterValue=true,ApplyMethod=immediate"
    "ParameterName=auto_explain.log_analyze,ParameterValue=true,ApplyMethod=immediate"
    "ParameterName=auto_explain.log_buffers,ParameterValue=true,ApplyMethod=immediate"
    "ParameterName=auto_explain.log_timing,ParameterValue=true,ApplyMethod=immediate"
    "ParameterName=auto_explain.log_triggers,ParameterValue=true,ApplyMethod=immediate"
    "ParameterName=track_io_timing,ParameterValue=true,ApplyMethod=immediate"
    "ParameterName=pg_stat_statements.track,ParameterValue=top,ApplyMethod=immediate"
)

aws rds modify-db-cluster-parameter-group `
    --db-cluster-parameter-group-name $customParameterGroupName `
    --parameters $parameters1

aws rds modify-db-cluster-parameter-group `
    --db-cluster-parameter-group-name $customParameterGroupName `
    --parameters $parameters2

# Apply the custom parameter group to the DB cluster
aws rds modify-db-cluster `
    --db-cluster-identifier $clusterIdentifier `
    --db-cluster-parameter-group-name $customParameterGroupName `
    --apply-immediately

# Reboot the DB instance to apply pending-reboot parameters
aws rds reboot-db-instance --db-instance-identifier $dbInstanceIdentifier

# Retrieve the DB Instance Endpoint
$dbInstance = Get-RDSDBInstance -DBInstanceIdentifier $dbInstanceIdentifier
$endpoint = $dbInstance.Endpoint.Address

# Check if the endpoint is retrieved correctly
if (-not $endpoint) {
    Write-Output "Error: DB instance endpoint not found."
    exit
}

Write-Output "$(Get-Timestamp) - Custom parameter group applied successfully."