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

sudo dnf update
sudo dnf install epel-release
sudo dnf install htop
sudo dnf install atop

Install postgresql 16

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 postgresql16-server
yum install -y glibc-langpack-en
localectl set-locale LANG=en_US.UTF-8
/usr/pgsql-16/bin/postgresql-16-setup initdb
systemctl start postgresql-16.service
systemctl status postgresql-16.service
systemctl enable postgresql-16.service

Install build tools

sudo yum install gcc-c++ make
sudo dnf --enablerepo=crb install perl-IPC-Run
sudo yum install postgresql16-devel
sudo yum install postgresql16-contrib # pg_trgm btree_gist require by gitlab

Install pip

Run as root

dnf install python3-pip
pip install pgxnclient --no-warn-script-location
export PATH=$PATH:/usr/pgsql-16/bin

Install pgvector extension

Run as root

yum install redhat-rpm-config
pgxnclient install vector

Install pg gem correctly

bundle config build.pg --with-pg-config=/usr/pgsql-16/bin/pg_config
bundle install

Disable SELinux

vi /etc/selinux/config

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

vi /var/lib/pgsql/16/data/postgresql.conf
listen_addresses = '*'

Create user and DB

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/16/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
host kq_ai_db kq_ai 0.0.0.0/0 scram-sha-256

Reload conf without restart DB

sudo su - postgres
/usr/pgsql-16/bin/pg_ctl reload

Enable vector

psql -d kq_ai_db
CREATE EXTENSION vector;
ALTER EXTENSION vector UPDATE;

check with normal user kq_ai sql

SELECT extversion FROM pg_extension WHERE extname = 'vector';

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