PostgreSQL Major 업그레이드

EPAS, PostgreSQL은 major 업그레이드도 migration 없이 가능한 것으로 보인다...

 

 

0) 사전체크

\dx \db+ (테이블스페이스 있으면 pg_tblspc 경로 가서 심볼릭 링크 확인) \dn \du (superuser 확인도) \l+ select name, setting from pg_settings where name like '%encod%'; select name, setting from pg_settings where name like 'lc%'; select schema_name, object_type, count(*) from dba_objects where schema_name not in ('SYS', 'INFORMATION_SCHEMA', 'PG_CATALOG') group by schema_name, object_type order by schema_name, object_type; postgresql.conf, pg_hba.conf EPAS(or PostgreSQL) 유저 .bash_profile

 

1) 업그레이드 대상 EPAS 정지
 /usr/edb/as16/bin/pg_ctl stop -D /data1/as16/data

 

2) epas 17 버전 설치
 su - root
 dnf install edb-as17-server'

ㄴ 해당 패키지를 미리 설치하거나, 반입하여 repo를 생성해두어야겠다.

 

3) 17 버전 클러스터 생성
 /usr/edb/as17/bin/initdb -D /data1/as17/data

 

4) 신규 bash profile 생성 후 적용

# If you want to customize your settings, # Use the file below. This is not overridden # by the RPMS. [ -f /var/lib/edb/.enterprisedb_profile ] && source /var/lib/edb/.enterprisedb_profileexport PGHOME=/usr/edb/as17 export PGDATA=/data1/as17/data export PATH=$PGHOME/bin:$PATH export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH export PGDATABASE=edb export PGUSER=enterprisedbalias ll='ls -alrt' alias pcon='psql -d edb -U enterprisedb -p 5444' alias pghome='cd $PGHOME' alias pgdata='cd $PGDATA' alias pgcfgv='vi $PGDATA/postgresql.conf' alias pgcliv='vi $PGDATA/pg_hba.conf' alias pglog='cd $PGDATA/log' alias pstart='$PGHOME/bin/pg_ctl start -D $PGDATA' alias pstop='$PGHOME/bin/pg_ctl stop -D $PGDATA' export PS1="[`whoami`@`hostname`:\$PWD]$"

5) 메이저 업그레이드 사전 체크 진행  

/usr/edb/as17/bin/pg_upgrade -d /data1/as16/data -D /data1/as17/data -b /usr/edb/as16/bin -B /usr/edb/as17/bin -c -p 5444 -P 5445 -U enterprisedb\

 

6) 메이저 업그레이드 수행
 /usr/edb/as17/bin/pg_upgrade -d /data1/as16/data -D /data1/as17/data -b /usr/edb/as16/bin -B /usr/edb/as17/bin -p 5444 -P 5445 -U enterprisedb

 

7) 업그레이드 완료된 EPAS 기동
 /usr/edb/as17/bin/pg_ctl start -D /data1/as17/data

 

8) 전체 DB 통계 수행
 /usr/edb/as17/bin/vacuumdb -U enterprisedb --all --analyze-in-stages

 

9) 버전 확인
 psql -V or select version();

 

10) Extension 복원
edb=# ALTER EXTENSION "pg_stat_statements" UPDATE;
edb=# \dx