Configuring PostgreSQL

Configuring PostgreSQL:

Check status:

systemctl status postgresql

Enable (if needed):

sudo systemctl enable postgresql

Initalize db directory:

sudo postgresql-setup --initdb

This creates /var/lib/pgsql/data (the PGDATA directory), sets up the initial database cluster, configures default postgresql.conf and pg_hba.conf, and sets ownership to the postgres system user.

Start psql (create user/pass+db):

sudo -u postgres psql

Run (one at a time):

    CREATE USER localagent WITH PASSWORD 'abcd1234';
    CREATE DATABASE localagentllm OWNER localagent;
    GRANT ALL PRIVILEGES ON DATABASE localagentllm TO localagent;
    \q

This should coincide with DATABASE_URL in the .env file. (app root directory) (e.g. DATABASE_URL=postgresql://localagent:abcd1234@localhost:5432/localagentllm)

Check connection:

psql -h localhost -U localagent -d localagentllm

It will ask for the password, if configured properly.

If you have an authentication error: psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: Ident authentication failed for user "localagent"

This means you need to change the ident, to either scram-sha-256 (modern) or md5 (legacy), in your pg_hba.conf file.

sudo nano /var/lib/pgsql/data/pg_hba.conf

(If it's blank, that's not the path for your system. Use sudo find /var/lib -name pg_hba.conf to locate the file.)

At the bottom you'll see a table:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident

Change both ident values to scram-sha-256. Save and exit.

Now, retry the login (it should prompt for password):

psql -h localhost -U localagent -d localagentllm

If you log in, it's working as expected. Exit using \q.

Now you are ready to run the DB migration:

npm run migrate