pyVideoTrans

English

简体中文

English

简体中文

Appearance

Setting Up a Development Environment on Ubuntu: PostgreSQL + pgvector + Latest Node.js (fnm) ​

Introduction

Recently, I was setting up the development version of MaxKB on Ubuntu and needed a PostgreSQL database, vector search capabilities (via the pgvector extension), and the latest Node.js environment. I encountered some typical pitfalls along the way, but I also found solutions. To solidify my learning and to 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, 17)
  • Database Extension: pgvector (for vector similarity search)
  • Runtime: Latest version of Node.js (managed by fnm)

The default Ubuntu repositories might not have the latest PostgreSQL version. To use a newer version (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 the 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 the repository (will automatically detect 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 a specific version (e.g., PostgreSQL 17)
sudo apt install postgresql-17 postgresql-contrib-17 -y
# To install the latest stable version, remove the version number: sudo apt install postgresql postgresql-contrib

    postgresql-contrib includes some useful extensions and tools.

  3. Verify Installation & Service Status:

    bash
    sudo systemctl status [email protected]
# If the service name is different, 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.

    bash
    sudo -u postgres psql

    You should 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 execute CREATE DATABASE maxkb (without a semicolon) and immediately try to connect with \c maxkb, you'll encounter this error. Reason: Without a semicolon, the CREATE DATABASE command is not sent to the server for execution. \c is a psql meta-command that executes immediately, and the database doesn't exist yet. Solution: Ensure the CREATE DATABASE command ends with a semicolon and you see the CREATE DATABASE confirmation message before executing \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 your application.

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

Part 3: Installing the pgvector Extension (Crucial Steps) ​

We need to use vector search in the maxkb database.

  1. Attempt to Enable the Extension (Usually Fails): At 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 a database. The system-level files (e.g., .so, .sql, .control) of the extension must be installed in a location where the PostgreSQL server can find them (the DETAIL in the error message specifies the path).

  3. Solution: System-Level Installation of pgvector

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

      bash
      sudo apt update
# The package name is usually postgresql-<version>-pgvector
sudo apt install postgresql-17-pgvector -y

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

    • Method 2: Compile from Source Code (If APT is Unavailable or a Specific Version is Required) a. Install Dependencies (Resolve postgres.h Missing Issue): This is a necessary step for compiling PostgreSQL extensions! bash sudo apt update # The package name is usually postgresql-server-dev-<version> sudo apt install postgresql-server-dev-17 -y b. Download and Compile pgvector Source Code:bash # Assuming the 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 the PostgreSQL directory c. Restart the PostgreSQL Service:bash sudo systemctl restart [email protected]

  4. Try Enabling the Extension Again: After system-level installation, reconnect to the maxkb database (sudo -u postgres psql -d maxkb), and then execute again:

    sql
    CREATE EXTENSION vector;

    This time, you should see CREATE EXTENSION, indicating success!

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

We need the latest version of Node.js. fnm (Fast Node Manager) is an excellent choice, offering fast and easy multi-version management.

  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 the changes 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 display v22.x.x
npm -v   # Should display the corresponding npm version, e.g., 10.x.x

This environment setup journey covered installing and configuring PostgreSQL, database operations, the tricky extension installation issue (distinguishing between system-level installation and database-level enabling is key!), and using the modern tool fnm to manage Node.js versions. Key takeaways:

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