Fail to recovery from pgbackrest backup

blackqw commented 7 months ago

问题现象：

创建测试表，使用完全备份备份数据，删除测试表，恢复备份数据，测试表未恢复

Pigsty版本号与操作系统版本号

pigsty：2.4.0
postgres: 14
操作系统：centos7.9

Pigsty配置文件

all:
  children:

    # infra cluster for proxy, monitor, alert, etc..
    infra:
      hosts:
        10.72.9.4: { infra_seq: 1 }
      vars:
        ansible_ssh_port: 65530

    # etcd cluster for ha postgres
    etcd:
      hosts:
        10.72.9.4: { etcd_seq: 1 }
        10.72.9.5: { etcd_seq: 2 }
        10.72.9.6: { etcd_seq: 3 }
      vars:
        ansible_ssh_port: 65530
        etcd_cluster: etcd

    # minio cluster, optional backup repo for pgbackrest
    #minio: { hosts: { 10.90.13.41: { minio_seq: 1 } }, vars: { minio_cluster: minio } }

    # postgres cluster 'pg-meta' with single primary instance
    qidong-pg-pro:
      hosts:
        10.72.9.4: { pg_seq: 1, pg_role: primary }
        10.72.9.5: { pg_seq: 2, pg_role: replica }
        10.72.9.6: { pg_seq: 3, pg_role: replica }
      vars:
        ansible_ssh_port: 65530
        pg_cluster: qidong-pg-pro
        pg_databases:
          - name: meta
            baseline: cmdb.sql
            comment: pigsty meta database
            schemas:
              - pigsty
            extensions:
              - name: postgis
                schema: public
              - name: timescaledb
          - name: security_environment_db
            schemas:
              - workflow
              - security_environment
            extensions:
              - name: postgres-fdw
                schema: security_environment
        pg_version: 14
        pg_extensions:                    # 要安装的 pg 扩展，`${pg_version}` 将被替换
          - pg_repack_${pg_version}* wal2json_${pg_version}* passwordcheck_cracklib_${pg_version}*
        pg_hba_rules:
          - title: allow intranet password access
            role: common
            rules:
              - host    all   all  0.0.0.0/0    md5
        pg_storage_type: HDD
        pgbackrest_enabled: true
        pgbackrest_clean: true
        pg_conf: olap.yml
        pg_data: /pg/data
        pg_max_conn: 1000
        pg_shared_buffer_ratio: 0.3
        pg_weight: 100
        pg_default_service_dest: pgbouncer
        pg_vip_enabled: true
        pg_vip_address: 10.72.9.7/24
        pg_vip_interface: ens192
        pg_dns_target: auto
        pg_users:
          - { name: admin ,password: xxxx   ,pgbouncer: true ,roles: [ dbrole_admin ]    ,comment: pigsty admin user }
          - { name: view ,password: xxxx ,pgbouncer: true ,roles: [ dbrole_readonly ] ,comment: read-only viewer for meta database }
        node_crontab:
          - '00 01 * * * postgres /pg/bin/pg-backup full'
          - '00 01 * * 2,3,4,5,6,7 postgres /pg/bin/pg-backup'

  vars:                               # global parameters
    version: v2.4.0                   # pigsty version string
    admin_ip: 10.72.9.4             # admin node ip address
    region: china                     # upstream mirror region: default,china,europe
    infra_portal:                     # domain names and upstream servers
      home         : { domain: h.pigsty }
      grafana      : { domain: g.pigsty ,endpoint: "${admin_ip}:3000" , websocket: true }
      prometheus   : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
      alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
      blackbox     : { endpoint: "${admin_ip}:9115" }
      loki         : { endpoint: "${admin_ip}:3100" }
      minio        : { domain: sss.pigsty  ,endpoint: "xxxx:39000" ,scheme: http , websocket: true  }

    # if you want to use minio as backup repo instead of local fs, uncomment minio related lines
    # don't forget to configure pgbackrest_repo and change credentials there!
    #pgbackrest_method: minio
    pgbackrest_method: minio
    pgbackrest_repo:
      minio:                          # pgbackrest 的可选minio仓库
        type: s3                      # minio 是s3兼容的，因此使用s3
        s3_endpoint: sss.pigsty       # minio终端域名，默认为`sss.pigsty`
        s3_region: us-east-1          # minio区域，默认为us-east-1，对minio来说没有用
        s3_bucket: pgsql              # minio桶名，默认为`pgsql`
        s3_key: admin                 # pgbackrest的minio用户访问密钥
        s3_key_secret: xxxx  # pgbackrest的minio用户密钥，这里请按实际情况填写密码，最好不要使用默认密码。
        s3_uri_style: path            # 使用路径风格的uri，而不是主机风格的uri
        path: /pgbackrest             # minio备份路径，默认为`/pgbackrest`
        storage_port: 443            # minio端口，默认为9000
        storage_ca_file: /etc/pki/ca.crt  # minio的ca文件路径，默认为`/etc/pki/ca.crt`
        retention_full_type: time     # 在minio仓库上按时间保留完整备份
        retention_full: 14            # 保留过去14天的完整备份
    # if disabled, original /etc/yum.repos.d will be kept
    repo_remove: true       # remove existing repo on admin node during repo bootstrap
    node_repo_remove: true  # remove existing node repo for node managed by pigsty

    # WARNING: CHANGE THESE PASSWORDS
    #grafana_admin_username: admin
    grafana_admin_password: xx
    #pg_admin_username: dbuser_dba
    pg_admin_password: xxx
    #pg_monitor_username: dbuser_monitor
    pg_monitor_password: xxx
    #pg_replication_username: replicator
    pg_replication_password: xx
    #patroni_username: postgres
    patroni_password: xx
    #haproxy_admin_username: admin
    haproxy_admin_password: xx

    # this config template assume you are using pre-packed offline packages
    # If you wish to download upstream yum packages directly from internet,
    # consider using ad hoc `el7.yml`, `el8.yml`, `el9.yml` config instead.