Point-In-Time Recovery without Cloning?

[rsteppac]

I am having some difficulties wrapping my head around the PITR process...

The primary issue that I have is that I could not find out how to restore a DB to a point in time without cloning it. The clone certainly is a great way to test whether the restored point-in-time actually fixes whatever problem forces the recovery, but ultimately the actual production DB (cluster) has to be point-in-time restored? How can we perform a PITR for the production DB "in place"?

I am also struggling with a detail of the cloning process: It appears the only way to specify S3 storage coordinates (endpoint, id, key, etc.) for the point-in-time restore is via the clone cluster parameters. The S3 coordinates supplied in the env vars AWS_SECRET_ACCESS_KEY and AWS_ACCESS_KEY_ID don't seem to be used for the cloning case. What is the recommended way to clone from S3 storage without exposing the key in the cluster resource?
(It is easy enough to find out that the cloning attributes get translated into environment variables CLONE_XXX by the operator. Though I did not manage to trace them through Spilo to wal-g. Spilo uses them to set up a test docker container. But I was not able to find their use in the Spilo code. Nor are they listed in the documentation. I found no trace at all in the wal-g project.)

UPDATE: I just exercised the scenario of complete data loss in the pgdata mount point and on restart the Cluster contents were successfully restored via WAL-G from WAL backups on S3 storage. Which makes it an ever bigger mystery to me why this should not work when cloning unless the CLONE_XXX env variables get populated?

Thanks in advance!
Ralf

[Jan-M]

There is no in place, this is not supported by Postgres. You can delete and then restore to desired point in time, which is essentially clone/restore to same name.

[rsteppac]

We have done this but in 1 out of 1 tests we observed a weird phenomena after finishing the cycle

1. Undeploy "production" postgresql resource with cascade to DB cluster resources (i.e. simulate disaster)
2. Deploy clone: original postgresql resource + clone section to restore removed prod DB cluster
3. Verify restored DB (looked OK)
4. Deploy original prod cluster postgresql resource (via ArgoCD) to go full circle into original state (only delta is the clone section)

The DB logs of the restart after step 4 warn about disabled WAL backups. And indeed no backups are pushed to our S3 storage anymore. We can trigger them manually no problem. But automatic backups are broken. We have not had time to investigate the root cause for this yet.