1. YUM Repository 업데이트
# rpm -Uvh https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Retrieving https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Preparing... ################################# [100%]
package pgdg-redhat-repo-42.0-11.noarch is already installed
2. PostgreSQL 12를 설치
# rpm -qi pgdg-redhat-repo
Name : pgdg-redhat-repo
Version : 42.0
Release : 11
Architecture: noarch
Install Date: Thu 14 May 2020 04:49:07 AM KST
Group : Unspecified
Size : 10880
License : PostgreSQL
Signature : DSA/SHA1, Fri 01 May 2020 10:41:44 PM KST, Key ID 1f16d2e1442df0f8
Source RPM : pgdg-redhat-repo-42.0-11.src.rpm
Build Date : Fri 01 May 2020 10:41:42 PM KST
Build Host : koji-centos7-x86-64-pgbuild
Relocations : (not relocatable)
Vendor : PostgreSQL Global Development Group
URL : https://yum.postgresql.org
Summary : PostgreSQL PGDG RPMs- Yum Repository Configuration for Red Hat / CentOS
Description :
This package contains yum configuration for Red Hat Enterprise Linux, CentOS,
and also the GPG key for PGDG RPMs.
# yum -y install postgresql12-server postgresql12-contrib
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirror.kakao.com
* epel: mirrors.yun-idc.com
* extras: mirror.kakao.com
* ius: mirrors.kernel.org
* updates: mirror.kakao.com
Resolving Dependencies
--> Running transaction check
---> Package postgresql12-contrib.x86_64 0:12.2-2PGDG.rhel7 will be installed
--> Processing Dependency: postgresql12-libs(x86-64) = 12.2-2PGDG.rhel7 for package: postgresql12-contrib-12.2-2PGDG.rhel7.x86_64
--> Processing Dependency: postgresql12(x86-64) = 12.2-2PGDG.rhel7 for package: postgresql12-contrib-12.2-2PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql12-contrib-12.2-2PGDG.rhel7.x86_64
---> Package postgresql12-server.x86_64 0:12.2-2PGDG.rhel7 will be installed
--> Running transaction check
---> Package postgresql12.x86_64 0:12.2-2PGDG.rhel7 will be installed
---> Package postgresql12-libs.x86_64 0:12.2-2PGDG.rhel7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
========================================================================================================================================================================================
Package Arch Version Repository Size
========================================================================================================================================================================================
Installing:
postgresql12-contrib x86_64 12.2-2PGDG.rhel7 pgdg12 608 k
postgresql12-server x86_64 12.2-2PGDG.rhel7 pgdg12 4.9 M
Installing for dependencies:
postgresql12 x86_64 12.2-2PGDG.rhel7 pgdg12 1.6 M
postgresql12-libs x86_64 12.2-2PGDG.rhel7 pgdg12 367 k
Transaction Summary
========================================================================================================================================================================================
Install 2 Packages (+2 Dependent packages)
Total download size: 7.4 M
Installed size: 31 M
Downloading packages:
(1/4): postgresql12-12.2-2PGDG.rhel7.x86_64.rpm | 1.6 MB 00:00:02
(2/4): postgresql12-libs-12.2-2PGDG.rhel7.x86_64.rpm | 367 kB 00:00:00
(3/4): postgresql12-contrib-12.2-2PGDG.rhel7.x86_64.rpm | 608 kB 00:00:03
(4/4): postgresql12-server-12.2-2PGDG.rhel7.x86_64.rpm | 4.9 MB 00:00:00
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 1.9 MB/s | 7.4 MB 00:00:03
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql12-libs-12.2-2PGDG.rhel7.x86_64 1/4
Installing : postgresql12-12.2-2PGDG.rhel7.x86_64 2/4
Installing : postgresql12-contrib-12.2-2PGDG.rhel7.x86_64 3/4
Installing : postgresql12-server-12.2-2PGDG.rhel7.x86_64 4/4
Verifying : postgresql12-12.2-2PGDG.rhel7.x86_64 1/4
Verifying : postgresql12-contrib-12.2-2PGDG.rhel7.x86_64 2/4
Verifying : postgresql12-server-12.2-2PGDG.rhel7.x86_64 3/4
Verifying : postgresql12-libs-12.2-2PGDG.rhel7.x86_64 4/4
Installed:
postgresql12-contrib.x86_64 0:12.2-2PGDG.rhel7
postgresql12-server.x86_64 0:12.2-2PGDG.rhel7
Dependency Installed:
postgresql12.x86_64 0:12.2-2PGDG.rhel7
postgresql12-libs.x86_64 0:12.2-2PGDG.rhel7
Complete!
3. 기본 데이터베이스 생성
# ls /usr
bin etc games include lib lib64 libexec local pgsql-12 sbin share src tmp
# /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OK
4. 서비스 실행 및 등록
# systemctl enable --now postgresql-12
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service.
# systemctl list-unit-files --type=service | grep postgre
postgresql-12.service enabled
# systemctl status postgresql-12
[0m postgresql-12.service - PostgreSQL 12 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2020-05-14 18:50:21 KST; 54s ago
Docs: https://www.postgresql.org/docs/12/static/
Process: 128643 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 128659 (postmaster)
CGroup: /system.slice/postgresql-12.service
쒋128659 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
쒋128664 postgres: logger
쒋128667 postgres: checkpointer
쒋128668 postgres: background writer
쒋128669 postgres: walwriter
쒋128670 postgres: autovacuum launcher
쒋128671 postgres: stats collector
붴128672 postgres: logical replication launcher
May 14 18:50:20 localhost.localdomain systemd[1]: Starting PostgreSQL 12 database server...
May 14 18:50:21 localhost.localdomain postmaster[128659]: 2020-05-14 18:50:21.002 KST [128659] LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC)...9), 64-bit
May 14 18:50:21 localhost.localdomain postmaster[128659]: 2020-05-14 18:50:21.003 KST [128659] LOG: listening on IPv6 address "::1", port 5432
May 14 18:50:21 localhost.localdomain postmaster[128659]: 2020-05-14 18:50:21.003 KST [128659] LOG: listening on IPv4 address "127.0.0.1", port 5432
May 14 18:50:21 localhost.localdomain postmaster[128659]: 2020-05-14 18:50:21.004 KST [128659] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
May 14 18:50:21 localhost.localdomain postmaster[128659]: 2020-05-14 18:50:21.005 KST [128659] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
May 14 18:50:21 localhost.localdomain postmaster[128659]: 2020-05-14 18:50:21.021 KST [128659] LOG: redirecting log output to logging collector process
May 14 18:50:21 localhost.localdomain postmaster[128659]: 2020-05-14 18:50:21.021 KST [128659] HINT: Future log output will appear in directory "log".
May 14 18:50:21 localhost.localdomain systemd[1]: Started Postg
5. postgresql 관리자 패스워드 설정
# su - postgres
Last failed login: Thu May 14 16:44:30 KST 2020 from 159.89.231.172 on ssh:notty
There were 146 failed login attempts since the last successful login.
-bash-4.2$ psql
psql (12.2)
Type "help" for help.
postgres=# alter user postgres with password '********';
ALTER ROLE
postgres=#
6. 원격접속이 가능하도록 설정 (접속허용할 Interfaces 설정)
# vi /var/lib/pgsql/12/data/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)
7. pg_hba.conf 수정 (접속허용할 remotoe address and subnet 설정)
# vi /var/lib/pgsql/12/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
host all all 0.0.0.0/0 md5
8. 서비스 재시작
# systemctl restart postgresql-12
9. Windows 서버 방화벽 포트 열고, 내 경우엔 VMWare NAT 설정
10. DB Tool
https://dbeaver.io/