@mabis and @mayailurus, thanks again for your help working through this. With your suggestions and some related links in this forum, I ended up recovering my data by using the technique described here: Can't recreate pg from snapshot: "The database was created using collation version 2.31, but the operating system provides version 2.36" . Basically, trying fly pg create
but choosing a different--image-ref
than the one reported by fly image show
. (For me, fly image show
returned flyio/postgres-flex 15.3
, but I was able to recover my data with the same ref in the linked post, flyio/postgres-flex:15.1@sha256:4af8e07ae57ff7d31228b32ceebd34bf7508c131bc86f67c2025c669b56eff70
. )
After that, I had a running machine with my data on it. (That snapshot was 18 hours old but the data doesn’t change much.)
I had to do a bit of massaging to get my Rails app to use the new machine:
- Update the
DATABASE_URL
environment variable to use the new machine - Stop the machine … because it worked, but the application was looking in the wrong database
- Copy data from one Postgres database to another. I think this was because of how I migrated from Heroku in the past: it was using a database named after the fly app, but Rails’s default is to use a database named after the Rails app. I probably could have changed a Rails config instead, but I figured I’d be doing myself a favor to use the Rails default. I did this using pgAdmin as described here: postgresql - Copy a table from one database to another in Postgres - Stack Overflow
- Restart the machine now that the data was present
Now, everything is running smoothly again, with all my data in-tact.
@mabis and @mayailurus, thanks so much for helping me ! I thought I’d be spending the next couple of days trying to scrape data together from previous reports, etc, but now everything is working properly.
Next, I’ll get my fly setup improved so I’ll have redundancy if this happens again!