R
Railway13mo ago
Doug

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
Percy
Percy13mo ago
Project ID: 32e736d8-c921-4437-bbeb-a2aace884a5f
Doug
Doug13mo ago
32e736d8-c921-4437-bbeb-a2aace884a5f
Brody
Brody13mo ago
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?
Doug
Doug13mo ago
"No Tables" is appearing on the railway console, which I believe is an indicator that the database is empty.
Brody
Brody13mo ago
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
Doug
Doug13mo ago
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.
Brody
Brody13mo ago
railway doesnt have a console, perhaps you meant from the plugins settings?
ElGato
ElGato13mo ago
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
'pg_dump: error:
server version: 16.0 (Debian 16.0-1.pgdg120+1);
pg_dump version: 13.12' ( <-- version of "postgresql-client" which is used in the dockerfile of the "postgres backup s3 template")
'pg_dump: error:
server version: 16.0 (Debian 16.0-1.pgdg120+1);
pg_dump version: 13.12' ( <-- version of "postgresql-client" which is used in the dockerfile of the "postgres backup s3 template")
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
Brody
Brody13mo ago
current postgres database version is 16 pg_dump needs to be updated it would seen
ElGato
ElGato13mo ago
There is no version 16 for the alpine package "postgresql-client" it seems, only 15 from what ive seen
Brody
Brody13mo ago
pg_dump 15x is very likely compatible with postgres 16 just pg_dump 13x is likely not
ElGato
ElGato13mo ago
Ah ill test it tommorow, i hope it works 😄
Brody
Brody13mo ago
if you get it working, pr it!
ElGato
ElGato13mo ago
Will do 😉 Ah im still having the error
pg_dump:
error: aborting because of server version mismatch
'pg_dump: detail:
server version: 16.0 (Debian 16.0-1.pgdg120+1);
(dockerfile ->) pg_dump version: 15.4'
pg_dump:
error: aborting because of server version mismatch
'pg_dump: detail:
server version: 16.0 (Debian 16.0-1.pgdg120+1);
(dockerfile ->) pg_dump version: 15.4'
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)
RUN apk add --update --no-cache postgresql15-client nodejs npm
RUN apk add --update --no-cache postgresql15-client nodejs npm
I will make a pull request later today or tommorow
Brody
Brody13mo ago
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
ElGato
ElGato13mo ago
Now im getting this error on my directus service
No description
Brody
Brody13mo ago
when did you deploy this directus template
ElGato
ElGato13mo ago
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
Brody
Brody13mo ago
i dont think deploying postgres 15 is a valid workaround to get the backup service working
ElGato
ElGato13mo ago
How would you fix this issue?
Brody
Brody13mo ago
use a version of pg_dump that supports postgres 16
ElGato
ElGato13mo ago
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
Brody
Brody13mo ago
im sure there is somewhere lol
ElGato
ElGato13mo ago
Do you know how to make pg_dump v15 and v16 compatible?
Brody
Brody13mo ago
not yet
ElGato
ElGato13mo ago
Ill also take a look
Brody
Brody13mo ago
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
ElGato
ElGato13mo ago
Oh damn, crazy, i mustve been half asleep yesterday evening
Brody
Brody13mo ago
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
ElGato
ElGato13mo ago
Awesome
Brody
Brody13mo ago
I will also make sure it works to backup a database that's only accessible over the private network
ElGato
ElGato13mo ago
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?
No description
ElGato
ElGato13mo ago
No description
Brody
Brody13mo ago
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
ElGato
ElGato13mo ago
I like that plan!
ElGato
ElGato12mo ago
@Brody should i use the public database url instead if im getting this error?
No description
ElGato
ElGato12mo ago
Its the logs of the s3 backups service
ElGato
ElGato12mo ago
Awesome it worked 😎 Great fix @Brody, I also learned a lot from this bug and your solution
Brody
Brody12mo ago
thank you 🙂
ElGato
ElGato12mo ago
How long have you been developing in general?
Brody
Brody12mo ago
not long, 5 years maybe
ElGato
ElGato12mo ago
Good skills you got and level headed 👍
Want results from more Discord servers?
Add your server