PostgreSQL 17 with pgvector on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and use of PostgreSQL 17 with pgvector on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images. It pairs the proven PostgreSQL 17 relational database with pgvector, the open source extension that adds a native vector data type, distance operators and approximate nearest neighbour indexes. The result is a single appliance that stores high dimensional embeddings next to ordinary relational data and ranks rows by similarity to a query vector — the storage layer behind semantic search and retrieval augmented generation (RAG), all on CPU with no GPU required.

PostgreSQL 17 and the matching postgresql-17-pgvector package are installed from the official PostgreSQL PGDG repository. The extension is pre enabled (CREATE EXTENSION vector) in a default database named vectordb, which also ships a small demo items table with a vector(3) column and an HNSW index so you can run a nearest neighbour query the moment the VM boots.

Security by design — no baked credential. The postgres superuser role is password less by construction: a fresh install leaves it with no password, and the captured image ships that way, so there is no default or blank password to discover. On first boot each VM generates a unique password for the postgres role and a unique self signed TLS server certificate, writes them to the root only file /root/postgresql-pgvector-credentials.txt, then enables TLS. Remote clients are accepted only over TLS (hostssl with scram-sha-256); on box administration stays password less through the local unix socket. Until first boot mints the certificate, the database is not reachable off box at all.

What is included:

• PostgreSQL 17 from the official PGDG repository, running under systemd as postgresql.service

• The pgvector extension pre enabled in a default database vectordb, with a demo items table and an HNSW index

• A per VM postgres password and a per VM TLS certificate generated on first boot, written to a root only credentials file

• pg_hba.conf configured so remote clients connect only over TLS with the per VM password, while local access stays password less via the unix socket

• Unattended security upgrades left enabled so the appliance keeps receiving patches

Prerequisites

• Active Azure subscription, an SSH public key, and a VNet + subnet in the target region

• Subscription to this listing on Azure Marketplace

• A Network Security Group allowing TCP 22 (administration) and TCP 5432 (the PostgreSQL wire protocol) from your client network. In production, restrict 5432 to your application subnet.

Recommended virtual machine size: Standard_B2s (2 vCPU, 4 GB RAM) for development and light workloads. For larger vector collections and higher query rates, choose a memory rich size such as Standard_E2s_v5 or larger so more of the index fits in RAM.

Deploy the virtual machine

Create the VM from the image, opening only SSH and the PostgreSQL port to your own network:

az vm create \
  --resource-group my-rg \
  --name pgvector-1 \
  --image <this-marketplace-image> \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard
az vm open-port --resource-group my-rg --name pgvector-1 --port 5432 --priority 900

Retrieve your per VM credentials

On the first boot the VM generates its own postgres password and TLS certificate. SSH in and read the root only credentials file:

sudo cat /root/postgresql-pgvector-credentials.txt

The file is mode 0600, owned by root, and contains the per VM password plus a ready to paste TLS connection string. Nothing is baked into the image — every deployed VM has its own password and certificate.

Confirm the service is healthy

Check that PostgreSQL is active and listening, that TLS is on, and that the pgvector extension is enabled in the default database:

sudo systemctl is-active postgresql.service
sudo ss -tlnp | grep 5432
sudo -u postgres psql -tAc 'SHOW ssl;'
sudo -u postgres psql -d vectordb -c "SELECT extname, extversion FROM pg_extension WHERE extname='vector';"

You should see the service active, PostgreSQL listening on 5432, ssl reported as on, and the vector extension with its version.

Connect to the database

On the VM itself you can connect as the postgres superuser with no password, through the local unix socket:

sudo -u postgres psql -d vectordb -c "SELECT version();"

From a remote client, connect over TLS with the per VM password from the credentials file (replace <vm-ip> with your VM's address and <POSTGRES_PASSWORD> with the value from the file):

PGPASSWORD=<POSTGRES_PASSWORD> psql "host=<vm-ip> port=5432 dbname=vectordb user=postgres sslmode=require" -c "SELECT ssl, version AS tls FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

The pg_stat_ssl view confirms the connection is encrypted (ssl is t) and reports the negotiated TLS version.

Store embeddings and run a similarity search

pgvector adds a vector column type and distance operators. Store your embeddings in a vector column, build an approximate nearest neighbour index, and order by distance to a query vector. The <-> operator is Euclidean (L2) distance; <=> is cosine distance and <#> is inner product. This example uses three dimensional vectors for clarity; real embeddings are typically hundreds or thousands of dimensions.

sudo -u postgres psql -d vectordb <<'SQL'
DROP TABLE IF EXISTS guide_demo;
CREATE TABLE guide_demo (id bigserial PRIMARY KEY, title text, embedding vector(3));
INSERT INTO guide_demo (title, embedding) VALUES
  ('red apple',   '[0.9, 0.1, 0.1]'),
  ('green apple', '[0.8, 0.2, 0.1]'),
  ('blue sky',    '[0.1, 0.1, 0.9]'),
  ('night sky',   '[0.1, 0.2, 0.8]');
CREATE INDEX ON guide_demo USING hnsw (embedding vector_l2_ops);
SELECT title, round((embedding <-> '[0.85, 0.15, 0.1]')::numeric, 4) AS distance
FROM guide_demo ORDER BY embedding <-> '[0.85, 0.15, 0.1]' LIMIT 3;
DROP TABLE guide_demo;
SQL

The query vector [0.85, 0.15, 0.1] is closest to the two apples, which are returned first with the smallest distances, ahead of the sky rows — a nearest neighbour ranking by meaning, in plain SQL.

Security posture

• No baked credential. The postgres role ships password less; the per VM password is generated only on first boot and stored in /root/postgresql-pgvector-credentials.txt (mode 0600, root only).

• TLS for every remote connection. pg_hba.conf permits remote clients only via hostssl ... scram-sha-256. Each VM mints its own self signed certificate on first boot, so no certificate is shared between deployments.

• Local access stays password less through the unix socket for on box administration (sudo -u postgres psql).

• Network. Expose only TCP 22 and TCP 5432 in your NSG, and restrict 5432 to your application subnet. The NSG is the first layer, TLS and the per VM password the second.

To rotate the password later, run sudo -u postgres psql -c "ALTER ROLE postgres PASSWORD '<new>'" and update the credentials file to match.

Operations

Manage the service through systemd and inspect logs with journalctl:

sudo systemctl status postgresql.service --no-pager
sudo journalctl -u postgresql@17-main.service --no-pager | tail -20

Take a logical backup of a database with pg_dump, for example sudo -u postgres pg_dump vectordb > vectordb.sql. The data directory lives under /var/lib/postgresql/17/main.

Support

Every cloudimg image includes 24/7 support. If you have any questions about this PostgreSQL with pgvector image, contact us at support@cloudimg.co.uk.