当前位置：首页 > news >正文

安装postgresql和PGVector

news 来源：原创 2024/9/21 19:01:09

1. 概述

研发有需要，要使用PGVector做向量。简单记录安装postgresql和PGVector过程。

2. 参考

postgresql官方下载连接
postgresql官方linux yum安装
PostgreSQL的安装、配置与使用指南
PostgreSQL向量数据插件–pgvector安装

3. 安装

3.1 只安装postgresql，不安装PGVector

postgresql可以直接从官网下载仓库源，然后直接安装。
如果不想升级CentOS7，可以直接安装postgresql14。
注意：这种方式无法安装PGVector。安装PGVector必须编译安装postgresql

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql16-server
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14-server
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

postgresql安装完成后，查看端口（5432）

netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:6379            0.0.0.0:*               LISTEN      69818/redis-server  
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      11566/sshd          
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      66829/postmaster    
tcp6       0      0 192.168.8.63:9200       :::*                    LISTEN      79905/java          
tcp6       0      0 192.168.8.63:9300       :::*                    LISTEN      79905/java          
tcp6       0      0 :::22                   :::*                    LISTEN      11566/sshd          
tcp6       0      0 ::1:5432                :::*                    LISTEN      66829/postmaster    
tcp6       0      0 :::5666                 :::*                    LISTEN      58730/xinetd

默认系统限制本地登录，密码为空
注意！请登录后修改密码

sudo -u postgres psql

[root@localhost public]# sudo -u postgres psql
psql (14.12)
输入 "help" 来获取帮助信息.postgres=# ALTER USER postgres WITH PASSWORD 'yourpassword';
postgres-# exit
使用\q 退出.
postgres-# \q

切换到postgres账号下编辑配置文件，避免权限出错

su - postgres

cd /var/lib/pgsql/14/data/

编辑postgresql.conf，修改以下内容

vim postgresql.conf

listen_addresses = '*'          # what IP address(es) to listen on;max_connections = 5000                  # (change requires restart)# 这里取了服务器内存的25%
shared_buffers = 32000MB                        # min 128kBwork_mem = 32MB                         # min 64kBmaintenance_work_mem = 128MB            # min 1MB

编辑pg_hba.conf，修改以下内容

vim pg_hba.conf

增加允许网络连接。注意需要加在IPv4 local connections 下第一条

# IPv4 local connections:
host    all             all             0.0.0.0/0               scram-sha-256

3.2 安装postgresql和PGVector

安装PGVector，需要采用编译安装postgresql方式

3.2.1 安装postgresql 16

移除系统中的postgresql（系统中没有安装过，可以忽略）

rpm -qa|grep postgresql

yum remove postgresql*

准备安装环境

yum install -y perl-ExtUtils-Embed python-devel bison flex readline-devel zlib-devel gcc gcc-c++ wget libicu-devel

下载需要的安装源码
下载网址：https://www.postgresql.org/ftp/source/v16.3/

下载并解压

tar -zxvf postgresql-16.3.tar.gz  && cd postgresql-16.0

./configure --prefix=/usr/local/pgsql --enable-debug
sudo make && sudo make install

cd contrib/
make && sudo make install

创建账号

useradd postgres

mkdir -p /public/postgresql/data

chown postgres:postgres /public/postgresql -R
chown postgres:postgres /usr/local/pgsql -R

添加环境变量

vim /etc/profile

export PGHOME=/usr/local/pgsql
export PGDATA=/public/postgresql/data
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

初始化数据库

su postgres

source /etc/profile

initdb -D /public/postgresql/data

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".Data page checksums are disabled.fixing permissions on existing directory /public/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... okinitdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.Success. You can now start the database server using:pg_ctl -D /public/postgresql/data -l logfile start

编辑配置文件（同上）

cd /public/postgresql/datavim postgresql.confvim pg_hba.conf

启动数据库

pg_ctl -D /public/postgresql/data -l logfile start

修改密码
postgres@localhost data]$ psql

psql (16.3)
Type "help" for help.postgres=# ALTER USER postgres WITH PASSWORD 'yourpassword';
ALTER ROLE
postgres=# \q
[postgres@localhost data]$

3.2.1 安装pgvector 0.7.4

安装git

yum install -y git

下载pgvector

下载网址：https://github.com/pgvector/pgvector/tags

解压源码

tar -zxvf pgvector-0.7.4.tar.gz && cd pgvector-0.7.4

在命令行export设置临时环境变量

sudo make PG_CONFIG=/usr/local/pgsql/bin/pg_config 
sudo make PG_CONFIG=/usr/local/pgsql/bin/pg_config install

登录数据库安装vector 插件

su - postgres

[postgres@localhost ~]$ psql

postgres=# CREATE EXTENSION vector;

测试向量

postgres=# CREATE TABLE sj_test (id bigserial PRIMARY KEY, embedding vector(3));
postgres=# INSERT INTO sj_test (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');
postgres=# select * from sj_test;