PostgreSQL

This guide walks you through preparing your PostgreSQL environment for MCPMark evaluation.

1. Setup PostgreSQL Environment

1.1 Start PostgreSQL with Docker

1. Run PostgreSQL Container Start a PostgreSQL instance using Docker:

   docker run -d \
     --name mcpmark-postgres \
     -e POSTGRES_PASSWORD=password \
     -e POSTGRES_USER=postgres \
     -p 5432:5432 \
     pgvector/pgvector:0.8.0-pg17-bookworm
   

2. Verify Container is Running

   docker ps | grep mcpmark-postgres
   

---

1.2 Import Sample Databases

1. Download Database Backups Download the backup files and place them in ./postgres_state/ directory:

   mkdir -p ./postgres_state
   cd ./postgres_state
   
   # Download all database backups
   wget https://storage.mcpmark.ai/postgres/employees.backup
   wget https://storage.mcpmark.ai/postgres/chinook.backup
   wget https://storage.mcpmark.ai/postgres/dvdrental.backup
   wget https://storage.mcpmark.ai/postgres/sports.backup
   wget https://storage.mcpmark.ai/postgres/lego.backup
   
   cd ..
   

2. Create Databases and Restore from Backups

   Make sure your Postgres client version matches the server's version (e.g., pg17).

   # Set the password environment variable
   export PGPASSWORD=password
   
   # Create and restore each database
   createdb -h localhost -U postgres employees
   pg_restore -h localhost -U postgres -d employees -v ./postgres_state/employees.backup
   
   createdb -h localhost -U postgres chinook
   pg_restore -h localhost -U postgres -d chinook -v ./postgres_state/chinook.backup
   
   createdb -h localhost -U postgres dvdrental
   pg_restore -h localhost -U postgres -d dvdrental -v ./postgres_state/dvdrental.backup
   
   createdb -h localhost -U postgres sports
   pg_restore -h localhost -U postgres -d sports -v ./postgres_state/sports.backup
   
   createdb -h localhost -U postgres lego
   pg_restore -h localhost -U postgres -d lego -v ./postgres_state/lego.backup
   

3. Verify Databases are Imported

   # List all databases
   PGPASSWORD=password psql -h localhost -U postgres -c "\l"
   

---

2. Configure Environment Variables

Configure environment variables: make sure the following enservice credentials are added in .mcp_env:

## PostgreSQL Configuration
POSTGRES_HOST="localhost"
POSTGRES_PORT="5432"
POSTGRES_USERNAME="postgres"
POSTGRES_PASSWORD="password"

3. Verify Connection

Verify the PostgreSQL setup is working correctly:

# Test connection using psql
PGPASSWORD=password psql -h localhost -U postgres -c "SELECT version();"

4. Common Operations

Stop PostgreSQL Container

docker stop mcpmark-postgres

Start PostgreSQL Container

docker start mcpmark-postgres

Remove PostgreSQL Container (Clean Setup)

docker stop mcpmark-postgres
docker rm mcpmark-postgres

Access PostgreSQL Shell

PGPASSWORD=mysecretpassword psql -h localhost -U postgres

5. Running Postgres Experiment

For single task or task group, run

python -m pipeline --exp-name EXPNAME --mcp postgres --tasks POSTGRESTASK --models MODEL

Here EXPNAME refers to customized experiment name, POSTGRESTASK refers to the postgres task or task group selected (see tasks/ for specific task information), MODEL refers to the selected model (see Introduction Page for model supported), K refers to the time of independent experiments.

6. Troubleshooting

Port Already in Use

If port 5432 is already in use, you can use a different port:

docker run -d \
   ```bash
   docker run -d \
     --name mcpmark-postgres \
     -e POSTGRES_PASSWORD=password \
     -e POSTGRES_USER=postgres \
     -p 5433:5432 \
     pgvector/pgvector:0.8.0-pg17-bookworm

Remember to update POSTGRES_PORT="5433" in your .mcp_env file.

Connection Refused

Ensure the Docker container is running and the port mapping is correct:

docker ps
docker logs mcpmark-postgres