Railway•13mo ago

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•13mo ago

Project ID: 32e736d8-c921-4437-bbeb-a2aace884a5f

Doug•13mo ago

32e736d8-c921-4437-bbeb-a2aace884a5f

Brody•13mo 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•13mo ago

"No Tables" is appearing on the railway console, which I believe is an indicator that the database is empty.

Brody•13mo 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•13mo 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•13mo ago

railway doesnt have a console, perhaps you meant from the plugins settings?

ElGato•12mo 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•12mo ago

current postgres database version is 16 pg_dump needs to be updated it would seen

ElGato•12mo ago

There is no version 16 for the alpine package "postgresql-client" it seems, only 15 from what ive seen

Brody•12mo ago

pg_dump 15x is very likely compatible with postgres 16 just pg_dump 13x is likely not

ElGato•12mo ago

Ah ill test it tommorow, i hope it works 😄

Brody•12mo ago

if you get it working, pr it!

ElGato•12mo 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•12mo 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•12mo ago

Now im getting this error on my directus service

Brody•12mo ago

when did you deploy this directus template

ElGato•12mo 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•12mo ago

i dont think deploying postgres 15 is a valid workaround to get the backup service working

ElGato•12mo ago

How would you fix this issue?

Brody•12mo ago

use a version of pg_dump that supports postgres 16

ElGato•12mo 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•12mo ago

im sure there is somewhere lol

ElGato•12mo ago

Do you know how to make pg_dump v15 and v16 compatible?

Brody•12mo ago

not yet

ElGato•12mo ago

Ill also take a look

Brody•12mo 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•12mo ago

Oh damn, crazy, i mustve been half asleep yesterday evening

Brody•12mo 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•12mo ago

Awesome

Brody•12mo ago

I will also make sure it works to backup a database that's only accessible over the private network

ElGato•12mo 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?