PostgreSQL (1)

1. PostgreSQL 14 설치

$ dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-aarch64/pgdg-redhat-repo-latest.noarch.rpm --nogpgcheck
$ dnf -qy module disable postgresql --nogpgcheck
$ dnf install -y postgresql14-server --nogpgcheck
$ /usr/pgsql-14/bin/postgresql-14-setup initdb
$ systemctl enable postgresql-14
$ systemctl start postgresql-14

2. psql 접속

$ su - postgres
$ psql
psql (14.5)
Type "help" for help.

postgres=#

3. Role 확인

postgres-# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

4. 데이터베이스 생성

$ createdb mydb

5. 데이터베이스 삭제

$ dropdb mydb

6. 데이터베이스 접속

$ psql mydb

7. 버젼 확인

# SELECT version();

8. 날짜 확인

# SELECT current_date;

9. 원격 접속시 에러 해결 방안

The connection attempt failed.
  EOFException
  java.io.EOFException
-> postgresql.conf의 listen_addresses = '*' 로 설정
기본 설정 값은 listen_addresses = 'localhost'

FATAL: no pg_hba.conf entry for host "172.26.0.1", user "catchv", database "postgres", no encryption
FATAL: no pg_hba.conf entry for host "172.26.0.1", user "catchv", database "postgres", no encryption

pg_gba.conf 파일에 추가
host    all             all             0.0.0.0/0               scram-sha-256

10. /usr/lib/systemd/system/postgresql-14.service

[Unit]
Description=PostgreSQL 14 database server
Documentation=https://www.postgresql.org/docs/14/static/
After=syslog.target
After=network.target

[Service]
Type=notify

User=postgres
Group=postgres

# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.

# Location of database directory
Environment=PGDATA=/var/lib/pgsql/14/data/

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-14/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT

# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0

# 0 is the same as infinity, but "infinity" needs systemd 229
TimeoutStartSec=0

TimeoutStopSec=1h

[Install]
WantedBy=multi-user.target

11. 관련 디렉토리

PGDATA=/var/lib/pgsql/14/data/
PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
# config 관련
/var/lib/pgsql/14/data/postgresql.conf
/var/lib/pgsql/14/data/pg_hba.conf
/var/lib/pgsql/14/data/pg_ident.conf