Deploy pgvector for Private AI Hosting on a Hostperl VPS

What you get from pgvector on a VPS

pgvector for private AI hosting gives you semantic search inside PostgreSQL, so you can keep embeddings beside your application data and stay in control of the whole stack. For teams that want predictable costs, tighter privacy, and fewer moving parts, it is often a better fit than splitting vectors into a separate service. If you are sizing infrastructure for this kind of workload, a Hostperl VPS is usually the cleanest place to start because you can match CPU, RAM, and storage to the dataset instead of paying for oversized managed platforms.

This tutorial takes you from the first SSH login on a fresh server through PostgreSQL, pgvector, a test table, and a working similarity query. It assumes you want a private AI setup for documents, support content, or internal knowledge search, not a public chatbot service.

Connect to the server and identify the operating system

On your local computer, open a terminal and connect to your VPS:

ssh root@203.0.113.10

Replace 203.0.113.10 with the public IP assigned to your server. The example address is reserved for documentation.

If your provider gives you a non-root SSH account first, use that initial login instead, then switch to root with sudo once verified.

On the VPS as root, identify the distribution before you install anything:

cat /etc/os-release

You will use different package commands on Debian and Ubuntu than on AlmaLinux and Rocky Linux, so do not skip this step.

Create a sudo user and update the fresh VPS

A private AI database is not a reason to keep working as root. Create a named admin account, add your SSH key, and keep the original root session open until the new login works.

On the VPS as root, use the matching commands for your system.

Ubuntu and Debian

adduser deploy
usermod -aG sudo deploy
mkdir -p /home/deploy/.ssh
chmod 700 /home/deploy/.ssh
cp /root/.ssh/authorized_keys /home/deploy/.ssh/authorized_keys
chown -R deploy:deploy /home/deploy/.ssh
chmod 600 /home/deploy/.ssh/authorized_keys

This creates the deploy account, grants sudo access, and copies the existing key-based login so you can test a second session. If your root key lives elsewhere, copy that file instead.

AlmaLinux and Rocky Linux

useradd -m deploy
passwd deploy
usermod -aG wheel deploy
mkdir -p /home/deploy/.ssh
chmod 700 /home/deploy/.ssh
cp /root/.ssh/authorized_keys /home/deploy/.ssh/authorized_keys
chown -R deploy:deploy /home/deploy/.ssh
chmod 600 /home/deploy/.ssh/authorized_keys

On RHEL-compatible systems, the wheel group usually controls sudo access. Keep the password only if you need it for break-glass access; otherwise, your SSH key should be enough.

Now update the server and install the basic tools you will need for verification:

Ubuntu and Debian

apt update
apt -y upgrade
apt -y install curl gnupg lsb-release ca-certificates sudo ufw

AlmaLinux and Rocky Linux

dnf -y update
dnf -y install curl gnupg2 ca-certificates sudo firewalld

After the upgrade, reboot if the kernel or libc changed. That is safer than finding out later that PostgreSQL installed against stale system libraries.

Install PostgreSQL and pgvector

pgvector for private AI hosting depends on PostgreSQL with the extension package available. Hostperl customers often run this on a managed VPS hosting plan when they want room for vector search, app processes, and scheduled backups on the same machine.

Ubuntu and Debian

On current Ubuntu and Debian releases, install PostgreSQL and the vector extension package from the distribution repositories:

apt -y install postgresql postgresql-contrib postgresql-server-dev-all build-essential git
apt -y install postgresql-16-pgvector

If your release uses a different PostgreSQL major version, install the matching package name shown by apt search pgvector. The service name should be postgresql.

AlmaLinux and Rocky Linux

On AlmaLinux and Rocky Linux, enable the PostgreSQL repository first, then install the server and the extension package:

dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %rhel)-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf -qy module disable postgresql
dnf -y install postgresql16-server postgresql16 postgresql16-contrib postgresql16-devel pgvector_16

The exact package name can vary by repository release. If pgvector_16 is not available, check the repo metadata with dnf search pgvector.

Initialize and start the database service:

Ubuntu and Debian

systemctl enable --now postgresql
systemctl status postgresql --no-pager

AlmaLinux and Rocky Linux

/usr/pgsql-16/bin/postgresql-16-setup initdb
systemctl enable --now postgresql-16
systemctl status postgresql-16 --no-pager

A healthy status screen should show the service as active. If it does not, check the journal before moving on.

Create the database, extension, and test data

Next, create a database and the extension that adds vector types and similarity operators.

On the VPS as root for Ubuntu and Debian, or as the PostgreSQL superuser on RHEL-compatible systems, run:

sudo -iu postgres psql

Inside the PostgreSQL prompt, create the database and enable the extension:

CREATE DATABASE aiapp;
\c aiapp
CREATE EXTENSION vector;

Leave the prompt with \q after the extension is created.

Now create a small table that stores sample document embeddings. This uses a 3-dimensional example so you can test the workflow quickly before loading real model output.

On the VPS as root, open a SQL file:

nano /tmp/pgvector-demo.sql

Paste this content:

CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  title text NOT NULL,
  body text NOT NULL,
  embedding vector(3) NOT NULL
);

INSERT INTO documents (title, body, embedding) VALUES
('Backups', 'Nightly database backups with restore tests', '[0.10,0.20,0.30]'),
('WordPress', 'Staging sites and safe launches', '[0.12,0.18,0.33]'),
('DNS', 'Nameservers, records, and propagation', '[0.90,0.10,0.05]');

SELECT id, title FROM documents ORDER BY embedding <-> '[0.11,0.19,0.31]' LIMIT 2;

Save the file with Ctrl+O, press Enter, then exit with Ctrl+X.

Run it through PostgreSQL:

sudo -iu postgres psql -d aiapp -f /tmp/pgvector-demo.sql

You should see three inserted rows and a two-row similarity result. That confirms the extension is loaded and the vector operator works.

Lock down access and keep the database private

Do not expose PostgreSQL directly to the internet unless you absolutely need remote application access. For most private AI workloads, keep it bound to localhost and connect from your app or reverse proxy.

Check the listening address first:

ss -ltnp | grep 5432

If you see 127.0.0.1:5432 or ::1:5432, PostgreSQL is local-only. That is the safe default.

On Ubuntu and Debian, PostgreSQL settings usually live in /etc/postgresql/16/main/. On AlmaLinux and Rocky Linux, the main files are under /var/lib/pgsql/16/data/. Edit postgresql.conf only if you need app access from another local service or container.

If you do change the file, make one controlled edit and test syntax by restarting the service only after reviewing the config. For example, set:

listen_addresses = '127.0.0.1'
password_encryption = scram-sha-256

For firewall rules, only open PostgreSQL when a specific remote application requires it. For most customers, the better choice is to leave port 5432 closed and place the app on the same VPS or connect through a private network.

Run a simple application check

Before you hook this into an AI app, confirm that a client can read from the database. This quick check helps support teams distinguish a PostgreSQL issue from an embedding pipeline issue.

On the VPS as the non-root sudo user, test the database with a one-line query:

sudo -iu postgres psql -d aiapp -c "SELECT title FROM documents ORDER BY embedding <-> '[0.11,0.19,0.31]' LIMIT 1;"

You should get Backups as the closest match. That tells you the table, data, and vector comparison path are all working.

If you want to connect this stack to a private app later, pair it with a small VPS and an app process manager. Hostperl customers commonly run that pattern alongside Docker app deployment on VPS or a Python service behind Nginx.

Verification, reboot checks, and backup readiness

After installation, verify the service state, package version, and reboot behavior so you know the setup will survive maintenance.

On the VPS as root, run these checks:

psql --version
systemctl is-enabled postgresql || systemctl is-enabled postgresql-16
systemctl is-active postgresql || systemctl is-active postgresql-16
journalctl -u postgresql -n 50 --no-pager || journalctl -u postgresql-16 -n 50 --no-pager

You want to see a recent PostgreSQL version, an enabled service, and no repeated errors in the journal. If you get an extension loading error, confirm the pgvector package matches the PostgreSQL major version.

For backup discipline, schedule dumps and test a restore before production data lands on the server. If you already run PostgreSQL elsewhere, the workflow in PostgreSQL backup and restore on a Hostperl VPS is the right companion guide. Pair it with a smaller restore test on every major schema change.

For teams evaluating capacity, Hostperl's Hostperl VPS hosting for private AI is usually enough for pilots, internal knowledge search, and moderate document libraries. If your embeddings grow into tens of millions of rows, move to a larger VPS or a dedicated server before query latency becomes a support problem.

Troubleshooting the most common failures

Extension not found: Run apt search pgvector on Debian or Ubuntu, or dnf search pgvector on AlmaLinux and Rocky Linux. The clue is a package name mismatch. Install the version that matches your PostgreSQL major release, then rerun CREATE EXTENSION vector;.

Service will not start: Check journalctl -u postgresql -n 100 --no-pager or journalctl -u postgresql-16 -n 100 --no-pager. If the journal points to a data directory problem, rerun the init command for your distribution and confirm permissions on the PostgreSQL data directory.

Similarity query returns errors: Run SELECT extname FROM pg_extension; inside the database. If vector is missing, you created the database correctly but not the extension. Reconnect to aiapp and enable it again.

Remote app cannot connect: Run ss -ltnp | grep 5432. If PostgreSQL listens only on localhost, that is expected for a single-server deployment. Move the application onto the same VPS or configure private networking before opening the port.

Why this setup works for Hostperl customers

This design keeps your data close to your application, reduces the number of services you have to patch, and makes support easier when something breaks. It also fits the way many Hostperl customers launch private AI features: start on a VPS, validate the workload, then scale up only when usage proves the need.

If you are building a search feature, a document assistant, or an internal knowledge base, pgvector on PostgreSQL is usually the fastest path from prototype to production. Keep the deployment simple, test restores, and leave room for growth.

FAQ

Can I use pgvector with existing PostgreSQL data?
Yes. Create the extension in the target database, add a vector column, and backfill embeddings in batches so you do not lock large tables for too long.

Do I need a separate vector database?
Not for many private AI workloads. PostgreSQL with pgvector is enough for internal search, retrieval, and smaller customer-facing assistants.

Should I expose PostgreSQL to the public internet?
No, not by default. Keep it local or private, and let the app connect from the same VPS or a trusted network.

How do I know when to upgrade the server?
Watch query latency, RAM usage, and disk growth. If embeddings and indexes keep growing, move to a larger VPS or a dedicated server before the database slows your app.

What should I test after a reboot?
Check that PostgreSQL starts automatically, the extension still loads, and the similarity query still returns the expected row.