pig pitr

The pig pitr command performs Orchestrated Point-In-Time Recovery. Unlike pig pb restore, this command automatically coordinates Patroni, PostgreSQL, and pgBackRest to complete the full PITR workflow.

pig pitr - Perform PITR with automatic Patroni/PostgreSQL lifecycle management.

This command orchestrates a complete PITR workflow:
  1. Stop Patroni service (if running)
  2. Ensure PostgreSQL is stopped (with retry and fallback)
  3. Execute pgbackrest restore
  4. Start PostgreSQL
  5. Provide post-restore guidance

Recovery Targets (at least one required):
  --default, -d      Recover to end of WAL stream (latest)
  --immediate, -I    Recover to backup consistency point
  --time, -t         Recover to specific timestamp
  --name, -n         Recover to named restore point
  --lsn, -l          Recover to specific LSN
  --xid, -x          Recover to specific transaction ID

Time Format:
  - Full: "2025-01-01 12:00:00+08"
  - Date only: "2025-01-01" (defaults to 00:00:00)
  - Time only: "12:00:00" (defaults to today)

Examples:
  pig pitr -d                      # Recover to latest (most common)
  pig pitr -t "2025-01-01 12:00"   # Recover to specific time
  pig pitr -I                      # Recover to backup consistency point
  pig pitr -d --dry-run            # Show execution plan without running
  pig pitr -d -y                   # Skip confirmation (for automation)
  pig pitr -d --skip-patroni       # Skip Patroni management
  pig pitr -d --no-restart         # Don't auto-start PostgreSQL after restore

Overview

pig pitr is a highly automated recovery command that:

1. Automatically stops Patroni service (if running)
2. Ensures PostgreSQL is stopped (with retry and fallback strategies)
3. Executes pgBackRest restore
4. Starts PostgreSQL
5. Provides post-recovery guidance

Comparison with pig pb restore:

Quick Start

# Most common: recover to latest data
pig pitr -d

# Recover to specific point in time
pig pitr -t "2025-01-01 12:00:00+08"

# Recover to backup consistency point (fastest)
pig pitr -I

# View execution plan (dry-run)
pig pitr -d --dry-run

# Skip confirmation (for automation)
pig pitr -d -y

# Recover from specific backup set
pig pitr -d -b 20251225-120000F

# Standalone PostgreSQL (non-Patroni managed)
pig pitr -d --skip-patroni

# Don't auto-start PostgreSQL after recovery
pig pitr -d --no-restart

Parameters

Recovery Target (choose one)

Backup Selection

Flow Control

Recovery Options

Configuration

Time Format

The --time parameter supports multiple formats with automatic timezone completion:

Execution Flow

Phase 1: Pre-check

• Validate recovery target parameters (must specify exactly one)
• Check data directory exists and is initialized
• Detect Patroni service status
• Detect PostgreSQL running status

Phase 2: Stop Patroni

If Patroni service is running and --skip-patroni not specified:

• Execute systemctl stop patroni
• Wait for PostgreSQL to auto-stop with Patroni

Phase 3: Ensure PostgreSQL Stopped

Progressive strategy to ensure PostgreSQL is fully stopped:

1. Wait for auto-stop: wait 30 seconds after Patroni stops
2. Graceful stop: use pg_ctl stop -m fast (retry 3 times with exponential backoff)
3. Immediate stop: use pg_ctl stop -m immediate
4. Force kill: use kill -9 (last resort)

Phase 4: Execute Recovery

Call pgBackRest for actual data recovery:

pgbackrest restore --target-action=promote ...

Phase 5: Start PostgreSQL

Unless --no-restart specified, auto-start PostgreSQL:

• Wait for startup completion (timeout 120 seconds)
• Verify process is actually running

Phase 6: Post-Recovery Guidance

Display detailed follow-up instructions including:

• How to verify recovered data
• How to promote to primary
• How to resume Patroni cluster management
• How to re-create pgBackRest stanza

Examples

Scenario 1: Recover from accidental delete

# 1. Check available backups
pig pb info

# 2. Recover to time before deletion
pig pitr -t "2025-01-15 09:30:00+08"

# 3. Verify data
pig pg psql
SELECT * FROM important_table;

# 4. Promote after confirmation
pig pg promote

Scenario 2: Recover to latest state

# Restore to latest data after failure
pig pitr -d

Scenario 3: Quick restore to backup point

# Recover to backup consistency point (no WAL replay)
pig pitr -I

Scenario 4: Automation script

# Skip all confirmations
pig pitr -d -y

Scenario 5: Standalone PostgreSQL

# Instance not managed by Patroni
pig pitr -d --skip-patroni

Scenario 6: Restore without restart

# Restore and inspect before start
pig pitr -d --no-restart

# Check data directory
ls -la /pg/data/

# Start manually
pig pg start

Execution Plan Example

Running pig pitr -d --dry-run shows an execution plan like:

══════════════════════════════════════════════════════════════════
 PITR Execution Plan
══════════════════════════════════════════════════════════════════

Current State:
  Data Directory:  /pg/data
  Database User:   postgres
  Patroni Service: active
  PostgreSQL:      running (PID: 12345)

Recovery Target:
  Latest (end of WAL stream)

Execution Steps:
  [1] Stop Patroni service
  [2] Ensure PostgreSQL is stopped
  [3] Execute pgBackRest restore
  [4] Start PostgreSQL
  [5] Print post-restore guidance

══════════════════════════════════════════════════════════════════

[Dry-run mode] No changes made.

Post-Recovery Actions

After a successful recovery, the command prints guidance like:

══════════════════════════════════════════════════════════════════
 PITR Complete
══════════════════════════════════════════════════════════════════

[1] Verify recovered data:
   pig pg psql

[2] If satisfied, promote to primary:
   pig pg promote

[3] To resume Patroni cluster management:
   WARNING: Ensure data is correct before starting Patroni!
   systemctl start patroni

   Or if you want this node to be the leader:
   1. Promote PostgreSQL first: pig pg promote
   2. Then start Patroni: systemctl start patroni

[4] Re-create pgBackRest stanza if needed:
   pig pb create

══════════════════════════════════════════════════════════════════

Safety Mechanisms

Confirmation Countdown

Unless --yes is specified, the command shows a 5-second countdown before execution:

WARNING: This will overwrite the current database!
Press Ctrl+C to cancel, or wait for countdown...
Starting PITR in 5 seconds...

Progressive Stop Strategy

To ensure data safety, PostgreSQL is stopped progressively:

1. Try graceful stop first (preserve consistency)
2. If failed, try immediate stop
3. Use kill -9 only as last resort

Recovery Verification

After restore, the command verifies PostgreSQL startup and prompts to check logs if it fails.

Design Notes

Relationship with other commands:

• pig pitr internally calls pig pt stop, pig pg stop, pig pg start, and pig pb restore
• Provides higher-level automation than individual commands
• Suitable for production PITR workflows

Error handling:

• Each phase has detailed error messages
• On failure, suggests relevant log locations
• Supports manual continuation after interruption

Privilege execution:

• If the current user is DBSU: execute directly
• If current user is root: run su - postgres -c "..."
• Other users: run sudo -inu postgres -- ...

Platform support:

This command is designed for Linux systems and depends on Pigsty’s default directory layout.