Where is our Data? (Postgres Database Volume 0MB)

The next step, assuming that the mount point that you saw was /data and that the amount used was relatively small, would be…

fly sftp shell -a new-db
cd /
get data
^D

(That last line is Ctrl+D, the end-of-file/end-of-stream character.)

At this point you should have a file named data.zip on your local machine.

Warning: this .zip file may contain passwords and SSH private keys, so handle it with care!

Thus, you would be in the situation of the following Stack Overflow post:

https://serverfault.com/questions/336817/how-to-restore-a-file-system-level-copy-of-a-postgresql-database-not-dump-to-a

That’s for Windows instead of local Linux, and you would need to install your own Postgres binaries instead of having them in the .zip file, but the overall concepts would be the same.


(The gotcha with version mismatches that the answer mentions really is important, by the way. That’s one of the few things that I dislike about Postgres.)

There may turn out to be some head-scratching over what exactly to use for -D… You may find that you have multiple Postgres clusters within that .zip file, the reason being that many Postgres containers have entrypoint magic that creates a completely new one if it doesn’t find exactly what it was looking for (like with obiwan, :sparkles:).

This would best be resolved by looking at the filesystem timestamps (back on new-db) and going with the oldest.

(The PG_VERSION files will tell you which exact version of Postgres you need to install, incidentally.)


Once you have things running in a local Postgres instance, you are mostly home free… Assuming, again, that this is a relatively small amount of data, it is said that it could be uploaded all in one fell swoop:

https://community.fly.io/t/how-copy-local-postgres-db-to-fly-io/13074/5

(If it was larger, then I would have doubts about how it would resume failed transfers, and the like.)

The -a target in this case would be a freshly created (by you) even-newer-db, or such, as alluded to above. You may want to attach even-newer-db to your application before beginning the import, to ensure that the expected roles are in place—and similar. (I admittedly haven’t investigated this aspect in any detail.)

Finally, a couple other wrinkles that you may or may not encounter:

https://community.fly.io/t/postgres-flex-database-postgres-has-a-collation-version-mismatch/14391

https://community.fly.io/t/how-to-retain-roles-owns-with-fly-pg-import/16156

Hope this helps!

1 Like