A PostgreSQL cascading streaming replication setup with failover in a PowerShell script

CascadingStreamingReplication.ps1

# Warning! Only start this in an empty directory as it creates and deletes files and directories

# This is tested on Windows and Linux.
# The PostgreSQL bin directory has to be in your PATH and you need a modern PowerShell (> 6.0)

$port = $mainServerPort = 5433
$mainServerId = 1
$mainServerName = "cluster " + $mainServerId
$mainServerPath = "cluster" + $mainServerId.ToString("000")
$previousPort = -1
$numberOfClusters = 3

for ($i = 0; $i -lt $numberOfClusters; $i++)
{
    $clusterId = $i + $mainServerId
    $clusterName = "cluster " + $clusterId
    $clusterPath = "cluster" + $clusterId.ToString("000")
    $nextClusterPath = "cluster" + ($clusterId + 1).ToString("000")
    $slotName = $nextClusterPath + "_slot"

    Write-Host "Initializing $clusterName..."

    if ($clusterId -eq 1) # The main cluster
    {
        Write-Host "Preparing $clusterName as main cluster..."
        pg_ctl initdb -D $clusterPath -o '-A trust' -s

        Write-Host "Preparing postgresql.conf..."
        (Get-Content -Path $clusterPath/postgresql.conf -Encoding utf8NoBOM) `
            -replace "#?cluster_name = '[^']*'","cluster_name = '$clusterName'" `
            -replace "#?update_process_title = off","update_process_title = on" `
            -replace "#?wal_level = [a-z]+", "wal_level = logical" `
            -replace "#?unix_socket_directories = '[^']*'", "unix_socket_directories = ''" `
            -replace "#?port = [0-9]+","port = $port" | `
            Set-Content -Path $clusterPath/postgresql.conf -Encoding utf8NoBOM

        Write-Host "Starting $clusterName..."
        pg_ctl -s -D $clusterPath -l "`"$clusterName.log`"" -s start

        Write-Host "Creating replication slot $slotName..."
        psql -h localhost -p $port -d postgres -Atq -c "SELECT * FROM pg_create_physical_replication_slot('$slotName');" > $null
    }
    else # the replicas
    {
        Write-Host "Preparing $clusterName as replica..."
        pg_basebackup -D $clusterPath -p $previousPort -h localhost

        Write-Host "Preparing postgresql.conf..."
        (Get-Content -Path $clusterPath/postgresql.conf -Encoding utf8NoBOM) `
            -replace "#?cluster_name = '[^']*'","cluster_name = '$clusterName'" `
            -replace "#?primary_conninfo = '[^']*'", "primary_conninfo = 'host=localhost port=$previousPort'" `
            -replace "#?primary_slot_name = '[^']*'", "primary_slot_name = '${ClusterPath}_slot'" `
            -replace "#?port = [0-9]+","port = $port" | `
            Set-Content -Path $clusterPath/postgresql.conf -Encoding utf8NoBOM

        Write-Host "Creating a standby.signal file in the backup directory..."
        New-Item -Path "./$clusterPath" -Name "standby.signal" -ItemType "file" -Force > $null
            
        Write-Host "Starting $clusterName..."
        pg_ctl -s -D $clusterPath -l "`"$clusterName.log`"" start

        if ($clusterId -lt $numberOfClusters)
        {
            Write-Host "Creating replication slot $slotName..."
            psql -h localhost -p $port -d postgres -Atq -c "SELECT * FROM pg_create_physical_replication_slot('$slotName');" > $null
        }
    }

    $previousPort = $port
    $port++
}

$insertValue = "Test 1"
Write-Host "Inserting data into $mainServerName..."
psql -h localhost -p $mainServerPort -d postgres -c "CREATE TABLE test (value text, point_in_time timestamp with time zone DEFAULT CURRENT_TIMESTAMP)" -q
psql -h localhost -p $mainServerPort -d postgres -c "INSERT INTO test (value) VALUES('$insertValue')" -q

$port = $mainServerPort 
for ($i = 0; $i -lt $numberOfClusters; $i++)
{
    $clusterId = $i + 1
    $clusterName = "cluster " + $clusterId

    Write-Host "Retrieving data from $clusterName... " -NoNewline
    $selectValue = psql -h localhost -p $port -d postgres -Atq -c "SELECT value FROM test ORDER BY point_in_time DESC LIMIT 1"

    If ($selectValue -ne $insertValue)
    {
        Write-Error "ERROR: We expected `"$insertValue`" but found `"$selectValue`" on $clusterName!"
        exit 1
    }
    else
    {
        Write-Host "success!"
    }

    $port++
}

# Now let's kill the main server and fail over to the immediate replica

Write-Host "Stopping $mainServerName..."
pg_ctl -D $mainServerPath -m immediate -s stop

$mainServerName = "cluster " + ++$mainServerId
$numberOfClusters--
$mainServerPath = "cluster" + $mainServerId.ToString("000")
$port = ++$mainServerPort

Write-Host "Promoting $mainServerName as new main server..."
pg_ctl promote -D $mainServerPath -s

$insertValue = "Test 2"
Write-Host "Inserting data into $mainServerName..."
psql -h localhost -p $mainServerPort -d postgres -c "INSERT INTO test (value) VALUES('$insertValue')" -q

for ($i = 0; $i -lt $numberOfClusters; $i++)
{
    $clusterId = $i + $mainServerId
    $clusterName = "cluster " + $clusterId
    $clusterPath = "cluster" + $clusterId.ToString("000")

    Write-Host "Retrieving data from $clusterName... " -NoNewline
    $selectValue = psql -h localhost -p $port -d postgres -Atq -c "SELECT value FROM test ORDER BY point_in_time DESC LIMIT 1"

    If ($selectValue -ne $insertValue)
    {
        Write-Error "We expected `"$insertValue`" but found `"$selectValue`" on $clusterName!"
        exit 1
    }
    else
    {
        Write-Host "success!"
    }

    $port++
}

for ($i = $numberOfClusters; $i -gt 0; $i--)
{
    $clusterId = $i + ($mainServerId - 1)
    $clusterName = "cluster " + $clusterId
    $clusterPath = "cluster" + $clusterId.ToString("000")

    Write-Host "Stopping $clusterName..."
    pg_ctl -D $clusterPath -m smart -s stop
}

Remove-Item cluster* -Recurse -Force > $null