How to configure production database PostgreSQL in Phoenix app

Hi !
I succeed to set and deploy my Phoenix app with a PostgreSQL database. The migrations are ok on deployment, but when i try to add a comment in a post it’s not working.
I use Docker and Docker Compose and in my docker-compose.yml file i put the local database variables.
I don’t know where to put the production database variables :thinking:

Someone to help me please ? :face_holding_back_tears:

I haven’t had much experience with Phoenix but I recall you can either set secrets in a file or via ENV variables.

In the case of ENV variables, I would use the flyctl secrets ... to set them as ENV variables accessible by your application.

I’am less sure about using the secrets as a file (eg: “prod.secrets.exs”), my assumption is you’d need to include that in your deployment.

Hope this helps! Good luck

If you created your app with fly launch and accepted the “Add postgres database” option flyctl should have created a database and added a DATABASE_URL secret for you. Otherwise, if you have you want to use an already running postgres app. flyctl postgres attach should have the same. To make sure the secret on your exists you can run flyctl secrets list.

Yes i created my app with fly launch with a postgres database but in my docker-compose.yml i added the env variables to work in local, as below :

my docker-compose.yml

services:
app:
build: .
command: mix phx.server
working_dir: /app
ports:
- “4000:4000”
- “4001:4001”
- “8000:8000”
environment:
DATABASE_URL: ecto://postgres:postgres@db/<my_app_name_dev>
SECRET_KEY_BASE: <my_secret_key_base>
volumes:
- .:/app
depends_on:
- db
- mail

so i think that the env variables which are used in production are the ones in my docker-compose.yml, no ?

Hi @sevb just to be clear: your app on Fly.io doesn’t know about your docker-compose.yml file.

If you’ve used fly launch to detect and set up a Phoenix app with a Fly Postgres database, values for SECRET_KEY_BASE and DATABASE_URL will have been generated and set as secrets on your deployed app.

You can check which secrets are set on your app with fly secrets list, and they are available as environment variables within your app’s VM, so you can actually see what’s there by popping a shell on the running app.

One thing you can’t get in plain text again are the postgres user credentials that get printed out when you create the database.

Yes i’ve done that and checked if the secrets have been generated with the fly secrets list command. And it’s ok.
Just to be more specific, I have a Gitlab CI CD and i deploy in fly.io when pushing in main branch.

I don’t know what i’m doing wrong but at least when i try to add a comment in my blog in production, the comment does not appear (it’s working in local).

Hmm, this sounds like a job for fly logs on the Phoenix app and/or the Postgres app.

I tried to connect to the Postgres app with flyctl postgres connect -a <my_db_app_name>, i think the shell is ok (it displays a line with postgres=#), but then I don’t know how to access logs :face_with_raised_eyebrow:
And when i run fly logs in my phoenix app, it does not show anything when i try to save a comment :fearful:

Oh, sorry if I wasn’t clear: I meant run fly logs -a <phx_app_name> and fly logs -a <db_app_name> from your local machine.

Sorry, i’m a real newbie as you can see :sweat_smile:
Maybe i have an idea for my problem, it’s maybe not a database bug but a permission one (i only show comments when they are validated by the admin and i forgot to do it in production :confounded: )

Do you know how I can do a sql request in CLI in my postgres database ?
I have to run flyctl postgres connect -a <my_db_app_name> maybe ?

Yep, that’s exactly the command you need. There are more options(Postgres on Fly · Fly Docs) but this is the quickest.

Ok i found the way to connect and use psql to display the tables but i think i have a bigger problem, it does not show anything in my production database :thinking:

image

and it’s the same for the posts and comments

I found the solution to my problem. It was not a problem of database config at all :woozy_face:

I thought that my migrations were not running but in fact, i was not connected to the real db in psql.

To connect to my db, i had to list all the databases with \l and after that connect to my app db with \c <name_of_the_db_showed_in_the_list>, and it works !

Then i just had to give the admin role to my user, and then i was able to see the comments, approve or delete them and so on :partying_face:

Thank you so much for your help !

1 Like