postgres-azure-backup

Simple PostgreSQL backup tool to Azure with UI

Features

• Supports multiple databases
• List tables and records of actual database
• Show last backup time
• Creates backups with retention period
• Based on pg_dump and pg_restore official PostgreSQL utilities
• Persists dumps in Azure Blob Storage
• Cleanup expired backups
• Restore backups
• Exclude tables from backup
• Can be used without UI as REST API. For example using a cronjob and curl
• Fully covered with E2E Selenium tests
• Compatible with PostgreSQL 16
• Secured with Microsoft Entra ID
• Azure cloud-native application

Stack

• Java 21
• Spring Boot 3
• Angular
• Microsoft Authentication Library (MSAL)
• Azure
• PostgreSQL 16 client

Required environment variables

• AZURE_CLIENT_ID
• AZURE_TENANT_ID
• SPRING_ACTUATOR_PORT
• BLOBSTORAGE_ENDPOINT_URL
• DATABASES_CONFIG_PATH
• UI_CLIENT_ID

Database config file

[
  {
    "name": "db1",
    "host": "db1",
    "port": 5432,
    "database": "test",
    "schema": "test1",
    "backupContainerName": "db1-backup",
    "username": "postgres",
    "password": "postgres",
    "excludeTables": ["passwords", "secrets"],
    "dumpFormat": "custom"
  },
  {
    "name": "db2",
    "host": "db2",
    "port": 5432,
    "database": "test",
    "schema": "test2",
    "backupContainerName": "db1-backup",
    "username": "postgres",
    "password": "postgres",
    "excludeTables": ["passwords", "secrets"],
    "dumpFormat": "tar"
  }
]

Dump formats

• custom (default) - Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.
• directory - Output a directory-format archive suitable for input into pg_restore. This will create a directory with one file for each table and large object being dumped, plus a so-called Table of Contents file describing the dumped objects in a machine-readable format that pg_restore can read. A directory format archive can be manipulated with standard Unix tools; for example, files in an uncompressed archive can be compressed with the gzip, lz4, or zstd tools. This format is compressed by default using gzip and also supports parallel dumps.
• tar - Output a tar-format archive suitable for input into pg_restore. The tar format is compatible with the directory format: extracting a tar-format archive produces a valid directory-format archive. However, the tar format does not support compression. Also, when using tar format the relative order of table data items cannot be changed during restore.

Deployment with Helm

host=$(...)
blobStorageEndpointUrl=$(...)
apiClientId=$(...)
spaClientId=$(...)

helm repo add mucsi96 https://mucsi96.github.io/k8s-helm-charts
helm install mucsi96/spring-app \
    --namespace backup \
    --set image=mucsi96/postgres-azure-backup:latest \
    --set host=backup.$host \
    --set clientId=$apiClientId \
    --set serviceAccountName=postgres-azure-backup \
    --set env.BLOBSTORAGE_ENDPOINT_URL=$blobStorageEndpointUrl \
    --set env.DATABASES_CONFIG_PATH=/app/databases_config.json \
    --set env.UI_CLIENT_ID=$spaClientId \
    --set configFile[0].name=databases_config.json \
    --set configFile[0].mountPath=/app/databases_config.json \
    --set "configFile[0].data=$(cat scripts/databases_config.json | base64)" \
    --wait

Resources

• https://github.com/kananindzya/hello-world-aws-sdk-r2/blob/master/src/main/java/com/example/aws/api/r2/App.java

• https://github.com/esfandiar/vs-code-spring-boot-setup

• https://gist.github.com/valferon/4d6ebfa8a7f3d4e84085183609d10f14

• https://cwienczek.com/2020/06/simple-backup-of-postgres-database-in-kubernetes/

• https://developers.cloudflare.com/r2/examples/aws/boto3/

• https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/s3.html

• https://florianbuchner.com/kubernetes-curl-cronjob-for-internal-service/

• https://flowbite.com/docs/components/tables/

• https://hslpicker.com/

• https://learn.microsoft.com/en-us/azure/developer/java/spring-framework/spring-security-support?tabs=SpringCloudAzure5x#accessing-a-resource-server