Questions around best practice

[redscaresu]

Hi, apologies if any of these questions are poor ones. I am new to postgres-clustering.

1. I want to delete old incremental WAL files from S3 backup, according to my reading I cannot do this unless I create a new basebackup to S3. What is the recommended way of doing this via the operator? The reason for this is because I am using Oracle Cloud and using block storage via minio (as my S3) which is expensive. My requirement is a 3 month rolling backup (anything outside those 3 months can be deleted). According to this https://github.com/zalando/postgres-operator/issues/947, the suggestion is to use an S3 lifecyle however understanding is that you cannot delete any WAL files included in a base backup. What is the safe way to implement an S3 object lifecyle?

2. I want to test my understanding of WAL. Lets say I have the following configuration for WAL.

  BACKUP_SCHEDULE: "*/5 * * * *"
  BACKUP_NUM_TO_RETAIN: "12"

So does this mean that every 5 minutes a new incremental WAL backup is made? Meaning that I can restore to the nearest 5 minute increment in this case? I am a bit confused about BACKUP_NUM_TO_RETAIN. Have looked at the https://github.com/zalando/spilo/blob/master/postgres-appliance/scripts/postgres_backup.sh#L44-L65. What happens once a backup falls outside of the BACKUP_NUM_TO_RETAIN? Does WAL-E/G just remove them from the local cluster? My understanding is that WAL-E/G does not manage the backups on S3.

3. @FxKu mentions in this https://github.com/zalando/postgres-operator/issues/1032#issuecomment-648667634 that they recommend one database per cluster for using this operator, could you expand on that? Why is this best practice? I have done the same in my environment but I am feeling a slightly guilty about it for the following reasons. Each microservice is connected to a database which has its own cluster and there are 3 nodes in a cluster that means the ram and CPU is wasted on the 2 replica nodes. I understand that the operator provides my cluster-repl svc endpoints which is great but I probably dont need this yet. If this is a concern for the user would you ever recommend running a cluster with 2 nodes in a single site?

[CyberDem0n]

Making backups every 5 minutes is not the brightest idea ever.
Even on small clusters there is a chance that it wouldn't finish so fast.

I would recommend you first to read postgres documentation chapter about Continuous Archiving and Point-in-Time Recovery (PITR): https://www.postgresql.org/docs/current/continuous-archiving.html

[redscaresu]

Making backups every 5 minutes is not the brightest idea ever.
Even on small clusters there is a chance that it wouldn't finish so fast.

I would recommend you first to read postgres documentation chapter about Continuous Archiving and Point-in-Time Recovery (PITR): https://www.postgresql.org/docs/current/continuous-archiving.html

OK thanks @CyberDem0n finger in the air (obviously depends on CPU and mem performance) what is the most frequent sensible WAL backup frequency.

[mabushey]

I've always used nightly backups during the quiet time, usually around 4am. I have run DB's on ZFS and created snapshots every 5 minutes. ZFS snapshots are nearly instantaneous. ZFS compression also drastically speeds up DB performance on spinning rust.