Problem connecting Xen Orchestra to PostgreSQL (Ubuntu 22.04 + PostgreSQL in TrueNAS jail)

openaithedolphinshop-svg

Hi,

I’m currently migrating my two Xen Orchestra setups to use PostgreSQL as the backend database. My environment looks like this:

2x XO (xen-orchestra from sources): running on Ubuntu 22.04
PostgreSQL: running in a TrueNAS Core jail (FreeBSD)
Database: xo
User: xo_user
Password: contains a special character !
IP of PostgreSQL server: 192.168.1.90

What I did:

Verified that PostgreSQL is reachable from both XO servers (xo1 and xo2) with a simple TCP test:

nc -zv 192.168.1.90 5432
# Connection to 192.168.1.90 5432 port [tcp/postgresql] succeeded!

2. Verified that the database login works directly with the URI:

```bash
psql "postgres://xo_user:abc12%21@192.168.2.90/xo"
# worked fine

(Important: the ! in the password must be percent-encoded as %21 in the URI.)

In /opt/xo/xo-src/xen-orchestra/packages/xo-server/config.toml on both servers I added:

[databases.pgsql]
host = "192.168.1.90"
port = 5432
database = "xo"
user = "xo_user"
password = "abc12!"

[database]
uri = "postgres://xo_user:abc12%21@192.168.2.89:5432/xo"

Restarted XO:

systemctl restart xo-server

Problem:

Even though manual tests with psql work, I don’t see any connections from xo1 or xo2 appearing in PostgreSQL (pg_stat_activity).
It looks like XO isn’t actually opening the connection to the external Postgres DB.

Question:

Is this the correct way to configure XO for Postgres?
Should I expect XO to create its own schema/tables automatically on first run, or do I need to initialize them manually?
Any known issues when PostgreSQL runs inside a FreeBSD jail (TrueNAS Core) but XO is on Ubuntu?

Thanks in advance for clarification — I’d like to make sure this setup works and is supported.

olivierlambert

OK

XO doesn't work like that. Most objects are in memory (all XCP-ng objects), we only use a key/value DB with Redis to store ACLs, backup jobs, users etc.

There's no specific value to get it in HA, you can simply have multiple XOAs connected to the same pool.

florent

@openaithedolphinshop-svg said in Problem connecting Xen Orchestra to PostgreSQL (Ubuntu 22.04 + PostgreSQL in TrueNAS jail):

databases

I may be missing something, but as far as I know, there are no sql database in XO

olivierlambert

I don't know where you have seen XO could use a SQL database, can you point us to the doc exactly where it's explained?

Please tell me you didn't trust and simply use a LLM and their hallucinations to configure XO.

openaithedolphinshop-svg

@olivierlambert said in Problem connecting Xen Orchestra to PostgreSQL (Ubuntu 22.04 + PostgreSQL in TrueNAS jail):

Please tell me you didn't trust and simply use a LLM and their hallucinations to configure XO.

And if I did...

Glad you point that out, Olivier. After I installed the XO VM on the same hardware as XCP-ng and I lost the XO metadata, I thought to install a redundant XO. Well and then it was not far away to ask AI if that was possible. Anyway even if it is not - what I guess - I am glad to start the project because it was the first time to install PostgreSQL and I may have use for it somewhere else. Also, BTW, it was fun to see how AI was trying to configure XO and fail.

Good job from your side on XO - BIG thank you!

olivierlambert

OK

XO doesn't work like that. Most objects are in memory (all XCP-ng objects), we only use a key/value DB with Redis to store ACLs, backup jobs, users etc.

There's no specific value to get it in HA, you can simply have multiple XOAs connected to the same pool.

openaithedolphinshop-svg

Once again, thanks for all the work you’ve done. If it hadn’t been for last night’s AI hallucination, I wouldn’t have dug in so deeply — and I ended up learning a lot. All the best!