Prepare PostgreSQL
This procedure checks that your PostgreSQL 18 instance meets the SmartAGT requirements, then installs the pgvector extension on it.
Prerequisites
- An operating system meeting the prerequisites.
- A console with
sudo(Linux) or 64-bit PowerShell as administrator (Windows).
- A PostgreSQL instance reachable through a stable virtual address, for example
postgres.example.test. Its high availability belongs to your infrastructure. - An operating system meeting the prerequisites on every PostgreSQL server.
- A console with
sudo(Linux) or 64-bit PowerShell as administrator (Windows).
Requirements
| Requirement | Expected | Reference |
|---|---|---|
| Version | PostgreSQL 18 x64, UTF-8 cluster, psql client |
Linux, Windows |
| Listening | localhost by default, or port 5432 open to SmartAGT for a remote instance |
Connections |
| Access | SmartAGT roles only reach their own databases: access rules below | pg_hba.conf |
| Extensions | pgvector, installed below, and unaccent, shipped with PostgreSQL |
pgvector |
| Encryption | Optional for a PoC, required in production or for a remote instance | PostgreSQL SSL |
| Requirement | Expected | Reference |
|---|---|---|
| Version | PostgreSQL 18 x64, UTF-8 cluster, psql client |
Linux, Windows |
| Listening | Port 5432 of the virtual address, open to the Backend and Frontend nodes |
Connections |
| Connections | max_connections sized for the services of every node |
Connections |
| Access | SmartAGT roles only reach their own databases: access rules below | pg_hba.conf |
| Extensions | pgvector, installed below, and unaccent, shipped with PostgreSQL |
pgvector |
| Encryption | Required: the nodes reach a remote instance | PostgreSQL SSL |
Access rules
Place these rules before the general permissions of pg_hba.conf:
host smartagt,smartagt_vector smartagt 127.0.0.1/32 scram-sha-256
host smartagt,smartagt_vector smartagt ::1/128 scram-sha-256
host smartagt_bff smartagt_bff 127.0.0.1/32 scram-sha-256
host smartagt_bff smartagt_bff ::1/128 scram-sha-256
host all smartagt,smartagt_bff 0.0.0.0/0 reject
host all smartagt,smartagt_bff ::/0 reject
The final two rejections prevent the SmartAGT roles from falling back on a general permission. Components on other servers: add one rule per source CIDR before these rejections, Platform hosts for smartagt,smartagt_vector, Frontend hosts for smartagt_bff.
On every PostgreSQL server of the instance, place these rules before the general permissions of pg_hba.conf:
host smartagt,smartagt_vector smartagt <CIDR of the Backend nodes> scram-sha-256
host smartagt_bff smartagt_bff <CIDR of the Frontend nodes> scram-sha-256
host all smartagt,smartagt_bff 0.0.0.0/0 reject
host all smartagt,smartagt_bff ::/0 reject
The final two rejections prevent the SmartAGT roles from falling back on a general permission. The Backend nodes, workers included, reach smartagt and smartagt_vector, the Frontend nodes smartagt_bff.
Then reload the configuration (SELECT pg_reload_conf();).
Install pgvector
The extension is installed on the PostgreSQL server.
The extension is installed on every PostgreSQL server of the instance, primary and replicas.
On Debian or Ubuntu, with PostgreSQL 18 installed from the PGDG repository:
sudo apt-get update
sudo apt-get install -y postgresql-18-pgvector
Expected result: vector.control is present in /usr/share/postgresql/18/extension/.
Prerequisites:
- the pgvector 0.8.7 sources extracted to
C:\build\pgvector - the Visual Studio C++ tools
- the x64 Native Tools Command Prompt opened as administrator
Compilation can take place on a Windows x64 preparation workstation with PostgreSQL 18.
Adapt PGROOT to the PostgreSQL directory:
set "PGROOT=C:\app\PostgreSQL\18"
cd /d C:\build\pgvector
nmake /F Makefile.win
nmake /F Makefile.win install
If you compile on a preparation workstation, transfer the compiled files to the same subdirectories of the server: lib\vector.dll, share\extension\vector.control and share\extension\vector--*.sql.
Expected result: vector.control is present in C:\app\PostgreSQL\18\share\extension\.
Source: official pgvector installation.