PG backups

pb_backup.org

Pgbackup

Questions

I am interested in discussing two items

1. Backup and restore of Aidbox (like incremental backup & pg_dump - also things like online backup and backup jobs etc.,)
   • WALG_COMPRESSION_METHOD=brotli ;; supported lz4, lzma, zstd, brotli

     WALG_DELTA_MAX_STEPS=6 ;; weekly full backup / dayly incremental

   • any kind of jobs: linux crontab, kubernetes CronJob resource, custom operator
2. Discuss running Postgres database along with other DB and running on Azure SQL for postgresql .
   • Yes, it possible. Some of our clients already use this approach

Aidbox Requirements

Any PostgreSQL

• self-hosted - https://docs.aidbox.app/storage-1/aidboxdb-image
• or cloud manged - https://docs.aidbox.app/getting-started/run-aidbox-on-managed-postgresql

Recomended 14+ Extensions - unaccent pgcrypto pg_trgm - required

Self-managed

• Kubernetes STS https://docs.aidbox.app/getting-started/run-aidbox-in-kubernetes/deploy-aidbox-in-kubernetes
• Operators
  • crunchy - https://github.com/CrunchyData/postgres-operator
  • patrony/postres_operator - https://github.com/zalando/postgres-operator

Backup Tools

• wal-g - https://wal-g.readthedocs.io/PostgreSQL/
• pgbackrest - https://pgbackrest.org/
• pg_basebackup/pg_probackup - https://www.postgresql.org/docs/15/app-pgbasebackup.html
• barman - https://pgbarman.org/

Wal-g

Preinstalled in AidboxDb image Recommended for cloud Support incremental backups, Compression, Encryption

Configuration

For Azure

AZURE_STORAGE_ACCOUNT=aidboxbackups
WALG_AZ_PREFIX=azure://db-backup/prod
AZURE_STORAGE_ACCESS_KEY= SA_KEY
PGDATA=/data/pg

postgresql.conf

archive_mode = on
wal_level = replica # or logical
archive_command = 'wal-g wal-push %p'
restore_command = 'wal-g wal-fetch %f %p'

How to?

2 Jobs

• Do backup

  # exec into db pod
  wal-g backup-push $PGDATA
      

• Backup retain

  # exec into db pod
  wal-g delete retain FULL 4 --confirm
      

PITR

• Pull nearest backup

  # exec into db recovery pod
  wal-g backup-fetch /new/pgdata LATEST
      

• Setup recovery_target*

  recovery_target = 'immediate'
  # or
  recovery_target_time =  (timestamp)
      

• Create recovery.signal file

  touch /new/pgdata/recovery.signal
      

• Run pg cluster

  pg_ctl start $PGDATA