728x90
1. rocky linux 9 버전용 repo 설치
[app@test11 ~]$ sudo dnf install https://ftp.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
2. postgesql -server 버전 확인 및 패키지 설치 : 17까지 설치가 되는걸로 확인
[app@test11 ~]$ sudo dnf install postgresql
PostgreSQL common RPMs for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 1.0 kB/s | 659 B 00:00
PostgreSQL common RPMs for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Is this ok [y/N]: y
PostgreSQL common RPMs for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 1.0 MB/s | 695 kB 00:00
PostgreSQL 18 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 3.9 kB/s | 659 B 00:00
PostgreSQL 18 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Is this ok [y/N]: y
PostgreSQL 18 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 434 kB/s | 335 kB 00:00
PostgreSQL 17 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 6.1 kB/s | 659 B 00:00
PostgreSQL 17 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Is this ok [y/N]: y
PostgreSQL 17 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 1.6 MB/s | 835 kB 00:00
PostgreSQL 16 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 4.4 kB/s | 659 B 00:00
PostgreSQL 16 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Is this ok [y/N]: y
PostgreSQL 16 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 2.1 MB/s | 1.0 MB 00:00
PostgreSQL 15 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 1.7 kB/s | 659 B 00:00
PostgreSQL 15 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Is this ok [y/N]: y
PostgreSQL 15 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 2.3 MB/s | 1.3 MB 00:00
PostgreSQL 14 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 1.7 kB/s | 659 B 00:00
PostgreSQL 14 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Is this ok [y/N]: y
PostgreSQL 14 for RHEL / Rocky Linux / AlmaLinux 9 - x86_64
3. postgre sql 13 설치
wildfly와 연동할 예정이라, tested configurtaion에 따라 정리
[app@test11 ~]$ sudo dnf install postgresql
[app@test11 ~]$ psql --version
psql (PostgreSQL) 13.23
지금까지는 postgresql client 패키지만 설치가 된 상황.
postgresql-server를 설치
[app@test11 ~]$ sudo dnf install postgresql-server
Last metadata expiration check: 0:22:11 ago on Thu 12 Mar 2026 10:18:06 AM KST.
Dependencies resolved.
==============================================================================================================================================================================================================
Package Architecture Version Repository Size
==============================================================================================================================================================================================================
Installing:
postgresql-server x86_64 13.23-2.el9_7 appstream 5.7 M
Transaction Summary
==============================================================================================================================================================================================================
Install 1 Package
Total download size: 5.7 M
Installed size: 23 M
Is this ok [y/N]: y
Downloading Packages:
postgresql-server-13.23-2.el9_7.x86_64.rpm 1.3 MB/s | 5.7 MB 00:04
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 1.2 MB/s | 5.7 MB 00:04
postgresql 은 설치 후 데이터 디렉토리 초기화가 필요.
데이터 디렉토리는 보통
/var/lib/pgsql/data 이다.
[app@test11 ~]$ sudo postgresql-setup --initdb
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[app@test11 ~]$
postgresql 기동
[app@test11 ~]$ sudo systemctl start postgresql
[app@test11 ~]$ systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; preset: disabled)
Active: active (running) since Thu 2026-03-12 10:46:24 KST; 2s ago
Process: 54004 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
Main PID: 54006 (postmaster)
Tasks: 8 (limit: 22984)
Memory: 16.8M (peak: 17.0M)
CPU: 40ms
CGroup: /system.slice/postgresql.service
├─54006 /usr/bin/postmaster -D /var/lib/pgsql/data
├─54009 "postgres: logger "
├─54011 "postgres: checkpointer "
├─54012 "postgres: background writer "
├─54013 "postgres: walwriter "
├─54014 "postgres: autovacuum launcher "
├─54015 "postgres: stats collector "
└─54016 "postgres: logical replication launcher "
4. postgresql 관리자 계정 접속
기본적으로 postgres Linux 계정으로 접속한다.
[app@test11 ~]$ sudo -i -u postgres
[postgres@test11 ~]$
5. DB 접속
[postgres@test11 ~]$ psql
psql (13.23)
Type "help" for help.
postgres=#
6. 테스트 DB 생성
postgres=# CREATE DATABASE appdb;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
appdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
7. 테스트 계정 생성
postgres=# CREATE USER appuser WITH PASSWORD '1234!';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
GRANT
postgres=#
8. listen address 허용 설정
[app@test11 ~]$ sudo vi /var/lib/pgsql/data/postgresql.conf
[app@test11 ~]$ sudo vi /var/lib/pgsql/data/pg_hba.conf
※ 만약 아래의 scram-sha-256을 테스트 장비에서까지 하기 사용하기 귀찮다면, scram-sha-256을 md5로 작성해주자
9. postgresql 계정에서 appuser 계정으로 로그인
※ 아래와 같은 계정 인증 관련 ERROR 을 마주할 수 있다
[app@test11 ~]$ sudo -i -u postgres
[postgres@test11 ~]$ psql -h 192.168.56.104 -U appuser -d appdb
Password for user appuser:
psql: error: FATAL: password authentication failed for user "appuser"
이 때는 빠르게 다음과 같이 db password가 어떻게 처리되는지를 확인
[postgres@test11 ~]$ psql
psql (13.23)
Type "help" for help.
postgres=# SHOW password_encryption;
password_encryption
---------------------
md5
(1 row)
현재의 설정으로는, 192.168.56.104 로 붙을 때는 반드시 scram-sha-256 인증을 탄다.
그런데 현재 appuser 비밀번호가 SCRAM 형식으로 저장되지 않았을 가능성이 큼.
PostgreSQL 문서상 scram-sha-256 인증은 SCRAM 비밀번호 검증을 요구하고,
MD5로 저장된 비밀번호는 md5 또는 password 방식에서만 동작.
조치로는 appuser의 비밀번호를 SCRAM으로 저장, 그러고서 appuser로 로그인 하면 됨
[postgres@test11 ~]$ psql
psql (13.23)
Type "help" for help.
# 비밀번호 처리 방식 확인
postgres=# SHOW password_encryption;
password_encryption
---------------------
md5
(1 row)
#SCRAM으로 변환 작업 수행
postgres=# ALTER SYSTEM SET password_encryption = 'scram-sha-256';
ALTER SYSTEM
#혹시 모르니 생성한 계정의 비밀번호 변경작업 수행
postgres=# ALTER ROLE appuser WITH PASSWORD '1234!';
ALTER ROLE
#appuser로 접속하기 위해 관리자 계정 나가기
postgres=# \q
#appuser로 접속
[postgres@test11 ~]$ psql -h 192.168.56.104 -U appuser -d appdb
Password for user appuser: 1234!
psql (13.23)
Type "help" for help.
#접속완료 확인
appdb=>
10. 테이블 생성 및 데이터 값 입력
#테이블 생성
appdb=> CREATE TABLE member (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
dept VARCHAR(100),
email VARCHAR(200),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE
#데이터 넣기
appdb=> INSERT INTO member (name, dept, email) VALUES
('Kim', 'Middleware', 'kim@test.com'),
('Lee', 'Web', 'lee@test.com'),
('Park', 'Infra', 'park@test.com');
INSERT 0 3
#테이블 조회
appdb=> SELECT * FROM member;
id | name | dept | email | created_at
----+------+------------+---------------+---------------------------
1 | Kim | Middleware | kim@test.com | 2026-03-12 13:15:42.41461
2 | Lee | Web | lee@test.com | 2026-03-12 13:15:42.41461
3 | Park | Infra | park@test.com | 2026-03-12 13:15:42.41461
(3 rows)
'DB관련' 카테고리의 다른 글
| postgresql 관련 정리(connection refused) (0) | 2026.04.10 |
|---|---|
| cubrid DB 설치 (0) | 2026.04.02 |
| 테스트 용 Rocky Linux9 에 Maria DB 설치 (0) | 2026.03.05 |
| [mysql8] JNDI 테스트 관련 계정 생성 및 테이블 생성 (0) | 2024.08.07 |
| JDBC_Driver Certi 관련 정리 (0) | 2022.01.27 |