34.6 保留型故障的安全路由

SELECT pid,
       datname,
       usename,
       application_name,
       state,
       xact_start,
       backend_xid,
       backend_xmin,
       wait_event_type,
       wait_event
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL
   OR backend_xmin IS NOT NULL
ORDER BY xact_start NULLS LAST;

SELECT slot_name,
       slot_type,
       database,
       active,
       xmin,
       catalog_xmin,
       restart_lsn,
       inactive_since,
       invalidation_reason
FROM pg_replication_slots
ORDER BY slot_name;

SELECT transaction,
       gid,
       prepared,
       owner,
       database
FROM pg_prepared_xacts
ORDER BY prepared;

SELECT datname,
       age(datfrozenxid) AS xid_age,
       mxid_age(datminmxid) AS multixact_age
FROM pg_database
ORDER BY xid_age DESC;

owner
business or replication purpose
last successful progress
expected outage/retention window
recoverability if released
approved decision maker

SELECT pg_current_wal_lsn() AS current_lsn,
       pg_wal_lsn_diff(
         pg_current_wal_lsn(),
         '0/0'::pg_lsn
       ) AS absolute_lsn_bytes;

SELECT slot_name,
       slot_type,
       active,
       restart_lsn,
       pg_wal_lsn_diff(
         pg_current_wal_lsn(),
         restart_lsn
       ) AS retained_wal_bytes,
       wal_status,
       safe_wal_size,
       invalidation_reason
FROM pg_replication_slots
ORDER BY retained_wal_bytes DESC NULLS LAST;

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

physical replica receive/replay progress
logical consumer confirmed progress
archive command/repository health
base backup / restore / WAL summarization activity
checkpoint timing
WAL generation rate by workload
filesystem free bytes and inode headroom

backup run id / start LSN / stop LSN
repository and archive confirmation
base backup progress
restore validation state
current RPO source
alternative healthy backup/replica

consumer maximum outage
WAL generation envelope
alert lead time
reseed procedure
accepted data-loss semantics

reduce noncritical WAL generation
preserve SQL + filesystem + archive evidence
identify exact retention owner
repair archive/consumer or establish a new recovery source
release only an exact, authorized object through PostgreSQL
verify recovery chain and filesystem headroom

facts:
  system_identifier_timeline: ...
  current_and_retained_lsn: ...
  owner_consumer: ...
  archive_backup_state: ...
  growth_rate_time_to_full: ...
actions_already_taken: [...]
unknowns: [...]
forbidden_actions:
  - manual delete pg_wal
  - drop unknown slot

temporary headroom gained
new estimated time to full
retention owner unchanged
permanent repair owner/deadline
rollback or capacity reconciliation

exact retention owner identified
oldest required horizon is advancing or deliberately re-established
consumer/archive/backup has a valid recovery path
released capability and accepted loss are recorded
WAL/XID growth rate returns inside envelope
replicas and PITR validation pass
temporary storage/traffic controls are reconciled