PostgreSQL multiple hosts configuration and database cluster failover

[lenaing]

The current library used for the physical PostgresSQL backend is lib/pq.
However, this library does not :

• Handle multiple hosts in connection string : add support for multiple hosts in connection settings (for feature parity with libpq) lib/pq#683
• Handle failover options : DB failover mechanism lib/pq#800

To reproduce :

1. Configure vault to use postgresql as backend and use multiple hosts in connection string, and enable target_session_attrs parameter (Cf C libpq parameters keywords)
2. Start Vault
3. Vault does not start, see error.

Vault Config excerpt :

...
"postgresql": {
      "connection_url": "postgresql://user:password@host1.example.com:5432,host2.example.com:5432/vault_database?sslmode=disable&target_session_attrs=read-write",
      "table": "vault_kv_store",
      "ha_enabled": "true"
}
...

Results :

Error initializing storage of type postgresql: failed to check for native upsert: dial tcp: lookup host1.example.com:5432,host2.example.com:5432: no such host
Error initializing storage of type postgresql: failed to check for native upsert: pq: unrecognized configuration parameter "target_session_attrs"

A stale PR has been opened to solve these issues but it seems it won't be merged on this project : lib/pq#714

The pgx project seems to have merged this kind of handling here : jackc/pgx#545

Expected behavior :

It would be nice to support multiple hosts in connection string and target session attributes so that on connection lost Vault would reconnect automatically, enable one to have a database cluster that can failover.

Environment :

• Vault version : 1.2.0
• Operating System / Architecture :
  • CentOS Servers / PostgresSQL 10 Cluster

Regards,

[innovationhub-asia]

Any plan to resolve this? Otherwise it's hard to use Postgres as a backend in production environments.

[cpoule23]

Hello, +1. Today, the switch of postgres database need to cut the service, time to allow write access on the failover DB.

[KaymeKaydex]

Any plan to resolve this? Otherwise it's hard to use Postgres as a backend in production environments.

lets use pgx

[caniko]

It makes more sense to implement multiple hosts support and RW/RO connection selector support rather than what is proposed by the OP (limited to failover).

Looking forward to this implementation. Vault will hog the master node of our postgres cluster in production for now.

[fairclothjm]

As of Vault 1.11 we are using pgx. See #15343

@lenaing Can you confirm that this resolves this issue for you?

[lenaing]

Hi, sorry for the delay.

I do not have two databases anymore to test this, but I believe that we can close the issue as pgx does handle multiple hosts configuration and target selection.

Thanks !