반응형
안녕하세요.
작은 프로젝트 개념으로 진행했던 작업을 정리해서 올립니다.
OS version : redhat 8
DB version : PG 16
작업 내용 요약 : 신규DB 설치 및 aws RDS 로부터 이관하기
디렉토리 설정 및 포트설정은 default가 아닌 특정 포트와 경로로 변경하는 부분이 포함되었고, 기존 DB의 데이터를 이관하는 내용까지 포함하였습니다.
OS 버전 확인
$ cat /etc/redhat-release
Red Hat Enterprise Linux release 8.10 (Ootpa)
1.신규DB 설치
https://www.postgresql.org/download/linux/redhat/
1) repo 설치
yum이 되는 환경이라 yum으로 기본설치 후 디테일하게 수정하는 방식으로 설치했습니다.
redhat 8버전부터는 dnf라는 명령어로 yum을 대체하네요.
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered with an entitlement server. You can use subscription-manager to register.
Last metadata expiration check: 1:23:56 ago on Mon 22 Jul 2024 04:44:34 PM KST.
pgdg-redhat-repo-latest.noarch.rpm 11 kB/s | 15 kB 00:01
Dependencies resolved.
========================================================================================================================================
Package Architecture Version Repository Size
========================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-43PGDG @commandline 15 k
Transaction Summary
========================================================================================================================================
Install 1 Package
Total size: 15 k
Installed size: 15 k
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : pgdg-redhat-repo-42.0-43PGDG.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-43PGDG.noarch 1/1
Installed products updated.
Installed:
pgdg-redhat-repo-42.0-43PGDG.noarch
Complete!
2) built in PostgreSQL Module disable
dnf -qy module disable postgresql
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
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
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
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
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
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
3) PostgreSQL DB 설치
# dnf install -y postgresql16-server
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered with an entitlement server. You can use subscription-manager to register.
Last metadata expiration check: 0:01:21 ago on Wed 24 Jul 2024 09:23:50 AM KST.
Dependencies resolved.
=============================================================================================================================================================
Package Architecture Version Repository Size
=============================================================================================================================================================
Installing:
postgresql16-server x86_64 16.3-3PGDG.rhel8 pgdg16 6.7 M
Installing dependencies:
libicu x86_64 60.3-2.el8_1 rhel-8-baseos-rhui-rpms 8.8 M
lz4 x86_64 1.8.3-3.el8_4 rhel-8-baseos-rhui-rpms 104 k
postgresql16 x86_64 16.3-3PGDG.rhel8 pgdg16 1.9 M
postgresql16-libs x86_64 16.3-3PGDG.rhel8 pgdg16 329 k
Transaction Summary
=============================================================================================================================================================
Install 5 Packages
Total download size: 18 M
Installed size: 71 M
Downloading Packages:
(1/5): postgresql16-libs-16.3-3PGDG.rhel8.x86_64.rpm 148 kB/s | 329 kB 00:02
(2/5): libicu-60.3-2.el8_1.x86_64.rpm 46 MB/s | 8.8 MB 00:00
(3/5): lz4-1.8.3-3.el8_4.x86_64.rpm 8.8 MB/s | 104 kB 00:00
(4/5): postgresql16-16.3-3PGDG.rhel8.x86_64.rpm 608 kB/s | 1.9 MB 00:03
(5/5): postgresql16-server-16.3-3PGDG.rhel8.x86_64.rpm 1.8 MB/s | 6.7 MB 00:03
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 4.7 MB/s | 18 MB 00:03
PostgreSQL 16 for RHEL / Rocky / AlmaLinux 8 - 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
Key imported successfully
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/5
Running scriptlet: libicu-60.3-2.el8_1.x86_64 1/5
Installing : postgresql16-libs-16.3-3PGDG.rhel8.x86_64 2/5
Running scriptlet: postgresql16-libs-16.3-3PGDG.rhel8.x86_64 2/5
Installing : lz4-1.8.3-3.el8_4.x86_64 3/5
Installing : postgresql16-16.3-3PGDG.rhel8.x86_64 4/5
Running scriptlet: postgresql16-16.3-3PGDG.rhel8.x86_64 4/5
Running scriptlet: postgresql16-server-16.3-3PGDG.rhel8.x86_64 5/5
Installing : postgresql16-server-16.3-3PGDG.rhel8.x86_64 5/5
Running scriptlet: postgresql16-server-16.3-3PGDG.rhel8.x86_64 5/5
Verifying : postgresql16-16.3-3PGDG.rhel8.x86_64 1/5
Verifying : postgresql16-libs-16.3-3PGDG.rhel8.x86_64 2/5
Verifying : postgresql16-server-16.3-3PGDG.rhel8.x86_64 3/5
Verifying : libicu-60.3-2.el8_1.x86_64 4/5
Verifying : lz4-1.8.3-3.el8_4.x86_64 5/5
Installed products updated.
Installed:
libicu-60.3-2.el8_1.x86_64 lz4-1.8.3-3.el8_4.x86_64 postgresql16-16.3-3PGDG.rhel8.x86_64 postgresql16-libs-16.3-3PGDG.rhel8.x86_64
postgresql16-server-16.3-3PGDG.rhel8.x86_64
Complete!
4)데이터베이스 초기화 및 자동시작 사용
/usr/pgsql-16/bin/postgresql-16-setup initdb
Initializing database ... OK
systemctl start postgresql-16
systemctl status postgresql-16
● postgresql-16.service - PostgreSQL 16 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; disabled; vendor preset: disabled)
Active: active (running) since Wed 2024-07-24 09:26:14 KST; 7s ago
Docs: https://www.postgresql.org/docs/16/static/
Process: 26285 ExecStartPre=/usr/pgsql-16/bin/postgresql-16-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 26291 (postgres)
Tasks: 7 (limit: 46854)
Memory: 17.7M
CGroup: /system.slice/postgresql-16.service
├─26291 /usr/pgsql-16/bin/postgres -D /var/lib/pgsql/16/data/
├─26292 postgres: logger
├─26293 postgres: checkpointer
├─26294 postgres: background writer
├─26296 postgres: walwriter
├─26297 postgres: autovacuum launcher
└─26298 postgres: logical replication launcher
신규 DB 설치 종료.
2.설정 변경
port 변경, data directory, log directory를 변경하고 client 접속이 가능하게 수정하도록 하겠습니다.
경로변경은 이전에 pg15 DATA directory 경로를 수정했던 글을 참고하여 수정하였습니다.
https://bae9086.tistory.com/478
[pg15] DATA Directory 변경하기
이번 글을 yum install로 설치하여 default 경로에 설치된 data directory를 변경하는 시나리오입니다. OS : CentOS 7 DB version : Postgres 15 1.yum으로 DB 설치 sudo yum -y install https://download.postgresql.org/pub/repos/yum/repor
bae9086.tistory.com
AS-IS 경로 : /var/lib/pgsql/16/data
TO-BE 경로 : /pgdata
1) postmaster.opts 수정
##as is
/usr/pgsql-16/bin/postgres "-D" "/var/lib/pgsql/16/data/"
##to be
/usr/pgsql-16/bin/postgres "-D" "/pgdata/data/"
2) postgresql.conf 수정
##as is
#data_directory = 'ConfigDir' # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
#wal_level = replica # minimal, replica, or logical
# (change requires restart)
log_directory = 'log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes##to be
data_directory = '/pgdata/data/' # use data in another directory
# (change requires restart)
hba_file = '/pgdata/data/pg_hba.conf' # host-based authentication file
# (change requires restart)
ident_file = '/pgdata/data/pg_ident.conf' # ident configuration file
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for a ll
port = 15432 # (change requires restart)
wal_level = replica # minimal, replica, or logical
# (change requires restart)
log_directory = '/pgdata/log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
3) pg_hba.conf 수정
##as is
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# 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
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
local로 접속할때 접속을 허용하게 peer -> trust로 변경하였고,
host all all 0.0.0.0/0 md5를 추가하여 외부에서 ipv4로 db 접속시 접근허용되게 추가합니다.
##to be
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
DB 서비스 내리고 물리적으로 디렉토리 옮기고 기동합니다.
systemctl stop postgresql-16
cp -r /var/lib/pgsql/16/data /pgdata/.
chown -R postgres:postgres /pgdata
mv /var/lib/pgsql/15/data /var/lib/pgsql/15/data_bak
systemctl start postgresql-16
DB의 postgres유저 패스워드 변경
alter user postgres with password 'Password';
설정완료.
3.RDS로부터 dump 다운 후 pg_restore 사용하여 복구(pg_dump,pg_restore)
postgreSQL 엔진을 설치하면 pg_dump와 pg_restore가 있습니다. 이것을 이용해서 데이터를 받아 이관하도록 하겠습니다.
pg_dump사용하여 export
aws console에서 endpoint와 port 정보를 확인할 수 있습니다.
pg_dump -h aurora-psql-bishop-prd-db-instance-1.caiuhckq3lhc.ap-northeast-2.rds.amazonaws.com -p 15432 -U root -d bishop --format=custom --compress=gzip:9 -f /pgdata/dba/pg_bishop.tar.gz -v
...로그 떨어짐
...
-rw-------. 1 postgres postgres 9886458259 Jul 24 12:35 pg_bishop.tar.gz
pg_restore이용하여 import
import하려는 DB와 유저를 미리 생성해놔야 합니다.
AS-IS쪽에서 root라는 이름으로 사용중인 DB였고, 유저와 DB를 생성합니다.
생성하지 않고 restore 시도시 아래의 에러가 발생합니다.
pg_restore -h localhost -p 15432 -U admin -Fc -d bishop /pgdata/dba/pg_bishop.tar.gz
pg_restore: error: connection to server at "localhost" (::1), port 15432 failed: FATAL: database "bishop" does not exist
DB 및 유저 생성
postgres=# CREATE DATABASE BISHOP
postgres-# ;
CREATE DATABASE
postgres=# CREATE ROLE root WITH
postgres-# SUPERUSER
postgres-# CREATEDB
postgres-# CREATEROLE
postgres-# INHERIT
postgres-# LOGIN
postgres-# REPLICATION
postgres-# BYPASSRLS
postgres-# PASSWORD 'Password'
postgres-# CONNECTION LIMIT -1;
CREATE ROLE
복구명령어
pg_restore -U postgres -C -Fc -p 15432 -d bishop /pgdata/dba/pg_bishop.tar.gz -v
...로그 떨어짐
...
완료하였습니다.
반응형
'다른 DBMS > PostgresDB' 카테고리의 다른 글
| [pg16] 백업 쉘 만들어서 자동 백업되게 설정하기 (0) | 2024.07.31 |
|---|---|
| .pgpass 활용하여 password 입력 없이 접속하기 (0) | 2024.07.30 |
| 오류: 기타 다른 개체들이 이 롤에 의존하고 있어, "유저명" 롤을 삭제할 수 없음 (0) | 2024.05.29 |
| [postgresql] 데이터타입 총정리 (0) | 2024.04.02 |
| vacuum 과 vacuum full 차이 보기 (pageinspect extention 사용) (0) | 2024.03.31 |
댓글