Administrator Guide is missing a section on sending WAL to Azure storage accounts

[ssinger]

The Administrator Guide has a section on how to configure WAL to be sent to AWS S3 buckets and Google GCP buckets but not for azure storage accounts.

[RenaudDemarneffe]

Hi @ssinger,

Thank you for your PR. It helped me to setup backup on Azure.
However, when I'm trying to clone the cluster, I have some errors and the cloining is not possible.

My configuration parameters are the following:
ConfigMap:

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-pod-env-overrides
data:
  # Any env variable used by spilo can be added
  USE_WALG_BACKUP: "true"
  USE_WALG_RESTORE: "true"
  CLONE_USE_WALG_RESTORE: "true"

Secret:

apiVersion: v1
kind: Secret
metadata:
  name: psql-walg-creds
type: Opaque
stringData:
  AZURE_STORAGE_ACCOUNT: 'REDACTED'
  # Use either AZURE_STORAGE_ACCESS_KEY or AZURE_STORAGE_SAS_TOKEN
  # Remark: AZURE_STORAGE_SAS_TOKEN can give access to only one 'container' and not the full storage account
  AZURE_STORAGE_ACCESS_KEY: 'REDACTED'
  WALG_AZ_PREFIX: azure://testrde-to-remove
  # Number of backup to retain
  BACKUP_NUM_TO_RETAIN: '5'
  # Backup schedule (each day at 01h00)
  BACKUP_SCHEDULE: '00 01 * * *'

I set the WALG_AZ_PREFIX into the Secret because I neeed to use a different storage container per cluster.

When I try to create a new cluster with a clone of the previous one, I set the following configuration into the postgresql resource:

clone:
    uid: "ff9a685c-b8ea-46be-abfd-26c5f15e90ba"
    cluster: "test-cluster"
    timestamp: "2021-06-08T12:20:00+02:00"  # timezone required (offset relative to UTC, see RFC 3339 section 5.6)

And I get the following log. I think the third line represent an error saying that it cannot restore the wal-g backup (confirmed below by the fact that it takes a new fresh backup).

2021-06-08 13:51:54,838 - bootstrapping - INFO - Figuring out my environment (Google? AWS? Openstack? Local?)
2021-06-08 13:51:54,841 - bootstrapping - INFO - Could not connect to 169.254.169.254, assuming local Docker setup
2021-06-08 13:51:54,842 - bootstrapping - WARNING - Cloning with WAL-E is only possible when CLONE_WALE_*_PREFIX or CLONE_WALG_*_PREFIX or CLONE_WAL_*_BUCKET and CLONE_SCOPE are set.
2021-06-08 13:51:54,842 - bootstrapping - INFO - No meta-data available for this provider
2021-06-08 13:51:54,842 - bootstrapping - INFO - Looks like your running local
2021-06-08 13:51:55,023 - bootstrapping - INFO - Configuring patroni
2021-06-08 13:51:55,033 - bootstrapping - INFO - Writing to file /run/postgres.yml
2021-06-08 13:51:55,034 - bootstrapping - INFO - Configuring pgbouncer
2021-06-08 13:51:55,034 - bootstrapping - INFO - No PGBOUNCER_CONFIGURATION was specified, skipping
2021-06-08 13:51:55,034 - bootstrapping - INFO - Configuring crontab
2021-06-08 13:51:55,034 - bootstrapping - INFO - Skipping creation of renice cron job due to lack of SYS_NICE capability
2021-06-08 13:51:55,051 - bootstrapping - INFO - Configuring pam-oauth2
2021-06-08 13:51:55,051 - bootstrapping - INFO - Writing to file /etc/pam.d/postgresql
2021-06-08 13:51:55,051 - bootstrapping - INFO - Configuring wal-e
2021-06-08 13:51:55,052 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/WALG_AZ_PREFIX
2021-06-08 13:51:55,052 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/AZURE_STORAGE_ACCOUNT
2021-06-08 13:51:55,052 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/AZURE_STORAGE_ACCESS_KEY
2021-06-08 13:51:55,052 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/WALG_DOWNLOAD_CONCURRENCY
2021-06-08 13:51:55,052 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/WALG_UPLOAD_CONCURRENCY
2021-06-08 13:51:55,053 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/USE_WALG_BACKUP
2021-06-08 13:51:55,053 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/USE_WALG_RESTORE
2021-06-08 13:51:55,053 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/WALE_LOG_DESTINATION
2021-06-08 13:51:55,053 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/PGPORT
2021-06-08 13:51:55,053 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/BACKUP_NUM_TO_RETAIN
2021-06-08 13:51:55,053 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/TMPDIR
2021-06-08 13:51:55,122 - bootstrapping - INFO - Configuring standby-cluster
2021-06-08 13:51:55,122 - bootstrapping - INFO - Configuring bootstrap
2021-06-08 13:51:55,122 - bootstrapping - INFO - Configuring log
2021-06-08 13:51:55,122 - bootstrapping - INFO - Configuring pgqd
2021-06-08 13:51:55,123 - bootstrapping - INFO - Configuring certificate
2021-06-08 13:51:55,123 - bootstrapping - INFO - Generating ssl certificate
2021-06-08 13:51:58,067 WARNING: Kubernetes RBAC doesn't allow GET access to the 'kubernetes' endpoint in the 'default' namespace. Disabling 'bypass_api_service'.
2021-06-08 13:51:58,165 INFO: No PostgreSQL configuration items changed, nothing to reload.
2021-06-08 13:51:58,168 INFO: Lock owner: None; I am testrdebis-cluster-0
2021-06-08 13:51:58,253 INFO: trying to bootstrap a new cluster
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

fixing permissions on existing directory /home/postgres/pgdata/pgroot/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /usr/lib/postgresql/13/bin/pg_ctl -D /home/postgres/pgdata/pgroot/data -l logfile start

2021-06-08 13:52:04 UTC [96]: [1-1] 60bf7604.60 0     LOG:  Auto detecting pg_stat_kcache.linux_hz parameter...
2021-06-08 13:52:04 UTC [96]: [2-1] 60bf7604.60 0     LOG:  pg_stat_kcache.linux_hz is set to 1000000
2021-06-08 13:52:04 UTC [96]: [3-1] 60bf7604.60 0     LOG:  redirecting log output to logging collector process
2021-06-08 13:52:04 UTC [96]: [4-1] 60bf7604.60 0     HINT:  Future log output will appear in directory "../pg_log".
2021-06-08 13:52:04,234 INFO: postmaster pid=96
/var/run/postgresql:5432 - no response
/var/run/postgresql:5432 - accepting connections
/var/run/postgresql:5432 - accepting connections
2021-06-08 13:52:05,427 INFO: establishing a new patroni connection to the postgres cluster
2021-06-08 13:52:05,468 INFO: running post_bootstrap
DO
DO
DO
CREATE EXTENSION
NOTICE:  version "1.0" of extension "pg_auth_mon" is already installed
ALTER EXTENSION
GRANT
CREATE EXTENSION
NOTICE:  version "1.3" of extension "pg_cron" is already installed
ALTER EXTENSION
ALTER POLICY
REVOKE
GRANT
...
GRANT
RESET
2021-06-08 13:52:08,234 WARNING: Could not activate Linux watchdog device: "Can't open watchdog device: [Errno 2] No such file or directory: '/dev/watchdog'"
2021-06-08 13:52:08,323 INFO: initialized a new cluster
2021-06-08 13:52:08,327 INFO: Lock owner: testrdebis-cluster-0; I am testrdebis-cluster-0
2021-06-08 13:52:08,347 INFO: no action.  i am the leader with the lock
2021-06-08 13:52:08,511 INFO: Lock owner: testrdebis-cluster-0; I am testrdebis-cluster-0
2021-06-08 13:52:08,552 INFO: no action.  i am the leader with the lock
2021-06-08 13:52:19,011 INFO: Lock owner: testrdebis-cluster-0; I am testrdebis-cluster-0
2021-06-08 13:52:19,056 INFO: no action.  i am the leader with the lock
2021-06-08 13:52:29,011 INFO: Lock owner: testrdebis-cluster-0; I am testrdebis-cluster-0
2021-06-08 13:52:29,074 INFO: no action.  i am the leader with the lock
2021-06-08 13:52:39,011 INFO: Lock owner: testrdebis-cluster-0; I am testrdebis-cluster-0
2021-06-08 13:52:39,056 INFO: no action.  i am the leader with the lock
2021-06-08 13:52:49,011 INFO: Lock owner: testrdebis-cluster-0; I am testrdebis-cluster-0
2021-06-08 13:52:49,025 INFO: no action.  i am the leader with the lock
2021-06-08 13:52:55.670 - /scripts/postgres_backup.sh - I was called as: /scripts/postgres_backup.sh /home/postgres/pgdata/pgroot/data
2021-06-08 13:52:56.350 - /scripts/postgres_backup.sh - producing a new backup
INFO: 2021/06/08 13:52:56.426629 Doing full backup.
INFO: 2021/06/08 13:52:56.445604 Calling pg_start_backup()
2021-06-08 13:52:57.029 55 LOG Starting pgqd 3.3
2021-06-08 13:52:57.030 55 LOG auto-detecting dbs ...
INFO: 2021/06/08 13:52:57.640210 Walking ...
INFO: 2021/06/08 13:52:57.641357 Starting part 1 ...
WARNING: 2021/06/08 13:52:58.223048 /home/postgres/pgdata/pgroot/data/base/1/pg_internal.init  deleted during filepath walk
2021-06-08 13:52:59,023 INFO: Lock owner: testrdebis-cluster-0; I am testrdebis-cluster-0
2021-06-08 13:52:59,126 INFO: no action.  i am the leader with the lock
INFO: 2021/06/08 13:52:59.526389 Finished writing part 1.
INFO: 2021/06/08 13:53:00.003435 Starting part 2 ...
INFO: 2021/06/08 13:53:00.003616 /global/pg_control
INFO: 2021/06/08 13:53:00.011287 Finished writing part 2.
INFO: 2021/06/08 13:53:00.012831 Calling pg_stop_backup()
INFO: 2021/06/08 13:53:01.689244 Starting part 3 ...
INFO: 2021/06/08 13:53:01.835058 backup_label
INFO: 2021/06/08 13:53:01.836563 tablespace_map
INFO: 2021/06/08 13:53:01.836794 Finished writing part 3.
INFO: 2021/06/08 13:53:02.366828 Wrote backup with name base_000000010000000000000002
2021-06-08 13:53:09,012 INFO: Lock owner: testrdebis-cluster-0; I am testrdebis-cluster-0
2021-06-08 13:53:09,058 INFO: no action.  i am the leader with the lock
2021-06-08 13:53:19,012 INFO: Lock owner: testrdebis-cluster-0; I am testrdebis-cluster-0
2021-06-08 13:53:19,054 INFO: no action.  i am the leader with the lock
2021-06-08 13:53:27.062 55 LOG {ticks: 0, maint: 0, retry: 0}
2021-06-08 13:53:29,012 INFO: Lock owner: testrdebis-cluster-0; I am testrdebis-cluster-0
2021-06-08 13:53:29,068 INFO: no action.  i am the leader with the lock

finally, the operator logs also send an error about the restore.

time="2021-06-08T14:11:30Z" level=info msg="Clone from S3 bucket" cluster-name=postgres2/testrdebis-cluster pkg=cluster worker=1
time="2021-06-08T14:11:30Z" level=info msg="Figure out which S3 bucket to use from env" cluster-name=postgres2/testrdebis-cluster pkg=cluster worker=1
time="2021-06-08T14:11:30Z" level=error msg="Cannot figure out S3 or GS bucket. Both are empty." cluster-name=postgres2/testrdebis-cluster pkg=cluster worker=1
time="2021-06-08T14:11:30Z" level=info msg="Mount additional volumes: []" cluster-name=postgres2/testrdebis-cluster pkg=cluster worker=1

Do you tried to clone a cluster based on the wal-g backup files stored into Azure?
Could you, please, help me to find the right configuration?
Do I miss some parameters into the postgresql resource?

Regards,
Renaud

[ssinger]

@RenaudDemarneffe At one point I did have cloning working.

Try also adding

CLONE_AZURE_STORAGE_ACCOUNT: 'foo'
CLONE_AZURE_STORAGE_ACCESS_KEY: 'foo'

[kirillka415]

Hi @ssinger @RenaudDemarneffe
the following setting works for me in Azure AKS

apiVersion: v1
kind: ConfigMap
metadata:
  name:  postgres-pod-config
data:
  USE_WALG_BACKUP: "true"
  BACKUP_SCHEDULE: "0 3,15 * * *" # Schedule a base backup at 3:00 and 15:00
  BACKUP_NUM_TO_RETAIN: "180" # For 2 backups per day, keep 90 days of base backups
  WALG_AZ_PREFIX: "azure://postgresql-backup/$(SCOPE)/$(PGVERSION)"
  #  For point in time recovery/restore
  USE_WALG_RESTORE: "true"
  CLONE_WALG_AZ_PREFIX: "azure://postgresql-backup/$(CLONE_SCOPE)/$(PGVERSION)"
  CLONE_USE_WALG_BACKUP: "true"
  CLONE_USE_WALG_RESTORE: "true"

Please note that $(SCOPE) and $(CLONE_SCOPE) convert automatically in a pod into
$(SCOPE)=> Cluster name
$(CLONE_SCOPE) => name of the source cluster from clone section (clone: cluster:)
and $(PGVERSION) into postgres version of cluster
For example: azure://postgresql-backup/test-cluster/12

apiVersion: v1
kind: Secret
metadata:
  name:  postgres-pod-secrets
data:
  AZURE_STORAGE_ACCESS_KEY: <AZURE_STORAGE_ACCESS_KEY_in_base64>
  AZURE_STORAGE_ACCOUNT: <AZURE_STORAGE_ACCOUNT_in_base64>
  CLONE_AZURE_STORAGE_ACCESS_KEY: <AZURE_STORAGE_ACCESS_KEY_in_base64>
  CLONE_AZURE_STORAGE_ACCOUNT: <AZURE_STORAGE_ACCOUNT_in_base64>

I hope it would be helpful
@ssinger Thank you for initial pull request.

[RenaudDemarneffe]

Hi @ssinger , @kirillka415 ,

Thank you for your replies. I tried this configuration and all works well :-)

[FxKu]

Closing as #1537 got merged.