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.

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 recreate pgBackRest stanza

Usage Examples

Scenario 1: Accidental Data Deletion Recovery

# 1. View 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 to primary if satisfied
pig pg promote

Scenario 2: Recover to Latest State

# Recover to latest data after server failure
pig pitr -d

Scenario 3: Fast Recovery to Backup Point

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

Scenario 4: Automation Scripts

# Skip all confirmations, suitable for automation
pig pitr -d -y

Scenario 5: Standalone PostgreSQL Instance

# Non-Patroni managed instance
pig pitr -d --skip-patroni

Scenario 6: Recover Without Starting

# Recover then manually inspect before deciding to start
pig pitr -d --no-restart

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

# Manual start
pig pg start

Execution Plan Example

Running pig pitr -d --dry-run shows a 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 Operations

After successful recovery, detailed follow-up instructions are displayed:

══════════════════════════════════════════════════════════════════
 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 used, a 5-second countdown is displayed 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. First try graceful stop (ensures data consistency)
2. Then try immediate stop on failure
3. Finally use kill -9 (only in extreme cases)

Recovery Verification

Automatically verify PostgreSQL started successfully after recovery, prompting to check logs on failure.

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 automated coordination than individual commands
• Suitable for production environment complete PITR workflow

Error Handling:

• Detailed error messages at each phase
• Prompts relevant log locations on failure
• Supports manual continuation after interruption

Permission Execution:

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

Platform Support:

This command is designed for Linux systems, depends on Pigsty’s default directory structure.