pyVideoTrans

English

简体中文

English

简体中文

Appearance

Hands-on Guide: Setting Up Ubuntu Development Environment with PostgreSQL + pgvector + Latest Node.js (fnm) ​

Introduction

Recently, while setting up the development version of MaxKB on Ubuntu, I needed a project environment with PostgreSQL, vector search capabilities (via the pgvector extension), and the latest Node.js. Along the way, I encountered some typical pitfalls but found solutions. To reinforce my learning and help others facing similar issues, I've compiled the entire process into this guide. I hope it saves you some debugging time!

Target Environment:

  • Operating System: Ubuntu (commands based on APT package manager)
  • Database: PostgreSQL (specific version, e.g., 17)
  • Database Extension: pgvector (for vector similarity search)
  • Runtime: Latest Node.js (managed via fnm)

The default Ubuntu repositories may not have the latest PostgreSQL version. To use newer versions (like v17 as needed later) and receive timely updates, it's recommended to use the official PostgreSQL Global Development Group (PGDG) APT repository.

  1. Import GPG Key & Add Repository:

    bash
    # Install dependencies
sudo apt update
sudo apt install curl ca-certificates gnupg -y

# Import key
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/postgresql-archive-keyring.gpg >/dev/null

# Add repository (auto-detects Ubuntu version)
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

  2. Update Package List & Install PostgreSQL:

    bash
    sudo apt update
# Install specific version (e.g., PostgreSQL 17)
sudo apt install postgresql-17 postgresql-contrib-17 -y
# To install the latest stable version, omit the version number: sudo apt install postgresql postgresql-contrib

    postgresql-contrib includes useful extensions and tools.

  3. Verify Installation & Service Status:

    bash
    sudo systemctl status [email protected]
# If the service name differs, try: sudo systemctl status postgresql

    Ensure the service is running (active (running)).

Part 2: Connecting to PostgreSQL & Creating a Database (and Common Errors) ​

  1. Connect to the Database: Use the default postgres superuser to connect.

    bash
    sudo -u postgres psql

    Upon success, you'll see the postgres=# prompt.

  2. Create a Database (e.g., maxkb):

    sql
    CREATE DATABASE maxkb;

    Note: SQL statements in psql must end with a semicolon (;)!

  3. Common Error: FATAL: database "X" does not exist If you run CREATE DATABASE maxkb (without a semicolon) and immediately try to connect with \c maxkb, you'll encounter this error. Reason: Without the semicolon, the CREATE DATABASE command isn't sent to the server for execution. \c is a psql meta-command that executes immediately, and at that point, the database indeed doesn't exist. Solution: Ensure the CREATE DATABASE command ends with a semicolon and you see the CREATE DATABASE confirmation message before running \c maxkb.

  4. Connect to the New Database:

    sql
    \c maxkb

    The prompt will change to maxkb=#.

  5. (Recommended) Create a Dedicated User: For security, it's best to create a separate user for the application.

    sql
    -- Execute in the postgres=# or maxkb=# prompt
CREATE USER maxkb_user WITH PASSWORD 'your_strong_password';
ALTER DATABASE maxkb OWNER TO maxkb_user;
-- Or grant privileges: GRANT ALL PRIVILEGES ON DATABASE maxkb TO maxkb_user;
\q -- Exit psql

Part 3: Installing the pgvector Extension (Critical Step) ​

We need to enable vector search in the maxkb database.

  1. Attempt to Enable the Extension (Usually Fails Initially): In the maxkb=# prompt:

    sql
    CREATE EXTENSION vector;

  2. Typical Error: ERROR: extension "vector" is not available

    ERROR:  extension "vector" is not available
DETAIL:  Could not open extension control file "/usr/share/postgresql/17/extension/vector.control": No such file or directory.
HINT:  The extension must first be installed on the system where PostgreSQL is running.

    Reason: CREATE EXTENSION only enables an extension within the database, but the system-level files (like .so, .sql, .control) for that extension must be already installed where the PostgreSQL server can find them (the DETAIL in the error message shows the path).

  3. Solution: System-Level Installation of pgvector

    • Method 1: Install via APT (Recommended) This is the simplest and most recommended method.

      bash
      sudo apt update
# Package name is typically postgresql-<version>-pgvector
sudo apt install postgresql-17-pgvector -y

# Restart PostgreSQL service after installation
sudo systemctl restart [email protected]

    • Method 2: Compile from Source (If APT doesn't have it or a specific version is needed) a. Install Dependencies (to fix postgres.h missing error): This is essential for compiling PostgreSQL extensions! bash sudo apt update # Package name is typically postgresql-server-dev-<version> sudo apt install postgresql-server-dev-17 -y b. Download and Compile pgvector Source Code:bash # Assuming source code is in /tmp/pgvector git clone --branch v0.7.0 https://github.com/pgvector/pgvector.git /tmp/pgvector # Optionally specify a version cd /tmp/pgvector make clean # Clean old builds (optional) make # Compile sudo make install # Install to PostgreSQL directory c. Restart PostgreSQL Service:bash sudo systemctl restart [email protected]

  4. Retry Enabling the Extension: After system-level installation is complete, reconnect to the maxkb database (sudo -u postgres psql -d maxkb), then run again:

    sql
    CREATE EXTENSION vector;

    This time, it should show CREATE EXTENSION, indicating success!

Part 4: Installing the Latest Node.js (Using fnm) ​

We need the latest Node.js version. fnm (Fast Node Manager) is an excellent choice—it's fast and easy for managing multiple versions.

  1. Install fnm:

    bash
    curl -fsSL https://fnm.vercel.app/install | bash

  2. Activate fnm (Crucial!): The installation script modifies your shell configuration. You need to reload it or open a new terminal for it to take effect.

    bash
    # For Bash (default)
source ~/.bashrc
# Or close the current terminal and open a new one

  3. Use fnm to Install Node.js (e.g., v22):

    bash
    fnm install 22
# Or install the latest LTS: fnm install --lts
# Or install the latest Current: fnm install node

  4. Verify Installation:

    bash
    node -v  # Should show v22.x.x
npm -v   # Should show the corresponding npm version, e.g., 10.x.x

Summary

This environment setup journey covered PostgreSQL installation and configuration, database operations, tricky extension installation issues (understanding the difference between system-level installation and database-level enabling is key!), and using modern tools like fnm for Node.js version management. Key takeaways:

  • Use official or recommended software sources (like PGDG for PostgreSQL).
  • Read error messages carefully; DETAIL and HINT often contain crucial clues.
  • Understand how PostgreSQL extensions work: install system-wide first, then enable in the database.
  • Compiling C extensions requires corresponding development packages (-dev or -devel).
  • Pay attention to semicolons in psql!
  • Using version managers (like fnm or nvm) for Node.js is a smart choice.
  • After installing a version manager, remember to reload your shell configuration or open a new terminal.