728x90
반응형
Postgresql 13을 CentOS 에 RPM 방식으로 설치해보자.
Postgresql 13 버전 공식문서
PostgreSQL 13.1 Documentation
PostgreSQL 13.1 Documentation The PostgreSQL Global Development Group Copyright © 1996–2020 The PostgreSQL Global Development Group Legal Notice Table of …
www.postgresql.org
한눈에 살펴보는 Postgresql
d2.naver.com/helloworld/227936
0. OS설정
# 호스트명 postgres로 변경
hostnamectl set-hostname postgres
# 방화벽 해제
systemctl stop firewalld
systemctl disable firewalld
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
/etc/selinux/config 파일의 SELINUX 속성을 변경하면 재부팅하여도 방화벽해제가 영구적용된다
# 프로세스 개수제한 및 파일오픈 개수제한 조정
vi /etc/security/limits.conf
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
:wq
리눅스 OS는 자원관리를 위해 프로세스 제한개수, 파일오픈 제한개수를 설정하고 있어 운영환경에서는 기본수치값 보다 높은 수치값을 적용해야 추후 장애요소를 줄일 수 있다.
# OS 재기동
reboot
재기동을 하여 설정을 영구 적용하도록 하자.
1. postgresql 사이트에 접속하여 설치파일 확인
PostgreSQL: Downloads
Downloads PostgreSQL Downloads PostgreSQL is available for download as ready-to-use packages or installers for various platforms, as well as a source code archive if you want to build it yourself. Packages and Installers Select your operating system family
www.postgresql.org
CentOS에 설치를 해야 하니 Linux > RedHat/CentOS 를 선택
2021년 1월 7일 기준으로 Postgresql 최신 13 버전(13.1) - CentOS 7, x86_64bit 을 선택하면 설치할 수 있는 스크립트를 확인할 수 있다.
설치 스크립트
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql13-server
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13
2. rpm 파일 다운로드
Postgresql 13 의 마지막(latest) 버전으로 다운로드
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[root@postgres ~]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Loaded plugins: fastestmirror
pgdg-redhat-repo-latest.noarch.rpm | 6.8 kB 00:00:00
Examining /var/tmp/yum-root-pCgmZG/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-14.noarch
Marking /var/tmp/yum-root-pCgmZG/pgdg-redhat-repo-latest.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat-repo.noarch 0:42.0-14 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==============================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-14 /pgdg-redhat-repo-latest.noarch 11 k
Transaction Summary
==============================================================================================================================================================================================================================================================================
Install 1 Package
Total size: 11 k
Installed size: 11 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : pgdg-redhat-repo-42.0-14.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-14.noarch 1/1
Installed:
pgdg-redhat-repo.noarch 0:42.0-14
Complete!
[root@postgres ~]#
3. Postgresql 13 RPM 방식으로 설치
2021년 1월 7일기준으로 Postgresql 13의 최신버전인 13.1 으로 설치될 것이다.
yum install -y postgresql13-server
[root@postgres ~]# yum install -y postgresql13-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.kakao.com
* extras: mirror.kakao.com
* updates: mirror.kakao.com
base | 3.6 kB 00:00:00
extras | 2.9 kB 00:00:00
pgdg-common | 2.9 kB 00:00:00
pgdg10 | 3.6 kB 00:00:00
pgdg11 | 3.6 kB 00:00:00
pgdg12 | 3.6 kB 00:00:00
pgdg13 | 3.6 kB 00:00:00
pgdg95 | 3.6 kB 00:00:00
pgdg96 | 3.6 kB 00:00:00
updates | 2.9 kB 00:00:00
(1/13): pgdg11/7/x86_64/group_gz | 245 B 00:00:01
(2/13): pgdg12/7/x86_64/group_gz | 245 B 00:00:00
(3/13): pgdg10/7/x86_64/group_gz | 245 B 00:00:01
(4/13): pgdg13/7/x86_64/group_gz | 246 B 00:00:00
(5/13): pgdg-common/7/x86_64/primary_db | 152 kB 00:00:02
(6/13): pgdg12/7/x86_64/primary_db | 167 kB 00:00:00
(7/13): pgdg95/7/x86_64/group_gz | 249 B 00:00:00
(8/13): pgdg96/7/x86_64/group_gz | 249 B 00:00:00
(9/13): pgdg95/7/x86_64/primary_db | 266 kB 00:00:00
(10/13): pgdg13/7/x86_64/primary_db | 78 kB 00:00:00
(11/13): pgdg10/7/x86_64/primary_db | 298 kB 00:00:02
(12/13): pgdg11/7/x86_64/primary_db | 305 kB 00:00:02
(13/13): pgdg96/7/x86_64/primary_db | 291 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package postgresql13-server.x86_64 0:13.1-3PGDG.rhel7 will be installed
--> Processing Dependency: postgresql13-libs(x86-64) = 13.1-3PGDG.rhel7 for package: postgresql13-server-13.1-3PGDG.rhel7.x86_64
--> Processing Dependency: postgresql13(x86-64) = 13.1-3PGDG.rhel7 for package: postgresql13-server-13.1-3PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql13-server-13.1-3PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql13.x86_64 0:13.1-3PGDG.rhel7 will be installed
---> Package postgresql13-libs.x86_64 0:13.1-3PGDG.rhel7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==============================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================================================================================================================================================
Installing:
postgresql13-server x86_64 13.1-3PGDG.rhel7 pgdg13 5.4 M
Installing for dependencies:
postgresql13 x86_64 13.1-3PGDG.rhel7 pgdg13 1.4 M
postgresql13-libs x86_64 13.1-3PGDG.rhel7 pgdg13 379 k
Transaction Summary
==============================================================================================================================================================================================================================================================================
Install 1 Package (+2 Dependent packages)
Total download size: 7.2 M
Installed size: 30 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/pgdg13/packages/postgresql13-libs-13.1-3PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY ] 429 kB/s | 527 kB 00:00:15 ETA
Public key for postgresql13-libs-13.1-3PGDG.rhel7.x86_64.rpm is not installed
(1/3): postgresql13-libs-13.1-3PGDG.rhel7.x86_64.rpm | 379 kB 00:00:02
(2/3): postgresql13-13.1-3PGDG.rhel7.x86_64.rpm | 1.4 MB 00:00:02
(3/3): postgresql13-server-13.1-3PGDG.rhel7.x86_64.rpm | 5.4 MB 00:00:02
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 1.4 MB/s | 7.2 MB 00:00:04
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-14.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql13-libs-13.1-3PGDG.rhel7.x86_64 1/3
Installing : postgresql13-13.1-3PGDG.rhel7.x86_64 2/3
Installing : postgresql13-server-13.1-3PGDG.rhel7.x86_64 3/3
Verifying : postgresql13-libs-13.1-3PGDG.rhel7.x86_64 1/3
Verifying : postgresql13-server-13.1-3PGDG.rhel7.x86_64 2/3
Verifying : postgresql13-13.1-3PGDG.rhel7.x86_64 3/3
Installed:
postgresql13-server.x86_64 0:13.1-3PGDG.rhel7
Dependency Installed:
postgresql13.x86_64 0:13.1-3PGDG.rhel7 postgresql13-libs.x86_64 0:13.1-3PGDG.rhel7
Complete!
[root@postgres ~]#
4. db 초기화
/usr/pgsql-13/bin/postgresql-13-setup initdb
[root@postgres ~]# /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OK
[root@postgres ~]#
5. Postgresql 서비스 등록 및 구동
systemctl enable postgresql-13
systemctl start postgresql-13
systemctl status postgresql-13
[root@postgres ~]# systemctl enable postgresql-13
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-13.service to /usr/lib/systemd/system/postgresql-13.service.
[root@postgres ~]# systemctl start postgresql-13
[root@postgres ~]# systemctl status postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2021-01-07 10:09:24 KST; 4s ago
Docs: https://www.postgresql.org/docs/13/static/
Process: 21718 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 21723 (postmaster)
CGroup: /system.slice/postgresql-13.service
├─21723 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
├─21725 postgres: logger
├─21727 postgres: checkpointer
├─21728 postgres: background writer
├─21729 postgres: walwriter
├─21730 postgres: autovacuum launcher
├─21731 postgres: stats collector
└─21732 postgres: logical replication launcher
Jan 07 10:09:24 postgres systemd[1]: Starting PostgreSQL 13 database server...
Jan 07 10:09:24 postgres postmaster[21723]: 2021-01-07 10:09:24.351 KST [21723] LOG: redirecting log output to logging collector process
Jan 07 10:09:24 postgres postmaster[21723]: 2021-01-07 10:09:24.351 KST [21723] HINT: Future log output will appear in directory "log".
Jan 07 10:09:24 postgres systemd[1]: Started PostgreSQL 13 database server.
[root@postgres ~]#
6. Postgresql 설치정보
systemctl 에 표시된 /usr/lib/systemd/system/postgresql-13.service 파일을 보면 확인할 수 있다.
cat /usr/lib/systemd/system/postgresql-13.service
[root@postgres ~]# cat /usr/lib/systemd/system/postgresql-13.service
[Unit]
Description=PostgreSQL 13 database server
Documentation=https://www.postgresql.org/docs/13/static/
After=syslog.target
After=network.target
[Service]
Type=notify
User=postgres
Group=postgres
Environment=PGDATA=/var/lib/pgsql/13/data/
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0
ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-13/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.target
[root@postgres ~]#- 실행 User/Group : postgres
- App 설치경로 : /usr/pgsql-13
- DB Data 저장소경로 : /var/lib/pgsql/13/data
7. psql(Postgresql Client Tool) 로 db를 접속
su - postgres
select version();
[root@postgres data]# su - postgres
Last login: Thu Jan 7 10:27:18 KST 2021 on pts/0
-bash-4.2$ psql
psql (13.1)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=#버전이 정상적으로 표시되면 설치 및 구동은 완료된 것이다.
8. 외부접속설정
postgresql.conf 파일에서 listen_addresses = '*' 로 수정처리
cd /var/lib/pgsql/13/data/
vi postgresql.conf
listen_addresses = '*'
-bash-4.2$ cd /var/lib/pgsql/13/data/
-bash-4.2$ vi postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # 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)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
pg_hba.conf 파일 - 마지막줄에 host all all 0.0.0.0/0 md5 를 추가한다.
cd /var/lib/pgsql/13/data/
vi pg_hba.conf
[root@postgres data]# vi pg_hba.conf
# 외부접속설정
host all all 0.0.0.0/0 md5
:wq
[root@postgres data]#
설정파일을 변경뒤 postgresql 서비스를 재기동한다.
su - root
systemctl restart postgres-13
9. 하이디SQL 로 접속
에러창이 표시되면 URL로 들어가 Visual-C 관련 패키지를 설치해 주면 된다.
FATAL : no pg_hba.conf entry for host "192.168.0.17", user "postgres", database "postgres", SSL off
이 메세지가 표시되면서 접속이 안되면 <8.외부접속설정> 부분을 다시 확인하자.
FATAL: password authentication failed for user "postgres"
이 메세지가 표시되면 postgres role 의 암호를 변경하자.
10. postgres 암호변경
su - postgres
\password postgres
[root@postgres data]# su - postgres
Last login: Thu Jan 7 10:52:44 KST 2021 on pts/0
-bash-4.2$ psql
psql (13.1)
Type "help" for help.
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=#
11. 다시 하이디SQL에 접속하고 SQL를 실행
select version(), current_database(), current_schema(), current_user
## 에필로그
* Postgresql 13.1 설치가 무난히(?) 완료되었다. 이제 오픈소스이기도 하면서 강력한 기능을 가지고 있는 RDBMS인 Postgresql를 맘껏 사용하도록 하자.
* Postgresql, Greenplum 계열의 db는 dbeaver sql tool 이 더 사용하기가 편한것 같아 추후 포스팅을 진행 해야 겠다.
728x90
반응형
'Database > Postgresql' 카테고리의 다른 글
| Postgresql function 개발 및 예제 (0) | 2021.01.22 |
|---|---|
| pgagroal 란? (0) | 2021.01.15 |
| Postgresql 13 - pg_stat_statements 정의 및 기능정리 (0) | 2021.01.12 |
| Postgresql 13 - pg_stat_statements 설치 및 사용방법 (0) | 2021.01.11 |
| Postgresql 13 - 운영환경 구성방법 (2) | 2021.01.10 |
