手工恢复

curl https://repo.pigsty.io/get | bash; cd ~/pigsty/
./configure -c ha/full
./deploy.yml

sudo su - postgres    # 切换到 dbsu: postgres 用户
pb info               # 打印 pgbackrest 备份信息

function pb() {
    local stanza=$(grep -o '\[[^][]*]' /etc/pgbackrest/pgbackrest.conf | head -n1 | sed 's/.*\[\([^]]*\)].*/\1/')
    pgbackrest --stanza=$stanza $@
}

$ pg-pitr -c -t "2025-07-13 03:03:00+00"   # -c / --check / --dry-run：只检查并打印命令

Perform time PITR on pg-meta
[1. Stop PostgreSQL] ===========================================
   1.1 Pause Patroni (if there are any replicas)
       $ pg pause <cls>  # 暂停 patroni 自动故障切换
   1.2 Shutdown Patroni
       $ pt-stop         # sudo systemctl stop patroni
   1.3 Shutdown Postgres
       $ pg-stop         # pg_ctl -D /pg/data stop -m fast

[2. Perform PITR] ===========================================
   2.1 Restore Backup
       $ pgbackrest --stanza=pg-meta --delta --force --type=time --target='2025-07-13 03:03:00+00' restore
   2.2 Start PG to Replay WAL
       $ pg-start        # pg_ctl -D /pg/data start
   2.3 Validate and Promote
     - If database content is ok, promote it to finish recovery, otherwise goto 2.1
       $ pg-promote      # pg_ctl -D /pg/data promote

[3. Restore Primary] ===========================================
   3.1 Check Archive Mode (Reset Only If Disabled)
       $ psql -Atqc 'show archive_mode'
       $ psql -c 'ALTER SYSTEM RESET archive_mode;'  # 仅当恢复时显式关闭过归档
   3.2 Stop the manually started Postgres
       $ pg-stop         # Patroni will start PostgreSQL again with the corrected setting
   3.3 Remove stale DCS metadata (all Patroni instances must remain stopped)
       $ pg remove pg-meta
   3.4 Start Patroni
       $ pt-start        # sudo systemctl start patroni

[4. Restore Cluster] ===========================================
   4.1 Re-Init All [**REPLICAS**] (if any)
       - 4.1.1 option 1: restore replicas with same pgbackrest cmd (require central backup repo)
           $ pgbackrest --stanza=pg-meta --delta --force --type=time --target='2025-07-13 03:03:00+00' restore
       - 4.1.2 option 2: nuke the replica data dir and restart patroni (may take long time to restore)
           $ rm -rf /pg/data/*; pt-restart
       - 4.1.3 option 3: reinit with patroni; verify the rebuilt cluster and DCS state afterward
           $ pg reinit pg-meta
   4.2 Full Backup (recommended)
       $ pg-backup full      # 建议在 PITR 后执行新的全量备份

psql -Atqc 'SELECT pg_is_in_recovery(), pg_is_wal_replay_paused(), pg_last_xact_replay_timestamp()'
# 预期仍在恢复、且回放已暂停：t|t|<目标附近的时间>

postgres@pg-meta-1:~$ psql -c 'table monitor.heartbeat'
   id    |              ts               |    lsn    | txid
---------+-------------------------------+-----------+------
 pg-meta | 2025-07-13 03:02:59.214104+00 | 302005504 | 4912

postgres@pg-meta-1:~$ cat /pg/data/postgresql.auto.conf
# Do not edit this file or use ALTER SYSTEM manually!
# It is managed by Pigsty & Ansible automatically!

# Recovery settings generated by pgBackRest restore on 2025-07-13 03:17:08
archive_mode = 'off'
restore_command = 'pgbackrest --stanza=pg-meta archive-get %f "%p"'
recovery_target_time = '2025-07-13 03:03:00+00'