scenario
  -> recovery point objective
      -> base backup lineage
          -> continuous WAL
              -> repository and key availability
                  -> target selection
                      -> isolated restore
                          -> PostgreSQL consistency
                              -> business validation
                                  -> controlled cutover

fresh full backup                 通过
named-point physical PITR         通过
base + keep / no discard          通过
isolated Unix-socket postmaster   通过
new timeline and same lineage     通过
source cluster remains healthy    通过
arbitrary-time recovery           未测试
missing WAL / lost key            未注入
immutable off-site repository     未证明
production-sized RTO              未证明
regional disaster recovery        未测试
production approval               pending

business loss scenario
  -> authoritative truth + tolerated loss
      -> RPO / RTO / granularity / retention
          -> logical vs physical vs snapshot vs replica
              -> base backup + WAL continuity + timeline
                  -> repository dependency graph
                      -> select backup and target
                          -> restore into isolation
                              -> wait through promotion
                                  -> database + business proof
                                      -> gaps and next drill

target           pg36-l2-vagrant/pg-test
Pigsty           v4.5.0
PostgreSQL       18.6
pgBackRest       2.59.0
source           pg-test-1, live primary, timeline 7
repository       S3-compatible MinIO, AES-256-CBC, one sandbox target
restore host     pg-test-3
live member      Patroni replica on 5432, unchanged
isolated copy    fresh path + private Unix socket + port 55432
archive push     off
recovery target  named point
target action    promote
target timeline  latest

base      committed before fresh full backup       must exist
keep      committed after backup, before target    must exist
target    named restore point
discard   committed after target                    must not exist

backup label                        20260729-201041F
backup command                         2.086 s
pgBackRest check                       0.598 s
logical backup bytes                   36,121,841
repository delta bytes                  4,539,288
restore copy                            2.758 s
start -> first connection               0.963 s
first connection state                  recovery=true, read_only=true
start -> promoted and writable          1.319 s
read-only -> promoted                   0.356 s
source timeline -> restored timeline    7 -> 8
system identifier relation              matches source
source replica lag after drill          0 bytes
counterexamples rejected                14

EX19-SHARED-HYPERVISOR
EX19-SINGLE-ETCD
EX19-SINGLE-BACKUP-TARGET
EX19-VIRTUAL-STORAGE
EX19-INVENTORY-SECRETS
EX19-LAB-RESOURCE-FLOOR

EX21-SHARED-RESTORE-HOST
  恢复进程、目录与网络隔离，但与 live replica 共用 guest/hypervisor；
  不能声称主机、内核、设备和故障域隔离。

EX21-REPOSITORY-NOT-IMMUTABLE
  只有本地单 MinIO；未证明 object lock、独立凭据或跨区域副本。

EX21-SMALL-SYNTHETIC-DATA
  数据量很小；不能拿时间结果做生产容量规划。

EX21-NAMED-POINT-ONLY
  只验证一个命名点，并主动切 WAL 后检查；不能代表任意时间点或最坏
  归档间隙 RPO。

capture / verify / review / all
  L0 read-only

drill:pitr
  guarded local sandbox mutation
  insert markers + full backup + fresh isolated restore + stopped retention

reset:fixture
  destructive and separate
  delete exactly one reviewed run only

backup command succeeded        != recoverable
catalog status ok               != WAL chain complete for every target
replica healthy                 != independent backup
physical restore started        != recovery complete
read-only query succeeded       != promotion complete
PostgreSQL consistent           != business truth correct
one fast sandbox restore        != production RTO
encrypted repository            != ransomware resistance
retained for 14 days            != 14 days of arbitrary PITR
restore directory retained      != service approved

declared scenario
+ selected truth boundary
+ complete lineage and WAL
+ isolated executable restore
+ engine and business proof
+ controlled service decision
+ explicit residual risk

周日全备
周一到周六增量
保留 14 天

什么会丢？
丢了什么算业务损失？
要回到哪个边界？
由谁判断边界正确？
依赖也丢失时怎么办？
多快恢复到什么能力？

logical loss
  数据库仍运行，但某些正确事实被删除、覆盖或错误变更

physical loss
  数据文件、WAL、设备或整个集群不可用/不可信

site loss
  数据库、控制面、接入层和本地备份一起不可用

historical obligation
  必须证明某个历史版本可恢复、可查询或已按政策删除

DELETE FROM orders;
UPDATE account SET balance = 0;
DROP TABLE customer;
deploy buggy migration;
application writes wrong currency or tenant id;

restore historical cluster in isolation
  -> validate target boundary
      -> export affected logical set
          -> compare with current production
              -> reviewed merge or repair

data volume lost
filesystem corruption
all HA members share failed storage
operator removes whole cluster
encryption layer/key unavailable
malware changes data and online copies

compatible PostgreSQL runtime
+ one usable base backup
+ every required WAL segment
+ timeline history
+ tablespace/path mapping
+ repository credential and encryption key
+ configuration and identity material

source region unavailable
local object store unavailable
local DNS/control plane unavailable
operator identity federation degraded
secrets/KMS endpoint unavailable
network dependency changed
capacity not pre-provisioned

operational recovery retention
  为误删、故障与日常恢复保留

historical record retention
  为审计、诉讼、监管或业务档案保留

legal hold
  正常过期规则暂时不能删除特定材料

right-to-delete / minimization
  到期后必须删除或不可再识别

id: accidental-delete-orders
trigger: confirmed destructive transaction against orders
authoritative_boundary:
  source: audit event + restore point
  timezone: UTC
scope: selected tenant and time range
maximum_tolerated_loss:
  committed_correct_orders: zero
service_target:
  historical cluster queryable: 60 minutes
  repaired rows in production: 4 hours
dependencies:
  - base backup and continuous WAL
  - audit event identity
  - isolated restore capacity
  - reviewed logical merge procedure
stop_conditions:
  - target transaction ambiguous
  - WAL gap
  - post-target correct orders cannot be reconstructed
proof:
  - restored marker boundary
  - row and amount reconciliation
  - approved repair change
owner: data-platform + orders-domain

path A: service continuity
  current production remains online

path B: historical reconstruction
  isolated PITR -> export -> compare -> repair

path A: promote designed DR replica
  lower RTO, possibly different RPO/consistency contract

path B: restore off-site backup
  slower, but independent historical recovery

t_loss     事故发生或最后可信事实时间
t_recover  实际可恢复边界

daily base backup + continuous WAL
  RPO 主要由 WAL 最后耐久位置决定，不是 24 小时

daily logical dump only
  RPO 可能接近 24 小时

streaming replica
  对主机故障可能接近复制延迟
  对误删可能是 0 秒地复制了错误，无法提供历史点

primary process loss RPO
single-AZ loss RPO
repository outage RPO
operator logical error RPO
region loss RPO

restore copy                  2.758 s
start -> first read-only      0.963 s
start -> promoted             1.319 s

cluster
  物理备份/PITR 常见粒度；包含 cluster 内所有数据库

database/schema/table
  逻辑导出或从隔离物理恢复中再导出

row/business object
  比较、审计、补偿与应用语义

service capability
  read-only、read-write、reporting、limited tenant 等

time
transaction ID
LSN
named restore point
immediate consistency
end of available WAL

10:00:00 application request sent
10:00:01 database transaction committed
10:00:02 external payment confirmed
10:00:03 audit pipeline wrote event

过去 14 天每一秒都可恢复

capture cadence
  多久产生一个新逻辑/物理基线

version count
  保留多少组 backup

calendar coverage
  最旧可恢复事实距现在多远

at 2026-08-01 12:00 UTC
  must restore any named daily checkpoint since 2026-07-18
  must restore any WAL time since 2026-07-25
  must retain month-end logical archive for 7 years

RPO
  -> WAL archive latency / dump cadence / replication mode

RTO
  -> restore bandwidth / provisioned capacity / runbook automation

granularity
  -> physical, logical, audit or application repair path

retention
  -> backup dependency graph + WAL policy + legal hold

failure domain
  -> repository placement + credential + key independence

proof
  -> drill frequency + oldest target + business invariant

all databases in cluster
catalogs and physical relation state
transaction status
extension physical objects inside cluster
system identifier lineage

earlier backup manifest
WAL summaries covering the required LSN interval
pg_combinebackup at restore preparation
all earlier dependent backups
normal WAL recovery requirements

which tool
which version
dependency chain
restore assembly step
expiration owner

primary DELETE wrong rows
  -> WAL
      -> replica replays same DELETE

delay guarantee
pause/stop authority
monitoring that does not auto-heal away the delay
protection from direct reads/writes
independent backup for other failures

HA replicas
  handle selected availability failures

physical full/diff/incr + WAL
  handle cluster restore and PITR

logical export
  handle object-level portability and long-term selected records

audit/event history
  identify business boundary and support repair

off-site immutable copy
  handle control-domain and destructive repository loss

restore drills
  prove that all of the above compose

1. loss is logical, physical, site-wide, or compliance?
2. recover whole cluster or selected facts?
3. need historical point or latest state?
4. how independent must the copy be?
5. what PostgreSQL/version compatibility exists?
6. what target precision can be observed?
7. how will business correctness be proven?
8. what is the controlled return-to-service path?

selected rows from yesterday
  isolated physical PITR -> logical export -> reviewed merge

whole cluster after storage loss
  physical backup + WAL -> replacement infrastructure

cross-major migration
  logical dump/restore or logical replication, not physical replay

low-RTO host failure
  HA promotion, while independent backup remains separate

seven-year selected archive
  policy-specific logical record, not indefinite operational WAL by default

scenario defines truth
truth defines target
target defines mechanism
mechanism defines dependencies
dependencies define retention and placement
proof defines the drill

relation A copied at 10:00:01
relation B page modified at 10:00:02
relation B copied at 10:00:03
catalog copied at 10:00:04

recoverable file image + exact WAL obligations

checkpoint redo LSN
  <= backup start LSN
      <= copied data interval
          <= backup stop LSN
              <= selected recovery target

SELECT pg_backup_start(label => 'reviewed-label', fast => false);
-- keep this session alive while the file copy runs
SELECT * FROM pg_backup_stop(wait_for_archive => true);

which backup session
start WAL file
start LSN
checkpoint location
start time

plain or tar output
server/client compression
backup manifest
WAL streaming or fetch
rate limiting
tablespace mapping
standby signal/config generation
checkpoint mode
progress reporting

pg_basebackup \
  --host=source \
  --pgdata=/safe/new/path \
  --format=plain \
  --wal-method=stream \
  --checkpoint=fast \
  --progress \
  --verbose

none
  backup output 不带 WAL；必须另有完整 archive

fetch
  在备份末尾从 source pg_wal 取所需 WAL；
  必须确保 WAL 未被回收

stream
  另开 replication connection 同步流式接收；
  需要额外 wal sender

pg_verifybackup /path/to/basebackup

checksum matches
  means bytes match expected checksum

checksum matches
  does not mean business values are semantically correct

pg_basebackup --incremental=/path/to/prior/backup_manifest ...

prior full
  -> prior/current manifests
      -> every required intermediate incremental
          -> WAL summaries at backup time
              -> pg_combinebackup
                  -> normal WAL recovery

CPU architecture and page format expectations
PostgreSQL major
extension shared libraries
collation/locale providers
tablespace layout
configuration parameters needed during recovery

recovery aborted because of insufficient parameter settings
max_connections = 20 is lower than on the primary, where it was 500

max_connections
max_worker_processes
max_wal_senders
max_prepared_transactions
max_locks_per_transaction

0/200002D0

SELECT pg_current_wal_lsn();
SELECT pg_walfile_name(pg_current_wal_lsn());
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/20000000');
SELECT pg_switch_wal();

base -> WAL A -> WAL B -> [missing C] -> WAL D -> target

wal_level >= replica
archive_mode = on
archive_command or archive_library configured

%p  source path relative to data directory
%f  WAL filename

exit 0
  PostgreSQL believes WAL is durably archived and may recycle local file

nonzero
  PostgreSQL retries

target absent
  atomically write and durably persist

target exists with identical content
  return success

target exists with different content
  return failure and alert

timeline 7: A -> B -> C -> D
                         \
timeline 8:              C' -> E -> F

system identifier
  initdb 生成的物理 cluster 血缘标识

timeline
  同一物理血缘中的历史分支

checkpoint timeline
  最近 checkpoint 记录的 timeline

current WAL timeline
  当前写入 WAL filename 所在 timeline

system identifier relation = matches source
timeline 7 -> 8

recovery.signal
  archive recovery / PITR

standby.signal
  standby mode, can continue waiting for WAL/stream

SELECT pg_create_restore_point('before_risky_change');

target transaction known
  recovery_target_xid = destructive xid
  recovery_target_inclusive = false

choose an old backup
target time belongs to a child timeline
force target_timeline=current

backup timeline
  -> history file
      -> parent fork LSN
          -> target timeline

consistent recovery state reached
database system is ready to accept read-only connections
recovery stopping at restore point
selected new timeline ID: 8
archive recovery complete
end-of-recovery checkpoint
database system is ready to accept connections

SELECT pg_is_in_recovery();          -- must be false
SHOW transaction_read_only;          -- must be off
BEGIN;
CREATE TEMP TABLE write_probe(x int);
INSERT INTO write_probe VALUES (1);
ROLLBACK;

postgresql.conf external changes
pg_hba.conf
pg_ident.conf
Patroni YAML/DCS policy
TLS certificates
Pigsty inventory
DNS/proxy config
application secrets

checkpoint/recovery requirement
archive not yet successful
physical replication slot
logical replication slot
wal_keep_size
backup in progress
standby/restartpoint needs

completed segment remains needed
  -> retry
      -> pg_wal grows
          -> filesystem full
              -> PostgreSQL PANIC/offline

SELECT archived_count,
       last_archived_wal,
       last_archived_time,
       failed_count,
       last_failed_wal,
       last_failed_time,
       stats_reset
FROM pg_stat_archiver;

failed_count = 21
last successful archive is after last failure

operational backlog
  current WAL - last archived WAL

disaster data gap
  source lost时，last business truth - last independently durable WAL

SELECT pg_wal_lsn_diff(
         pg_current_wal_lsn(),
         'last known archived LSN'
       );

important transaction commits
segment remains open
no archive object yet

SELECT slot_name,
       slot_type,
       active,
       restart_lsn,
       wal_status,
       safe_wal_size,
       inactive_since
FROM pg_replication_slots;

slot retained WAL on primary disk
  != independent disaster copy

active
restart_lsn
confirmed_flush_lsn
wal_status
safe_wal_size
xmin/catalog_xmin
owner and consumer

archive_mode=on
archive command configured
repository stanza status=ok
one S3-compatible repository
target restore-point segment <= repository maximum segment
source timeline remains 7
restore timeline becomes 8

1. stop unsafe cleanup/expiration
2. measure pg_wal free space and growth rate
3. identify exact archive error and repository availability
4. preserve source WAL
5. restore archive path/credential/capacity
6. confirm backlog drains and repository maximum advances
7. prove a restore target, not just count successes
8. write incident and prevention

delete pg_wal files
drop unknown slots
reset stanza
expire repository aggressively

SHOW wal_level;
SHOW archive_mode;
SHOW archive_command;       -- do not copy secrets into tickets/evidence
SHOW archive_timeout;
SHOW wal_keep_size;
SHOW max_slot_wal_keep_size;

SELECT * FROM pg_stat_archiver;

SELECT slot_name, slot_type, active, restart_lsn,
       confirmed_flush_lsn, wal_status, safe_wal_size
FROM pg_replication_slots;

SELECT pg_current_wal_lsn(),
       pg_walfile_name(pg_current_wal_lsn());

SELECT system_identifier
FROM pg_control_system();    -- compare securely; do not publish raw id

SELECT timeline_id, redo_lsn, checkpoint_lsn
FROM pg_control_checkpoint();

one reviewed physical lineage
+ complete base image
+ exact backup metadata
+ continuous WAL
+ reachable timeline history
+ compatible recovery runtime

full
  不依赖同仓库的更早 backup set

diff
  依赖最近 full，保存相对 full 的变化

incr
  依赖最近一次可作为父级的 backup，保存相对父级的变化

F1
├── D1
│   ├── I1
│   └── I2
├── I3
└── D2
    └── I4

F2
└── I5

logical incremental
  应用/CDC 按变化导出

file-level incremental
  只复制 mtime/size/checksum 判定变化的文件

block incremental
  只保存改变的数据块

PostgreSQL 18 native incremental
  WAL summaries + manifests + pg_combinebackup

pgBackRest incr
  pgBackRest repository dependency and restore semantics

logical database size
backup delta read
repository delta written
repository total referenced size

logical size             36,121,841 bytes
repository delta          4,539,288 bytes

weekly full
daily diff
intra-day incr

count
  保留多少 full backup set

time
  以天为阈值保留 full

full: weekly, retain by time 35 days
diff: daily, retain 7
incr: every 6 hours
WAL: follow oldest retained recovery anchor
month-end logical archive: separate 13 months

expire backup dependency graph first
derive which WAL is no longer useful
dry-run aggressive archive expiration
never delete WAL merely because it is "old"

choose oldest required target
  -> identify required backup chain
      -> retrieve cold/off-site objects
          -> obtain historical key
              -> replay full WAL span
                  -> validate business marker

latest target
oldest target
random time target
target across timeline switch
target just before destructive transaction
target whose objects are in cold tier

integrity
  bytes 未损坏/未被替换

confidentiality
  未授权者不能读取

immutability
  在保留窗口内，包括高权限主体也不能轻易删除/改写

availability
  事故时对象、密钥、网络和权限仍可用

sudo -iu postgres \
  pgbackrest --stanza=pg-test --log-level-console=info check

same tool != every command accepts same option
successful check != successful restore

client-side/repository encryption by backup tool
object storage server-side encryption
disk/volume encryption
transport TLS
application/column encryption

backup writer can put
restore reader can get
operator can delete

retention class
legal hold process
minimum/maximum lock
authorized bypass
evidence and audit
test bucket before production

physical region
cloud/account/project
identity provider
KMS/key
network/control plane
operator role
automation blast radius
object lifecycle
billing/organization dependency

3 copies
2 media/system types
1 off-site

repository
  -> stanza
      -> database/system-id lineage
          -> archive id
              -> timeline/WAL

one local MinIO
S3-compatible
AES-256-CBC repository cipher
shared laptop/hypervisor context
object lock not validated
cross-region copy not validated
independent credential domain not validated

EX21-REPOSITORY-NOT-IMMUTABLE

database growth
change rate and full-page images
compression/dedup
block incremental
bundle
index churn
vacuum/rewrite
WAL generated by bulk load/DDL
object versioning
failed/in-progress backup
multipart residue
cold tier overhead

data growth rate
  determines future full/restore size

WAL generation rate
  determines archive bandwidth, RPO exposure and replay work

SELECT now(),
       pg_current_wal_lsn(),
       pg_walfile_name(pg_current_wal_lsn());

SELECT archived_count, failed_count,
       last_archived_wal, last_archived_time,
       last_failed_wal, last_failed_time
FROM pg_stat_archiver;

source read throughput
source network egress
repository write/read throughput
archive sustained throughput
restore download throughput
WAL replay CPU/storage
shared production contention

repository used / total
growth per day/week
forecast days to full
oldest/newest backup
oldest/newest recoverable target
WAL archive backlog
pg_wal filesystem free
backup duration and bytes
restore duration by representative size
object lifecycle transitions

warning when forecast leaves enough time to provision
critical before next backup/archive can exhaust capacity

scheduler did not start
backup started but failed
backup completed but required WAL missing
repository inaccessible
archive delayed
checksum/integrity failure
retention did not expire
retention expired too much
credential/key near expiry
restore drill failed
business validation failed

age_of_latest_successful_base
age_of_last_independently_archived_wal
duration_of_archive_backlog
days_since_last_successful_restore
age_of_oldest_proven_restore_target

continuous
  WAL/archive/capacity alert

daily
  latest backup, duration, bytes, catalog status

weekly
  dependency/retention review, sampled checksum

monthly
  automated isolated latest/selected restore

quarterly
  representative business validation and oldest target

annually or major change
  regional/break-glass/cutover exercise

service: orders
tier: critical
scenarios:
  logical_error:
    rpo: named/audited transaction boundary
    rto_historical_query: 60m
  region_loss:
    rpo: 5m
    rto_read_write: 4h
backup:
  implementation: pgBackRest
  full: weekly
  diff: daily
  incr: 6h
  wal_archive: continuous
repository:
  primary: object-store-a
  immutable_copy: object-store-b
  failure_domain: separate account and region
  encryption_key: independent-dr-key
retention:
  operational_pitr: 35d
  monthly_logical: 13m
validation:
  latest_restore: monthly
  oldest_target: quarterly
  regional_cutover: annual
owners:
  platform: team-db
  business_validation: team-orders
  key: team-security

backup dependency graph
+ continuous WAL window
+ integrity
+ confidentiality
+ immutability
+ independent availability
+ capacity
+ ownership

select historical lineage
  -> copy base
      -> replay WAL
          -> stop at boundary
              -> possibly promote onto new timeline
                  -> decide what may consume this history

incident start and detection time in UTC
suspected destructive transaction/request
source cluster identity and current timeline
current repository catalog
archive maximum and gaps
relevant application/audit logs
last known correct business marker
authority and requested outcome

kind: name
value: before_release_20260729
inclusive: not-applicable
timezone: UTC
source_evidence:
  - pg_create_restore_point result
  - release change record
expected:
  present:
    - schema version 41
    - business token A
  absent:
    - migration transaction B
approved_by:
  - incident commander
  - data owner

2026-07-29 20:10:44.000+00

known pre-created boundary
  name

known destructive transaction
  xid + inclusive=false, after verifying identity

known physical log point
  lsn

only trusted event time
  time + explicit timezone/inclusive

need earliest consistent database
  immediate

need latest independently archived state
  end of archive, with RPO evidence

finish before or otherwise be valid for target
belong to same physical lineage
have all dependent backup objects
have continuous required WAL to target
have reachable timeline history
have usable encryption key
fit target runtime/version

backup A ends 09:00
backup B starts 11:00

--set
  chooses base/dependency set

--type / --target
  chooses where WAL replay stops

new host/failure domain
  best isolation

same host, new path/process/socket/port
  useful lab compromise with explicit exception

overwrite original data directory
  destructive replacement only, separate authority

df -h
df -i
findmnt
lsblk

owner postgres
mode 0700
not symlink
new and empty
not /pg/data
not a Patroni member path

listen_addresses=''
private Unix socket mode 0700
custom restrictive pg_hba
distinct port
no service registration
no DNS/VIP/proxy route

--archive-mode=off

SHOW archive_mode;  -- off

read-only catalog/capture
  no database or repository mutation

isolated restore drill
  synthetic source writes + new backup/path/postmaster

production replacement/cutover
  stops/overwrites/reroutes real service

exact source/stanza/system lineage
exact backup label and type
backup status and dependency completeness
backup start/stop WAL
target kind/value/inclusive
target WAL covered by archive
timeline history available
restore_command configured
recovery.signal present
standby.signal absent unless desired
tablespace path and ownership
PostgreSQL major/binaries/extensions
recovery-critical max settings
network/HBA/archive isolation
log destination writable

sudo -iu postgres \
  pgbackrest --stanza=pg-test --repo=1 --output=json info

stanza status code
database/repository id
PostgreSQL version
backup label/type/error
start/stop timestamp
start/stop archive
logical/repository bytes
archive min/max by archive id
locks

0
  requested file delivered

nonzero
  file unavailable; PostgreSQL may try pg_wal/stream/next source as applicable

test -f "$PGDATA/recovery.signal"
test ! -f "$PGDATA/standby.signal"   # for this promoted PITR design
sed -n '1,200p' "$PGDATA/postgresql.auto.conf"

SELECT system_identifier
FROM pg_control_system();

source == restored

backup timeline = Tb
target timeline = Tt

source/target timeline 7
restored promoted timeline 8

primary current WAL filename timeline
Patroni TL
replica receive/replay evidence
checkpoint timeline

max_connections
max_worker_processes
max_wal_senders
max_prepared_transactions
max_locks_per_transaction

stop
read PostgreSQL FATAL/DETAIL/HINT
compare with captured source settings
correct isolated runtime
start as a new reviewed attempt
never weaken source or edit WAL/control data

install exact extension packages
or explicitly disable reviewed preload components

no Patroni process
no DCS registration
no HAProxy service
no exporter discovery
no archive push

starting backup recovery
restored history/WAL from archive
starting point-in-time recovery to ...
redo starts
consistent recovery state reached
ready to accept read-only connections
recovery stopping at target
selected new timeline
archive recovery complete
end-of-recovery checkpoint
ready to accept connections

requested WAL not found
recovery ended before configured target
invalid checkpoint record
insufficient parameter settings
could not load library
tablespace path failure
permission denied

first connection:
  in_recovery=true
  transaction_read_only=true

355.896 ms later:
  in_recovery=false
  transaction_read_only=false

orders
  no orphan order lines
  order total = sum(lines)
  accepted payment token unique
  known checkpoint token present
  destructive release token absent

ledger
  debits = credits per journal
  immutable entries not missing
  sequence/event continuity

tenant SaaS
  no row crosses tenant boundary
  tenant counts match reference
  row-level security policies installed

SELECT order_id
FROM order_line l
LEFT JOIN orders o USING (order_id)
WHERE o.order_id IS NULL
LIMIT 1;

SELECT journal_id
FROM ledger_entry
GROUP BY journal_id
HAVING sum(debit) <> sum(credit)
LIMIT 1;

before target marker present
at/allowed target marker present
after target marker absent

base     present
keep     present
discard  absent

run_id + stage
unique token
database commit timestamp
primary key(run_id, stage)
unique(token)

database says payment pending
payment provider says captured

database row absent
object file already created

database event offset rewound
consumer has processed later events

isolated historical restore
  -> validate historical target
      -> export only affected data
          -> normalize identifiers/format
              -> compare against current
                  -> reviewed repair transaction
                      -> audit and reconcile

disable external network
disable schedulers/background workers
use read-only role for analysts
export with COPY/pg_dump
record checksum/count

freeze or fence old writer
validate candidate
configure identity/secrets
register monitoring
update routing
drain/reconnect clients
run smoke/invariants
reconcile unknown outcomes
monitor backlog
retain old evidence

database/data proof = accepted with exceptions
service cutover = not run

authority and exact target
source preflight
repository/catalog projection
backup label and WAL range
target decision
restore options and path
recovery phase timestamps
effective settings
lineage relation and timeline
business queries/results
source postflight
shutdown state
exceptions
counterexamples
review hashes

repository key
cipher pass
database password
raw secret inventory
unnecessary raw system identifier

development:
  max_connections lower than source
  PostgreSQL rejects recovery start

first formal attempt:
  pgBackRest check given unsupported --repo=1
  pgBackRest exits 31 before restore

failed drills
failure phase
time to diagnose
unsafe side effects
runbook correction
repeat proof

restore command exit 0                      insufficient
PostgreSQL accepts read-only                insufficient
PostgreSQL promoted                         engine milestone
business boundary passes                    data milestone
source remains healthy                      safety milestone
isolated instance stopped                   containment milestone
service controlled and reconciled           service milestone

which history?
which target?
which evidence?
which isolation?
which completion state?
which business truth?
which authority to expose it?

PostgreSQL
  physical history and recovery semantics

pgBackRest
  repository, backup sets, archive transport, restore orchestration

Pigsty
  declarative delivery, scheduling, service integration and observability

pgbackrest_enabled: true
pgbackrest_method: minio       # or local/custom
pgbackrest_repo:
  minio:
    type: s3
    s3_endpoint: sss.pigsty
    s3_region: us-east-1
    s3_bucket: pgsql
    path: /pgbackrest
    storage_port: 9000
    block: y
    bundle: y
    bundle_limit: 20MiB
    bundle_size: 128MiB
    cipher_type: aes-256-cbc
    retention_full_type: time
    retention_full: 14

local
  local POSIX repository, default path /pg/backup

minio
  S3-compatible repository, optional MinIO service

custom
  pgBackRest supports other repository backends/config

pgbackrest --stanza=pg-test stanza-create
pgbackrest --stanza=pg-test check
pgbackrest --stanza=pg-test info

./pgsql.yml -t pg_backup

remove database service
  != authorize deleting recovery history

pg-backup          # documented default/incremental behavior
pg-backup full
pg-backup diff
pg-backup incr

sudo -iu postgres \
  pgbackrest --stanza=pg-test --repo=1 \
             --type=full --log-level-console=info backup

pg_crontab:
  - '00 01 * * 1 /pg/bin/pg-backup full'
  - '00 01 * * 2-7 /pg/bin/pg-backup'

Monday 01:00 full
other days 01:00 default backup

which node owns cron
which node is repository stanza's database endpoint
archive push source
backup-from-standby policy

PostgreSQL archive command
  -> pgBackRest spool queue
      -> repository worker

spool path
archive-push async log
queue size/age
repository max WAL
pg_stat_archiver
pg_wal free

/data/pg36-ch21-restore/<run>/spool

S3 access key/secret
repository cipher pass
repository TLS client material
remote host key
database backup/replication credential
KMS token

TLS enabled
expected CA
hostname/SAN
certificate rotation
clock
no silent downgrade

inventory declaration
generated /etc/pgbackrest config
effective command/catalog behavior

stanza
repo type/path class
endpoint class
cipher type
retention type/value
block/bundle
archive async
spool path

sudo -iu postgres \
  pgbackrest --stanza=pg-test --repo=1 info

sudo -iu postgres \
  pgbackrest --stanza=pg-test --repo=1 --output=json info

stanza status code/message
backup/restore locks
repository id/cipher
database version and repository key
archive id/min/max
backup label/type/error
timestamp start/stop
archive start/stop
logical and repository bytes
prior/reference dependency

20260729-201041F

F full
D differential
I incremental

sudo -iu postgres \
  pgbackrest --stanza=pg-test --log-level-console=info check

info/backup/restore accept --repo=1
check in pgBackRest 2.59.0 rejects --repo=1

SELECT archived_count,
       last_archived_wal,
       last_archived_time,
       failed_count,
       last_failed_wal,
       last_failed_time
FROM pg_stat_archiver;

SELECT pg_current_wal_lsn(),
       pg_walfile_name(pg_current_wal_lsn());

PostgreSQL last archived
pgBackRest archive max
current WAL
spool queue

archived_count > 0
failed_count = 21
last successful time > last failed time

failed_count > 0 forever critical

failure counter increased recently
and/or last failure later than last success
and/or archive maximum stops advancing while WAL advances
and/or spool/backlog/free-space breaches

now - latest successful backup stop

latest full age
latest diff age
latest any backup age
oldest retained full

last archive time old
current WAL same segment

last archive time recent
but repository is many segments behind

df -h /pg/data /pg
du -sh /pg/data/pg_wal

used bytes
object count
growth forecast
version/lock overhead
cold tier
retention expiry

df -h /data
df -i /data

/pg/log/pgbackrest/

backup
expire
archive-push-async
archive-get-async
restore

PG36_EVIDENCE_DIR=/new/path \
  static/labs/ch21/task.sh capture

Patroni topology
PostgreSQL archive/current WAL/settings
sanitized pgBackRest catalog
no raw system ID
no credential

recoverability = not-proven-by-capture

manual pg-pitr prompt/script path
pgsql-pitr.yml playbook path

./pgsql-pitr.yml \
  -e '{"pg_pitr": {"time": "2026-07-29 20:10:44+00"}}'

pg_pitr: {}
pg_pitr: { time: "2026-07-29 20:10:44+00" }
pg_pitr: { lsn: "0/200002D0" }
pg_pitr: { xid: "250000", exclusive: true }
pg_pitr: { name: "before_release" }
pg_pitr: { type: "immediate" }

source cluster remains running
restore under a brand-new path
no Patroni
no DCS
no TCP
no service endpoint
archive_mode=off
stop after proof
retain directory

sudo -iu postgres pgbackrest \
  --stanza=pg-test \
  --repo=1 \
  --set=20260729-201041F \
  --type=name \
  --target=pg36_ch21_<run>_keep \
  --target-action=promote \
  --target-timeline=latest \
  --archive-mode=off \
  --pg1-path=/data/pg36-ch21-restore/<run>/data \
  --spool-path=/data/pg36-ch21-restore/<run>/spool \
  --log-path=/data/pg36-ch21-restore/<run>/log \
  restore

hba_file=/pg/data/pg_hba.conf
ident_file=/pg/data/pg_ident.conf
log path under /pg/log/postgres
TLS path under /pg/cert
listen_addresses=0.0.0.0
port=5432
standby primary_conninfo/slot

live:
  /pg/data
  port 5432
  Patroni member pg-test-3

isolated:
  /data/pg36-ch21-restore/<run>/data
  Unix socket only
  internal port 55432
  no Patroni

pg_ctl -D "$restore/data" -w start

SELECT pg_is_in_recovery(),
       current_setting('transaction_read_only'),
       current_setting('archive_mode'),
       current_setting('listen_addresses');

recovery=false
transaction_read_only=false
archive_mode=off
listen_addresses=''

pg_ctl -D "$restore/data" -w -m fast stop

postmaster.pid absent
Unix socket absent
TCP listener absent
restore directory present
live replica still streaming on 5432

capture|verify|review|all
  no mutation

drill:pitr
  requires exact target + four nonproduction guards + confirmation

reset:fixture
  separate destructive token + exact run ID

review PostgreSQL recovery setting changes
review pgBackRest command options/output schema
review Pigsty variables/playbooks
recreate sanitized baseline
run negative guards
perform fresh restore
compare readiness phases
update exceptions and evidence

declare with Pigsty
inspect with pgBackRest + PostgreSQL
prove with isolated restore
govern with explicit authority

fresh synthetic marker
fresh full pgBackRest backup
post-backup marker
named restore point
post-target marker
forced WAL switch + pgBackRest check
exact-label restore to a fresh isolated path
read-only and promotion phase measurement
positive/negative business boundary
lineage and timeline proof
isolated shutdown
source postflight
14 adversarial counterexamples

local nonproduction sandbox only
production data/traffic forbidden
source cluster not stopped
live /pg/data not touched
no DCS/Patroni membership for restored copy
no TCP listener
archive push disabled
no routing change
no cleanup during acceptance
production approval pending

pg36-l2-vagrant/pg-test
Pigsty v4.5.0
PostgreSQL 18.6
pgBackRest 2.59.0
pg-test-1 primary
pg-test-2/3 streaming replicas

PG36_EVIDENCE_DIR=/new/empty/path \
  static/labs/ch21/task.sh drill:pitr

exit=77
refusing PITR drill: exact target, nonproduction, data, traffic,
and confirmation guards are required

PG36_CH19_INVENTORY=/absolute/mode-0600/reviewed.yml \
PG36_EVIDENCE_DIR="$run/preflight-ch19" \
  static/labs/ch19/task.sh all

hosts=4-distinct
pg-meta one primary
pg-test one primary + two streaming replicas
secret values redacted
sandbox_l2=accepted-with-exceptions
production_ch19_gate=pending

Patroni exact member set
pg-test-1 only leader/running
pg-test-2/3 replica/streaming
replay lag <= 1 MiB
source in_recovery=false
archive_mode=on
current WAL segment/timeline
recovery-critical settings
sanitized pgBackRest catalog

CREATE SCHEMA IF NOT EXISTS pg36_ch21;

CREATE TABLE IF NOT EXISTS pg36_ch21.recovery_probe (
    run_id       text        NOT NULL,
    stage        text        NOT NULL
                             CHECK (stage IN ('base','keep','discard')),
    token        text        NOT NULL UNIQUE,
    committed_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (run_id, stage)
);

run_YYYYMMDDTHHMMSSZ_<8 hex>

insert base
  -> take full backup
      -> insert keep
          -> pg_create_restore_point(name)
              -> insert discard
                  -> pg_switch_wal
                      -> pgBackRest check

sleep 2

run ID
unique token
database commit timestamp
restore-point LSN
target WAL filename

sudo -iu postgres pgbackrest \
  --stanza=pg-test \
  --repo=1 \
  --type=full \
  --log-level-console=info \
  backup

type=full
error=false
stanza status code=0

label                 20260729-201041F
backup command        2085.526 ms
logical bytes         36,121,841
repository delta       4,539,288
backup archive        timeline 7

SELECT pg_create_restore_point(
  'pg36_ch21_run_20260729T201040Z_961665aa_keep'
);

name
LSN
WAL segment
created_at UTC

SELECT pg_switch_wal();

sudo -iu postgres \
  pgbackrest --stanza=pg-test --log-level-console=info check

check duration          598.471 ms
target WAL              ...000020
repository max WAL      ...000021
target_wal_covered      true

--repo=1

ERROR [031]: option 'repo' not valid for command 'check'

backup succeeded
markers retained
no isolated directory
no postmaster
source healthy