R
Railway•2y ago
meijin

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
Percy
Percy•2y ago
Project ID: a4502355-3a2e-4b1a-8423-538dbf6e55ec
Brody
Brody•2y ago
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?
meijin
meijinOP•2y ago
OK, I'll try restart plugin. No, I had upgraded to the dev plan over 1 year ago.
Brody
Brody•2y ago
okay cool
meijin
meijinOP•2y ago
Is it that you said?
Brody
Brody•2y ago
yes
meijin
meijinOP•2y ago
I restarted postgres plugin, but redeploy still failed... And my App still stopped.
Brody
Brody•2y ago
did it fail during build or during deployment
meijin
meijinOP•2y ago
Maybe during build. Here is part of Build Logs that shows 'error command failed' (and hidden the real hostname)
meijin
meijinOP•2y ago
There is additional log.
Brody
Brody•2y ago
show me the table at the very top of the build please no cropped screenshots again
meijin
meijinOP•2y ago
It is top of the build. Correct?
Brody
Brody•2y ago
oh dockerfile would have been helpful to know that before hand lol please send your dockerfile
meijin
meijinOP•2y ago
OMG. Here is my dockerfile!
FROM node:lts

RUN mkdir /src
RUN mkdir -p /src/server/credentials

ARG PORT
ARG API_SERVER_PORT
ARG API_BASE_PATH
ARG DATABASE_URL
ARG FIREBASE_CREDENTIAL_BASE64
ARG GOOGLE_APPLICATION_CREDENTIALS

WORKDIR /src

COPY . .
RUN yarn install --cwd server

RUN echo $FIREBASE_CREDENTIAL_BASE64 | base64 -di > ./server/credentials/firebase-adminsdk-credentials.json
RUN yarn run build:server

EXPOSE 80
CMD yarn run start:server
FROM node:lts

RUN mkdir /src
RUN mkdir -p /src/server/credentials

ARG PORT
ARG API_SERVER_PORT
ARG API_BASE_PATH
ARG DATABASE_URL
ARG FIREBASE_CREDENTIAL_BASE64
ARG GOOGLE_APPLICATION_CREDENTIALS

WORKDIR /src

COPY . .
RUN yarn install --cwd server

RUN echo $FIREBASE_CREDENTIAL_BASE64 | base64 -di > ./server/credentials/firebase-adminsdk-credentials.json
RUN yarn run build:server

EXPOSE 80
CMD yarn run start:server
Brody
Brody•2y ago
I think I see the problem, what kind of app is this again though
meijin
meijinOP•2y ago
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.
Brody
Brody•2y ago
sounds good, I will make changes to your provided dockerfile when I get back to my computer
meijin
meijinOP•2y ago
OK, thanks.Just to be thorough, I'll post a screenshot of the error log that shows the full context.
Brody
Brody•2y ago
show me your service variables please
meijin
meijinOP•2y ago
Is it OK?
Brody
Brody•2y ago
you are using references so that's good show me your schema.prisma file?
meijin
meijinOP•2y ago
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.
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}

generator client {
provider = "prisma-client-js"
previewFeatures = ["interactiveTransactions"]
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}

generator client {
provider = "prisma-client-js"
previewFeatures = ["interactiveTransactions"]
}
Brody
Brody•2y ago
looks good to me did you recently update the prisma dependency?
meijin
meijinOP•2y ago
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.
Brody
Brody•2y ago
try updating it?
meijin
meijinOP•2y ago
Update prisma/client and redeploy today? No.
Brody
Brody•2y ago
what? I'm asking you to update your prisma client dependency
meijin
meijinOP•2y ago
Oh, sorry Try it!
Brody
Brody•2y ago
let me know how it goes
meijin
meijinOP•2y ago
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.
Brody
Brody•2y ago
but they are using different databases, right?
meijin
meijinOP•2y ago
Yes!
Brody
Brody•2y ago
but you said you can connect to this database with some tool you mentioned
meijin
meijinOP•2y ago
Yes. I use Tableplus. I can connect both dev and production databases. And can run SQL.
meijin
meijinOP•2y ago
Failed...and same Build Log.
Brody
Brody•2y ago
do you use the exact same dockerfile for the other enviroment
meijin
meijinOP•2y ago
Oh, I use same Dockerfile at dev and production. It is put on root of my project.
Brody
Brody•2y ago
can you add a second postgre database to your project, and switch over to using the new database with the variable references
meijin
meijinOP•2y ago
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?
Brody
Brody•2y ago
data will be missing so dont worry about that yet, this is just to test if the build will not fail
meijin
meijinOP•2y ago
OK!
meijin
meijinOP•2y ago
The new DB plugin name is Postgres-1 so is this setting correct?
Brody
Brody•2y ago
let me check yes correct
meijin
meijinOP•2y ago
It succeeded...!!
meijin
meijinOP•2y ago
This might be unrelated, but the older Postgres was created around January 2022.
Brody
Brody•2y ago
can you manually move the data over to the new database with that software you are using?
meijin
meijinOP•2y ago
I'll give it a try, but it might be challenging given the large volume of data.
Brody
Brody•2y ago
how large are we talking?
meijin
meijinOP•2y ago
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.
Brody
Brody•2y ago
okay lets ask how the team could help out here since the malfunctioning database is not your fault
meijin
meijinOP•2y ago
thanks
Percy
Percy•2y ago
Flagging this thread. A team member will be with you shortly.
Brody
Brody•2y ago
they should just be able to upgrade the old database instance, or move your data for you
meijin
meijinOP•2y ago
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.
Brody
Brody•2y ago
are you a part of a railway team
meijin
meijinOP•2y ago
No. lol
Brody
Brody•2y ago
from the sounds of it you are running a commercial product on railway, so you would need to be on a team plan
meijin
meijinOP•2y ago
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.
Brody
Brody•2y ago
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
meijin
meijinOP•2y ago
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?
Brody
Brody•2y ago
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
meijin
meijinOP•2y ago
OK.
Brody
Brody•2y ago
also, you will get direct support from the railway engineers, so you want have to go through me 🙂
meijin
meijinOP•2y ago
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?
Brody
Brody•2y ago
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
meijin
meijinOP•2y ago
I immediately subscribed to the Team Plan and transferred my project. However, it still shows "Developer Plan" in the Billing section. Is this correct?
meijin
meijinOP•2y ago
↑ in my personal account, so I must cancel subscription in my personal account, ok?
Brody
Brody•2y ago
do you have two railway accounts?
meijin
meijinOP•2y ago
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?
Brody
Brody•2y ago
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
meijin
meijinOP•2y ago
oh, OK. I see
Brody
Brody•2y ago
@Angelo - pinging for visibility 🙂 could it be possible to upgrade their old database instance that was created in 2022?
meijin
meijinOP•2y ago
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.
Brody
Brody•2y ago
will do
meijin
meijinOP•2y ago
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)
Brody
Brody•2y ago
unless you are a part of business class, im not aware of any SLAs or SLOs https://docs.railway.app/reference/support#business-class
angelo
angelo•2y ago
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.
meijin
meijinOP•2y ago
Oh... I see
Want results from more Discord servers?
Add your server