R
Railway•10mo ago
pierre

deleted my postgres database by accident, any way to recover?

title says it all :p
Solution:
but the final answer would be, if you didn't take a database backup yourself and you where responsible for the data loss, the data is unfortunately gone
Jump to solution
44 Replies
Percy
Percy•10mo ago
Project ID: N/A
pierre
pierre•10mo ago
is there anyway i can download the logs for a week? i can recover my database from that
pierre
pierre•10mo ago
F
maddsua
maddsua•10mo ago
do you work for gitlab? I'm just wondering
pierre
pierre•10mo ago
nope its a side project and i was messing with some dangerous sql queries hehe but wanted to recover
maddsua
maddsua•10mo ago
alright, "the gitlab employee" is just a reference to the 2017 accident when they deleted production db
pierre
pierre•10mo ago
its hard to host a database at railway because of the no backup thing
Brody
Brody•10mo ago
there is a backup template though
Solution
Brody
Brody•10mo ago
but the final answer would be, if you didn't take a database backup yourself and you where responsible for the data loss, the data is unfortunately gone
Pepijn
Pepijn•10mo ago
@Brody In this article https://blog.railway.app/p/launch-01-next-gen-databases there is mentioned "In the future, we plan to add native support for database backups and high availability by following the same pattern: Add the primitives that can be used by anyone.".
Railway Blog
The Future of Databases is Services
Starting today, it’s now possible to deploy ANY type of database on Railway, including new technologies like PGVector, Chroma, Temporal, etc.
Pepijn
Pepijn•10mo ago
Is there any ETA on when native backups will be made possible?
Brody
Brody•10mo ago
no sorry I don't have an eta
specca
specca•10mo ago
Hi, unfortunately I'm in the same boat... I had two projects open and deleted my instance of psql from the wrong one. I do still have a volume, 'Postgres data' which is present in my project dashboard and I can 'mount to service'. Is there any way that I can use this to restore the previous data? Thanks in advance for any help!
Brody
Brody•10mo ago
that is the data from the database, I think you could just spin up another database, delete it's volume, then mount your current left over volume to the service and it would work, but you would need to know the old username and password
specca
specca•10mo ago
Ok, that's promising. Thanks. I do have a record of the username and pw. Is there anything I'd need to do beyond just updating that info within the Postgres variables tab on my project dashboard?
Brody
Brody•10mo ago
those variables are single set and would have no effect, thats why you would need to use the old username and password when you connect to it
specca
specca•10mo ago
Sorry, I'm not sure I understand. I have a new postgres service running in the project, which has a new set of variables, e.g. the db url, host name, port, etc. I have also mounted the pgdata to this service (by clicking mount in the dashboard). When you say that I need to use the old username and password, do you mean that I update the existing POSTGRES_USER and POSTGRES_PASSWORD variables within this service to match those of my previous service?
Brody
Brody•10mo ago
you don't strictly need to, but it would be easier that way I suppose I was just able to use a stray volume with data on it in a new postgres database and access the data so yes entirely possible as long as you know the old username and password
specca
specca•10mo ago
Great, thank you. I will give it a try and update the thread if I find a solution.
specca
specca•10mo ago
Hi, I've tried mounting the data to a postgres service, however have been getting these errors in my deploy logs and don't know how I can rectify them? (presumably by either upgrading the pg database or using v15 of psql for the deployment - not sure how I can configure any of this within railway). Thanks in advance for any help.
No description
Brody
Brody•10mo ago
the original database that was deleted, how was it originally created? from deploying a database or from a migration process?
specca
specca•10mo ago
it was deployed
Brody
Brody•10mo ago
manually from the within the + new button?
specca
specca•10mo ago
out of curiosity, is it possible to download the volume of data? I was thinking if so, I could try and access it locally from within a docker container where I can try and either use the older version of postgres or upgrade the database to work with v16 Yes. the db was created, and schema would have been pushed from prisma using prisma db push
Brody
Brody•10mo ago
you can use an older version of postgres on railway, I just need to know exactly how the database was deployed originally
specca
specca•10mo ago
with the add new button
Brody
Brody•10mo ago
how was the new database you created deployed
specca
specca•10mo ago
the same way, through add new +
Brody
Brody•10mo ago
what's that database's source image?
specca
specca•10mo ago
GitHub
Build software better, together
GitHub is where people build software. More than 100 million people use GitHub to discover, fork, and contribute to over 420 million projects.
From An unknown user
From An unknown user
From An unknown user
Brody
Brody•10mo ago
remove it then put this ghcr.io/railwayapp-templates/postgres-ssl:15 back in
specca
specca•10mo ago
will do!
specca
specca•10mo ago
Ok, now I'm getting a the following in the deploy log:
No description
Brody
Brody•10mo ago
I thought so, it looks like this database was the result of a migration or you did deploy a timescale database on purpose use this image instead ghcr.io/railwayapp-templates/timescale-postgis-ssl:pg15-ts2.12
specca
specca•10mo ago
Perhaps it is because of the railway database v2 migration? (https://community.railway.app/announcements/databases-v2-migration-983074c7)
Railway Community Forum
Databases V2 Migration
Hey all, We're improving how databases work on Railway, and a migration to the new version will be required by 31st Jan, 2024. Here's what you need to know: Databases in its current form will become a legacy feature on 31st Jan, 2024. All databases will become regular Railway services with a volume attached. You are required to migrate if yo...
specca
specca•10mo ago
and ok, will try this one
Brody
Brody•10mo ago
yep I did ask if the original database was the result of a migration
specca
specca•10mo ago
Yeah, my bad. I forgot about that, but yes that's the only migration it had been through.
Brody
Brody•10mo ago
then the image I sent above is what is used in the database created by the migration, if it doesn't work I'm out of ideas 😦
specca
specca•10mo ago
Great, will give it a shot and hope for the best. Thank you for all the help in any event!
specca
specca•10mo ago
Ok, it seems to be working, although I am getting this error in the logs, which presumably means that the pw is incorrect?
No description
Brody
Brody•10mo ago
looks like you need to set a service variable RAILWAY_RUN_UID to 0 (on the database service)
Want results from more Discord servers?
Add your server