오픈소스 설치 한글(hwp) 보고서 폼

PostgreSQL 10에서 PostgreSQ 13 설치하기

Rocky8.9 에서 Netbox 설치중에 Postgresql 버젼이 낮아 설치가 되지 않아
PostgreSQL10을 삭제하고 Postgresql 13으로 다시 설치 하는 로그(방법)

# 해당 에러 로그

    raise NotSupportedError(
django.db.utils.NotSupportedError: PostgreSQL 12 or later is required (found 10.23).

# psql -V
psql (PostgreSQL) 10.23

# sudo systemctl stop postgresql

# sudo systemctl list-unit-files postgresql*
UNIT FILE           STATE
postgresql.service  enabled
postgresql@.service disabled
2 unit files listed.

# sudo systemctl disable postgresql
Removed /etc/systemd/system/multi-user.target.wants/postgresql.service.

# rm -rf /var/lib/pgsql

# userdel postgres

# groupdel postgres
groupdel: group 'postgres' does not exist

# yum list installed *postgres*
Installed Packages
postgresql.x86_64                                     10.23-4.module+el8.9.0+1734+74bd286c                               @appstream
postgresql-server.x86_64                              10.23-4.module+el8.9.0+1734+74bd286c                               @appstream

# yum remove *postgre*
Dependencies resolved.
===================================================================================================================================
 Package                       Architecture       Version                                             Repository              Size
===================================================================================================================================
Removing:
 postgresql                    x86_64             10.23-4.module+el8.9.0+1734+74bd286c                @appstream             5.5 M
 postgresql-server             x86_64             10.23-4.module+el8.9.0+1734+74bd286c                @appstream              20 M

Transaction Summary
===================================================================================================================================
Remove  2 Packages

Freed space: 26 M
Is this ok [y/N]: y
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                           1/1
  Running scriptlet: postgresql-server-10.23-4.module+el8.9.0+1734+74bd286c.x86_64                                             1/1
  Running scriptlet: postgresql-server-10.23-4.module+el8.9.0+1734+74bd286c.x86_64                                             1/2
  Erasing          : postgresql-server-10.23-4.module+el8.9.0+1734+74bd286c.x86_64                                             1/2
warning: file /var/lib/pgsql: remove failed: No such file or directory

  Running scriptlet: postgresql-server-10.23-4.module+el8.9.0+1734+74bd286c.x86_64                                             1/2
  Erasing          : postgresql-10.23-4.module+el8.9.0+1734+74bd286c.x86_64                                                    2/2
  Running scriptlet: postgresql-10.23-4.module+el8.9.0+1734+74bd286c.x86_64                                                    2/2
  Verifying        : postgresql-10.23-4.module+el8.9.0+1734+74bd286c.x86_64                                                    1/2
  Verifying        : postgresql-server-10.23-4.module+el8.9.0+1734+74bd286c.x86_64                                             2/2

Removed:
  postgresql-10.23-4.module+el8.9.0+1734+74bd286c.x86_64       postgresql-server-10.23-4.module+el8.9.0+1734+74bd286c.x86_64

Complete!

dnf 로 Postgresql 설정 변경

dnf 로 Postgresql 10으로 설치했으면 다시 13으로 설치하려면 에러가 난다.
이런경우  dnf reset 명령어를 통하여 다시 설정 및 설치하는 방법이다.

# postgresql 10으로 설정되어 있음
# sudo dnf module list postgresql

Rocky Linux 8 - AppStream                                                                          5.9 kB/s | 4.8 kB     00:00
Rocky Linux 8 - BaseOS                                                                             3.9 kB/s | 4.3 kB     00:01
Rocky Linux 8 - Extras                                                                             3.1 kB/s | 3.1 kB     00:00
Extra Packages for Enterprise Linux 8 - x86_64                                                     4.6 kB/s | 5.1 kB     00:01
Rocky Linux 8 - AppStream
Name                     Stream                  Profiles                         Summary
postgresql               9.6                     client, server [d]               PostgreSQL server and client module
postgresql               10 [d][e]               client, server [d]               PostgreSQL server and client module
postgresql               12                      client, server [d]               PostgreSQL server and client module
postgresql               13                      client, server [d]               PostgreSQL server and client module
postgresql               15                      client, server [d]               PostgreSQL server and client module
postgresql               16                      client, server [d]               PostgreSQL server and client module

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled


# postgresql 13을 설치하면 module을 reset하라고 함
# sudo dnf module enable postgresql:13
Last metadata expiration check: 0:00:58 ago on Sat 01 Jun 2024 02:47:38 PM KST.
Dependencies resolved.
The operation would result in switching of module 'postgresql' stream '10' to stream '13'
Error: It is not possible to switch enabled streams of a module unless explicitly enabled via configuration option module_stream_switch.
It is recommended to rather remove all installed content from the module, and reset the module using 'dnf module reset <module_name>' command. After you reset the module, you can install the other stream.

# sudo dnf module reset postgresql
Rocky Linux 8 - AppStream                                                                          3.7 kB/s | 4.8 kB     00:01
Rocky Linux 8 - BaseOS                                                                             5.2 kB/s | 4.3 kB     00:00
Rocky Linux 8 - Extras                                                                             2.7 kB/s | 3.1 kB     00:01
Extra Packages for Enterprise Linux 8 - x86_64                                                     3.2 kB/s | 5.1 kB     00:01
Dependencies resolved.
===================================================================================================================================
 Package                        Architecture                  Version                         Repository                      Size
===================================================================================================================================
Resetting modules:
 postgresql

Transaction Summary
===================================================================================================================================

Is this ok [y/N]: y
Complete!

ation check: 0:03:14 ago on Sat 01 Jun 2024 02:53:04 PM KST.
Dependencies resolved.
===================================================================================================================================
 Package                        Architecture                  Version                         Repository                      Size
===================================================================================================================================
Enabling module streams:
 postgresql                                                   13

Transaction Summary
===================================================================================================================================

Is this ok [y/N]: y
Complete!

]# sudo dnf install postgresql-server
Last metadata expiration check: 0:03:46 ago on Sat 01 Jun 2024 02:53:04 PM KST.
Dependencies resolved.
===================================================================================================================================
 Package                        Architecture        Version                                           Repository              Size
===================================================================================================================================
Installing:
 postgresql-server              x86_64              13.14-1.module+el8.9.0+1736+ef8fa13a              appstream              5.6 M
Installing dependencies:
 libicu                         x86_64              60.3-2.el8_1                                      baseos                 8.8 M
 postgresql                     x86_64              13.14-1.module+el8.9.0+1736+ef8fa13a              appstream              1.5 M

Transaction Summary
===================================================================================================================================
Install  3 Packages

Total download size: 16 M
Installed size: 59 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): postgresql-13.14-1.module+el8.9.0+1736+ef8fa13a.x86_64.rpm                                  4.8 MB/s | 1.5 MB     00:00
(2/3): postgresql-server-13.14-1.module+el8.9.0+1736+ef8fa13a.x86_64.rpm                           7.4 MB/s | 5.6 MB     00:00
(3/3): libicu-60.3-2.el8_1.x86_64.rpm                                                              3.9 MB/s | 8.8 MB     00:02
-----------------------------------------------------------------------------------------------------------------------------------
Total                                                                                              2.7 MB/s |  16 MB     00:05
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                           1/1
  Installing       : libicu-60.3-2.el8_1.x86_64                                                                                1/3
  Running scriptlet: libicu-60.3-2.el8_1.x86_64                                                                                1/3
  Installing       : postgresql-13.14-1.module+el8.9.0+1736+ef8fa13a.x86_64                                                    2/3
  Running scriptlet: postgresql-server-13.14-1.module+el8.9.0+1736+ef8fa13a.x86_64                                             3/3
  Installing       : postgresql-server-13.14-1.module+el8.9.0+1736+ef8fa13a.x86_64                                             3/3
  Running scriptlet: postgresql-server-13.14-1.module+el8.9.0+1736+ef8fa13a.x86_64                                             3/3
  Verifying        : postgresql-13.14-1.module+el8.9.0+1736+ef8fa13a.x86_64                                                    1/3
  Verifying        : postgresql-server-13.14-1.module+el8.9.0+1736+ef8fa13a.x86_64                                             2/3
  Verifying        : libicu-60.3-2.el8_1.x86_64                                                                                3/3

Installed:
  libicu-60.3-2.el8_1.x86_64                                          postgresql-13.14-1.module+el8.9.0+1736+ef8fa13a.x86_64
  postgresql-server-13.14-1.module+el8.9.0+1736+ef8fa13a.x86_64

Complete!


# sudo postgresql-setup --initdb
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
 
 
 # systemctl start postgresql
 # systemctl enable postgresql
 # systemctl status postgresql

Maria DB HeidiSQL 접속하기

• DB설치 후 DB접속 환경 설정 
• 예) 서버 IP 192.168.56.231 

• 연결시 다음과 같은 오류 발생

• MariaDB접속후 접속가능한 ip대역 설정 

# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 150
Server version: 10.3.39-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


# DB 접속 후 현재 설정 확인하는 방법
# select Host,User,plugin,authentication_string FROM mysql.user;

MariaDB [(none)]> select Host,User,plugin,authentication_string FROM mysql.user;
+------------------+---------+-----------------------+-------------------------------------------+
| Host             | User    | plugin                | authentication_string                     |
+------------------+---------+-----------------------+-------------------------------------------+
| localhost        | root    | mysql_native_password | *CF0B70C060AC67B68E2C30F8B0E93BB61325D888 |
| rocky8phpipam231 | root    |                       |                                           |
| 127.0.0.1        | root    |                       |                                           |
| ::1              | root    |                       |                                           |
| localhost        | phpipam |                       |                                           |
+------------------+---------+-----------------------+-------------------------------------------+
5 rows in set (0.001 sec)

############### 각종 설정 변경 파일 ##################################

@ 모든 IP를 허용한다.
# GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '패스워드';

@ IP 대역대를 허용한다
# GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.56.0.%' IDENTIFIED BY '패스워드';

@ 특정 IP를 허용한다.
# GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.56.1' IDENTIFIED BY '패스워드';
 
@ IP 허용 후 원래 상태로 되돌리기
# DELETE FROM mysql.user WHERE Host='%' AND User='아이디';
# FLUSH PRIVILEGES;

• 모든 IP를 허용하도록 설정하고 접속 시도해 본다

# MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'phpipam';
Query OK, 0 rows affected (0.002 sec)

위의 방법으로 단될경우 서버의 방화벽 설정을 확인해 본다. 

• 방화벽 포트 추가 

@ 3306포트 혀용
# firewall-cmd --zone=public --add-port=3306/tcp --permanent

@ 80포트 허용
# firewall-cmd --zone=public --add-port=80/tcp --permanent

@ 방화벽 리스타트
# firewall-cmd --reload
# systemctl restart firewalld.service

• 방화벽 포트 확인

# firewall-cmd --list-all-zones

public (active)
  target: default
  icmp-block-inversion: no
  interfaces: eth0 eth1
  sources:
  services: cockpit dhcpv6-client ssh
  ports: 80/tcp 3306/tcp
  protocols:
  forward: no
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

..
..
..

설치

# https://www.postgresql.org/download/ 방문하여 OS 플랫폼, Ver 설정하면 스크립트 제공 (아래)

# 선택이 끝나면 위와 같이 설정 스크립트가 제공됨 (스크립트 순서에 따라 설치하면 됨)

# Install the repository RPM:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql

# Install PostgreSQL:
sudo dnf install -y postgresql15-server

# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15​

# Install the repository RPM: Postgresql 15.2ve

# sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the built-in PostgreSQL module:

# sudo dnf -qy module disable postgresql​

# Install PostgreSQL:

# sudo dnf install -y postgresql15-server​

# Optionally initialize the database and enable automatic start:

# sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
# sudo systemctl enable postgresql-15
# sudo systemctl start postgresql-15​

# 설치가 끝나면 
postgres 계정 생성됨 (/etc/passwd)
/var/lib/pgsql/15/data/ * 각종.conf 파일 생성
/usrpgql-15/bin/ *각종 실행명령어 파일  psql  생성 등

설치준비

# CentOS 8 
# CentOS 2대
- EDB-R1 : DB서버                    (모니터링을 당할 DB서버 / PEM-Agent 설치 추가)
- EDB-PEM : PEM DB서버        (모니터링 수집서버, 웹서버, EPM 서버)

# CentOS8 의 경우 기존 /etc/yum.repos.d/ 주소를 다음과 같이 변경함(2023. 1~ 이후 기존 repo 주소 안됨)

# sudo sed -i -e "s|mirrorlist=|#mirrorlist=|g" /etc/yum.repos.d/CentOS-*
# sudo sed -i -e "s|#baseurl=http://mirror.centos.org|baseurl=http://vault.centos.org|g" /etc/yum.repos.d/CentOS-*
  (/etc/yum.repos.d/파일의 미러주소를 mirror.centos... 에서 vault.centos.... 로 최신화) 

EDB 홈페이지 회원가입 - EDB 레포사이트 등록 

# https://www.enterprisedb.com/                                                                       / 회원가입 및 로그인
# https://www.enterprisedb.com/downloads/postgres-postgresql-downloads   / 각종 다운로드 사이트 주소

# EDB repo 주소를 /etc/yum.repos.d/에 추가함  
  - 위에 EDB Repo Token 값을 복사해야됨  - 아래 {     }  안의 값이 Token 값 붙혀넣기

# curl -1sLf 'https://downloads.enterprisedb.com/{OtRhf....v0j}/enterprise/setup.rpm.sh' | sudo -E bash

(폐쇄망에서는 EDB 전체 설치파일을 서버에 올려서 작업필요)

 

EDB v14설치 

==================== EDB 14 ver 설치방법 =======================================
# 설치 및 참고 주소 : https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
   (해당 os 클릭하면 설치방법 아래와 같이 나옴) 

# Install the repository RPM:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql

# Install PostgreSQL:
sudo dnf install -y postgresql14-server

# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14 sudo systemctl start postgresql-14


# 설치가 끝나면  주요 파일 위치
/etc/passwd  postgres 계정이 생성됨
tcp 5432 port 가 생성됨
/usr/pgsql-14/bin                                      / psql 등 실행파일
/var/lib/pgsql/14/data                               / pg_hba.conf     postgresql.conf 등 환경 설정파일

# /var/lib/pgsql/15/data/postgresql.conf
listen_addresses = 'localhost' 를 listen_addresses = '*' 로 변경

#/var/lib/pgsql/15/data/pg_hba.conf
host all all 0.0.0.0/0 scram-sha-256                        / 줄  추가

host all all 0.0.0.0/0 trust                                         / superuser 패스워드 없이 로그인시 줄 추가
(간혹 superuser 패스워드를 모를경우 trust mode로 설정하고
HeidiSQL 툴로 패스워드 없이 접속하여 superuser 초기 패스워드 변경)

# systemctl restart postgresql-14                               / 시스템 환경 변경시  restart는 필수

# 또는 

# /usr/pgsql-14/bin/./psql                                                                        #  관리자 모드로 진입 (postgre 계정으로 )
$ postgres=# alter user postgres with password `!........2'; 안먹힐때
$ postgres=# \password postgres
   Enter new password:<new-password>
$ postgres=# \q


# DB서버를 EDB-PEM으로 수집당해야 되기 때문에 EDB-PEM Agent를 설치함

# PEM Agent Install 

# yum install edb-pem-agetn 
# Agent 등록:(PEM repo database 에 등록)
export PEM_SERVER_PASSWORD=PEM-DB-PASSWD     /  -PEM에 설치되는 EDB superuser password 임


# /usr/edb/pem/agent/bin/pemworker --register-agent
 Postgres Enterprise Manager Server Hostname: 192.168.56.XX       / PEM 서버 IP 
 Postgres Enterprise Manager Server Username: enterprisedb           / PEM superuser user 이름
 Postgres Enterprise Manager Server Port: 5444                                 / PEM 서비스 포트
 Postgres Enterprise Manager Agent registered successfully!             

# Agent 설정 변경
 sed -i ‘/heartbeat_connection=false/heartbeat_connection=true/g’ /usr/edb/pem/agent/etc/agent.cfg

#  Agent 시작
 systemctl enable --now pemagent
 systemctl start pemagent

 EDB-PEM 서버 설치

# 설치
  dnf –y install edb-as14-server edb-pem

# DB 생성
  PGSETUP_INITDB_OPTIONS="-E UTF-8" /usr/edb/as14/bin/edb-as-14-setup initdb

#DB 설정 변경 및 상태 확인
  systemctl enable --now edb-as-14
  systemctl status edb-as-14

#  환경파일 설치가 끝나면 기본 경로

/etc/passwd                                   /    enterprisedb  계정이 생성됨
tcp 5444 port 가 생성됨
/usr/edb/as14                                 / edb 실행파일 경로
/usr/edb/pem                                  / edb pem 실행파일 경로
/var/lib/edb/as14/data                       / pg_hba.conf     postgresql.conf 등 환경 설정파일


# 방화벽 등 포트 오픈 또는 Disable
  firewall-cmd --permanent --zone=public --add-port=8443/tcp
  firewall-cmd –reload
  또는
   systemctl disable firewalld

# Agent 서버에 접속하게 pg_hba.conf 설정
vi $PGDATA/pg_hba.conf
host all all 0.0.0.0/0 scram-sha-256 << 추가

# edb pem 서버 db설치 (Config PEM)
  /usr/edb/pem/bin/configure-pem-server.sh

-------------------------- EDB Postgres Enterprise Manager --------------------------
Install type: 1:Web Services and Database, 2:Web Services 3: Database [ ] :1
Enter local database server installation path (i.e. /usr/edb/as12 , or /usr/pgsql-12, etc.) [ ] :/usr/edb/as14
Enter database super user name [ ] :enterprisedb
Enter database server port number [ ] :5444
Enter database super user password [ ] :  (여기서 문제임!!!!!!)
Please enter CIDR formatted network address range that agents will connect to the server from, to be added to the server's pg_hba.conf file. For example, 192.168.1.0/24 [ 0.0.0.0/0 ] : 0.0.0.0/0
Enter database systemd unit file or init script name (i.e. edb-as-12 or postgresql-12, etc.) [ ] :edb-as-14
Please specify agent certificate path (Script will attempt to create this directory, if it does not exists) [ /root/.pem/ ] :

# pem Config 설치할때  enterprisedb 의 패스워드를 신규로 설정하거나 묻는게 없어서
   enterprisedb의 초기 패스워드를 모름  (설치 에러남)
  이럴경후  pg_hba.conf 파일의  host all all 0.0.0.0/0 scram-sha-256   ---> 에서  trust mode로 변경하고 
  HeidiSQL 툴로 패스워드 없이 접속하여 superuser 초기 패스워드 변경하여야 함

# pem 추가 서비스 시작
HTTP
# systemctl start httpd.service

EPAS Server
# systemctl start edb-as-14.service

PEM agent
systemctl start pemagent.service

=== 설치완료 ==

PEM 서버 접속 웹화면   https://PEM-IP주소:8443/pem/   로그인은 계정은 enterprisedb 및 password 임