安装postgresql和PGVector

石小千

4341人浏览 · 2024-08-17 16:58:00

石小千 · 2024-08-17 16:58:00 发布

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 ]# 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 128kB

work_mem = 32MB                         # min 64kB

maintenance_work_mem = 128MB            # min 1MB

# 开启ssl，配置自签名证书和key，注意文件的权限是600，属主是postgres:postgres
ssl = on
ssl_cert_file = '/public/postgresql/data/postgresql-crt.pem'
ssl_key_file = '/public/postgresql/data/postgresql-key.pem'

# 开启日志收集器
logging_collector = on          # Enable capturing of stderr, jsonlog,
                                        # and csvlog into log files. Required
                                        # to be on for csvlogs and jsonlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
# 日志存放路径，需预先创建并赋权
log_directory = '/public/postgresql/log' # directory where log files are written,
                                        # can be absolute or relative to PGDATA
# 文件格式
log_filename = 'postgresql-%Y-%m-%d.log'        # log file name pattern,
                                        # can include strftime() escapes
log_file_mode = 0600                    # creation mode for log files,
                                        # begin with 0 to use octal notation
# 每天1个                                        
log_rotation_age = 1d                   # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
# 不限制文件大小
log_rotation_size = 0                   # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.

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

vim pg_hba.conf

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

# IPv4 local connections:
# 示例，请根据需要修改IP
hostssl 	all             all             172.31.0.0/16           scram-sha-256
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/

wget https://ftp.postgresql.org/pub/source/v16.3/postgresql-16.3.tar.gz

下载并解压

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

指定安装目录编译安装
- 如果编译使用–with-ssl=openssl，openssl版本>1.1.1+，且需要指定下列“头文件”和“库文件”路径
- CFLAGS=“-I/usr/local/openssl/include” 指定 OpenSSL头文件路径路径,比如ssl.h
- LDFLAGS="-L/usr/local/openssl/lib 指定openssl库文件路径，比如libssl.so
- -lssl -lcrypto 显式链接 OpenSSL 的 libssl 和 libcrypto 库
- 上述/usr/local/openssl是openssl自定义安装路径，根据实际情况修改

./configure --prefix=/usr/local/pgsql --enable-debug --with-ssl=openssl  CFLAGS="-I/usr/local/openssl/include" \
    LDFLAGS="-L/usr/local/openssl/lib -lssl -lcrypto" 
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 ... ok

initdb: 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/data

vim postgresql.conf

vim pg_hba.conf

启动数据库

pg_ctl -D /public/postgresql/data  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]$

安装postgresql客户端

装官方源

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

搜索客户端包

[root@centos7 ~]# yum search postgresql14
已加载插件：fastestmirror
Loading mirror speeds from cached hostfile
===================================================== N/S matched: postgresql14 ======================================================
postgresql14.x86_64 : PostgreSQL client programs and libraries
postgresql14-contrib.x86_64 : Contributed source and binaries distributed with PostgreSQL
postgresql14-devel.x86_64 : PostgreSQL development header files and libraries
postgresql14-docs.x86_64 : Extra documentation for PostgreSQL
postgresql14-libs.x86_64 : The shared libraries required for any PostgreSQL clients
postgresql14-llvmjit.x86_64 : Just-in-time compilation support for PostgreSQL
postgresql14-odbc.x86_64 : PostgreSQL ODBC driver
postgresql14-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql14-plpython3.x86_64 : The Python3 procedural language for PostgreSQL
postgresql14-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql14-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql14-tcl.x86_64 : A Tcl client library for PostgreSQL
postgresql14-test.x86_64 : The test suite distributed with PostgreSQL

安装客户端

yum install postgresql14.x86_64

远程连接

未开启ssl连接的情况（pg_hba.conf中配置host）

psql -h 192.168.X.X -p 5432 -U postgres -W

开启ssl的连接情况（pg_hba.conf中配置hostssl）

root@centos7:~# psql "host=192.168.5.145 user=postgres sslmode=require"
Password for user postgres: 
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1), server 16.3)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=#

root@centos7:~# psql "host=192.168.5.145 user=postgres sslmode=disable"
psql: error: connection to server at "192.168.5.145", port 5432 failed: FATAL:  no pg_hba.conf entry for host "172.31.1.1", user "postgres", database "postgres", no encryption

3.2.2 安装pgvector 0.7.4

安装git

yum install -y git

下载pgvector

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

# gitcode镜像站
git clone --branch v0.7.4 https://gitcode.com/gh_mirrors/pg/pgvector.git

解压源码

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;