Postgres data lost after a few days of delayed payment. Please help 😣
I have a postgres database running, I hadn't realized that the services were down due to none payment. After paying and resuming the services, I have noticed that my production environment database has no tables. Is this supposed to happen? Please note that I did have database backups set up but for some odd reason the files are also empty and the backup is only 20B.
44 Replies
Project ID:
32e736d8-c921-4437-bbeb-a2aace884a5f
32e736d8-c921-4437-bbeb-a2aace884a5f
the 20 byte backups are likely indicative of incorrect connection details for the backup service, and then you've ended up with tiny files that probably contain text that says something about a database connection failing
either way, where are you viewing these missing tables?
"No Tables" is appearing on the railway console, which I believe is an indicator that the database is empty.
the data tab is not to be trusted and ideally shouldn't really be trusted, please check with a proper desktop database management client like dbgate or pgadmin
Let me try to do that
I am unable to connect to the database. I am not sure what the problem but I am attempting to restart the database from the railway console.
railway doesnt have a console, perhaps you meant from the plugins settings?
I also noticed my backups were 20B and i think its because of an issue with the "postgres backup s3 template" I believe, im looking into it at the moment
I noticed after trying a lot of stuff that there is a version mismatch between the dockerfile of the "postgres backup s3 template" and the pg_dump version that the postgres database service uses
As you can see from this error
postgresql-client is a package from linux alpine which is 13.12 at the moment
source: https://pkgs.alpinelinux.org/package/v3.14/main/x86_64/postgresql-client)
------
So we need to be able to either update the pg_dump version of the "postgres backup s3 template" or the pg_dump version of the postgres service
current postgres database version is 16
pg_dump needs to be updated it would seen
There is no version 16 for the alpine package "postgresql-client" it seems, only 15 from what ive seen
pg_dump 15x is very likely compatible with postgres 16
just pg_dump 13x is likely not
Ah ill test it tommorow, i hope it works 😄
if you get it working, pr it!
Will do 😉
Ah im still having the error
I guess i will have to create a dockerfile that creates a lower version postgres database perhaps so it matches the dockerfile of the S3 backup template
Or the docker image that railway is using should maybe give the option to change the version of postgres in the railway UI?
Alright i think i fixed it
I see theres also images provided by railway for postgres for version 15
https://github.com/railwayapp-templates/postgres-ssl/pkgs/container/postgres-ssl/131617458?tag=15
So to make it work you need to create a new service with that image, so that you can get pg_dump v15 on your database service
And in the "S3 backup template" the dockerfile should run this to also get pg_dump v15 there (postgresql15-client)
I will make a pull request later today or tommorow
having to run a postgres 15 database is not a suitable solution, I appreciate the effort but I don't think a pr should be made if your fix won't work with the default postgres database deployment
Now im getting this error on my directus service
when did you deploy this directus template
it was deployed together with the postgres v16 db service that was inside the template
but i replaced the v16 with a new postgres v15 db service
i dont think deploying postgres 15 is a valid workaround to get the backup service working
How would you fix this issue?
use a version of pg_dump that supports postgres 16
There is no package for that it seems, postgresql16-client doesnt exist in alpine
I also tried with debian instead of alpine but i couldnt get it to work either
im sure there is somewhere lol
Do you know how to make pg_dump v15 and v16 compatible?
not yet
Ill also take a look
first, i have to test the current backup template without any modifications
yes it does
https://pkgs.alpinelinux.org/package/edge/main/x86_64/postgresql16-client
Oh damn, crazy, i mustve been half asleep yesterday evening
though when using binarys for postges 16, it still produces 20 byte files
i have fixed the code, will polish it up later and maybe add a selectable pg_dump version
Awesome
I will also make sure it works to backup a database that's only accessible over the private network
I made a project with v16 postgresql database and v16 in my "s3 backup" dockerfile, works great
Added it like this (first picture)
But still only 20B tar.gz files in my S3 bucket
How did you solve the 20B issue?
instead of me explaining, I'll make my pr later tonight, you can fork the pr and use that as the source to your backup service
I like that plan!
@ElGato - here you go! https://github.com/railwayapp-templates/postgres-s3-backups/pull/11
@Brody should i use the public database url instead if im getting this error?
Its the logs of the s3 backups service
ah yes, this is my bad, i should have told you
https://discord.com/channels/713503345364697088/1170943727867547719/1170944676606517339
Awesome it worked 😎
Great fix @Brody, I also learned a lot from this bug and your solution
thank you 🙂
How long have you been developing in general?
not long, 5 years maybe
Good skills you got and level headed 👍