Solved Unable to connect to a DigitalOcean managed database

[shrey]

When trying to connect to an external, digitalocean based Postgres database, by setting the appropriate config values in the .env file, i'm seeing this error:

Feb 21 09:39:30 ==> DB Migration
Feb 21 09:39:31 box:settings initCache: pre-load settings
Feb 21 09:39:31 box:taskworker Starting task 2364. Logs are at /home/yellowtent/platformdata/logs/b762d2dd-7293-4f24-b4bf-50970ecfde88/apptask.log
Feb 21 09:39:31 box:apptask run: startTask installationState: pending_stop runState: stopped
Feb 21 09:39:31 box:tasks update 2364: {"percent":20,"message":"Stopping container"}
Feb 21 09:39:31 box:shell reload spawn: /usr/bin/sudo -S /home/yellowtent/box/src/scripts/restartservice.sh nginx
Feb 21 09:39:33 [04:09:32.729] INFO: Running migrations...
Feb 21 09:39:33 [04:09:32.798] ERROR: self-signed certificate in certificate chain
Feb 21 09:39:33 err: {
Feb 21 09:39:33 "type": "Error",
Feb 21 09:39:33 "message": "self-signed certificate in certificate chain",
Feb 21 09:39:33 "stack":
Feb 21 09:39:33 Error: self-signed certificate in certificate chain
Feb 21 09:39:33 at TLSSocket.onConnectSecure (node:_tls_wrap:1538:34)
Feb 21 09:39:33 at TLSSocket.emit (node:events:513:28)
Feb 21 09:39:33 at TLSSocket._finishInit (node:_tls_wrap:952:8)
Feb 21 09:39:33 at ssl.onhandshakedone (node:_tls_wrap:733:12)
Feb 21 09:39:33 "code": "SELF_SIGNED_CERT_IN_CHAIN"
Feb 21 09:39:33 }

The strange thing is, that i already have another instance of Directus running on the same Cloudron server, connected in the same way to a DO Postgres db. And that's been working just fine!

Can't figure out why it's not working in the second case.

[shrey]

This post is deleted!

[shrey]

I noticed that the certificate issued was self-signed and not by Let's Encrypt (why ?).

So, i recreated the app with a different subdomain. This time, a proper certificate was issued.

But now the error is:

Feb 21 10:29:26 ==> DB Migration
Feb 21 10:29:29 [04:59:28.983] INFO: Running migrations...
Feb 21 10:29:29 [04:59:29.185] ERROR: select * from "directus_migrations" order by "version" asc - relation "directus_migrations" does not exist
Feb 21 10:29:29 err: {
Feb 21 10:29:29 "type": "DatabaseError",
Feb 21 10:29:29 "message": "select * from \"directus_migrations\" order by \"version\" asc - relation \"directus_migrations\" does not exist",
Feb 21 10:29:29 "stack":
Feb 21 10:29:29 error: select * from "directus_migrations" order by "version" asc - relation "directus_migrations" does not exist
Feb 21 10:29:29 at Parser.parseErrorMessage (/app/code/node_modules/pg-protocol/dist/parser.js:287:98)
Feb 21 10:29:29 at Parser.handlePacket (/app/code/node_modules/pg-protocol/dist/parser.js:126:29)
Feb 21 10:29:29 at Parser.parse (/app/code/node_modules/pg-protocol/dist/parser.js:39:38)
Feb 21 10:29:29 at TLSSocket.<anonymous> (/app/code/node_modules/pg-protocol/dist/index.js:11:42)
Feb 21 10:29:29 at TLSSocket.emit (node:events:513:28)
Feb 21 10:29:29 at addChunk (node:internal/streams/readable:324:12)
Feb 21 10:29:29 at readableAddChunk (node:internal/streams/readable:297:9)
Feb 21 10:29:29 at Readable.push (node:internal/streams/readable:234:10)
Feb 21 10:29:29 at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23)
Feb 21 10:29:29 "length": 118,
Feb 21 10:29:29 "name": "error",
Feb 21 10:29:29 "severity": "ERROR",
Feb 21 10:29:29 "code": "42P01",
Feb 21 10:29:29 "position": "15",
Feb 21 10:29:29 "file": "parse_relation.c",
Feb 21 10:29:29 "line": "1381",
Feb 21 10:29:29 "routine": "parserOpenTable"
Feb 21 10:29:29 }

[shrey]

Also, i'm unable to see any requests on the db end in DO. Seems like the Directus app on Cloudron is unable to reach the db.

Even though: i'm able to connect to the db, from dBeaver on my local system, via a SSH tunnel on the Cloudron server!

[girish]

@shrey The Web Terminal of directus app has the psql CLI installed. Are you able to connect to DO database using the pg CLI tool?

It's a bit unclear to me if the self signed cert message is because of connecting to DO database or migration related issue.

[shrey]

@girish just tried connecting via the psql CLI and got connected, successfully.

But, when i switched over the config to DO database, in the env file, i'm getting the error again:

Feb 21 16:52:29 ==> DB Migration
Feb 21 16:52:33 [11:22:32.321] INFO: Running migrations...
Feb 21 16:52:33 [11:22:32.457] ERROR: select * from "directus_migrations" order by "version" asc - relation "directus_migrations" does not exist
Feb 21 16:52:33 err: {
Feb 21 16:52:33 "type": "DatabaseError",
Feb 21 16:52:33 "message": "select * from \"directus_migrations\" order by \"version\" asc - relation \"directus_migrations\" does not exist",
Feb 21 16:52:33 "stack":
Feb 21 16:52:33 error: select * from "directus_migrations" order by "version" asc - relation "directus_migrations" does not exist
Feb 21 16:52:33 at Parser.parseErrorMessage (/app/code/node_modules/pg-protocol/dist/parser.js:287:98)
Feb 21 16:52:33 at Parser.handlePacket (/app/code/node_modules/pg-protocol/dist/parser.js:126:29)
Feb 21 16:52:33 at Parser.parse (/app/code/node_modules/pg-protocol/dist/parser.js:39:38)
Feb 21 16:52:33 at TLSSocket.<anonymous> (/app/code/node_modules/pg-protocol/dist/index.js:11:42)
Feb 21 16:52:33 at TLSSocket.emit (node:events:513:28)
Feb 21 16:52:33 at addChunk (node:internal/streams/readable:324:12)
Feb 21 16:52:33 at readableAddChunk (node:internal/streams/readable:297:9)
Feb 21 16:52:33 at Readable.push (node:internal/streams/readable:234:10)
Feb 21 16:52:33 at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23)
Feb 21 16:52:33 "length": 118,
Feb 21 16:52:33 "name": "error",
Feb 21 16:52:33 "severity": "ERROR",
Feb 21 16:52:33 "code": "42P01",
Feb 21 16:52:33 "position": "15",
Feb 21 16:52:33 "file": "parse_relation.c",
Feb 21 16:52:33 "line": "1381",
Feb 21 16:52:33 "routine": "parserOpenTable"
Feb 21 16:52:33 }

[girish]

@shrey ahh, i see now. So you are trying to make directus use the DO database instead of the internal cloudron database? This won't work. Cloudroni packages don't support external databases and you should not change the env vars that are database related .

To take a step back, why do you want an external database?

[shrey]

@girish

@girish said in Unable to connect to a DigitalOcean managed database:

This won't work.

Already have a live project running on Cloudron where the Directus app is connected to an external database.

@girish said in Unable to connect to a DigitalOcean managed database:

Cloudroni packages don't support external databases and you should not change the env vars that are database related .

That's one of the main purposes of these apps, Directus/Nocodb: to be a layer on top of existing databases.

@girish said in Unable to connect to a DigitalOcean managed database:

To take a step back, why do you want an external database?

I wouldn't, personally, prefer putting a major data heavy app's database within Cloudron, amongst all the other existing installed apps. Don't think that would provide me enough scalability, accessibility from multiple vectors, dedicated & incremental db backups, and simply, database independence for any future requirements.

[girish]

@shrey said in Unable to connect to a DigitalOcean managed database:

Already have a live project running on Cloudron where the Directus app is connected to an external database.

So, this just works by accident. None of the feature like backup/restore will work. This will also break in future releases. Cloudron is not designed to use external database as an app's main database. It's not designed for apps to work with existing external databases.

That's one of the main purposes of these apps, Directus/Nocodb: to be a layer on top of existing databases.

Not an expert on the apps and I don't know if it's the main purpose. But they work just fine with fresh databases as well where you use it like a nocode/locode app.

I wouldn't, personally, prefer putting a major data heavy app's database within Cloudron, amongst all the other existing installed apps. Don't think that would provide me enough scalability, accessibility from multiple vectors, dedicated & incremental db backups, and simply, database independence for any future requirements.

Cloudron is not designed for such setups which require massive scalability. It's designed for vertical scalability and not horizontal scalability. If you require something where a single app is spread out over multiple VMs (and all the resulting complexity), I am afraid our app packages are not designed for this currently. These are quite complicated setups (and afaik there is no solution in the market for these).

[shrey]

@girish said in Unable to connect to a DigitalOcean managed database:

None of the feature like backup/restore will work

So far, they've been working fine. Because, all that's required to be backed up in such cases is the app code. Of course, as the database is external, it is accepted that the backup system is external & independent to Cloudron.

@girish said in Unable to connect to a DigitalOcean managed database:

Not an expert on the apps and I don't know if it's the main purpose. But they work just fine with fresh databases as well where you use it like a nocode/locode app.

Yes, pairing up with external databases is indeed, one of the primary features of these apps. And using a fresh database is just one of the features, and not always preferred.

You might want to state clearly and prominently, particularly in the cases of such apps, that external connections are not allowed/recommended.