No databases in Postgres after restoring from a snapshot

[korkybuchek]

After the hardware failure a lot of people already experienced, I had a Postgres app that stopped working. I restored a snapshot from it to another Postgres app in hopes to get my data.

The latest snapshot failed to get restored due to some versioning issue. I managed to restore an older snapshot and successfully get a new Postgres online.

The problem is that now when I connect to it and see what schemas are existing on it, I only see the service ones (the ones that seem to be coming with every Postgres installation). The database I was using for my web app is missing. The snapshot doesn’t seem empty - in fact, it says it’s around 400MB in size. And yet, the data is not there.

Am I missing something? Where could my database be? Is it gone? Or can I still bring it back somehow?

[mayailurus]

Hi… People do run into this every so often, unfortunately, . The following recent mega-thread should provide several ideas of things to try:

https://community.fly.io/t/reuse-old-volume-in-new-machine-after-irrepairable-hardware-damage/21230

It’s admittedly a long read, but all three users did get their data back in the end…

[korkybuchek]

Ok, that did it - thanks @mayailurus !

Let me summarise that happened - basically a summarisation of the thread posted above. That way people can get help faster (and fast is good when your whole DB is down).

TL;DR

Between Postgres image versions, the path the data was saved to, changed and the new images didn’t see my old data. The easiest way to fix it is to use the --image-ref flyio/postgres-flex:15.1 while restoring (not sure how well this is going to age - there might be another version mismatch in the future).

What I did prior to creating this thread

I listed my existing snapshots for the volume used by my DB:

$ fly volumes list -a my-app-name

Using the latest snapshot, I created a new Postgres app:

$ fly postgres create --snapshot-id snap-id

This ran for a while, but ultimately the new app started crash looping. The reason I saw in the logs was the infamous:

FATAL: database “repmgr” does not exist
WARNING: database “postgres” has a collation version mismatch
DETAIL: The database was created using collation ...

I read that this was due to some version mismatch with Postgres and tried the same with an older image:

fly postgres create --snapshot-id vs_yGwPjg6koZ0ZHjwAgb --image-ref flyio/postgres:13

@mayailurus - I just used postgres 13 because I saw it in other thread. I didn’t manage to find all images supported by Fly.io. Maybe that’s an improvement on the documentation side that would help?

At that point I landed on the situation in this thread. The new Postgres application was up, but didn’t have my data.

As correctly identified in the other thread, the problem was that the Posgres data moved paths in the different versions:

 $ fly ssh console -C 'ls -l /data/postgresql/PG_VERSION /data/postgres/PG_VERSION' -a gps-assistant-db
Connecting to fdaa:0:e6ba:a7b:3e:3eab:cf20:2... complete
-rw------- 1 stolon stolon 3 Aug 13 06:50 /data/postgres/PG_VERSION
-rw------- 1 stolon stolon 3 Jun 22  2023 /data/postgresql/PG_VERSION

Now, I assume one could somehow copy the data over from the old location - either dump it elsewhere, or maybe even symlink it within the running app. However, I wasn’t comfortable with that, so I forked the volume and setup a new Postgres app with it, also using the flex postgres 15 image. Nicely enough, it can be done with a simple command:

fly postgres create --fork-from <app-name>:<valume-id> --image-ref flyio/postgres-flex:15.1

That create a new application with all my previous data intact.