Backend create one database per plugin | Backstage | Page 1

worthy lantern Aug 30, 2024, 12:47 PM

#

New Backend system is now creating one separate database for every backend plugin, and in most of the cases they are not used at all. Only Knex standard tables are created (at least in out case using a Postgres db).
In our Backstage based application we currently have 21 backend plugins, so 21 mostly unused databases.

Additionally, considering that Backend keeps at least one open process per database, we easily reach around 22-23 open processes (one per db plus 1-2 additional) per container, and considering that we run 4 replicas, we reach by default around 90 processes, that increase during processor/provider runs or even in standard usage.
The normal maximum allowed processes in Postgres are 100 so we had already some cases were the limit was reached causing instability, forcing us to increase the limit.

My questions are:

Is there a reason to open one database per backend plugin?
Is there a way to set the backend plugin to avoid the db creation/usage (I couldn't find any in documentation nor in code)?
Thanks for all the possible help

turbid yacht Aug 30, 2024, 12:50 PM

#

Hi,

There's the configuration backend.database.pluginDivisionMode which can have database or schema values, database being the default.

docs say:

How plugins databases are managed/divided in the provided database instance.

`database` -> Plugins are each given their own database to manage their schemas/tables.

`schema` -> Plugins will be given their own schema (in the specified/default database) to manage their tables.

NOTE: Currently only supported by the `pg` client.

Hope this helps!

hallow sigil Aug 30, 2024, 12:56 PM

#

Hi @worthy lantern, just some additional background, this is related to the Auth Improvement and was introduced in 1.26.0, always recommend reading the release notes as they often cover things like this and other helpful information: https://backstage.io/docs/releases/v1.26.0#auth-improvements, this is the bit related to your question:

Note also that the plugin service auth system uses the plugin database to store asymmetric keys. This means that any plugin that wants to make requests to other plugins now must have a database (and also accept incoming HTTP requests on the JWKS endpoint). For most adopters this will not require any action since the default setup auto-creates logical databases as needed. But if you have custom or locked-down setups, you may in rare cases encounter the need to create additional plugin databases.

wild mural Sep 9, 2024, 6:52 AM

#

this interests me as well, so if I change backend.database.pluginDivisionMode from database to schema I'll have only the default Backstage DB and the plugins tables inside that DB, and no changes are required? Or moving to this will need some code refactoring?

In AWS the max connection is tight to the RDS's RAM, so having less DB will be beneficial for us.

cunning zodiac Sep 9, 2024, 7:18 AM

#

That setting makes it so that each plugin creates a pg schema inside one single logical database, instead of making distinct logical databases

#

that's the only change

#

apart from that, everything else stays the same (number of connections etc)

#

But you can configure your backend.database settings to have a smaller pool

#

with like

connection: '<connection string>'
knexConfig:
  pool:
    min: 0
    max: 2

or so

wild mural Sep 9, 2024, 8:16 AM

#

yeah we configured the setting like this:

  database:
    client: pg
    connection:
      host: ${POSTGRES_HOST}
      port: 5432
      user: ${POSTGRES_USER}
      password: ${POSTGRES_PASSWORD}
      ssl:
        rejectUnauthorized: false
    knexConfig:
      pool:
        min: 0
        max: 5
        acquireTimeoutMillis: 60000
        createTimeoutMillis: 30000
        destroyTimeoutMillis: 5000
        reapIntervalMillis: 1000
        createRetryIntervalMillis: 200
        propagateCreateError: false

The SSL is something to do with PG 16 in AWS RDS

worthy lantern Sep 9, 2024, 1:33 PM

#

@cunning zodiac I tried the suggested option but I'm getting an error on backend startup from 2 out of the 3 backend plugins that has migration files because they need to store some datas.

#

logs

#

Error message is on txt file attached as it was too long.

I checked on db and backend is creating the schemas for the 2 plugins ionside the default database, but the knex_migrations tables are missing on both. What is even more strange is that there is a third backend plugin with same design (with migration files) where the problem is not present (the knex_migrations table is created and so no error). Any idea?

📎 Untitled.rtf

worthy lantern Sep 9, 2024, 1:50 PM

#

#

I just added some screenshot of the 3 schemas, 1 working and 2 not

cunning zodiac Sep 9, 2024, 2:07 PM

#

Hm. Kinda sounds like somehow the plugin IDs got mixed up or something

#

Something's off in the setup somewhere

#

like a plugin trying to install itself in another plugin's database or something

#

is this on the old backend system or the new one? what does your packages/backend/src/index.ts look like?

worthy lantern Sep 9, 2024, 2:10 PM

#

is the new backend

#

added a couple of screenshot of index.ts

worthy lantern Sep 9, 2024, 2:34 PM

#

like a plugin trying to install itself in another plugin's database: right, the plugin is searching for the migration files of the other

cunning zodiac Sep 9, 2024, 2:37 PM

#

our index file looks more like this - no plugins folder at all https://github.com/backstage/backstage/blob/060e02d706a890e93466e95d2acbe8e91b9332e4/packages/backend/src/index.ts

GitHub

backstage/packages/backend/src/index.ts at 060e02d706a890e93466e95d...

Backstage is an open framework for building developer portals - backstage/backstage

#

but i see that a lot of those are internal, so maybe you have the code locally for them in there, which is fine to start with

worthy lantern Sep 9, 2024, 2:49 PM

#

cunning zodiac but i see that a lot of those are internal, so maybe you have the code locally f...

but i see that a lot of those are internal,
correct, are all internal

worthy lantern Sep 9, 2024, 3:14 PM

#

@cunning zodiac I found the issue with the 2 plugins not working: on the migrate.latest() command I was passing (differentlly from the ones working)
tableName: 'knex_migrations', schemaName: 'public',
as additional parameters, I suspect that the schemaName is the issue.

#

My last question related to this is: the backend.database.pluginDivisionMode parameter is set on backend level, so for all the plugins: is there a way to set it on plugin level, in order to decide with plugin have a separate db and which not?
I ask this question as if I switch now my production deployment to let all plugins use the default db, I will lose all the existing data and will have to refill completely the database (Catalog, ...) causing 1-2 hours of service interruption.

cunning zodiac Sep 9, 2024, 3:29 PM

#

i think that's a global setting yes

worthy lantern Sep 10, 2024, 7:50 AM

#

An update for this issue: even switching to schema, so using one single database instead of one per plugin, the n umber of connections are not reducing, so the problem is the same

cunning zodiac Sep 10, 2024, 8:26 AM

#

As we said above, the division mode does not affect pool sizes at all, that's unrelated

#

did you lower your pool sizes though?

#

backend:
  database:
    connection: '<connection string>'
    knexConfig:
      pool:
        min: 0
        max: 2

etc

worthy lantern Sep 12, 2024, 12:22 PM

#

@freben the issue was caused from a wrong configuration of the pool: we where declaring it directly under backend.database key instead of under backend.database.knexConfig so the pull was ever at maximum range. Correcting the key fixed the problem.Thanks to you and all the other for the help

wild mural Sep 13, 2024, 7:04 AM

#

I'm switching from database to schema, is there a way to set a database name as well instead of using the default postgres database?

turbid yacht Sep 13, 2024, 9:56 AM

#

There's the backend.database.prefix configuration, is that what you are looking?

wild mural Sep 13, 2024, 10:18 AM

#

dunno, I'm using backend.database.pluginDivisionMode: schema would that backend.database.prefix create a new DB inside the RDS with what I set instead of just using the default postgres DB?

turbid yacht Sep 13, 2024, 10:31 AM

#

Not sure actually how they work together with schema division mode 😄

cunning zodiac Sep 13, 2024, 10:33 AM

#

No it would be the prefixes for the schema names

#

I believe

turbid yacht Sep 13, 2024, 10:33 AM

#

at least with the default division, it's the prefix of the databases but not sure of the schema

cunning zodiac Sep 13, 2024, 10:34 AM

#

Am not at my computer now but I believe you can set the db name as part of the connection

wild mural Sep 13, 2024, 10:34 AM

#

I pushed the change into staging and I took it down 🤣 I don't get why is searching for the pgadmin DB 🤔 FATAL: database "pgadmin" does not exist while locally with PG is just using the postgres one.

#Backend create one database per plugin