Operations grimoire/PostgreSQL

From Nasqueron Agora
Revision as of 19:11, 29 March 2023 by Dereckson (talk | contribs) (Created page with "PostgreSQL is available as a standalone role and can also be enabled on devserver. Applications on the Docker PaaS can use our PostgreSQL infrastructure (Airflow, datasources) or have their own container (Sentry, with custom wal2json extension). This resource documents our own db- servers. == Howto == === Open a console === Use peer authentication from the postgres user: <code>sudo -u postgres psql</code> === Create a new database or user === It's a simple two step...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

PostgreSQL is available as a standalone role and can also be enabled on devserver.

Applications on the Docker PaaS can use our PostgreSQL infrastructure (Airflow, datasources) or have their own container (Sentry, with custom wal2json extension). This resource documents our own db- servers.

Howto

Open a console

Use peer authentication from the postgres user: sudo -u postgres psql

Create a new database or user

It's a simple two steps process:

  1. Edit the relevant pillar file, for example pillar/dbserver/cluster-A.sls in rOPS
  2. Deploy the change: salt db-A-001 state.apply roles/dbserver-pgsql

You generally need:

  • an user block in dbserver_postgresql.users
    • block title is the username
    • a password needs to be set in Flow, key is under ops/secrets/
  • a database block
  • a connection triplet db, user, ips (use /32 for an unique IP)

By default, external connections are NOT enabled for any user and database, an entry for pg_hba.conf MUST be added in connections part.

Servers don't have a public ICANN IP, so you can only connect from other Nasqueron servers.

Clusters

Letters can be discontinuous: for example, B will be a MySQL cluster.

Cluster A

A is the general cluster, for Nasqueron services. It currently has one server, db-A-001.

Databases
Database Managed by Description
airflow Nasqueron Ops Workflows runner, used by datasources
fantoir Datasources PostgreSQL version of FANTOIR for geocoding