[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/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum -y install epel-release yum-utils
sudo yum-config-manager --enable pgdg15
sudo yum -y install postgresql15-server postgresql15

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

 

2.서비스 확인

# systemctl status postgresql-15.service
3● postgresql-15.service - PostgreSQL 15 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2023-03-26 12:54:39 UTC; 2min 30s ago
     Docs: https://www.postgresql.org/docs/15/static/
  Process: 21311 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 21316 (postmaster)
   CGroup: /system.slice/postgresql-15.service
           ├─21316 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
           ├─21318 postgres: logger
           ├─21319 postgres: checkpointer
           ├─21320 postgres: background writer
           ├─21322 postgres: walwriter
           ├─21323 postgres: autovacuum launcher
           └─21324 postgres: logical replication launcher

Mar 26 12:54:39 localhost.localdomain systemd[1]: Starting PostgreSQL 15 database server...
Mar 26 12:54:39 localhost.localdomain postmaster[21316]: 2023-03-26 12:54:39.187 UTC [21316] LOG:  redirecting log output to logging collector process
Mar 26 12:54:39 localhost.localdomain postmaster[21316]: 2023-03-26 12:54:39.187 UTC [21316] HINT:  Future log output will appear in directory "log".
Mar 26 12:54:39 localhost.localdomain systemd[1]: Started PostgreSQL 15 database server.

 

3.default 경로 확인

$ pwd
/var/lib/pgsql/15/data

$ ls -al
total 72
drwx------. 20 postgres postgres  4096 Mar 28 05:47 .
drwx------.  4 postgres postgres    51 Mar 28 05:46 ..
drwx------.  5 postgres postgres    33 Mar 28 05:46 base
-rw-------.  1 postgres postgres    30 Mar 28 05:47 current_logfiles
drwx------.  2 postgres postgres  4096 Mar 28 05:50 global
drwx------.  2 postgres postgres    32 Mar 28 05:47 log
drwx------.  2 postgres postgres     6 Mar 28 05:46 pg_commit_ts
drwx------.  2 postgres postgres     6 Mar 28 05:46 pg_dynshmem
-rw-------.  1 postgres postgres  4577 Mar 28 05:46 pg_hba.conf
-rw-------.  1 postgres postgres  1636 Mar 28 05:46 pg_ident.conf
drwx------.  4 postgres postgres    68 Mar 28 05:52 pg_logical
drwx------.  4 postgres postgres    36 Mar 28 05:46 pg_multixact
drwx------.  2 postgres postgres     6 Mar 28 05:46 pg_notify
drwx------.  2 postgres postgres     6 Mar 28 05:46 pg_replslot
drwx------.  2 postgres postgres     6 Mar 28 05:46 pg_serial
drwx------.  2 postgres postgres     6 Mar 28 05:46 pg_snapshots
drwx------.  2 postgres postgres     6 Mar 28 05:47 pg_stat
drwx------.  2 postgres postgres     6 Mar 28 05:46 pg_stat_tmp
drwx------.  2 postgres postgres    18 Mar 28 05:46 pg_subtrans
drwx------.  2 postgres postgres     6 Mar 28 05:46 pg_tblspc
drwx------.  2 postgres postgres     6 Mar 28 05:46 pg_twophase
-rw-------.  1 postgres postgres     3 Mar 28 05:46 PG_VERSION
drwx------.  3 postgres postgres    60 Mar 28 05:46 pg_wal
drwx------.  2 postgres postgres    18 Mar 28 05:46 pg_xact
-rw-------.  1 postgres postgres    88 Mar 28 05:46 postgresql.auto.conf
-rw-------.  1 postgres postgres 29440 Mar 28 05:46 postgresql.conf
-rw-------.  1 postgres postgres    58 Mar 28 05:47 postmaster.opts
-rw-------.  1 postgres postgres   104 Mar 28 05:47 postmaster.pid

 

4.

(여기부터 경로를 바꾸는 작업입니다)

AS-IS 경로 : /var/lib/pgsql/15/data

TO-BE 경로 : /pgdata

 

TO-BE 경로 생성 및 권한 및 소유자 변경

서비스 중지
(root로 수행)
# systemctl stop postgresql-15.service

[root@ip-10-0-2-15 vagrant-postgres]# systemctl stop postgresql-15.service
[root@ip-10-0-2-15 vagrant-postgres]# systemctl status postgresql-15.service
● postgresql-15.service - PostgreSQL 15 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Tue 2023-03-28 06:05:22 UTC; 5s ago
     Docs: https://www.postgresql.org/docs/15/static/
  Process: 21272 ExecStart=/usr/pgsql-15/bin/postmaster -D ${PGDATA} (code=exited, status=0/SUCCESS)
  Process: 21267 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 21272 (code=exited, status=0/SUCCESS)

Mar 28 05:47:01 ip-10-0-2-15.ap-northeast-2.compute.internal systemd[1]: Starting PostgreSQL 15 database ....
Mar 28 05:47:01 ip-10-0-2-15.ap-northeast-2.compute.internal postmaster[21272]: 2023-03-28 05:47:01.209 UT...
Mar 28 05:47:01 ip-10-0-2-15.ap-northeast-2.compute.internal postmaster[21272]: 2023-03-28 05:47:01.209 UT...
Mar 28 05:47:01 ip-10-0-2-15.ap-northeast-2.compute.internal systemd[1]: Started PostgreSQL 15 database s....
Mar 28 06:05:22 ip-10-0-2-15.ap-northeast-2.compute.internal systemd[1]: Stopping PostgreSQL 15 database ....
Mar 28 06:05:22 ip-10-0-2-15.ap-northeast-2.compute.internal systemd[1]: Stopped PostgreSQL 15 database s....

TO-BE 디렉토리 생성 및 AS-IS 디렉토리 MV
# mkdir /pgdata
# cp -r /var/lib/pgsql/15/data /pgdata/.
# chown -R postgres:postgres /pgdata
# mv /var/lib/pgsql/15/data /var/lib/pgsql/15/data_bak

 

5.postmaster.opts 파일 변경

postgres를 기동하는데 data directory가 manual하게 지정되어 있으며 TO-BE쪽으로 변경해줍니다.

"-D" "/var/lib/pgsql/15/data"  를 "-D" "/pgdata/data/"  로 변경해줍니다.

(postgres 유저)
$ cat postmaster.opts
/usr/pgsql-15/bin/postgres "-D" "/pgdata/data/"

 

6.postgresql-15.service파일 변경

default로 설치시 systemctl 명령어로 관리하게 되는데 이 파일을 수정해줍니다.

 

# cat postgresql-15.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  It is recommended to use systemd
# "dropin" feature;  i.e. create file with suffix .conf under
# /etc/systemd/system/postgresql-15.service.d directory overriding the
# unit's defaults. You can also use "systemctl edit postgresql-15"
# Look at systemd.unit(5) manual page for more info.

# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.

# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-15-setup.
[Unit]
Description=PostgreSQL 15 database server
Documentation=https://www.postgresql.org/docs/15/static/
After=syslog.target
After=network-online.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/15/data/ ##AS-IS
Environment=PGDATA=/pgdata/data/ ##TO-BE
# 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-15/bin/postgresql-15-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-15/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

 

7.변경 후 systemctl 데몬 재기동 및 DB 서비스 기동

systemctl 서비스 reload
# systemctl daemon-reload

DB서비스 기동
# systemctl start postgresql-15.service
# systemctl status postgresql-15.service
● postgresql-15.service - PostgreSQL 15 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2023-03-28 06:17:11 UTC; 1s ago
     Docs: https://www.postgresql.org/docs/15/static/
  Process: 21750 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 21755 (postmaster)
   CGroup: /system.slice/postgresql-15.service
           ├─21755 /usr/pgsql-15/bin/postmaster -D /pgdata/data/
           ├─21757 postgres: logger
           ├─21758 postgres: checkpointer
           ├─21759 postgres: background writer
           ├─21761 postgres: walwriter
           ├─21762 postgres: autovacuum launcher
           └─21763 postgres: logical replication launcher

Mar 28 06:17:11 ip-10-0-2-15.ap-northeast-2.compute.internal systemd[1]: Starting PostgreSQL 15 database server...
Mar 28 06:17:11 ip-10-0-2-15.ap-northeast-2.compute.internal postmaster[21755]: 2023-03-28 06:17:11.349 UTC [21755] L...ss
Mar 28 06:17:11 ip-10-0-2-15.ap-northeast-2.compute.internal postmaster[21755]: 2023-03-28 06:17:11.349 UTC [21755] H...".
Mar 28 06:17:11 ip-10-0-2-15.ap-northeast-2.compute.internal systemd[1]: Started PostgreSQL 15 database server.

 

※ deault로 설치시에는 postgres.conf 파일에 data_directory에 대한 정보가 들어있지 않아서 , 따로 수정하지 않았습니다.

postgres.conf 에 data_directory가 설정이 되어 있다면 그것도 수정해야 할 것 같네요. :)

 

 

참고 : https://confluence.curvc.com/pages/releaseview.action?pageId=10092649 

Postgres Data directory 변경하기 - CURVC DevOps - Confluence

끝.