Skip to content

Prepare the databases

This procedure creates the three SmartAGT databases, their two application roles and the vector database extensions.

Database Owner Content
smartagt smartagt Application data, configuration and processing state
smartagt_vector smartagt Document indexes, vector and unaccent extensions
smartagt_bff smartagt_bff Frontend service session data

Prerequisites

  • PostgreSQL 18 and pgvector prepared: Prepare PostgreSQL.
  • PostgreSQL administrator access, for the preparation only.

Procedure

1. Open an SQL session

sudo -u postgres psql -X -d postgres -v ON_ERROR_STOP=1
& 'C:\app\PostgreSQL\18\bin\psql.exe' -X -h localhost -U postgres -d postgres -W -v ON_ERROR_STOP=1

Adapt the psql.exe path to your installation. Enter the administrator password chosen when installing PostgreSQL.

Open the session over TCP, on the virtual address of the instance:

psql -X -h postgres.example.test -U postgres -d postgres -W -v ON_ERROR_STOP=1
& 'C:\app\PostgreSQL\18\bin\psql.exe' -X -h postgres.example.test -U postgres -d postgres -W -v ON_ERROR_STOP=1

Adapt the psql.exe path to your installation.

Enter the administrator password of the instance. If PostgreSQL is already encrypted (PostgreSQL SSL), set PGSSLMODE=verify-full and PGSSLROOTCERT to the authority before the session.

Expected result: the postgres=# prompt is displayed.

2. Create the roles

In psql:

CREATE ROLE smartagt LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE smartagt_bff LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;

Set a different password for each role, with at least 20 characters among letters, digits, - and _, and keep it in your secrets manager. psql prompts for it twice without echo: complete both prompts before continuing.

\password smartagt

Do the same for the Frontend service:

\password smartagt_bff

Expected result: psql replies CREATE ROLE for each role, then accepts both passwords without error.

3. Create the databases

CREATE DATABASE smartagt OWNER smartagt ENCODING 'UTF8' TEMPLATE template0;
CREATE DATABASE smartagt_vector OWNER smartagt ENCODING 'UTF8' TEMPLATE template0;
CREATE DATABASE smartagt_bff OWNER smartagt_bff ENCODING 'UTF8' TEMPLATE template0;
REVOKE ALL ON DATABASE smartagt FROM PUBLIC;
REVOKE ALL ON DATABASE smartagt_vector FROM PUBLIC;
REVOKE ALL ON DATABASE smartagt_bff FROM PUBLIC;

Expected result: psql replies CREATE DATABASE three times, then REVOKE three times.

4. Enable the extensions

\connect smartagt_vector
CREATE EXTENSION vector;
CREATE EXTENSION unaccent;
\quit

Expected result: psql replies CREATE EXTENSION twice, then the session closes.

Special cases

Remote PostgreSQL server

Open the session over TCP, with the server name:

psql -X -h postgres.example.test -U postgres -d postgres -W -v ON_ERROR_STOP=1

On Windows, replace localhost with this name in the step 1 command. If PostgreSQL is encrypted (PostgreSQL SSL), set PGSSLMODE=verify-full and PGSSLROOTCERT to the authority before the session. With the scripts, specify the server with --host (Linux) or -DatabaseHost (Windows).

Replicated instance

The databases, roles and extensions are created once, on the primary reached through the virtual address: replication carries them to the other servers. pgvector must be installed on each of them: Prepare PostgreSQL.

Databases already created by the DBA

Check with the DBA that the three databases are owned by the roles shown in the table, and that vector and unaccent are enabled in smartagt_vector. The procedure then does not need to be run again.

Interrupted creation

If interrupted after partial creation, inspect the created objects with the DBA before resuming at the relevant step.