PITR recovery issues

Bug #1753606 reported by james beedy
This bug affects 1 person
Affects Status Importance Assigned to Milestone
PostgreSQL Charm

Bug Description

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.

Revision history for this message
james beedy (jamesbeedy) wrote :

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.

Revision history for this message
Stuart Bishop (stub) wrote :

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.

Changed in postgresql-charm:
status: New → Triaged
importance: Undecided → High
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Duplicates of this bug

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.