Last active
September 21, 2020 14:24
-
-
Save Brar/3b2d6ad69590585b89f379e14141e98b to your computer and use it in GitHub Desktop.
A PostgreSQL cascading streaming replication setup with failover in a PowerShell script
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment