멋지게 놀아라

PostgreSQL 9.3 ~ 14을 테스트 용으로 하나의 서버에 구성해야 할 필요가 생겨서 설치했던 문서 공유 

Cent7.9 minimal + PostgreSQL source

Download : https://www.postgresql.org/ftp/source/

#############
 설치파일 업로드
#############

/source 에 압축 파일들 업로드
cd /source
ls
postgresql-10.20.tar.gz  postgresql-12.10.tar.gz  postgresql-14.2.tar.gz    postgresql-9.4.26.tar.gz  postgresql-9.6.24.tar.gz       
postgresql-11.15.tar.gz  postgresql-13.6.tar.gz   postgresql-9.3.25.tar.gz  postgresql-9.5.25.tar.gz  

#############
 Limit
#############

vi  /etc/security/limits.conf
*                -       nofile          65536

#############
 Firewall
#############

systemctl stop firewalld
systemctl disable firewalld

#############
 Packages
#############

yum -y update
yum -y net-tools
yum -y install epel-release
yum -y install gcc gcc-c++ make autoconf readline readline-devel zlib zlib-devel openssl openssl-devel gettext gettext-devel python python-devel wget

##################
 Add user & group
##################

useradd postgres

###################
 Make Directories
###################

mkdir -p /engine/pg10.20
mkdir -p /engine/pg11.15
mkdir -p /engine/pg12.10
mkdir -p /engine/pg13.6
mkdir -p /engine/pg14.2
mkdir -p /engine/pg9.3
mkdir -p /engine/pg9.4
mkdir -p /engine/pg9.5
mkdir -p /engine/pg9.6

mkdir -p /data/pg10.20
mkdir -p /data/pg11.15
mkdir -p /data/pg12.10
mkdir -p /data/pg13.6
mkdir -p /data/pg14.2
mkdir -p /data/pg9.3
mkdir -p /data/pg9.4
mkdir -p /data/pg9.5
mkdir -p /data/pg9.6

chown -R postgres:postgres /source /engine /data

############
 Install
############


#########
 9.3
#########

su - postgres
cd /source
tar xvfz postgresql-9.3.25.tar.gz
cd postgresql-9.3.25

./configure --prefix=/engine/pg9.3
make
make world
make install
make install-world

/engine/pg9.3/bin/initdb -D /data/pg9.3

vi /data/pg9.3/postgresql.conf
listen_addresses = '*' 
port = 5493

log_destination = 'stderr'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age = 1d

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,remote=%r'

vi /data/pg9.3/pg_hba.conf
host    all             all             0.0.0.0/0               md5

/engine/pg9.3/bin/pg_ctl -D /data/pg9.3 -l logfile start
/engine/pg9.3/bin/pg_ctl -D /data/pg9.3 status
/engine/pg9.3/bin/psql -p 5493 -d postgres
\l
select * from pg_available_extensions; 
alter user postgres with password 'postgres' ;
\q
--원격 접속 확인 (dbeaver같은 툴 이용)
/engine/pg9.3/bin/pg_ctl -D /data/pg9.3 stop -mf -w

#########
 9.4
#########

su - postgres
cd /source
tar xvfz postgresql-9.4.26.tar.gz
cd postgresql-9.4.26

./configure --prefix=/engine/pg9.4
make
make world
make install
make install-world

/engine/pg9.4/bin/initdb -D /data/pg9.4

vi /data/pg9.4/postgresql.conf
listen_addresses = '*' 
port = 5494

log_destination = 'stderr'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age = 1d

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,remote=%r'

vi /data/pg9.4/pg_hba.conf
host    all             all             0.0.0.0/0               md5

/engine/pg9.4/bin/pg_ctl -D /data/pg9.4 -l logfile start
/engine/pg9.4/bin/pg_ctl -D /data/pg9.4 status
/engine/pg9.4/bin/psql -p 5494 -d postgres
\l
select * from pg_available_extensions; 
alter user postgres with password 'postgres' ;
\q
--원격 접속 확인 (dbeaver같은 툴 이용)
/engine/pg9.4/bin/pg_ctl -D /data/pg9.4 stop -mf -w


#########
 9.5
#########

su - postgres
cd /source
tar xvfz postgresql-9.5.25.tar.gz
cd postgresql-9.5.25

./configure --prefix=/engine/pg9.5
make
make world
make install
make install-world

/engine/pg9.5/bin/initdb -D /data/pg9.5

vi /data/pg9.5/postgresql.conf
listen_addresses = '*' 
port = 5495

log_destination = 'stderr'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age = 1d

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,remote=%r'

vi /data/pg9.5/pg_hba.conf
host    all             all             0.0.0.0/0               md5

/engine/pg9.5/bin/pg_ctl -D /data/pg9.5 -l logfile start
/engine/pg9.5/bin/pg_ctl -D /data/pg9.5 status
/engine/pg9.5/bin/psql -p 5495 -d postgres
\l
select * from pg_available_extensions; 
alter user postgres with password 'postgres' ;
\q
--원격 접속 확인 (dbeaver같은 툴 이용)
/engine/pg9.5/bin/pg_ctl -D /data/pg9.5 stop -mf -w


#########
 9.6
#########

su - postgres
cd /source
tar xvfz postgresql-9.6.24.tar.gz
cd postgresql-9.6.24

./configure --prefix=/engine/pg9.6
make
make world
make install
make install-world

/engine/pg9.6/bin/initdb -D /data/pg9.6

vi /data/pg9.6/postgresql.conf
listen_addresses = '*' 
port = 5496

log_destination = 'stderr'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age = 1d

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,remote=%r'

vi /data/pg9.6/pg_hba.conf
host    all             all             0.0.0.0/0               md5

/engine/pg9.6/bin/pg_ctl -D /data/pg9.6 -l logfile start
/engine/pg9.6/bin/pg_ctl -D /data/pg9.6 status
/engine/pg9.6/bin/psql -p 5496 -d postgres
\l
select * from pg_available_extensions; 
alter user postgres with password 'postgres' ;
\q
--원격 접속 확인 (dbeaver같은 툴 이용)
/engine/pg9.6/bin/pg_ctl -D /data/pg9.6 stop -mf -w


#########
 10
#########

su - postgres
cd /source
tar xvfz postgresql-10.20.tar.gz
cd postgresql-10.20

./configure --prefix=/engine/pg10.20
make
make world
make install
make install-world

/engine/pg10.20/bin/initdb -D /data/pg10.20

vi /data/pg10.20/postgresql.conf
listen_addresses = '*' 
port = 5410

log_destination = 'stderr'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age = 1d

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,remote=%r'

vi /data/pg10.20/pg_hba.conf
host    all             all             0.0.0.0/0               md5

/engine/pg10.20/bin/pg_ctl -D /data/pg10.20 -l logfile start
/engine/pg10.20/bin/pg_ctl -D /data/pg10.20 status
/engine/pg10.20/bin/psql -p 5410 -d postgres
\l
select * from pg_available_extensions; 
alter user postgres with password 'postgres' ;
\q
--원격 접속 확인 (dbeaver같은 툴 이용)
/engine/pg10.20/bin/pg_ctl -D /data/pg10.20 stop -mf -w


#########
 11
#########

su - postgres
cd /source
tar xvfz postgresql-11.15.tar.gz
cd postgresql-11.15

./configure --prefix=/engine/pg11.15
make
make world
make install
make install-world

/engine/pg11.15/bin/initdb -D /data/pg11.15

vi /data/pg11.15/postgresql.conf
listen_addresses = '*' 
port = 5411

log_destination = 'stderr'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age = 1d

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,remote=%r'

vi /data/pg11.15/pg_hba.conf
host    all             all             0.0.0.0/0               md5

/engine/pg11.15/bin/pg_ctl -D /data/pg11.15 -l logfile start
/engine/pg11.15/bin/pg_ctl -D /data/pg11.15 status
/engine/pg11.15/bin/psql -p 5411 -d postgres
\l
select * from pg_available_extensions; 
alter user postgres with password 'postgres' ;
\q
--원격 접속 확인 (dbeaver같은 툴 이용)
/engine/pg11.15/bin/pg_ctl -D /data/pg11.15 stop -mf -w


#########
 12
#########

su - postgres
cd /source
tar xvfz postgresql-12.10.tar.gz
cd postgresql-12.10

./configure --prefix=/engine/pg12.10
make
make world
make install
make install-world

/engine/pg12.10/bin/initdb -D /data/pg12.10

vi /data/pg12.10/postgresql.conf
listen_addresses = '*' 
port = 5412

log_destination = 'stderr'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age = 1d

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,remote=%r'

vi /data/pg12.10/pg_hba.conf
host    all             all             0.0.0.0/0               md5

/engine/pg12.10/bin/pg_ctl -D /data/pg12.10 -l logfile start
/engine/pg12.10/bin/pg_ctl -D /data/pg12.10 status
/engine/pg12.10/bin/psql -p 5412 -d postgres
\l
select * from pg_available_extensions; 
alter user postgres with password 'postgres' ;
\q
--원격 접속 확인 (dbeaver같은 툴 이용)
/engine/pg12.10/bin/pg_ctl -D /data/pg12.10 stop -mf -w


#########
 13
#########

su - postgres
cd /source
tar xvfz postgresql-13.6.tar.gz
cd postgresql-13.6

./configure --prefix=/engine/pg13.6
make
make world
make install
make install-world

/engine/pg13.6/bin/initdb -D /data/pg13.6

vi /data/pg13.6/postgresql.conf
listen_addresses = '*' 
port = 5413

log_destination = 'stderr'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age = 1d

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,remote=%r'

vi /data/pg13.6/pg_hba.conf
host    all             all             0.0.0.0/0               md5

/engine/pg13.6/bin/pg_ctl -D /data/pg13.6 -l logfile start
/engine/pg13.6/bin/pg_ctl -D /data/pg13.6 status
/engine/pg13.6/bin/psql -p 5413 -d postgres
\l
select * from pg_available_extensions; 
alter user postgres with password 'postgres' ;
\q
--원격 접속 확인 (dbeaver같은 툴 이용)
/engine/pg13.6/bin/pg_ctl -D /data/pg13.6 stop -mf -w


#########
 14
#########

su - postgres
cd /source
tar xvfz postgresql-14.2.tar.gz
cd postgresql-14.2

./configure --prefix=/engine/pg14.2
make
make world
make install
make install-world

/engine/pg14.2/bin/initdb -D /data/pg14.2

vi /data/pg14.2/postgresql.conf
listen_addresses = '*' 
port = 5414

log_destination = 'stderr'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age = 1d

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,remote=%r'

vi /data/pg14.2/pg_hba.conf
host    all             all             0.0.0.0/0               md5

/engine/pg14.2/bin/pg_ctl -D /data/pg14.2 -l logfile start
/engine/pg14.2/bin/pg_ctl -D /data/pg14.2 status
/engine/pg14.2/bin/psql -p 5414 -d postgres
\l
select * from pg_available_extensions; 
alter user postgres with password 'postgres' ;
\q
--원격 접속 확인 (dbeaver같은 툴 이용)
/engine/pg14.2/bin/pg_ctl -D /data/pg14.2 stop -mf -w


####################
 Profile Setting
####################

su - postgres

vi .bash_profile

### For PG

BASEPATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

list_version=( "9.3" "9.4" "9.5" "9.6" "10.20" "11.15" "12.10" "13.6" "14.2")

echo " "
echo "------------------------------------ "
echo "     select PostgreSQL Version       "
echo "------------------------------------ "

PS3=`echo -e "\n>> "`
select select_version in "${list_version[@]}"
do

    if [ -n "$select_version" ]
    then
    value_ver=$select_version

    pghome=`echo /engine/pg$value_ver`
    pgdata=`echo /data/pg$value_ver`

    echo -e "\n\n\n * You selected $value_ver version\n\n\n"

    unset PATH
    unset PGHOME
    unset PGDATA
    
    export PATH=$pghome/bin:$BASEPATH
    export PGHOME=$pghome
    export PGDATA=$pgdata
    export PGDATABASE=postgres
  
    break;

    else
         echo -e "\n. . . Please choose the correct number . . ."
    fi
done

## Aliases

alias start_pg93='/engine/pg9.3/bin/pg_ctl -D /data/pg9.3 -l logfile start'
alias status_pg93='/engine/pg9.3/bin/pg_ctl -D /data/pg9.3 status'
alias client_pg93='/engine/pg9.3/bin/psql -p 5493 -d postgres'
alias stop_pg93='/engine/pg9.3/bin/pg_ctl -D /data/pg9.3 stop -mf -w'

alias start_pg94='/engine/pg9.4/bin/pg_ctl -D /data/pg9.4 -l logfile start'
alias status_pg94='/engine/pg9.4/bin/pg_ctl -D /data/pg9.4 status'
alias client_pg94='/engine/pg9.4/bin/psql -p 5494 -d postgres'
alias stop_pg94='/engine/pg9.4/bin/pg_ctl -D /data/pg9.4 stop -mf -w'

alias start_pg95='/engine/pg9.5/bin/pg_ctl -D /data/pg9.5 -l logfile start'
alias status_pg95='/engine/pg9.5/bin/pg_ctl -D /data/pg9.5 status'
alias client_pg95='/engine/pg9.5/bin/psql -p 5495 -d postgres'
alias stop_pg95='/engine/pg9.5/bin/pg_ctl -D /data/pg9.5 stop -mf -w'

alias start_pg96='/engine/pg9.6/bin/pg_ctl -D /data/pg9.6 -l logfile start'
alias status_pg96='/engine/pg9.6/bin/pg_ctl -D /data/pg9.6 status'
alias client_pg96='/engine/pg9.6/bin/psql -p 5496 -d postgres'
alias stop_pg96='/engine/pg9.6/bin/pg_ctl -D /data/pg9.6 stop -mf -w'

alias start_pg10='/engine/pg10.20/bin/pg_ctl -D /data/pg10.20 -l logfile start'
alias status_pg10='/engine/pg10.20/bin/pg_ctl -D /data/pg10.20 status'
alias client_pg10='/engine/pg10.20/bin/psql -p 5410 -d postgres'
alias stop_pg10='/engine/pg10.20/bin/pg_ctl -D /data/pg10.20 stop -mf -w'

alias start_pg11='/engine/pg11.15/bin/pg_ctl -D /data/pg11.15 -l logfile start'
alias status_pg11='/engine/pg11.15/bin/pg_ctl -D /data/pg11.15 status'
alias client_pg11='/engine/pg11.15/bin/psql -p 5411 -d postgres'
alias stop_pg11='/engine/pg11.15/bin/pg_ctl -D /data/pg11.15 stop -mf -w'

alias start_pg12='/engine/pg12.10/bin/pg_ctl -D /data/pg12.10 -l logfile start'
alias status_pg12='/engine/pg12.10/bin/pg_ctl -D /data/pg12.10 status'
alias client_pg12='/engine/pg12.10/bin/psql -p 5412 -d postgres'
alias stop_pg12='/engine/pg12.10/bin/pg_ctl -D /data/pg12.10 stop -mf -w'

alias start_pg13='/engine/pg13.6/bin/pg_ctl -D /data/pg13.6 -l logfile start'
alias status_pg13='/engine/pg13.6/bin/pg_ctl -D /data/pg13.6 status'
alias client_pg13='/engine/pg13.6/bin/psql -p 5413 -d postgres'
alias stop_pg13='/engine/pg13.6/bin/pg_ctl -D /data/pg13.6 stop -mf -w'

alias start_pg14='/engine/pg14.2/bin/pg_ctl -D /data/pg14.2 -l logfile start'
alias status_pg14='/engine/pg14.2/bin/pg_ctl -D /data/pg14.2 status'
alias client_pg14='/engine/pg14.2/bin/psql -p 5414 -d postgres'
alias stop_pg14='/engine/pg14.2/bin/pg_ctl -D /data/pg14.2 stop -mf -w'