How to create a fully offline document storage and query information using postgres and ollama

Quite often we find ourselves staring at a form—whether a job application or an online profile—only to realize we can’t remember all the details we need. What was my exact start date at that company? What address did I list last time? Usually we end up digging through folders on our computer, opening PDFs, and hunting for that elusive info.

Imagine instead having a little assistant you could ask:

“Hey, when did I start at Ideas and Innovation Squared Technologies Pvt Ltd (II2) Technologies?” . When I did I start at CGI .

Instant answer, no document rummaging required. Sounds pretty handy, right? Even if you’re skeptical, read on!

What you’ll need

• A computer: To run your own LLM locally, I used a MacBook Pro.
• An LLM server: You can choose a hosted API (OpenAI, AWS, etc.) or run one locally. I’m using Ollama to serve the DeepSeek-R1:7 model on my machine
• A postgres server running locally .

Below you’ll find step-by-step setup instructions to get this running on your desktop. Let’s dive in!

Prerequisites

- Python 3.8+

- [PostgreSQL](https://www.postgresql.org/) with [pgvector extension](https://github.com/pgvector/pgvector)

- [Ollama](https://ollama.com/) installed and running (for local LLM inference)

- (Optional) [git](https://git-scm.com/) for cloning the repo

Setup Instructions

1. Clone the Repository

```sh
git clone https://github.com/balusasidharan/llmapp_docsearch
cd docsearch

``` 2. Create and Activate a Virtual Environment
```sh
python3 -m venv venv
source venv/bin/activate

```        

3. Install Python Dependencies

```sh
pip install --upgrade pip
pip install -r requirements.txt
```        

4. Set Up PostgreSQL with pgvector

- Ensure PostgreSQL is running.
- Create a database (e.g., docsearch).
- Enable the pgvector extension:
  ```sql
  CREATE EXTENSION IF NOT EXISTS vector;
 ```
- Set the connection string as an environment variable:
  ```sh
  export  PGVECTOR_CONNECTION_STRING="postgresql+psycopg://<<replace with postgres username>>:<<password>>@localhost:5432/docsearch" 
  ```        

5. Prepare Ollama and Model

- Install Ollama from [https://ollama.com/](https://ollama.com/)
- Pull the desired model (e.g., deepseek-r1:7b):
  ```sh
  ollama pull deepseek-r1:7b
  ```        

6. Index Your Documents

- Place your documents (PDFs, text files) in a folder (e.g., vectordocs/).
- Run the indexer script to populate the database:
  ```sh
  python postgres_indexer2.py
  ```        

This will load documents that you place in the location mentioned in the python postgres_indexer2.py file. For me Im setting the location as below. I have used PDF documents. I placed my resume and H1B docs in the folder. Then I ran the indexer whih will pace the documents in vector db

docs = load_and_split("/Users/balusasidharanpillai/vectordocs")        

Once it is succesfully run you will tables created like this in the database "docsearch" as shown below

7. Additional Step

- When you run the indexer you will probably face some errors with certain column names. So run the below SQL statements using the pgAdmin console or PSQL console

```
  ALTER TABLE langchain_pg_embedding
  ALTER COLUMN id SET DEFAULT gen_random_uuid();
  ALTER TABLE langchain_pg_embedding ADD COLUMN uuid TEXT;
  ALTER TABLE langchain_pg_embedding ADD COLUMN custom_id TEXT;
  ```        

After running the above queries run the postgres_indexer2.py again

8. Start the FastAPI Application

```sh
uvicorn app:app --reload --port 8000   
```        

9. Query the API

Send a POST request to /search/:

```sh
curl -X POST http://localhost:8000/search/ \
     -H "Content-Type: application/json" \
     -d '{"q":"Your question here"}'

```        

Notes

- Make sure your Postgres user has permission to create extensions and tables.
- The embedding model and Ollama model can be changed in the code as needed.
- For production, configure environment variables and security as appropriate.        

Troubleshooting

- If you see errors about missing columns, ensure your database schema matches the latest code.
- If you see errors about missing Python packages, re-run pip install -r requirements.txt. or install specific packages using

  for eg : in my case I had install psycopg library it can be done using
  ```
   (venv) ➜  docsearch git:(main) venv/bin/pip install "psycopg[binary]"
   ```
- For Ollama issues, ensure the Ollama server is running and the model is pulled. 

After doing the above steps and I ran the test using postman API and this is the result I get. Removed the actual DOB, but the result was accurate.