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

[[email protected] ~]$ 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:[email protected]: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