Unable to access the Postgres database only from my App but can access from local machine
I'm seeking assistance regarding connectivity issues with a Postgres database. When deploying my application, as well as while it's in operation, I'm unable to access the Postgres database. However, I can access it from my local DB client using the same DATABASE_URL.
The project ID is a4502355-3a2e-4b1a-8423-538dbf6e55ec.
During the application's deployment, I attempt to execute a migrate using Prisma, but I'm encountering the following error, which halts the process:
Error: P1001: Can't reach database server at containers-us-west-XXXX.railway.app:XXXX.
The operating application also stops, which I suspect is due to the same issue (as the API times out). The application refers to the environment variable DATABASE_URL, which is set as ${{Postgres.DATABASE_URL}}.
The issue started around 13:00 JST on June 2, as I received inquiries from service users around that time.
Interestingly, I can successfully connect to the database when using the TablePlus client, which leads me to believe the DB is functioning normally. Yet, even if I copy the connection info from TablePlus and set it to the DATABASE_URL in the application's Environment Variables, and redeploy, the same error occurs. Based on these findings, it appears the DB is functioning normally, but for some reason, it's become inaccessible from the application server (and the deployment environment).
Could it be due to some sort of network migration? Could you please guide me on how to handle this situation?
78 Replies
Project ID:
a4502355-3a2e-4b1a-8423-538dbf6e55ec
you can always try a good old restart, the restart button is in the plugin settings
just a quick question too, did you recently upgrade to the dev plan?
OK, I'll try restart plugin.
No, I had upgraded to the dev plan over 1 year ago.
okay cool
Is it that you said?
yes
I restarted postgres plugin, but redeploy still failed...
And my App still stopped.
did it fail during build or during deployment
Maybe during build. Here is part of Build Logs that shows 'error command failed' (and hidden the real hostname)
There is additional log.
show me the table at the very top of the build
please no cropped screenshots again
It is top of the build. Correct?
oh dockerfile
would have been helpful to know that before hand lol
please send your dockerfile
OMG.
Here is my dockerfile!
I think I see the problem, what kind of app is this again though
It's an API server built with a Node.js full-stack framework called frourio. Internally, it uses Prisma. I run the prisma migrate command during the build, but it seems to fail at that point.
sounds good, I will make changes to your provided dockerfile when I get back to my computer
OK, thanks.Just to be thorough, I'll post a screenshot of the error log that shows the full context.
show me your service variables please
Is it OK?
you are using references so that's good
show me your schema.prisma file?
That's right. The strange part is, I haven't deployed or rebooted this API server recently, yet suddenly it can't access the DB from the API. Moreover, attempts to redeploy are failing due to connection issues with the DB.
OK, here. But real table names are cropped.
looks good to me
did you recently update the prisma dependency?
Do you said about prisma client version?
I checked it, and it is very old...
"@prisma/client": "^3.13.0",
Latest update is 2022/04/29.
try updating it?
Update prisma/client and redeploy today? No.
what?
I'm asking you to update your prisma client dependency
Oh, sorry
Try it!
let me know how it goes
I'm currently waiting for the CI to run. I've updated the prisma/client to the latest version. By the way, as additional information, this issue is only happening in Production within the same project, and the dev environment is operating normally. Both production and dev are running on the same source code.
but they are using different databases, right?
Yes!
but you said you can connect to this database with some tool you mentioned
Yes. I use Tableplus. I can connect both dev and production databases. And can run SQL.
Failed...and same Build Log.
do you use the exact same dockerfile for the other enviroment
Oh, I use same Dockerfile at dev and production. It is put on root of my project.
can you add a second postgre database to your project, and switch over to using the new database with the variable references
Hmm, I see. In that case, even if the API were to recover, the data would be empty. So, to restore the service, would I be correct in understanding that a separate data migration would be necessary?
data will be missing so dont worry about that yet, this is just to test if the build will not fail
OK!
The new DB plugin name is Postgres-1 so is this setting correct?
let me check
yes correct
It succeeded...!!
This might be unrelated, but the older Postgres was created around January 2022.
can you manually move the data over to the new database with that software you are using?
I'll give it a try, but it might be challenging given the large volume of data.
how large are we talking?
It's difficult to assert without knowing how to quickly output the size (in bytes) of the data within the database, but there are about three tables each with around 26,000 rows that have a JSON column with images etc. embedded as base64. Do you know a way to output the byte size? For now, I'll try to export all tables using TablePlus.
okay lets ask how the team could help out here since the malfunctioning database is not your fault
thanks
Flagging this thread. A team member will be with you shortly.
they should just be able to upgrade the old database instance, or move your data for you
Okay, that would be helpful, as it looks like the export will take a lot of time. Even though the service has managed to recover with empty data, if users see this, it may cause distrust. If any new entries have been made into the new database by users currently using the service, it would be preferable to add these entries rather than overwrite them during the migration from the old database.
are you a part of a railway team
No. lol
from the sounds of it you are running a commercial product on railway, so you would need to be on a team plan
I see. It's a commercial product indeed, but where is it stated that a team plan is necessary in such a case?
Also, I'd like to know how to migrate. I couldn't find an upgrade button when I checked the billing page.
https://docs.railway.app/reference/plans#developer-plan-offering
The Developer Plan is meant for serious hobbyist use-cases. We do urge companies to upgrade to the Teams plan for a commercial license to use Railway.it uses relatively relaxed language, but recently it has been made into a requirement
I apologize for the detailed question, but while the product I'm operating does generate revenue, it's managed by me personally, not a corporation. Would this also require a team plan?
I totally understand where you're coming from with that question, the name of the plan "team" is very misleading (the railway team is aware of that)
but yes, even if you are a single person, if you are running a commercial product, especially one that generates revenue, it will require a team plan
let's think of it more like a Pro plan
OK.
also, you will get direct support from the railway engineers, so you want have to go through me 🙂
So I would like to join a Team Plan, but what are the specific steps? I would like to create a Team from the Create Team menu, is that all I need to do?
that's all, then you just drag your project into the teams project drag zone in the dashboard
but you can worry about that later, you'll sign up for the team plan sometime soon, that's all that matters
I immediately subscribed to the Team Plan and transferred my project. However, it still shows "Developer Plan" in the Billing section. Is this correct?
↑ in my personal account, so I must cancel subscription in my personal account, ok?
do you have two railway accounts?
No, only my personal account and a team. I have a question that after transferred my project, can I stop subscription at my personal account?
it's usage based, you won't incur any costs if you have nothing deployed on
you will only be charged for the project on the team plan
oh, OK. I see
@Angelo - pinging for visibility 🙂
could it be possible to upgrade their old database instance that was created in 2022?
I will be leaving for the time being, so please let me know when you upgrade the old database and I can connect, or when you migrate the data to the new database.
will do
I managed to manually migrate data from Postgres to Postgres-1. So there is no need to Upgrade the old database, etc. I will stop the old database after monitoring the stable operation of the new database.
This incident has caused distrust among service users. I have also seen cancellations of monthly billing. I would be happy to receive a refund or other action, if possible, in accordance with the SLA.
Translated with www.DeepL.com/Translator (free version)
unless you are a part of business class, im not aware of any SLAs or SLOs
https://docs.railway.app/reference/support#business-class
Hey @meijin - sorry about that, I would be more than happy to give you a refund, unfortunately this happened before you upgraded so we couldn't prioritize your request in time.
Oh...
I see