A simple PostgreSQL point in time recovery roundtrip in a PowerShell script

pitr.ps1

# 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