Deployment guide

PostgreSQL pgvector

Overview

This guide covers the deployment and use of PostgreSQL 17 with pgvector on Ubuntu 24.04 on Azure, using SecureAnyCloud 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-neighbor 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.

This is the storage layer behind semantic search and retrieval-augmented generation (RAG), and it runs 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. That database also ships a small demo items table with a vector(3) column and an HNSW index, so you can run a nearest-neighbor query the moment the VM boots.

Security by design: no baked credential. The postgres superuser role is passwordless 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. It 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 passwordless through the local unix socket. Until first boot creates the certificate, the database is not reachable off the box at all.

What you get:

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 and 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 passwordless through the unix socket
Unattended security upgrades left enabled, so the appliance keeps receiving patches

Prerequisites

An active Azure subscription, an SSH public key, and a VNet plus subnet in the target region
A subscription to this listing on Azure Marketplace
A Network Security Group that allows 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:

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:postgresql-pgvector-ubuntu-24-04:default:latest
az vm create \
  --resource-group my-rg \
  --name pgvector-1 \
  --image sac1647283583153:postgresql-pgvector-ubuntu-24-04:default:latest \
  --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, so 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-neighbor 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 typically have 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, so they are returned first with the smallest distances, ahead of the sky rows. This is a nearest-neighbor ranking by meaning, in plain SQL.

Security posture

No baked credential. The postgres role ships passwordless. 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 through hostssl ... scram-sha-256. Each VM creates its own self-signed certificate on first boot, so no certificate is shared between deployments.
Local access stays passwordless 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, and TLS plus the per-VM password are 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 SecureAnyCloud image includes 24/7 support. If you have questions about this PostgreSQL with pgvector image, contact us at sales@secureanycloud.com.

Deploy on Azure

Launch PostgreSQL 17 with pgvector on Ubuntu 24.04 on Azure with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com

Deploy on Azure

Overview

This guide covers the deployment and use of PostgreSQL with pgvector on AWS using SecureAnyCloud's preconfigured Amazon Machine Image (AMI). 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-neighbor 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.

This is the storage layer behind semantic search and retrieval-augmented generation (RAG), and it runs on CPU with no GPU required.

The cluster data directory lives on a dedicated, independently resizable EBS volume mounted at /var/lib/postgresql, separate from the operating system disk.

On first boot, each instance generates a unique password for the postgres role and a unique self-signed TLS server certificate. It 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 passwordless through the local unix socket. Until first boot creates the certificate, the database is not reachable off the box at all.

What you get:

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-instance postgres password and a per-instance TLS certificate, generated on first boot and written to a root-only credentials file
pg_hba.conf configured so remote clients connect only over TLS with the per-instance password, while local access stays passwordless through the unix socket
A dedicated EBS data volume for the database at /var/lib/postgresql
Unattended security upgrades left enabled, so the appliance keeps receiving patches

Prerequisites

An AWS account subscribed to this product in AWS Marketplace
An EC2 key pair in your target region for SSH access
A security group that allows inbound TCP 22 (administration) from your IP and TCP 5432 (the PostgreSQL wire protocol) from your application network. In production, restrict 5432 to your application subnet.
Recommended instance type: m5.large (2 vCPU, 8 GB RAM) for development and light workloads. For larger vector collections and higher query rates, choose a memory-rich type such as r5.large or larger, so more of the index fits in RAM.

Connecting to your instance

OS variant: Ubuntu 24.04
Login user: ubuntu
Example: ssh -i your-key.pem ubuntu@<instance-public-ip>

Step 1 - Launch from the AWS Marketplace console

Open the product page in AWS Marketplace and choose Continue to Subscribe, then Continue to Configuration.
Select the PostgreSQL 17 + pgvector on Ubuntu 24.04 delivery option and your region, then choose Continue to Launch.
Choose your instance type, VPC and subnet, key pair and the security group described above, then launch.

Step 2 - Launch from the AWS CLI

aws ec2 run-instances \
  --image-id ami-xxxxxxxxxxxxxxxxx \
  --instance-type m5.large \
  --key-name your-key \
  --security-group-ids sg-xxxxxxxx \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=pgvector}]'

Step 3 - Connect to your instance

ssh -i your-key.pem ubuntu@<instance-public-ip>

Step 4 - Retrieve your per-instance credentials

On the first boot, the instance 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-instance password plus a ready-to-paste TLS connection string. Nothing is baked into the image, so every deployed instance has its own password and certificate.

Step 5 - 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.

Step 6 - Connect to the database

On the instance itself, 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-instance password from the credentials file. Replace <public-ip> with your instance's address and <POSTGRES_PASSWORD> with the value from the file:

PGPASSWORD=<POSTGRES_PASSWORD> psql "host=<public-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.

Step 7 - Run a similarity search

The vectordb database ships a demo items table with three-dimensional embeddings and an HNSW index, so you can run a nearest-neighbor query immediately. The <-> operator is Euclidean (L2) distance, <=> is cosine distance and <#> is inner product.

This query ranks the seeded rows by distance to the query vector [1,1,1]:

sudo -u postgres psql -d vectordb -c "SELECT label, round((embedding <-> '[1,1,1]')::numeric, 4) AS distance FROM items ORDER BY embedding <-> '[1,1,1]' LIMIT 3;"

The near-origin row is returned first at distance 0, ahead of mid and far. This is a nearest-neighbor ranking by similarity to the query vector, in plain SQL.

Store your own embeddings

To store your own vectors, create a table with a vector column of your embedding dimension, insert your embeddings, build an approximate nearest-neighbor index, and order by distance to a query vector. Real embeddings typically have hundreds or thousands of dimensions. This example uses three for clarity:

sudo -u postgres psql -d vectordb <<'SQL' CREATE TABLE documents (id bigserial PRIMARY KEY, title text, embedding vector(3)); INSERT INTO documents (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]'); CREATE INDEX ON documents USING hnsw (embedding vector_l2_ops); SELECT title, round((embedding <-> '[0.85, 0.15, 0.1]')::numeric, 4) AS distance FROM documents ORDER BY embedding <-> '[0.85, 0.15, 0.1]' LIMIT 3; SQL

Swap vector(3) for your model's dimension (for example vector(1536) for many text embedding models) and generate the embeddings with your preferred model or an AWS service such as Amazon Bedrock.