#!/usr/bin/env bash
# Point-in-Time Recovery for PostgreSQL 12+ (tested on 16).  Usage:  ./pitr-restore.sh "2026-11-08 21:02:21"
# Run as the postgres OS user. Needs: base backup from  pg_basebackup -D $BASE -Ft -z -X stream  and WAL files in $WAL.
set -euo pipefail
TARGET="${1:?usage: ./pitr-restore.sh \"YYYY-MM-DD HH:MM:SS\"}"
PGDATA="${PGDATA:?set PGDATA to your data directory}"
BASE="${BASE:-/backups/base}"
WAL="${WAL:-/backups/wal}"
OLD="$PGDATA.before-restore.$(date +%Y%m%d%H%M%S)"

echo "Rewinding database to: $TARGET"
pg_ctl -D "$PGDATA" stop -m fast || true
mv "$PGDATA" "$OLD"                                   # nothing is deleted: the current state is kept here
mkdir -m 700 "$PGDATA"
tar -xzf "$BASE/base.tar.gz" -C "$PGDATA"
tar -xzf "$BASE/pg_wal.tar.gz" -C "$PGDATA/pg_wal"
cp -n "$OLD"/pg_wal/0* "$PGDATA/pg_wal/" 2>/dev/null || true   # newest WAL that was not archived yet

cat >> "$PGDATA/postgresql.auto.conf" <<CONF
restore_command = 'cp $WAL/%f %p'
recovery_target_time = '$TARGET'
recovery_target_action = 'promote'
CONF
touch "$PGDATA/recovery.signal"

pg_ctl -D "$PGDATA" -l "$OLD.restore.log" -w -t 600 start
until [ "$(psql -XAtc 'select pg_is_in_recovery()' postgres)" = "f" ]; do sleep 1; done
echo "Database restored to $TARGET"
