Ubuntu PostgreSQL扩展插件安装_产品动态_资讯中心

发布时间:2026-04-26 18:06:44

阅读量:2

Installing PostgreSQL Extensions in Ubuntu: A Step-by-Step Guide

PostgreSQL extensions enhance its core functionality, enabling features like geospatial data processing, vector similarity search, and external data connectivity. Below are guides for installing common extensions, categorized by complexity (built-in via apt vs. compiled from source).

1. Common Built-in Extensions (via `apt`)

Many essential extensions (e.g., pg_stat_statements, hstore, postgis) are available in Ubuntu’s default repositories and can be installed with a single command.

Steps to Install Built-in Extensions

Update System Packages:
Ensure your package list is current:
```
sudo apt update && sudo apt upgrade -y
```
Install PostgreSQL and Contrib Tools:
The postgresql-contrib package includes additional utilities and extensions:
```
sudo apt install postgresql postgresql-contrib -y
```
Install Specific Extensions:
Use apt to install desired extensions (replace with the target, e.g., postgresql-contrib, postgis, pg_stat_statements):
```
sudo apt install  -y
```
Enable Extensions in a Database:
Connect to your PostgreSQL database (replace with your database name) and run:
```
sudo -u postgres psql -d  -c "CREATE EXTENSION ;"
```
Example (enabling pg_stat_statements for performance monitoring):
```
sudo -u postgres psql -d postgres -c "CREATE EXTENSION pg_stat_statements;"
```

Key Built-in Extensions

pg_stat_statements: Tracks SQL statement performance (e.g., execution time, call count).
hstore: Stores and queries key-value pairs in a single column.
postgis: Adds geospatial data support (points, polygons, etc.) for GIS applications.
pgcrypto: Provides encryption functions for secure data storage.

2. Compiled Extensions (e.g., `pgvector`)

For extensions not available in apt (e.g., pgvector, which enables vector similarity search), you must compile from source.

Steps to Install `pgvector` (Latest Version)

Install Dependencies:
Compile pgvector requires build tools and PostgreSQL development headers:
```
sudo apt install build-essential postgresql-server-dev-$(pg_config --version | awk '{print $2}' | cut -d. -f1-2) libpq-dev -y
```
- pg_config --version: Retrieves your PostgreSQL version (e.g., 16.1).
- postgresql-server-dev-$(version): Installs headers for your specific PostgreSQL version.

Download and Compile pgvector:
Clone the pgvector repository (replace v0.7.4 with the latest tag) and compile:

cd /tmp
wget https://github.com/pgvector/pgvector/archive/refs/tags/v0.7.4.tar.gz
tar -xzf v0.7.4.tar.gz
cd pgvector-0.7.4
make clean
make PG_CONFIG=/usr/bin/pg_config  # Use the correct path to pg_config
sudo make install

Enable pgvector in a Database:
Connect to your target database and run:
```
sudo -u postgres psql -d  -c "CREATE EXTENSION vector;"
```

Verify Installation:
Check if the extension is listed:

sudo -u postgres psql -d  -c "\dx"

Create a test table and insert vectors to confirm functionality:

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));
INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;  # Find nearest neighbor

Notes for `pgvector`

Version Compatibility: Ensure pgvector matches your PostgreSQL version (e.g., pgvector 0.7.4 works with PostgreSQL 15 and below; newer versions may require updates).
Performance: Use appropriate indexes (e.g., IVFFLAT) for large datasets to optimize vector search.

Troubleshooting Tips

Permission Issues: Always use sudo -u postgres to run PostgreSQL commands as the postgres user.
Missing Headers: If compilation fails, verify postgresql-server-dev- is installed for your PostgreSQL version.
Extension Not Found: Ensure the extension is installed in the correct database (extensions are database-specific).

以上就是关于“Ubuntu PostgreSQL扩展插件安装”的相关介绍，筋斗云是国内较早的云主机应用的服务商，拥有10余年行业经验，提供丰富的云服务器、租用服务器等相关产品服务。云服务器资源弹性伸缩，主机vCPU、内存性能强悍、超高I/O速度、故障秒级恢复；电子化备案，提交快速，专业团队7×24小时服务支持！

简单好用、高性价比云服务器租用链接：https://www.jindouyun.cn/product/cvm