Strapi Postgres Database Failover

Questions and Answers Strapi Backend
1
System Information
  • Strapi Version: 4.8.2
  • Operating System: RH79
  • Database: Postgres
  • Node Version: 16.19
  • NPM Version: 9.6.2
  • Yarn Version:

We are are using Postgres DB as a service as follow:
DB cluster with Three DB instances. Primary and read-only replica in one AZ and other read-only replica in other datacenter with Sync Replication to other AZ.

And what have been told is to use the following, in case the primary in down or under maintenance:
postgres://@host1:port1,host2:port2,host3:port3/?connect_timeout=2&target_session_attrs=read-write&keepalives=1&keepalives_idle=1&keepalives_count=1&tcp_user_timeout=1000

Problem is that we are unable to find how can we do this in Strapi as does not look like it supports multi-host string. If we lose the single connection to the host, strapi wont automatically failover.

Any suggestions?

2

KNEX, supports PG_CONNECTION_STRING as below:

PostgreSQL

If connectionString is highest priority to use. If left unspecified then connection details will be determined using the individual connection fields (host, port, etc), and finally an SSL configuration will be enabled based on a truthy value of config["DB_SSL"] which will also accept self-signed certificates.

const pg = require('knex')({
  client: 'pg',
  connection: {
    connectionString: config.DATABASE_URL,
    host: config["DB_HOST"],
    port: config["DB_PORT"],
    user: config["DB_USER"],
    database: config["DB_NAME"],
    password: config["DB_PASSWORD"],
    ssl: config["DB_SSL"] ? { rejectUnauthorized: false } : false,
  }
});

But I have been unable to get it working. Anyone can provide a working example?

3

It seems Knex does not support multihost, which is a let down for this product, considering most of the companies now use multizone DB setups for resiliency, Unfortunately, we will need to look somewhere else for a CMS that supports it