Open Rinma opened 8 months ago
Hi @Rinma , thanks for the feedback!
appflowy_cloud
and gotrue
services shares the same database
gotrue
is set to take url from DATABASE_URL
appflowy_cloud
is set to take url from APPFLOWY_DATABASE_URL
DATABASE_URL
and APPFLOWY_DATABASE_URL
should only differs in the login username and password, but the database should kept the same.
From the screenshot, it seems like you have configured DATABASE_URL
to use appflowy
database, but appflowy_cloud is using another database.
I think you can solve it by editing docker-compose.yml
at gotrue section:
- DATABASE_URL=postgres://supabase_auth_admin:root@postgres:5432/${POSTGRES_DB}
so that it will use use the same database. Let me know if that solves :)
Hi @speed2exe , I think I already have it setup like this, here is my docker-compose file:
But as you can see in the appflowy-cloud
service, I had to set the APPFLOWY_DATABASE_NAME
environment variable to make it work. Otherwise, it tries to connect to postgres
instead of ${POSTGRES_DB}
@Rinma Yup, you have to set to something like postgres://postgres:password@postgres:5432/${POSTGRES_DB}
. I'm guessing you have it all figured out? Let me know if you need any more assistance in your set up.
I don't know if we are talking about the same thing currently. So let me rephrase the question I had:
Is it intended that I have to set the environment variable APPFLOWY_DATABASE_NAME=${POSTGRES_DB}
for the cloud service separately because it appears that it is not using the database set in the APPFLOWY_DATABASE_URL=postgres://postgres:password@postgres:5432/${POSTGRES_DB}
?
If so, the docker-compose.yml should be updated accordingly to also set APPFLOWY_DATABASE_NAME
.
@Rinma Not entirely if I get you, correct me if I'm wrong.
The current set up is as if you dont use external DB.
So the values DATABASE_URL
and APPFLOWY_DATABASE_URL
is the same in docker-compose.yaml
.
If you wish to change database, then you need to change both of them.
Does that make sense to you?
I don't have a problem with my setup, currently it is working.
But I think there is a bug in the code. That is what I try to figure out, if there is a bug, or it works like indented. If so, I would miss some documentation about APPFLOWY_DATABASE_NAME
which is not set in the docker-compose.yml
nor any *.env
file.
In this code repository, the docker-compose.yml file does not include APPFLOWY_DATABASE_NAME
, only the environment variables for the different DATABASE_URL
. So I assume I don't have to set APPFLOWY_DATABASE_NAME
.
But I needed to set this to get it working. Otherwise, the rust code tries to connect to the database with the name postgres
and not the database I set in the DATABASE_URL / APPFLOWY_DATABASE_URL
.
I only knew what environment variable I could set because I looked into the code. I doubt that this is the preferred way to look up why the APPFLOWY_DATABASE_URL
isn't working or what is needed to be set.
Like I said, I only started learning rust, but my guess would be that at this part of the code
db_settings: DatabaseSetting {
pg_conn_opts: PgConnectOptions::from_str(&get_env_var(
"APPFLOWY_DATABASE_URL",
"postgres://postgres:password@localhost:5433/postgres",
))?,
require_ssl: get_env_var("APPFLOWY_DATABASE_REQUIRE_SSL", "false").parse()?,
max_connections: get_env_var("APPFLOWY_DATABASE_MAX_CONNECTIONS", "20").parse()?,
database_name: get_env_var("APPFLOWY_DATABASE_NAME", "postgres"),
},
the database from the APPFLOWY_DATABASE_URL
is overridden by database_name: get_env_var("APPFLOWY_DATABASE_NAME", "postgres"),
so it always takes postgres
as the database name.
If this is not the case, then I don't understand why I had to set APPFLOWY_DATABASE_NAME
to get it working, but currently my setup works.
I hope this clarifies what I am trying to understand with this bug ticket, and thanks for taking your time with this @speed2exe I really appreciate that :smile:
@Rinma
Thanks for taking the time to look into the issue.
I've thought about it for a while.
For deployment, users are mainly looking into deploy.env
as their starting template and docker-compose.yml
.
There are no database access env var in it (DATABASE_URL
or APPFLOWY_DATABASE_URL
).
The DATABASE_URL
and APPFLOWY_DATABASE_URL
in docker-compose.yml
is fixed to a value (not using ${...}).
We have also adding FAQ on using a different postgres: FAQ
Do you think that is clear enough? We are open to suggestions :)
I am a little bit lost about database creation on appflowy, I have the following issue:
AppFlowy Cloud with RUST_LOG=debug
{"timestamp":"2023-12-28T01:08:40.169516Z","level":"INFO","fields":{"message":"Preparng to run database migrations..."},"target":"appflowy_cloud::application"}
{"v":0,"name":"appflowy_cloud","msg":"Preparng to run database migrations...","level":30,"hostname":"appflowy-575d55b7b8-r826d","pid":1,"time":"2023-12-28T01:08:40.169580788Z","target":"appflowy_cloud::application","line":160,"file":"src/application.rs"}
{"timestamp":"2023-12-28T01:08:40.169587Z","level":"INFO","fields":{"message":"Connecting to postgres database with setting: DatabaseSetting { pg_conn_opts: PgConnectOptions { host: \"postgres16-rw.databases.svc.cluster.local\", port: 5432, socket: None, username: \"appflowy\", password: Some(\"password\"), database: Some(\"appflowy\"), ssl_mode: Prefer, ssl_root_cert: None, ssl_client_cert: None, ssl_client_key: None, statement_cache_capacity: 100, application_name: None, log_settings: LogSettings { statements_level: Debug, slow_statements_level: Warn, slow_statements_duration: 1s }, extra_float_digits: Some(\"3\"), options: None }, require_ssl: false, max_connections: 20, database_name: \"postgres\" }"},"target":"appflowy_cloud::application"}
{"v":0,"name":"appflowy_cloud","msg":"Connecting to postgres database with setting: DatabaseSetting { pg_conn_opts: PgConnectOptions { host: \"postgres16-rw.databases.svc.cluster.local\", port: 5432, socket: None, username: \"appflowy\", password: Some(\"password\"), database: Some(\"appflowy\"), ssl_mode: Prefer, ssl_root_cert: None, ssl_client_cert: None, ssl_client_key: None, statement_cache_capacity: 100, application_name: None, log_settings: LogSettings { statements_level: Debug, slow_statements_level: Warn, slow_statements_duration: 1s }, extra_float_digits: Some(\"3\"), options: None }, require_ssl: false, max_connections: 20, database_name: \"postgres\" }","level":30,"hostname":"appflowy-575d55b7b8-r826d","pid":1,"time":"2023-12-28T01:08:40.169651513Z","target":"appflowy_cloud::application","line":334,"file":"src/application.rs"}
Error: Failed to initialize application state: Failed to run migrations: while executing migrations: error returned from database: permission denied for schema public
The postgres database is mandatory to perform db action on appflowy?
The intention is separate the db (appflowy) and do not use the postgres db.
@enmanuelmoreira if i get you right, you're planning to use an external postgres? you would need to run the initialization sql file in migrations/before
@speed2exe Yes, I plan to use an external postgres database server. I have already created manually the auth schema into the appflowy database.
@enmanuelmoreira Have you tried running the .sql files in migrations/before
and restarting the services after that? From the screenshot, the tables that are supposed to be generated by appflowy_cloud
services have not done the migrations. Let us know if you have difficulties.
@speed2exe Yes I tried, I'm getting this error now:
AppFlowy Cloud with RUST_LOG=debug
{"timestamp":"2023-12-29T01:25:21.673878Z","level":"INFO","fields":{"message":"Preparng to run database migrations..."},"target":"appflowy_cloud::application"}
{"v":0,"name":"appflowy_cloud","msg":"Preparng to run database migrations...","level":30,"hostname":"appflowy-5777854f44-9gzns","pid":1,"time":"2023-12-29T01:25:21.673904878Z","target":"appflowy_cloud::application","line":160,"file":"src/application.rs"}
{"timestamp":"2023-12-29T01:25:21.673915Z","level":"INFO","fields":{"message":"Connecting to postgres database with setting: DatabaseSetting { pg_conn_opts: PgConnectOptions { host: \"postgres16-rw.databases.svc.cluster.local\", port: 5432, socket: None, username: \"appflowy\", password: Some(\"password\"), database: Some(\"appflowy\"), ssl_mode: Prefer, ssl_root_cert: None, ssl_client_cert: None, ssl_client_key: None, statement_cache_capacity: 100, application_name: None, log_settings: LogSettings { statements_level: Debug, slow_statements_level: Warn, slow_statements_duration: 1s }, extra_float_digits: Some(\"3\"), options: None }, require_ssl: false, max_connections: 20, database_name: \"appflowy\" }"},"target":"appflowy_cloud::application"}
{"v":0,"name":"appflowy_cloud","msg":"Connecting to postgres database with setting: DatabaseSetting { pg_conn_opts: PgConnectOptions { host: \"postgres16-rw.databases.svc.cluster.local\", port: 5432, socket: None, username: \"appflowy\", password: Some(\"password\"), database: Some(\"appflowy\"), ssl_mode: Prefer, ssl_root_cert: None, ssl_client_cert: None, ssl_client_key: None, statement_cache_capacity: 100, application_name: None, log_settings: LogSettings { statements_level: Debug, slow_statements_level: Warn, slow_statements_duration: 1s }, extra_float_digits: Some(\"3\"), options: None }, require_ssl: false, max_connections: 20, database_name: \"appflowy\" }","level":30,"hostname":"appflowy-5777854f44-9gzns","pid":1,"time":"2023-12-29T01:25:21.673992812Z","target":"appflowy_cloud::application","line":334,"file":"src/application.rs"}
Error: Failed to initialize application state: Failed to run migrations: while executing migrations: error returned from database: relation "public.af_user" does not exist
I changed some values from the script before/migrations
, I replaced the user supabase_auth_admin
with appflowy
that user is I am using to create the db.
@enmanuelmoreira can you try to drop the schema_migrations
table in pgadmin?
Here we go
@enmanuelmoreira Looks like the migrations have run to completion. Are there any other errors in initializing the appflowy_cloud
service?
This one stills hit me
Error: Failed to initialize application state: Failed to run migrations: while executing migrations: error returned from database: policy "af_user_insert_policy" for table "af_user" alreadyexists
and this:
Error: Failed to initialize application state: Failed to run migrations: while executing migrations: error returned from database: function uuid_generate_v4() does not exist
But:
@enmanuelmoreira how about try to set the default schema to public
for postgres
user to public
?
ALTER USER postgres SET search_path TO public;
a quick google look up brings me to this which might help you: https://stackoverflow.com/questions/22446478/extension-exists-but-uuid-generate-v4-fails CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Yea, I deleted it and recreated it, same error.
@enmanuelmoreira Maybe you can try to log in to postgres as postgres
, try to call the uuid_generate_v4
function and debug from there?
I tried to reproduce by spinning up another postgres, then changing the host in docker-compose.yml
.
DATABASE_URL=postgres://supabase_auth_admin:root@<my-db-host>:5432/postgres
APPFLOWY_DATABASE_URL=postgres://postgres:password@<my-db-host>:5432/postgres
When I do docker compose up -d
, services are up and running. I'll be happy to help if you've got more questions.
maybe we should docker volume rm -f appflowy-cloud_postgres_data
first.
and then docker compose up -d postgres
but still have the error:
Error: Failed to initialize application state: code: 500, msg:Database error finding user, error_id: Some("aa0f85cd-648e-4a68-a27a-4b828e209cac")
The previous message seems to be a different error. It doesn't mention the schema "auth" does not exist
message.
After spending a few hours on this, I have come to the same conclusion, as expressed before.
The hard-coding of defaults for the configuration variable of the database connection sets a default database_name
, which is not inferred from the DATABASE_URL
, which also contains this information.
Indeed providing APPFLOWY_HISTORY_DATABASE_NAME
to the environment
of the appflowy_history
container together with a value of ${POSTGRES_DB}
helps to resolve this issue. The other reported errors should be investigated separately.
Please remove all hard-coded connection configuration values from the binary and make them only depend on the environment configuration, as people will use them. This will make it easier to catch errors, because the environment functions closer to the expected production system.
Additionally, it appears useful to make use of Compose's templating features, to reduce redundancies in the .env
.
3a4,10
> POSTGRES_USER=
> POSTGRES_PASSWORD=
> POSTGRES_DB=
> POSTGRES_HOST=
>
> GOTRUE_POSTGRES_USER=supabase_auth_admin
> GOTRUE_POSTGRES_PASSWORD=root # Another issue, another hard-coded value
8c15
< APPFLOWY_DATABASE_URL=postgres://postgres:password@postgres:5432/postgres
---
> APPFLOWY_DATABASE_URL=postgres://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:5432/${POSTGRES_DB}
55c62
< GOTRUE_DATABASE_URL=postgres://supabase_auth_admin:root@postgres:5432/postgres
---
> GOTRUE_DATABASE_URL=postgres://${GOTRUE_POSTGRES_USER}:${GOTRUE_POSTGRES_PASSWORD}@${POSTGRES_HOST}:5432/${POSTGRES_DB}
122c117,118
< APPFLOWY_HISTORY_DATABASE_URL=postgres://postgres:password@postgres:5432/postgres
---
> APPFLOWY_HISTORY_DATABASE_NAME=${POSTGRES_DB}
> APPFLOWY_HISTORY_DATABASE_URL=postgres://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:5432/${POSTGRES_DB}
This example leaves out the database URLs for the AI and indexer services. #565
This requires to put all POSTGRES_
variables at the top, in so they can be inferred further down. Another source of possible error.
The before/
migration also contains two hard-coded values: the "Supabase" password and the name of the database. These should be parametrised as well.
Which raises the need for a documented setup of a near-production system that comes closer to the environments as people will expect to use them. This is another issue.
When I start my docker with the actual docker-compose file, I get this error:
I updated my compose file so it uses
with these values
But as you can see it the log, it searches for the auth scheme in the database
postgres
. I'm still learning rust, but for me, it looks like a problem with this codeCould it be that it is not using the database name from the
DATABASE_URL
but overrides it withAPPFLOWY_DATABASE_NAME
, which is when not setpostgres
?So it tried to set the env variable
APPFLOWY_DATABASE_NAME
to$ {POSTGRES_DB} / appflowy
and the cloud started again.Here is a screenshot from my database structure: