deleted my postgres database by accident, any way to recover?
title says it all :p
Solution:Jump to 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
44 Replies
Project ID:
N/A
is there anyway i can download the logs for a week? i can recover my database from that
maybe try using this:
https://bookmarklets.up.railway.app/log-downloader/
F
do you work for gitlab?
I'm just wondering
nope
its a side project and i was messing with some dangerous sql queries hehe but wanted to recover
alright, "the gitlab employee" is just a reference to the 2017 accident when they deleted production db
its hard to host a database at railway because of the no backup thing
there is a backup template though
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
@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.
Is there any ETA on when native backups will be made possible?
no sorry I don't have an eta
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!
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
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?
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
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?
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
Great, thank you. I will give it a try and update the thread if I find a solution.
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.
the original database that was deleted, how was it originally created? from deploying a database or from a migration process?
it was deployed
manually from the within the + new button?
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
you can use an older version of postgres on railway, I just need to know exactly how the database was deployed originally
with the add new button
how was the new database you created deployed
the same way, through add new +
what's that database's source image?
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.
remove it then put this
ghcr.io/railwayapp-templates/postgres-ssl:15
back inwill do!
Ok, now I'm getting a the following in the deploy log:
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
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...
and ok, will try this one
yep I did ask if the original database was the result of a migration
Yeah, my bad. I forgot about that, but yes that's the only migration it had been through.
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 😦
Great, will give it a shot and hope for the best. Thank you for all the help in any event!
Ok, it seems to be working, although I am getting this error in the logs, which presumably means that the pw is incorrect?
looks like you need to set a service variable
RAILWAY_RUN_UID
to 0
(on the database service)