Last active
September 21, 2020 12:18
-
-
Save Brar/afad4f63d5da4f37a2531c1e31c9282e to your computer and use it in GitHub Desktop.
A simple PostgreSQL point in time recovery roundtrip 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 | |
# Choose a non-default port to avoid issues with a cluster that's already running | |
$port = 5433 | |
$beforePointInTimeValue = "We want to keep this one!" | |
$afterPointInTimeValue = "We want to get rid of this one!" | |
# Initialize a new cluster | |
Write-Host "Initializing a new cluster..." | |
initdb -D pitr_cluster -A trust > $null | |
New-Item -Path . -Name "pitr_cluster_wal_archive" -ItemType "directory" > $null | |
$archivePath = Join-Path (Resolve-Path "./pitr_cluster_wal_archive") "%f" | |
If ($IsWindows) { | |
$archivePath = ($archivePath -replace "\\","\\") | |
$archive_command = "'copy `"%p`" `"$archivePath`"'" | |
$restore_command = "'copy `"$archivePath`" `"%p`"'" | |
} | |
Else { | |
$archive_command = "'test ! -f $archivePath && cp %p $archivePath'" | |
$restore_command = "'cp $archivePath %p'" | |
} | |
# Set the configuration to enable wal archiving | |
Write-Host "Enabling wal archiving in postgresql.conf..." | |
(Get-Content -Path pitr_cluster/postgresql.conf -Encoding utf8NoBOM) -replace "#port = 5432","port = $port" -replace "#archive_mode = off", "archive_mode = on" -replace "#archive_command = ''", "archive_command = $archive_command" | Set-Content -Path pitr_cluster/postgresql.conf -Encoding utf8NoBOM | |
# Start the cluster | |
Write-Host "Starting the cluster..." | |
pg_ctl -s -D pitr_cluster -l pitr_cluster.log start | |
# Create a table | |
Write-Host "Creating a table..." | |
psql -p $port -d postgres -c "CREATE TABLE test(value text, point_in_time timestamp with time zone DEFAULT CURRENT_TIMESTAMP)" > $null | |
# Create a base backup | |
Write-Host "Creating a base backup..." | |
pg_basebackup -D pitr_cluster_backup -p 5433 | |
# Insert some value we want to keep (before the point in time) | |
Write-Host "Inserting value '$beforePointInTimeValue'..." | |
psql -p $port -d postgres -c "INSERT INTO test (value) VALUES('$beforePointInTimeValue')" > $null | |
# Remember the point in time we later want to recover to | |
$pointInTime = get-date -Format "yyyy-MM-dd HH:mm:ss.ffffffK" | |
Write-Host "Now is the point in time we later want to recover to ($pointInTime)!" | |
# Insert some value we want to get rid of (after the point in time) | |
Write-Host "Inserting value '$afterPointInTimeValue'..." | |
psql -p $port -d postgres -c "INSERT INTO test (value) VALUES('$afterPointInTimeValue')" > $null | |
# Get the latest falue from the database | |
$latestValue = psql -p $port -d postgres -At -c "SELECT value FROM test ORDER BY point_in_time DESC LIMIT 1" | |
# Make sure everything is as we expect it to be | |
If ($latestValue -ne $afterPointInTimeValue) { | |
Write-Error "ERROR: We expected the latest inserted value to be `"$afterPointInTimeValue`" but found `"$latestValue`"." | |
pg_ctl -D pitr_cluster stop | |
Remove-Item pitr_cluster* -Recurse -Force | |
exit 1 | |
} | |
# Output a message so that we get some progress information | |
Write-Host "The latest inserted value before PITR was: `"$latestValue`"" | |
# Stop the cluster | |
Write-Host "Stopping the cluster..." | |
pg_ctl -D pitr_cluster stop > $null | |
# Remove the pg_wal content from the backup | |
Write-Host "Removing WAL files from the backup..." | |
Remove-Item ./pitr_cluster_backup/pg_wal/* -Recurse -Force > $null | |
# Set the backup's configuration to restore to our point in time | |
Write-Host "Preparing the backup directory for point in time recovery..." | |
(Get-Content -Path pitr_cluster_backup/postgresql.conf -Encoding utf8NoBOM) -replace "#port = 5432","port = $port" -replace "#restore_command = ''","restore_command = $restore_command" -replace "#recovery_target_time = ''","recovery_target_time = '$pointInTime'" -replace "#recovery_target_action = 'pause'","recovery_target_action = 'promote'" | Set-Content -Path pitr_cluster_backup/postgresql.conf -Encoding utf8NoBOM | |
# Create a recovery.signal file in the backup directory | |
Write-Host "Creating a recovery.signal in the backup directory..." | |
New-Item -Path "./pitr_cluster_backup" -Name "recovery.signal" -ItemType "file" > $null | |
# Start the cluster with the backup's data directory | |
Write-Host "Starting the cluster from the backup directory..." | |
pg_ctl -s -D pitr_cluster_backup -l pitr_cluster.log start | |
# Get the latest falue from the database | |
$latestValue = psql -p $port -d postgres -At -c "SELECT value FROM test ORDER BY point_in_time DESC LIMIT 1" | |
# Make sure everything is as we expect it to be | |
If ($latestValue -ne $beforePointInTimeValue) { | |
Write-Error "ERROR: We expected the latest inserted value to be `"$beforePointInTimeValue`" but found `"$latestValue`"." | |
pg_ctl -D pitr_cluster stop | |
Remove-Item pitr_cluster* -Recurse -Force | |
exit 1 | |
} | |
# Output a message so that we get some progress information | |
Write-Host "The latest inserted value after PITR was: `"$latestValue`"" | |
# Stop the cluster | |
Write-Host "Stopping the cluster..." | |
pg_ctl -D pitr_cluster_backup stop > $null | |
Write-Host "Cleaning up files and directories..." | |
Remove-Item pitr_cluster* -Recurse -Force > $null | |
exit 0 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment