Recovery from logicalbackup/ Very poor documentation

[aditya1808]

Hello,
After going through the documentation and issues, i understood that logicalbackup cron job is triggered by postgresoperator and the logicalbackup is compressed and stored in S3.

But my question is,

1. is it possible to recover from logicalbackup from S3.
2. if its possible, is it same as cloning from existing s3? how is recovery different from cloning?

MY use case is to do periodical backup to minio (i guess its possible) and how to recover the database from it (both complete backup and also based on time stamp.)

https://postgres-operator.readthedocs.io/en/latest/reference/cluster_manifest/#parameters-defining-how-to-clone-the-cluster-from-another-one
In the above link its written ->
s3_wal_path the url to S3 bucket containing the WAL archive of the cluster to be cloned. Optional.

How did wal archive come into picture? does it means same as logical backup. why cant you use consistent naming to make it simpler for new users to understand.
If it actually means logical backup, does it mean that i can recover entier database from backup in S3??

If so then why did @Jan-M mention its not possible in this comment
#568 (comment)

And what is the point of logical backup if we cant recover from it???

[redscaresu]

logical backup == pg_dump. It is a one time entire backup of your entire cluster at the point in time you make the backup. It is not a delta, it contains everything you need to restore not only the database but the cluster at the time you took the backup.

For example you could restore with the following command from a logical backup with the following command.

psql --host=localhost --port=5432 --username=foo-admin --password --dbname=foo < spilo_foo-cluster_xxx-xxxx-xxxxx-xxxxx-xxxxx_logical_backups_xxxxxx.sql

You could also use pg_restore to restore from a logical backup or you could get fancy and use @vitobotta script.

WAL is something different, it allows for point in time recovery.

It is an incremental backup, each backup only contains the delta (the difference between the last backup and the current one).

For example

suppose you dropped a critical table at 5:15PM on Tuesday evening, but didn't realize your mistake until Wednesday noon. Unfazed, you get out your backup, restore to the point-in-time 5:14PM Tuesday evening

Unless you had made a logical backup at precisely 5:14PM (extremely unlikely) this option would not be available to you via the logical backup option.

Which bit in the doc do you find confusing?

https://postgres-operator.readthedocs.io/en/latest/user/#how-to-clone-an-existing-postgresql-cluster

Bear in mind ^^ only supports WAL PITR. For logical backups you could use the method i provided above.

[aditya1808]

This is much much better explanation than i was looking for. Thanks a lot @redscaresu .
Although, one last question,
You cleared it up for me that logical-backup is one time backup and not incremenetal. We have option to enable logical-backup using cron job in pg-operator. And this gets stored in S3. thats cool.

But how to trigger this basebackup and WAL backup? i believe these two go hand in hand.
So is there an option in pg-operator to have WAL archived in S3?

[redscaresu]

My pleasure @aditya1808!

But how to trigger this basebackup and WAL backup? i believe these two go hand in hand.
So is there an option in pg-operator to have WAL archived in S3?

Yes that is correct, this what I do to get wal files into S3. I include the following in the postgres-operator configmap.

wal_s3_bucket: <<s3_bucket_name>>

However the spilo container which actually does the heavy lifting needs some additional information so I mount a configmap on the spilo containers that looks something like this.

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-pod-config
  namespace: postgres-cluster
data: 
  BACKUP_SCHEDULE: "0 */12 * * *"
  BACKUP_NUM_TO_RETAIN: "12"
  AWS_ACCESS_KEY_ID: xxxxxx
  AWS_SECRET_ACCESS_KEY: xxxxxx
  AWS_ENDPOINT:  http://minio-endpoint:9000
  AWS_S3_FORCE_PATH_STYLE: "true"
  AWS_REGION: us-east-1
  USE_WALG_BACKUP: "true"
  WALG_DISABLE_S3_SSE: "true"

Once you provide this information WAL-E/G will handle the rest.