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.