Eric Guo's blog.cloud-mes.com

Hoping writing JS, Ruby & Rails and Go article, but fallback to DevOps note

Install PostgreSQL 16.1 on Rocky Linux 9.2 With Pgxn Client and Pgvector

• Permalink

Install htop and atop

Install htop and atop
sudo dnf update

sudo dnf install epel-release

sudo dnf install htop

sudo dnf install atop

Install postgresql 17 (original 16.1)

Install postgresql 17 (original 16.1)
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum update

yum install postgresql17-server

yum install -y glibc-langpack-en

localectl set-locale LANG=en_US.UTF-8

/usr/pgsql-17/bin/postgresql-17-setup initdb

systemctl start postgresql-17.service

systemctl status postgresql-17.service

systemctl enable postgresql-17.service

Install build tools

Install build tools
sudo yum install gcc-c++ make

sudo dnf --enablerepo=crb install perl-IPC-Run

sudo yum install postgresql17-devel

sudo yum install postgresql17-contrib # pg_trgm btree_gist require by gitlab

Install pip

Run as root

Install pip
dnf install python3-pip

pip install pgxnclient --no-warn-script-location

export PATH=$PATH:/usr/pgsql-17/bin

Install pgvector extension

Run as root

Install pgvector with PGXN on Rocky Linux
yum install redhat-rpm-config

pgxnclient install vector

If in MacOS

Install pgvector with PGXN on macOS
brew install pgxnclient

pgxnclient install --pg_config $(brew --prefix postgresql@17)/bin/pg_config vector

Install pg gem correctly

Install pg gem correctly
bundle config build.pg --with-pg-config=/usr/pgsql-17/bin/pg_config

bundle install

Disable SELinux

Disable SELinux
vi /etc/selinux/config

Disable firewall

Disable firewall
firewall-cmd --zone=public --add-port=5432/tcp

firewall-cmd --permanent --zone=public --add-port=5432/tcp

firewall-cmd --reload

systemctl restart firewalld

Allow Remote Addresses

Open the PostgreSQL configuration file
vi /var/lib/pgsql/17/data/postgresql.conf
Configure PostgreSQL to listen on all interfaces
listen_addresses = '*'

Create user and DB

Create the PostgreSQL role and database
sudo su - postgres

createuser kq_ai --pwprompt

psql

ALTER ROLE kq_ai LOGIN;

CREATE DATABASE kq_ai_db WITH ENCODING='UTF8' OWNER=kq_ai;

logout

vi /var/lib/pgsql/17/data/pg_hba.conf
Add the PostgreSQL host authentication rule
# TYPE  DATABASE        USER            ADDRESS                 METHOD

host    kq_ai_db        kq_ai           0.0.0.0/0               scram-sha-256

Reload conf without restart DB

Reload conf without restart DB
sudo su - postgres

/usr/pgsql-17/bin/pg_ctl reload

Enable vector

Create and update the vector extension
psql -d kq_ai_db

CREATE EXTENSION vector;

ALTER EXTENSION vector UPDATE;

check with normal user kq_ai sql

Check the installed vector extension version
SELECT extversion FROM pg_extension WHERE extname = 'vector';

Export DB

Export DB
pg_dump thape_forum_prod -O -x > thape_forum_prod_db.sql

zip thape_forum_prod_db.zip thape_forum_prod_db.sql
`

Import DB

psql --username=thape_forum --password --host=localhost -d thape_forum_prod -f thape_forum_prod_db.sql

Comments