PITR recovery issues

[james beedy]

Playing around with the PITR, I can't seem to get my db to recover correctly to a new application of postgresql.

I can't seem to find a correlation between what source backups you have and how to restore them to another db.

For example, when I list backups on my source db it shows: https://paste.ubuntu.com/p/sKdwKSyVy8/

Which looks great, I can see the backups being generated in the s3 bucket, but how do I know which portion of that output corresponds to what I need to run to restore a new postgresql unit to a point in time.

On restoring my new application, I'm running the command:

`juju run-action --wait postgresql/3 wal-e-restore storage-uri=s3://<source-s3-pitr-bucket> target-time='2018-03-05 00:10:20.000000+00' confirm=true`

where the time stamp doesn't seem to matter ... I can go forward or backward in time before and after I've made wal-e backups and have the same result of the database not being able to start post restore.

(possibly I'm doing this wrong somehow, no matter what point in time I try to restore to, my target database wont start following the restore http://paste.ubuntu.com/p/NHM3XwX6yH/)

I've ensure that storage is sufficient on the target unit, and that the postgresql version is consistent with the source. The target and source postgresql applications are configured to use buckets with the same aws creds and same region.

Not sure where I'm going wrong here. Any insight would be helpful, thanks.

[james beedy (jamesbeedy)]

So I just got a restore to work .... I'm pretty sure I had it borked due to a timestamp skew of some sort on my end. I think the issue was I needed to specify a timestamp ?close? to that of the latest backup. You can close this if you like.

[Stuart Bishop (stub)]

2018-03-06 00:09:29 UTC [20296]: [6-1] db=,user= FATAL: requested recovery stop point is before consistent recovery point

If the database is quite new or WAL archiving just enabled, such as testing or CI systems, then there might not be a recovery point to restore too. The charm should force or wait for one if necessary before kicking off the pg_basebackup. Its somewhat surprising that pg_basebackup does't handle this.