3.5 PostgreSQL with pgvector ¶

The Ubuntu package starts and enables PostgreSQL automatically:

sudo systemctl status postgresql

Create the application databases:

sudo -u postgres createdb ownsona
sudo -u postgres createdb ownsona_test     # used by sql/run_tests.sh

Create the application role and run the schema migration. The repository ships a one-shot setup script; pass it the password you want the application to use:

PGPW="$(openssl rand -base64 24 | tr -d '/+=' | head -c 32)"
echo "Save this somewhere safe: $PGPW"

cd /home/ownsona/ownsona     # see step 'Clone and build' if not yet cloned
sql/setup_db.sh "$PGPW"

The script:

• creates the ownsona PostgreSQL role with the supplied password;
• runs sql/001_init.sql against the ownsona database (creates the vector and pg_trgm extensions, the memories table, all indexes including the HNSW vector index, the duplicate-prevention partial unique index, and the updated_at trigger);
• grants the ownsona role the table and sequence privileges it needs plus CREATE ON SCHEMA public and ownership of memories (and its sequence). The auto-migrator (see Upgrading an existing install) needs these to create its db_version bookkeeping table and apply future migrations without requiring the postgres superuser at runtime.

Run the migration against the test database too:

sudo -u postgres psql -d ownsona_test -f sql/001_init.sql

You will use $PGPW again in Configure application.ini.