Skip to content

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.