Installing and managing PostgreSQL on Linux

Installing and managing PostgreSQL on Linux

This article explains how to install and manage PostgreSQL 15 on Debian/Ubuntu Linux, including the pgvector extension and Docker environments, for administrators working with applications such as PixelProza.

Requirements

- Linux server with Debian 11/12 or Ubuntu 20.04/22.04/24.04 - `sudo` access to the server - PostgreSQL 15 (specifically for pgvector support) - Optional: Docker and Docker Compose for container environments - Basic knowledge of the `psql` client and SQL commands

Steps

1. Install PostgreSQL via apt

Run the package manager update and install PostgreSQL 15 with the contrib packages:

sudo apt update
sudo apt install postgresql-15 postgresql-contrib-15

The contrib packages include additional functionality such as `pg_dump`, `pg_restore`, and support for advanced indexes and extensions. The service starts automatically after installation and configures itself to start at system boot.

2. Open the psql prompt

The installation creates a default system user `postgres`. Switch to this user and open the database prompt:

sudo -i -u postgres
psql

You are now in the PostgreSQL shell. Exit it temporarily with `\q` if you want to run other commands first.

3. Create a database and user

Create a dedicated database and user for your application. Replace `pixelproza` with the name used by your application:

CREATE USER pixelproza WITH PASSWORD 'choose_a_strong_password';
CREATE DATABASE pixelproza OWNER pixelproza;
GRANT ALL PRIVILEGES ON DATABASE pixelproza TO pixelproza;

Exit the psql prompt with `\q` and return to your normal user with `exit`.

4. Install the pgvector extension

pgvector 0.5.0+ is required for vector search functionality. Install the package:

sudo apt install postgresql-15-pgvector

This extension adds the `vector` type and index methods such as HNSW and IVFFLAT to PostgreSQL 15.

5. Enable pgvector for your database

Open the psql prompt again and activate the extension in the database:

sudo -i -u postgres
psql -U postgres -d pixelproza

Then run the SQL command:

CREATE EXTENSION IF NOT EXISTS vector;

Without this step, you cannot create vector columns or use vector indexes.

6. Create a table with a vector column

Create a table that includes an embedding column. This example shows a `knowledge_items` table as used in PixelProza:

CREATE TABLE knowledge_items (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding vector(1536)
);

The vector dimension (1536) must match the output dimension of your embedding model.

7. Create vector indexes

Create an HNSW index for fast, accurate searches:

CREATE INDEX ix_knowledge_embedding_hnsw ON knowledge_items 
USING hnsw (embedding vector_cosine_ops) 
WITH (m=16, ef_construction=64);

Additionally, create an IVFFLAT index for larger datasets:

CREATE INDEX idx_knowledge_embedding ON knowledge_items 
USING ivfflat (embedding vector_cosine_ops) 
WITH (lists=100);

HNSW offers better performance with smaller datasets; IVFFLAT scales better with millions of rows.

8. Configure network access (optional)

By default, PostgreSQL listens only on localhost. For connections from other hosts, edit the configuration file:

sudo nano /etc/postgresql/15/main/postgresql.conf

Find the line `#listen_addresses = 'localhost'` and change it to:

listen_addresses = '*'

Then edit the authentication file:

sudo nano /etc/postgresql/15/main/pg_hba.conf

Add this line for access from your application server:

host pixelproza pixelproza 192.168.1.0/24 md5

Replace `192.168.1.0/24` with the subnet of your application servers. Restart PostgreSQL:

sudo systemctl restart postgresql

Security is crucial here: open ports must be protected with firewalls or network isolation.

Verification

Check that the installation works correctly. Connect to the database and test the vector functionality:

psql -U pixelproza -h localhost -d pixelproza

Then run:

SELECT * FROM pg_extension WHERE extname = 'vector';
SELECT * FROM knowledge_items LIMIT 1;

The first query should return a row with `vector` as the extension name. The second query confirms that the table exists and is accessible.

Common issues

1. Cannot connect from another host

Check that `listen_addresses` is set correctly and that `pg_hba.conf` contains the correct host rule. Remember to restart PostgreSQL after changes:

sudo systemctl restart postgresql

Test the connection with `psql -h -U pixelproza -d pixelproza`.

2. pgvector extension cannot be enabled

The error `extension "vector" is not available` means the package is not installed correctly. Check that you are using PostgreSQL 15 and install the correct package:

sudo apt install postgresql-15-pgvector

3. Vector index is not used in queries

Force the use of the index with an explicit hint in your query:

SET enable_seqscan = off;
SELECT * FROM knowledge_items 
ORDER BY embedding <=> '[0.1, 0.2, ...]' 
LIMIT 10;

Use `EXPLAIN` to verify that the index is actually being used.

Docker environments

In a Docker environment such as PixelProza, you use the `pgvector/pgvector:pg15` image. This includes PostgreSQL 15 with pgvector pre-installed. Manage the database via the container layer:

docker compose up -d
docker exec -it postgres-db psql -U pixelproza -d pixelproza

Viewing logs:

docker compose logs -f pixelproza-api
docker compose logs -f postgres-db

Making backups from a container:

docker exec postgres-db pg_dump -U pixelproza -d pixelproza > backup.sql

This approach simplifies installation and scalability, but requires knowledge of Docker and the `docker compose.yml` configuration.