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

If in MacOS

brew install pgxnclient

pgxnclient install --pg_config $(brew --prefix postgresql@16)/bin/pg_config 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