Skip to main content

PostgreSQL

Install​

yay -S postgresql

Disable CoW​

sudo chattr +C /var/lib/postgres/data

Initialize​

sudo -u postgres -i initdb --locale=en_US.UTF-8 -E UTF8 -D /var/lib/postgres/data

Enable & Start Service​

sudo systemctl enable --now postgresql.service

Ignore Pkg​

/etc/pacman.conf
IgnorePkg = postgresql postgresql-libs

postgresql.conf​

/var/lib/postgres/data/postgresql.conf
listen_addresses = 'localhost,10.10.0.5'                # what IP address(es) to listen on;
password_encryption = scram-sha-256

pg_hba.conf​

/var/lib/postgres/data/pg_hba.conf
# "local" is for Unix domain socket connections only
local   all             postgres                                peer
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

Set password for postgres​

[postgres@delta ~]$ psql
psql (13.4)
Type "help" for help.

postgres=# \password postgres
Enter new password:
Enter it again:

Dump database​

pg_dump -U miniflux miniflux > miniflux.sql

Create database​

create database miniflux;
create user miniflux with encrypted password 'YOUR_PASSWORD';
grant all privileges on database miniflux to miniflux;

Import data​

psql -U miniflux -h localhost -d miniflux -f miniflux.sql

Upgrade​

Ref: https://wiki.archlinux.org/title/PostgreSQL#pg_upgrade

prometheus-postgresql-exporter​

/etc/conf.d/prometheus-postgresql-exporter
DATA_SOURCE_USER="postgres"
DATA_SOURCE_PASS="YOUR_PASSWORD"
DATA_SOURCE_NAME="postgresql://postgres:YOUR_PASSWORD@127.0.0.1:5432/postgres?sslmode=disable"
PG_EXPORTER_AUTO_DISCOVER_DATABASES=true
POSTGRES_EXPORTER_ARGS="--web.listen-address=10.10.0.5:9187"

TODO​

  • TLS
  • Access Permission
  • How to upgrade
  • How to backup