본문 바로가기
엑셈 경쟁력/DB 인사이드

DB 인사이드 | PostgreSQL Setup - Installation

by EXEM 2022. 8. 25.

 

 

본 문서에서는 Linux와 Windows에서 PostgreSQL 여러 설치 방법에 대해 설명하도록 하겠습니다.

※ 목차 ※
Install Overview
Install PostgreSQL on Linux (CentOS - yum)
Install PostgreSQL on Linux (CentOS - rpm)
Install PostgreSQL on Linux (Ubuntu - apt)
Install PostgreSQL on Linux (Ubuntu - dpkg)
Install PostgreSQL on Linux (Source File)
Install PostgreSQL on Windows (설치관리자)
Install PostgreSQL on Windows (Binary File)
부록 1. 외부 접속을 위한 설정 파일 변경
부록 2. Extension 사용을 위한 준비
부록 3. Windows Stack Builder 설치

 

Install Overview 목차

본 문서는 PostgreSQL을 Redhat 계열의 CentOS Linux, Debian 계열의 Ubuntu Linux, Windows Platform에 설치하는 방법을 설명합니다.

Linux Platform

Linux Platform에서는 3가지의 설치방법이 있습니다.

  • Package Manager를 통한 설치(yum, apt)
    설치되는 Package 간의 의존성(Dependency)을 자동 해소할 수 있는 CentOS의 yum(Yellodog Update Modified), Ubuntu의 apt(Advanced Packaging Tool)와 같은 Package Manager를 사용합니다. 이러한 Package Manager를 사용하기 위해서는 PostgreSQL Package Repository에 연결이 가능하도록 외부 인터넷 사용이 가능해야 합니다. 이 방법은 PostgreSQL Major 버전의 최신 Minor 버전으로 설치됩니다.
  • 개별 Package를 통한 설치(rpm, dpkg)
    PostgreSQL 설치 Package를 직접 다운로드하여서 CentOS의 rpm(RedHat Package Manager), Ubuntu의 dpkg(Debian Package)를 사용하여 설치할 수 있습니다. PostgreSQL Package가 준비되어 있으면, 외부 인터넷 사용이 불가능한 상황에서도 설치가 가능하며, PostgreSQL 버전을 선택하여 설치가 가능합니다. 단, Package 간의 의존성(Dependency)을 사용자가 직접 해결해야 합니다.
  • PostgreSQL Source File을 통한 설치
    PostgreSQL 커뮤니티(postgresql.org)에서 Source File을 다운로드할 수 있습니다. Source File을 통해 PostgreSQL 설치를 위한 파일을 Build 할 수 있습니다. Build를 위한 Library만 준비되어 있으면, 의존성(Dependency)에 상관없이 PostgreSQL 버전을 선택하여 설치할 수 있습니다.
📢 CentOS 등 RedHat 계열의 Linux는 배포 정책으로 인해 자동으로 PostgreSQL Database 초기화 및 서비스가 자동으로 시작되지 않기 때문에, Database 초기화 및 서비스 자동 시작을 수동으로 진행해야 합니다.
반면에 Ubuntu 등 Debian 계열의 Linux는 PostgreSQL Install 시 PostgreSQL Database 초기화 및 서비스 자동 시작이 되므로 initdb을 수동으로 수행할 필요가 없습니다.

 

Windows Platform

Windows Platform에서는 2가지의 설치방법이 있습니다.

  • 설치 관리자를 통한 설치
    설치하고자 하는 PostreSQL 버전 설치 파일을 설치관리자 GUI를 통해 몇 번의 클릭만으로 설치할 수 있습니다.
  • binary File(Zip Archive)를 통한 설치
    Linux Platform의 Source File과 비슷하지만, 설치 파일을 위한 Build는 필요 없습니다. 단순히 Binary File을 압축 해제하여, Initialzation만 수행하는 것으로 PostgreSQL을 사용할 수 있습니다.

각 Platform별 최초 설치 시 특이사항을 정리하면 아래 표와 같습니다.

  Linux Windows
yum apt rpm dpkg Source File 설치관리자 Binary File
설치 Package Repository Respority, 개별준비 개별준비 개별준비 개별준비
의존성 자동해결 사용자해결 사용자해결 사용자해결 사용자해결
설치 버전 Major별 최신 Minor 선택 선택 선택 선택
Engine 경로 변경불가 변경불가 변경가능 가능 가능 가능
Data Directory 변경가능 변경불가 변경가능 변경가능 변경가능 변경가능
특이사항 인터넷연결필수          

 

PostgreSQL 설치 시 Engine Directory, Data Directory는 기본 경로로 설치가 되며 기본 경로는 아래 표와 같이 Platform 별로 상이합니다. Directory 경로에 표시되는 VERSION은 Major버전을 의미합니다.

  CentOS
(Redhat 계열)
Ubuntu
(Debian 계열)
Windows
Engine /usr/pgsql-{VERSION} /usr/lib/postgresql/{VERSION} C:\Program Files\PostgreSQL\{VERSION}
Data /var/lib/pgsql/{VERSION}/data /var/lib/postgresql/{VERSION}/main C:\Program Files\PostgreSQL\{VERSION}\data
Configuration /var/lib/pgsql/{VERSION}/data /etc/postgresql/{VERSION}/main C:\Program Files\PostgreSQL\{VERSION}\data
서비스명 postgresql-{VERSION} postgresql@{VERSION}-main postgresql-x64-{VERSION}

 

이후부터는 각 Platform 별로 PostgreSQL 설치 방법을 설명합니다. 설명하는 데 사용되는 버전은 다음과 같으며, Linux는 Redhat계열과 Debian계열로 나누어 설명합니다.

  • OS Version : CentOS 7.9, Ubuntu 22.04, Windows 10
  • DB Version : PostgreSQL 14.5

 

Install PostgreSQL on Linux (CentOS - yum) 목차

Redhat 계열 Linux에서는 PostgreSQL yum Repository에서 yum을 통해 PostgreSQL을 설치할 수 있습니다. PostgreSQL yum Repository는 RedHat Enterprise Linux, Rocky Linux, CentOS(6 and 7 only), Oracle Linux, Redora에서만 사용이 가능합니다. yum으로 설치 시 Package 간의 의존성(Dependency)에 의해 여러 Pacakage가 설치됩니다.

Package 의존성(Dependency)에 의해 설치되는 Package 종류

Package 명 설명
postgresql14-libs 모든 PostgreSQL Client 프로그램에서 필요한 공유 라이브러리
postgresql14 PostgreSQL Client 프로그램 및 라이브러리
postgresq14-server PostgreSQL Server 생성 및 실행에 필요한 프로그램
libpq.so.5 PostgreSQL C언어 Client 라이브러리

추가 설치 가능한 PostgreSQL Package 종류

Package 명 설명
postgresql14-contrib PostgreSQL과 함께 배포되는 Source 및 바이너리
postgresql14-devel PostgreSQL 개발 헤더 파일 과 라이브러리
postgresql14-docs PostgreSQL에 대한 추가 문서
postgresql14-llvmjit PostgreSQL에서 JIT(Just-in-time) 컴파일 지원
postgresql14-odbc PostgreSQL ODBC 드라이버
postgresql14-plperl PostgreSQL용 Perl 절차 언어(Procedural Language Perl)
postgresql14-plpython3 PostgreSQL용 Python3 절차 언어(Procedural Language Python3)
postgresql14-pltcl PostgreSQL용 Tcl 절차 언어(Procedural Language Tcl)
postgresql14-tcl PostgreSQL용 Tcl Client 라이브러리
postgresql14-test PostgreSQL과 함께 배포되는 테스트 제품군

 

1. PostgreSQL yum Repository Install

## [root] PostgreSQL yum Repository 등록
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

## CentOS 6 : yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-6-x86_64/pgdg-redhat-repo-latest.noarch.rpm
## CentOS 7 : yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
## Redhat 8 : dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
## Redhat 9 : dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
더보기
[root@centos ~]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Loaded plugins: fastestmirror, langpacks
pgdg-redhat-repo-latest.noarch.rpm                                                                    | 8.2 kB  00:00:00
Examining /var/tmp/yum-root-ppr7j8/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-25.noarch
Marking /var/tmp/yum-root-ppr7j8/pgdg-redhat-repo-latest.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat-repo.noarch 0:42.0-25 will be installed
--> Finished Dependency Resolution
base/7/x86_64                                                                                         | 3.6 kB  00:00:00
extras/7/x86_64                                                                                       | 2.9 kB  00:00:00
updates/7/x86_64                                                                                      | 2.9 kB  00:00:00
updates/7/x86_64/primary_db                                                                           |  17 MB  00:00:01

Dependencies Resolved

=============================================================================================================================
 Package                       Arch                Version                Repository                                    Size
=============================================================================================================================
Installing:
 pgdg-redhat-repo              noarch              42.0-25                /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-25.noarch                                                                           1/1
  Verifying  : pgdg-redhat-repo-42.0-25.noarch                                                                           1/1

Installed:
  pgdg-redhat-repo.noarch 0:42.0-25

Complete!

 

2. PostgreSQL Install

## [root] yum install -y postgresql{VERSION}-server
yum install -y postgresql14-server
더보기
Loaded plugins: fastestmirror, langpacks
Determining fastest mirrors
 * base: mirror.kakao.com
 * extras: mirror.kakao.com
 * updates: mirror.kakao.com
pgdg-common/7/x86_64/signature                                                                        |  198 B  00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
 Package    : pgdg-redhat-repo-42.0-25.noarch (@/pgdg-redhat-repo-latest.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg-common/7/x86_64/signature                                                                        | 2.9 kB  00:00:00 !!!
pgdg10/7/x86_64/signature                                                                             |  198 B  00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
 Package    : pgdg-redhat-repo-42.0-25.noarch (@/pgdg-redhat-repo-latest.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg10/7/x86_64/signature                                                                             | 3.6 kB  00:00:00 !!!
pgdg11/7/x86_64/signature                                                                             |  198 B  00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
 Package    : pgdg-redhat-repo-42.0-25.noarch (@/pgdg-redhat-repo-latest.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg11/7/x86_64/signature                                                                             | 3.6 kB  00:00:00 !!!
pgdg12/7/x86_64/signature                                                                             |  198 B  00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
 Package    : pgdg-redhat-repo-42.0-25.noarch (@/pgdg-redhat-repo-latest.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg12/7/x86_64/signature                                                                             | 3.6 kB  00:00:00 !!!
pgdg13/7/x86_64/signature                                                                             |  198 B  00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
 Package    : pgdg-redhat-repo-42.0-25.noarch (@/pgdg-redhat-repo-latest.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg13/7/x86_64/signature                                                                             | 3.6 kB  00:00:00 !!!
pgdg14/7/x86_64/signature                                                                             |  198 B  00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
 Package    : pgdg-redhat-repo-42.0-25.noarch (@/pgdg-redhat-repo-latest.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg14/7/x86_64/signature                                                                             | 3.6 kB  00:00:00 !!!
(1/11): pgdg10/7/x86_64/group_gz                                                                      |  245 B  00:00:01
(2/11): pgdg11/7/x86_64/group_gz                                                                      |  245 B  00:00:01
(3/11): pgdg12/7/x86_64/group_gz                                                                      |  245 B  00:00:00
(4/11): pgdg13/7/x86_64/group_gz                                                                      |  246 B  00:00:00
(5/11): pgdg10/7/x86_64/primary_db                                                                    | 392 kB  00:00:02
(6/11): pgdg-common/7/x86_64/primary_db                                                               | 163 kB  00:00:02
(7/11): pgdg14/7/x86_64/group_gz                                                                      |  244 B  00:00:00
(8/11): pgdg11/7/x86_64/primary_db                                                                    | 428 kB  00:00:02
(9/11): pgdg12/7/x86_64/primary_db                                                                    | 309 kB  00:00:01
(10/11): pgdg14/7/x86_64/primary_db                                                                   | 132 kB  00:00:00
(11/11): pgdg13/7/x86_64/primary_db                                                                   | 215 kB  00:00:01
Resolving Dependencies
--> Running transaction check
---> Package postgresql14-server.x86_64 0:14.5-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql14-libs(x86-64) = 14.5-1PGDG.rhel7 for package: postgresql14-server-14.5-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql14(x86-64) = 14.5-1PGDG.rhel7 for package: postgresql14-server-14.5-1PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql14-server-14.5-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql14.x86_64 0:14.5-1PGDG.rhel7 will be installed
---> Package postgresql14-libs.x86_64 0:14.5-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================
 Package                             Arch                   Version                             Repository              Size
=============================================================================================================================
Installing:
 postgresql14-server                 x86_64                 14.5-1PGDG.rhel7                    pgdg14                 5.5 M
Installing for dependencies:
 postgresql14                        x86_64                 14.5-1PGDG.rhel7                    pgdg14                 1.5 M
 postgresql14-libs                   x86_64                 14.5-1PGDG.rhel7                    pgdg14                 270 k

Transaction Summary
=============================================================================================================================
Install  1 Package (+2 Dependent packages)

Total download size: 7.3 M
Installed size: 31 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/pgdg14/packages/postgresql14-libs-14.5-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Public key for postgresql14-libs-14.5-1PGDG.rhel7.x86_64.rpm is not installed
(1/3): postgresql14-libs-14.5-1PGDG.rhel7.x86_64.rpm                                                  | 270 kB  00:00:02
(2/3): postgresql14-14.5-1PGDG.rhel7.x86_64.rpm                                                       | 1.5 MB  00:00:02
(3/3): postgresql14-server-14.5-1PGDG.rhel7.x86_64.rpm                                                | 5.5 MB  00:00:01
-----------------------------------------------------------------------------------------------------------------------------
Total                                                                                        1.8 MB/s | 7.3 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 <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
 Package    : pgdg-redhat-repo-42.0-25.noarch (@/pgdg-redhat-repo-latest.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : postgresql14-libs-14.5-1PGDG.rhel7.x86_64                                                                 1/3
  Installing : postgresql14-14.5-1PGDG.rhel7.x86_64                                                                      2/3
  Installing : postgresql14-server-14.5-1PGDG.rhel7.x86_64                                                               3/3
  Verifying  : postgresql14-14.5-1PGDG.rhel7.x86_64                                                                      1/3
  Verifying  : postgresql14-server-14.5-1PGDG.rhel7.x86_64                                                               2/3
  Verifying  : postgresql14-libs-14.5-1PGDG.rhel7.x86_64                                                                 3/3

Installed:
  postgresql14-server.x86_64 0:14.5-1PGDG.rhel7

Dependency Installed:
  postgresql14.x86_64 0:14.5-1PGDG.rhel7                     postgresql14-libs.x86_64 0:14.5-1PGDG.rhel7

Complete!

 

3. PostgreSQL Initialization

기본 Data Directory 사용

## [root] Data Directory를 기본설정으로 PostgreSQL 초기화
/usr/pgsql-14/bin/postgresql-14-setup initdb
더보기
[root@centos ~]# /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK

 

사용자 지정 Data Directory 사용

1) Data Directory 생성

## [root] Data Directory 생성 및 소유권 변경
mkdir /PostgreSQL_Data
chown postgres:postgres /PostgreSQL_Data
📢 initdb 수행 시 postgres 계정 소유의 디렉토리 내에서는 Data Directory를 생성하지만, postgres 계정 소유가 아닌 디렉토리에는 Data Directory를 생성하지 못하고 Permission denied 에러가 발생합니다.

2) PostgreSQL Initialization

## [postgres] 앞서 생성한 Data Directory로 PostgreSQL 초기화
su - postgres
/usr/pgsql-14/bin/initdb -D /PostgreSQL_Data
더보기
## root 계정으로 수행
[root@centos ~]# /usr/pgsql-14/bin/initdb -D /PostgreSQL_Data
initdb: error: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.

## postgres 계정으로 수행
-bash-4.2$ /usr/pgsql-14/bin/initdb -D /PostgreSQL_Data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /PostgreSQL_Data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Seoul
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/pgsql-14/bin/pg_ctl -D /PostgreSQL_Data -l logfile start

3) PostgreSQL Service 파일 내용 수정

## [root] PostgreSQL 서비스 등록을 위해 Service Configuration 변경 (postgresql-14.service)
vi /usr/lib/systemd/system/postgresql-14.service
...
Environment=PGDATA=/var/lib/pgsql/14/data/ 내용을 Environment=PGDATA=/PostgreSQL_Data 로 변경
...

 

4. PostgreSQL 기동

## [root] PostgreSQL 서비스 기동
systemctl start postgresql-14
📢 ※ systemctl을 통한 PostgreSQL 기동 제어
systemctl { enable | start | stop | status } { PostgreSQL-Service-Name }

 

5. PostgreSQL 접속 및 확인

## [postgres]
su - postgres
psql -c "SELECT VERSION()"
더보기
[root@centos ~]# su - postgres
-bash-4.2$ psql -c "SELECT VERSION()"
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

 

Install PostgreSQL on Linux (CentOS - rpm) 목차

인터넷 연결이 가능한 장비(PC)에서 PostgreSQL을 설치하기 위한 rpm을 다운로드한 후 PostgreSQL을 설치하고자 하는 서버로 옮겨 설치 작업을 할 수 있습니다. rpm으로 설치 시 Package 간의 의존성(Dependency)을 주의해야 합니다. yum을 통해 설치하는 방법과는 파일 준비과정만 다릅니다.

 

1. PostgreSQL Package Download

PostgreSQL yum Repository(yum.postgresql.org/packages)에서 아래 표에 나열된 OS 버전에 맞는 Package를 다운로드하거나, 인터넷 연결이 가능한 Server에서 yum을 통해 Package를 다운로드할 수 있습니다.

## [root] PostgreSQL yum Repository 등록
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

## --downloadonly : Package들은 설치하지 않고 다운로드만 하는 옵션
## --downloaddir : Package를 다운로드 받을 위치
yum install -y --downloadonly --downloaddir=/root/PostgreSQL_Package postgresql14-server
더보기
[root@centos ~]# yum install -y --downloadonly --downloaddir=/root/PostgreSQL_Package postgresql14-server
Failed to set locale, defaulting to C
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirror.kakao.com
 * extras: mirror.kakao.com
 * updates: mirror.kakao.com
Resolving Dependencies
--> Running transaction check
---> Package postgresql14-server.x86_64 0:14.5-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql14-libs(x86-64) = 14.5-1PGDG.rhel7 for package: postgresql14-server-14.5-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql14(x86-64) = 14.5-1PGDG.rhel7 for package: postgresql14-server-14.5-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql14.x86_64 0:14.5-1PGDG.rhel7 will be installed
---> Package postgresql14-libs.x86_64 0:14.5-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================
 Package                            Arch                  Version                           Repository             Size
========================================================================================================================
Updating:
 postgresql14-server                x86_64                14.5-1PGDG.rhel7                  pgdg14                5.5 M
Updating for dependencies:
 postgresql14                       x86_64                14.5-1PGDG.rhel7                  pgdg14                1.5 M
 postgresql14-libs                  x86_64                14.5-1PGDG.rhel7                  pgdg14                270 k

Transaction Summary
========================================================================================================================
Upgrade  1 Package (+2 Dependent packages)

Total download size: 7.3 M
Background downloading packages, then exiting:
No Presto metadata available for pgdg14
(1/3): postgresql14-libs-14.5-1PGDG.rhel7.x86_64.rpm                                             | 270 kB  00:00:02
(2/3): postgresql14-14.5-1PGDG.rhel7.x86_64.rpm                                                  | 1.5 MB  00:00:02
(3/3): postgresql14-server-14.5-1PGDG.rhel7.x86_64.rpm                                           | 5.5 MB  00:00:01
------------------------------------------------------------------------------------------------------------------------
Total                                                                                   1.8 MB/s | 7.3 MB  00:00:04
exiting because "Download Only" specified

[root@centos ~]# cd PostgreSQL_Package/
[root@centos PostgreSQL_Package]# pwd
/root/PostgreSQL_Package

[root@centos PostgreSQL_Package]# ls -al
total 7440
drwxr-xr-x. 2 root root     162 Aug 23 12:00 .
dr-xr-x---. 8 root root    4096 Aug 23 12:00 ..
-rw-r--r--. 1 root root 1565068 Aug 10 21:11 postgresql14-14.5-1PGDG.rhel7.x86_64.rpm
-rw-r--r--. 1 root root  276136 Aug 10 21:11 postgresql14-libs-14.5-1PGDG.rhel7.x86_64.rpm
-rw-r--r--. 1 root root 5766468 Aug 10 21:11 postgresql14-server-14.5-1PGDG.rhel7.x86_64.rpm
Package 명 설명
postgresql14-libs 모든 PostgreSQL Client 프로그램에서 필요한 공유 라이브러리
postgresql14 PostgreSQL Client 프로그램 및 라이브러리
postgresq14-server PostgreSQL Server 생성 및 실행에 필요한 프로그램

 

2. PostgreSQL Install

Package 설치 시 Package 간의 의존성(Dependency) 문제로 인해 postgresql14-libs → postgresql14 → postgresql14-server 순으로 설치를 합니다.

## [root] PostgreSQL 설치에 필요한 Package 설치
rpm -ivh postgresql14-libs-14.5-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-14.5-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-server-14.5-1PGDG.rhel7.x86_64.rpm
더보기
[root@centos ~]# rpm -ivh postgresql14-libs-14.5-1PGDG.rhel7.x86_64.rpm
warning: postgresql14-libs-14.5-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql14-libs-14.5-1PGDG.rhel################################# [100%]

[root@centos ~]# rpm -ivh postgresql14-14.5-1PGDG.rhel7.x86_64.rpm
warning: postgresql14-14.5-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql14-14.5-1PGDG.rhel7    ################################# [100%]

[root@centos ~]# rpm -ivh postgresql14-server-14.5-1PGDG.rhel7.x86_64.rpm
warning: postgresql14-server-14.5-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql14-server-14.5-1PGDG.rh################################# [100%]

 

3. PostgreSQL Initialization

기본 Data Directory 사용

## [root] Data Directory를 기본설정으로 PostgreSQL 초기화
/usr/pgsql-14/bin/postgresql-14-setup initdb
더보기
[root@centos ~]# /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK

 

사용자 지정 Data Directory 사용

1) Data Directory 생성

## [root] Data Directory 생성 및 소유권 변경
mkdir /PostgreSQL_Data
chown postgres:postgres /PostgreSQL_Data
📢 initdb 수행 시 postgres 계정 소유의 디렉토리 내에서는 Data Directory를 생성하지만, postgres 계정 소유가 아닌 디렉토리에는 Data Directory를 생성하지 못하고 Permission denied 에러가 발생합니다.

2) PostgreSQL Initialization

## [postgres] 앞서 생성한 Data Directory로 PostgreSQL 초기화
su - postgres
/usr/pgsql-14/bin/initdb -D /PostgreSQL_Data
더보기
## root 계정으로 수행
[root@centos ~]# /usr/pgsql-14/bin/initdb -D /PostgreSQL_Data
initdb: error: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.

## postgres 계정으로 수행
-bash-4.2$ /usr/pgsql-14/bin/initdb -D /PostgreSQL_Data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /PostgreSQL_Data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Seoul
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/pgsql-14/bin/pg_ctl -D /PostgreSQL_Data -l logfile start

3) PostgreSQL Service 파일 내용 수정

## [root] PostgreSQL 서비스 등록을 위해 Service Configuration 변경 (postgresql-14.service)
vi /usr/lib/systemd/system/postgresql-14.service
...
Environment=PGDATA=/var/lib/pgsql/13/data/ 내용을 Environment=PGDATA=/PostgreSQL_Data 로 변경
...

 

4. PostgreSQL 기동

## [root] PostgreSQL 서비스 기동
systemctl start postgresql-14
📢 ※ systemctl을 통한 PostgreSQL 기동 제어
systemctl { enable | start | stop | status } { PostgreSQL-Service-Name }

 

5. PostgreSQL 접속 및 확인

## [postgres]
su - postgres
psql -c "SELECT VERSION()"
더보기
[root@centos ~]# su - postgres
-bash-4.2$ psql -c "SELECT VERSION()"
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

 

Install PostgreSQL on Linux (Ubuntu - apt) 목차

Debian 계열 Linux에서는 PostgreSQL apt Repository에서 apt을 통해 PostgreSQL을 설치할 수 있습니다.

Package 의존성(Dependency)에 의해 설치되는 Package 종류

Package 명 설명
libpq5 PostgreSQL C언어 Client 라이브러리
libcommon-sense-perl Perl 프로그램에 위한 모듈
libjson-perl JSON 형식 Data를 조작하기 위한 모듈
pgdg-keyring apt.postgresql.org Keyring
postgresql-client-common PostgreSQL Clinet 버전 관리자
postgresql-client-14 PostgreSQL Client 프로그램 및 관리 프로그램 포함
libtypes-serialiser-perl 일반적인 질렬화 형식에 대한 간단한 데이터 유형을 제공하는 모듈
libjson-xs-perl XML의 대안으로 사용되는 JSON::XS 형식 데이터를 조작하기 위한 모듈
libllvm14 PostgreSQL에서 JIT(Just-in-time) 컴파일 지원
postgresql-common PostgreSQL Database Cluster Manager
postgresql-14 PostgreSQL Server 생성 및 실행에 필요한 프로그램
libsensors-config lm-sensor 설정 파일(온도, 전압, 팬속도 등)
libsensors5 lm-sensor(온도,전압,팬 등)를 읽기 위한 라이브러리
ssl-cert OepnSSL
sysstat Linux용 시스템 성능 도구

 

1. PostgreSQL apt Repository Install

## [root] PostgreSQL apt Repository 등록
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

## [root] Repository Signing key 등록
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

## [root] Package Update
apt-get update
더보기
root@ubuntu:~# sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
root@ubuntu:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)).
OK
root@ubuntu:~# apt-get update
Hit:1 http://kr.archive.ubuntu.com/ubuntu jammy InRelease
Get:2 http://apt.postgresql.org/pub/repos/apt jammy-pgdg InRelease [91.7 kB]
Get:3 http://kr.archive.ubuntu.com/ubuntu jammy-updates InRelease [114 kB]
Get:4 http://kr.archive.ubuntu.com/ubuntu jammy-backports InRelease [99.8 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 Packages [240 kB]
Get:6 http://kr.archive.ubuntu.com/ubuntu jammy-security InRelease [110 kB]
Get:7 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main amd64 Packages [511 kB]
Get:8 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main Translation-en [122 kB]
Get:9 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main amd64 c-n-f Metadata [7,444 B]
Get:10 http://kr.archive.ubuntu.com/ubuntu jammy-updates/restricted amd64 Packages [276 kB]
Get:11 http://kr.archive.ubuntu.com/ubuntu jammy-updates/restricted Translation-en [42.5 kB]
Get:12 http://kr.archive.ubuntu.com/ubuntu jammy-updates/restricted amd64 c-n-f Metadata [524 B]
Get:13 http://kr.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 Packages [214 kB]
Get:14 http://kr.archive.ubuntu.com/ubuntu jammy-updates/universe Translation-en [71.5 kB]
Get:15 http://kr.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 c-n-f Metadata [4,404 B]
Get:16 http://kr.archive.ubuntu.com/ubuntu jammy-updates/multiverse amd64 Packages [7,000 B]
Get:17 http://kr.archive.ubuntu.com/ubuntu jammy-updates/multiverse Translation-en [2,264 B]
Get:18 http://kr.archive.ubuntu.com/ubuntu jammy-updates/multiverse amd64 c-n-f Metadata [420 B]
Get:19 http://kr.archive.ubuntu.com/ubuntu jammy-backports/universe amd64 Packages [6,392 B]
Get:20 http://kr.archive.ubuntu.com/ubuntu jammy-backports/universe Translation-en [9,104 B]
Get:21 http://kr.archive.ubuntu.com/ubuntu jammy-backports/universe amd64 c-n-f Metadata [328 B]
Get:22 http://kr.archive.ubuntu.com/ubuntu jammy-security/main amd64 Packages [267 kB]
Get:23 http://kr.archive.ubuntu.com/ubuntu jammy-security/main Translation-en [63.9 kB]
Get:24 http://kr.archive.ubuntu.com/ubuntu jammy-security/main amd64 c-n-f Metadata [3,924 B]
Get:25 http://kr.archive.ubuntu.com/ubuntu jammy-security/restricted amd64 Packages [243 kB]
Get:26 http://kr.archive.ubuntu.com/ubuntu jammy-security/restricted Translation-en [37.3 kB]
Get:27 http://kr.archive.ubuntu.com/ubuntu jammy-security/restricted amd64 c-n-f Metadata [524 B]
Get:28 http://kr.archive.ubuntu.com/ubuntu jammy-security/universe amd64 Packages [108 kB]
Get:29 http://kr.archive.ubuntu.com/ubuntu jammy-security/universe Translation-en [37.5 kB]
Get:30 http://kr.archive.ubuntu.com/ubuntu jammy-security/universe amd64 c-n-f Metadata [2,408 B]
Get:31 http://kr.archive.ubuntu.com/ubuntu jammy-security/multiverse amd64 Packages [4,192 B]
Get:32 http://kr.archive.ubuntu.com/ubuntu jammy-security/multiverse amd64 c-n-f Metadata [228 B]
Fetched 2,698 kB in 4s (688 kB/s)
Reading package lists... Done
W: http://apt.postgresql.org/pub/repos/apt/dists/jammy-pgdg/InRelease: Key is stored in legacy trusted.gpg keyring (/etc/apt/trusted.gpg), see the DEPRECATION section in apt-key(8) for details.

 

2. PostgreSQL Install

## [root] apt-get -y install postgresql-{VERSION}
apt-get -y install postgresql-14
더보기
root@ubuntu:~# apt-get -y install postgresql-14
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5 libsensors-config libsensors5 libtypes-serialiser-perl
  pgdg-keyring postgresql-client-14 postgresql-client-common postgresql-common ssl-cert sysstat
Suggested packages:
  lm-sensors postgresql-doc-14 isag
The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5 libsensors-config libsensors5 libtypes-serialiser-perl
  pgdg-keyring postgresql-14 postgresql-client-14 postgresql-client-common postgresql-common ssl-cert sysstat
0 upgraded, 15 newly installed, 0 to remove and 41 not upgraded.
Need to get 43.3 MB of archives.
After this operation, 172 MB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 libpq5 amd64 14.5-1.pgdg22.04+1 [172 kB]
Get:2 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libcommon-sense-perl amd64 3.75-2build1 [21.1 kB]
Get:3 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libjson-perl all 4.04000-1 [81.8 kB]
Get:4 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 pgdg-keyring all 2018.2 [10.7 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-client-common all 242.pgdg22.04+1 [92.2 kB]
Get:6 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-client-14 amd64 14.5-1.pgdg22.04+1 [1,615 kB]
Get:7 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libtypes-serialiser-perl all 1.01-1 [11.6 kB]
Get:8 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libjson-xs-perl amd64 4.030-1build3 [87.2 kB]
Get:9 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libllvm14 amd64 1:14.0.0-1ubuntu1 [24.0 MB]
Get:10 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-common all 242.pgdg22.04+1 [230 kB]
Get:11 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-14 amd64 14.5-1.pgdg22.04+1 [16.4 MB]
Get:12 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libsensors-config all 1:3.6.0-7ubuntu1 [5,274 B]
Get:13 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libsensors5 amd64 1:3.6.0-7ubuntu1 [26.3 kB]
Get:14 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 ssl-cert all 1.1.2 [17.4 kB]
Get:15 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 sysstat amd64 12.5.2-2build2 [487 kB]
Fetched 43.3 MB in 5s (9,378 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libcommon-sense-perl:amd64.
(Reading database ... 108753 files and directories currently installed.)
Preparing to unpack .../00-libcommon-sense-perl_3.75-2build1_amd64.deb ...
Unpacking libcommon-sense-perl:amd64 (3.75-2build1) ...
Selecting previously unselected package libjson-perl.
Preparing to unpack .../01-libjson-perl_4.04000-1_all.deb ...
Unpacking libjson-perl (4.04000-1) ...
Selecting previously unselected package libtypes-serialiser-perl.
Preparing to unpack .../02-libtypes-serialiser-perl_1.01-1_all.deb ...
Unpacking libtypes-serialiser-perl (1.01-1) ...
Selecting previously unselected package libjson-xs-perl.
Preparing to unpack .../03-libjson-xs-perl_4.030-1build3_amd64.deb ...
Unpacking libjson-xs-perl (4.030-1build3) ...
Selecting previously unselected package libllvm14:amd64.
Preparing to unpack .../04-libllvm14_1%3a14.0.0-1ubuntu1_amd64.deb ...
Unpacking libllvm14:amd64 (1:14.0.0-1ubuntu1) ...
Selecting previously unselected package libpq5:amd64.
Preparing to unpack .../05-libpq5_14.5-1.pgdg22.04+1_amd64.deb ...
Unpacking libpq5:amd64 (14.5-1.pgdg22.04+1) ...
Selecting previously unselected package libsensors-config.
Preparing to unpack .../06-libsensors-config_1%3a3.6.0-7ubuntu1_all.deb ...
Unpacking libsensors-config (1:3.6.0-7ubuntu1) ...
Selecting previously unselected package libsensors5:amd64.
Preparing to unpack .../07-libsensors5_1%3a3.6.0-7ubuntu1_amd64.deb ...
Unpacking libsensors5:amd64 (1:3.6.0-7ubuntu1) ...
Selecting previously unselected package pgdg-keyring.
Preparing to unpack .../08-pgdg-keyring_2018.2_all.deb ...
Unpacking pgdg-keyring (2018.2) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../09-postgresql-client-common_242.pgdg22.04+1_all.deb ...
Unpacking postgresql-client-common (242.pgdg22.04+1) ...
Selecting previously unselected package postgresql-client-14.
Preparing to unpack .../10-postgresql-client-14_14.5-1.pgdg22.04+1_amd64.deb ...
Unpacking postgresql-client-14 (14.5-1.pgdg22.04+1) ...
Selecting previously unselected package ssl-cert.
Preparing to unpack .../11-ssl-cert_1.1.2_all.deb ...
Unpacking ssl-cert (1.1.2) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../12-postgresql-common_242.pgdg22.04+1_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (242.pgdg22.04+1) ...
Selecting previously unselected package postgresql-14.
Preparing to unpack .../13-postgresql-14_14.5-1.pgdg22.04+1_amd64.deb ...
Unpacking postgresql-14 (14.5-1.pgdg22.04+1) ...
Selecting previously unselected package sysstat.
Preparing to unpack .../14-sysstat_12.5.2-2build2_amd64.deb ...
Unpacking sysstat (12.5.2-2build2) ...
Setting up pgdg-keyring (2018.2) ...
Removing apt.postgresql.org key from trusted.gpg: OK
Setting up libsensors-config (1:3.6.0-7ubuntu1) ...
Setting up libpq5:amd64 (14.5-1.pgdg22.04+1) ...
Setting up libcommon-sense-perl:amd64 (3.75-2build1) ...
Setting up ssl-cert (1.1.2) ...
Setting up libsensors5:amd64 (1:3.6.0-7ubuntu1) ...
Setting up libllvm14:amd64 (1:14.0.0-1ubuntu1) ...
Setting up libtypes-serialiser-perl (1.01-1) ...
Setting up libjson-perl (4.04000-1) ...
Setting up sysstat (12.5.2-2build2) ...

Creating config file /etc/default/sysstat with new version
update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode
Created symlink /etc/systemd/system/sysstat.service.wants/sysstat-collect.timer → /lib/systemd/system/sysstat-collect.timer.
Created symlink /etc/systemd/system/sysstat.service.wants/sysstat-summary.timer → /lib/systemd/system/sysstat-summary.timer.
Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service.
Setting up postgresql-client-common (242.pgdg22.04+1) ...
Setting up libjson-xs-perl (4.030-1build3) ...
Setting up postgresql-client-14 (14.5-1.pgdg22.04+1) ...
update-alternatives: using /usr/share/postgresql/14/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-common (242.pgdg22.04+1) ...
Adding user postgres to group ssl-cert

Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
Setting up postgresql-14 (14.5-1.pgdg22.04+1) ...
Creating new PostgreSQL cluster 14/main ...
/usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/main --auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/14/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
update-alternatives: using /usr/share/postgresql/14/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Processing triggers for man-db (2.10.2-1) ...
Processing triggers for libc-bin (2.35-0ubuntu3) ...
Scanning processes...
Scanning candidates...
Scanning linux images...

Restarting services...
 /etc/needrestart/restart.d/systemd-manager
 systemctl restart packagekit.service ssh.service systemd-journald.service systemd-networkd.service systemd-resolved.service systemd-timesyncd.service systemd-udevd.service udisks2.service
Service restarts being deferred:
 systemctl restart networkd-dispatcher.service
 systemctl restart systemd-logind.service
 systemctl restart unattended-upgrades.service
 systemctl restart user@0.service

No containers need to be restarted.

No user sessions are running outdated binaries.

No VM guests are running outdated hypervisor (qemu) binaries on this host.
📢 Ubuntu의 경우 PostgreSQL Install 단계에서 initdb와 서비스 등록까지 수행되므로 PostgreSQL Initialization, PostgreSQL 서비스 기동 단계 수행이 필요 없습니다. 따라서 Data Directory를 변경하려면 설치 후 경로를 바꾸고 재기동하는 작업이 필요합니다.

 

3. PostgreSQL 접속 및 확인

## [postgres]
su - postgres
psql -c "SELECT VERSION()"
더보기
root@ubuntu:~# su - postgres
postgres@ubuntu:~$ psql -c "SELECT VERSION()"
                                                           version
------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 (Ubuntu 14.5-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
(1 row)

 

Install PostgreSQL on Linux (Ubuntu - dpkg) 목차

rpm을 통한 설치와 마찬가지로 Debian 계열의 .deb Package 파일을 dpkg를 통해 PostgreSQL을 설치할 수 있습니다. Package 간의 의존성(Dependency)을 주의해야 하며, apt를 통해 설치하는 방법과는 파일 준비과정만 다릅니다.

 

1. PostgreSQL Package Download

PostgreSQL apt Repository(apt.postgresql.org/pub/repos/apt/pool/main/p/)에서 아래 표에 나열된 OS 버전에 맞는 Package를 다운로드하거나, 인터넷 연결이 가능한 Server에서 apt를 통해 Package를 다운로드할 수 있습니다.

## [root] PostgreSQL apt Repository 등록
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

## [root] Repository Signing key 등록
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

## [root] Package Update
apt-get update

## [root] PostgreSQL Package 다운로드 (--download-only 옵션)
## /var/cache/apt/archives 디렉토리에 의존성과 관련된 Package가 같이 다운로드 됩니다.
apt install --download-only -y postgresql-14
더보기
root@ubuntu:~# sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

root@ubuntu:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)).
OK

root@ubuntu:~# apt-get update
Get:1 http://apt.postgresql.org/pub/repos/apt jammy-pgdg InRelease [91.7 kB]
Hit:2 http://kr.archive.ubuntu.com/ubuntu jammy InRelease
Get:3 http://kr.archive.ubuntu.com/ubuntu jammy-updates InRelease [114 kB]
Get:4 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 Packages [240 kB]
Get:5 http://kr.archive.ubuntu.com/ubuntu jammy-backports InRelease [99.8 kB]
Get:6 http://kr.archive.ubuntu.com/ubuntu jammy-security InRelease [110 kB]
Get:7 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main amd64 Packages [511 kB]
Get:8 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main Translation-en [122 kB]
Get:9 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main amd64 c-n-f Metadata [7,444 B]
Get:10 http://kr.archive.ubuntu.com/ubuntu jammy-updates/restricted amd64 Packages [276 kB]
Get:11 http://kr.archive.ubuntu.com/ubuntu jammy-updates/restricted Translation-en [42.5 kB]
Get:12 http://kr.archive.ubuntu.com/ubuntu jammy-updates/restricted amd64 c-n-f Metadata [524 B]
Get:13 http://kr.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 Packages [214 kB]
Get:14 http://kr.archive.ubuntu.com/ubuntu jammy-updates/universe Translation-en [71.5 kB]
Get:15 http://kr.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 c-n-f Metadata [4,404 B]
Get:16 http://kr.archive.ubuntu.com/ubuntu jammy-updates/multiverse amd64 Packages [7,000 B]
Get:17 http://kr.archive.ubuntu.com/ubuntu jammy-updates/multiverse Translation-en [2,264 B]
Get:18 http://kr.archive.ubuntu.com/ubuntu jammy-updates/multiverse amd64 c-n-f Metadata [420 B]
Get:19 http://kr.archive.ubuntu.com/ubuntu jammy-backports/universe amd64 Packages [6,392 B]
Get:20 http://kr.archive.ubuntu.com/ubuntu jammy-backports/universe Translation-en [9,104 B]
Get:21 http://kr.archive.ubuntu.com/ubuntu jammy-backports/universe amd64 c-n-f Metadata [328 B]
Get:22 http://kr.archive.ubuntu.com/ubuntu jammy-security/main amd64 Packages [267 kB]
Get:23 http://kr.archive.ubuntu.com/ubuntu jammy-security/main Translation-en [63.9 kB]
Get:24 http://kr.archive.ubuntu.com/ubuntu jammy-security/main amd64 c-n-f Metadata [3,924 B]
Get:25 http://kr.archive.ubuntu.com/ubuntu jammy-security/restricted amd64 Packages [243 kB]
Get:26 http://kr.archive.ubuntu.com/ubuntu jammy-security/restricted Translation-en [37.3 kB]
Get:27 http://kr.archive.ubuntu.com/ubuntu jammy-security/restricted amd64 c-n-f Metadata [524 B]
Get:28 http://kr.archive.ubuntu.com/ubuntu jammy-security/universe amd64 Packages [108 kB]
Get:29 http://kr.archive.ubuntu.com/ubuntu jammy-security/universe Translation-en [37.5 kB]
Get:30 http://kr.archive.ubuntu.com/ubuntu jammy-security/universe amd64 c-n-f Metadata [2,408 B]
Get:31 http://kr.archive.ubuntu.com/ubuntu jammy-security/multiverse amd64 Packages [4,192 B]
Get:32 http://kr.archive.ubuntu.com/ubuntu jammy-security/multiverse amd64 c-n-f Metadata [228 B]
Fetched 2,698 kB in 4s (733 kB/s)
Reading package lists... Done
W: http://apt.postgresql.org/pub/repos/apt/dists/jammy-pgdg/InRelease: Key is stored in legacy trusted.gpg keyring (/etc/apt/trusted.gpg), see the DEPRECATION section in apt-key(8) for details.

root@ubuntu:~# apt install --download-only -y postgresql-14
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5 libsensors-config libsensors5 libtypes-serialiser-perl
  pgdg-keyring postgresql-client-14 postgresql-client-common postgresql-common ssl-cert sysstat
Suggested packages:
  lm-sensors postgresql-doc-14 isag
The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5 libsensors-config libsensors5 libtypes-serialiser-perl
  pgdg-keyring postgresql-14 postgresql-client-14 postgresql-client-common postgresql-common ssl-cert sysstat
0 upgraded, 15 newly installed, 0 to remove and 86 not upgraded.
Need to get 43.3 MB of archives.
After this operation, 172 MB of additional disk space will be used.
Get:1 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libcommon-sense-perl amd64 3.75-2build1 [21.1 kB]
Get:2 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 libpq5 amd64 14.5-1.pgdg22.04+1 [172 kB]
Get:3 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libjson-perl all 4.04000-1 [81.8 kB]
Get:4 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libtypes-serialiser-perl all 1.01-1 [11.6 kB]
Get:5 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libjson-xs-perl amd64 4.030-1build3 [87.2 kB]
Get:6 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 pgdg-keyring all 2018.2 [10.7 kB]
Get:7 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-client-common all 242.pgdg22.04+1 [92.2 kB]
Get:8 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-client-14 amd64 14.5-1.pgdg22.04+1 [1,615 kB]
Get:9 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libllvm14 amd64 1:14.0.0-1ubuntu1 [24.0 MB]
Get:10 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-common all 242.pgdg22.04+1 [230 kB]
Get:11 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-14 amd64 14.5-1.pgdg22.04+1 [16.4 MB]
Get:12 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libsensors-config all 1:3.6.0-7ubuntu1 [5,274 B]
Get:13 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 libsensors5 amd64 1:3.6.0-7ubuntu1 [26.3 kB]
Get:14 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 ssl-cert all 1.1.2 [17.4 kB]
Get:15 http://kr.archive.ubuntu.com/ubuntu jammy/main amd64 sysstat amd64 12.5.2-2build2 [487 kB]
Fetched 43.3 MB in 8s (5,594 kB/s)
Download complete and in download only mode
Package 명 설명
pgdg-keyring apt.postgresql.org Keyring
postgresql-client-common PostgreSQL Clinet 버전 관리자
postgresql-client-14 PostgreSQL Client 프로그램 및 관리 프로그램 포함
postgresql-common PostgreSQL Database Cluster Manager
postgresql-14 PostgreSQL Server 생성 및 실행에 필요한 프로그램

 

2. PostgreSQL Install

Package 간의 의존성(Dependency)에 따라 아래 나열된 순서로 Package를 설치합니다.

## [root] PostgreSQL 설치에 필요한 Package 설치
dpkg -i pgdg-keyring_2018.2_all.deb
dpkg -i postgresql-client-common_242.pgdg22.04+1_all.deb
dpkg -i libpq5_14.5-1.pgdg22.04+1_amd64.deb
dpkg -i postgresql-client-14_14.5-1.pgdg22.04+1_amd64.deb
dpkg -i libjson-perl_4.04000-1_all.deb
dpkg -i ssl-cert_1.1.2_all.deb
dpkg -i postgresql-common_242.pgdg22.04+1_all.deb
더보기
root@ubuntu:~# dpkg -i pgdg-keyring_2018.2_all.deb
Selecting previously unselected package pgdg-keyring.
(Reading database ... 73275 files and directories currently installed.)
Preparing to unpack pgdg-keyring_2018.2_all.deb ...
Unpacking pgdg-keyring (2018.2) ...
Setting up pgdg-keyring (2018.2) ...
Removing apt.postgresql.org key from trusted.gpg: OK

root@ubuntu:~# dpkg -i postgresql-client-common_242.pgdg22.04+1_all.deb
Selecting previously unselected package postgresql-client-common.
(Reading database ... 73282 files and directories currently installed.)
Preparing to unpack postgresql-client-common_242.pgdg22.04+1_all.deb ...
Unpacking postgresql-client-common (242.pgdg22.04+1) ...
Setting up postgresql-client-common (242.pgdg22.04+1) ...
Processing triggers for man-db (2.10.2-1) ...

root@ubuntu:~# dpkg -i libpq5_14.5-1.pgdg22.04+1_amd64.deb
Selecting previously unselected package libpq5:amd64.
(Reading database ... 73318 files and directories currently installed.)
Preparing to unpack libpq5_14.5-1.pgdg22.04+1_amd64.deb ...
Unpacking libpq5:amd64 (14.5-1.pgdg22.04+1) ...
Setting up libpq5:amd64 (14.5-1.pgdg22.04+1) ...
Processing triggers for libc-bin (2.35-0ubuntu3) ...

root@ubuntu:~# dpkg -i postgresql-client-14_14.5-1.pgdg22.04+1_amd64.deb
Selecting previously unselected package postgresql-client-14.
(Reading database ... 73335 files and directories currently installed.)
Preparing to unpack postgresql-client-14_14.5-1.pgdg22.04+1_amd64.deb ...
Unpacking postgresql-client-14 (14.5-1.pgdg22.04+1) ...
Setting up postgresql-client-14 (14.5-1.pgdg22.04+1) ...
update-alternatives: using /usr/share/postgresql/14/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode

root@ubuntu:~# dpkg -i libjson-perl_4.04000-1_all.deb
Selecting previously unselected package libjson-perl.
(Reading database ... 73680 files and directories currently installed.)
Preparing to unpack libjson-perl_4.04000-1_all.deb ...
Unpacking libjson-perl (4.04000-1) ...
Setting up libjson-perl (4.04000-1) ...
Processing triggers for man-db (2.10.2-1) ...

root@ubuntu:~# dpkg -i ssl-cert_1.1.2_all.deb
Selecting previously unselected package ssl-cert.
(Reading database ... 73699 files and directories currently installed.)
Preparing to unpack ssl-cert_1.1.2_all.deb ...
Unpacking ssl-cert (1.1.2) ...
Setting up ssl-cert (1.1.2) ...
Processing triggers for man-db (2.10.2-1) ...

root@ubuntu:~# dpkg -i postgresql-common_242.pgdg22.04+1_all.deb
Selecting previously unselected package postgresql-common.
(Reading database ... 73708 files and directories currently installed.)
Preparing to unpack postgresql-common_242.pgdg22.04+1_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (242.pgdg22.04+1) ...
Setting up postgresql-common (242.pgdg22.04+1) ...
Adding user postgres to group ssl-cert

Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
Processing triggers for man-db (2.10.2-1) ...

 

3. PostgreSQL Initialization

기본 Data Directory 사용

Ubuntu의 경우 PostgreSQL Install 시 initdb와 서비스 등록이 같이 진행됩니다. postgresql-14 Package를 설치해야 initdb 응용프로그램이 생성되어 PostgreSQL Database Initialization을 할 수 있습니다. postgresql-14 Package는 libllvm14 Library를 필요로 하기 때문에, 먼저 libllvm14 Library Package를 설치합니다.

## [root] PostgreSQL Database Install
dpkg -i libllvm14_1%3a14.0.0-1ubuntu1_amd64.deb
dpkg -i postgresql-14_14.5-1.pgdg22.04+1_amd64.deb
더보기
root@ubuntu:~# dpkg -i libllvm14_1%3a14.0.0-1ubuntu1_amd64.deb
Selecting previously unselected package libllvm14:amd64.
(Reading database ... 73836 files and directories currently installed.)
Preparing to unpack libllvm14_1%3a14.0.0-1ubuntu1_amd64.deb ...
Unpacking libllvm14:amd64 (1:14.0.0-1ubuntu1) ...
Setting up libllvm14:amd64 (1:14.0.0-1ubuntu1) ...
Processing triggers for libc-bin (2.35-0ubuntu3) ...

root@ubuntu:~# dpkg -i postgresql-14_14.5-1.pgdg22.04+1_amd64.deb
Selecting previously unselected package postgresql-14.
(Reading database ... 73843 files and directories currently installed.)
Preparing to unpack postgresql-14_14.5-1.pgdg22.04+1_amd64.deb ...
Unpacking postgresql-14 (14.5-1.pgdg22.04+1) ...
Setting up postgresql-14 (14.5-1.pgdg22.04+1) ...
Creating new PostgreSQL cluster 14/main ...
/usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/main --auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/14/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
update-alternatives: using /usr/share/postgresql/14/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Processing triggers for postgresql-common (242.pgdg22.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:

위와 같이 PostgreSQL Configuration 기본 설정으로 postgresql-14 Package를 설치하면, 기본 Data Directory를 사용합니다. 기본 Data Directory는 /var/lib/postgresql/14/main입니다.

 

사용자 지정 Data Directory 사용

아래 예시와 같이 PostgreSQL Configuration 설정을 변경한 후 postgresql-14 Package를 설치하면, Data Directory를 사용자가 지정하여 Database Initialization을 할 수 있습니다.

## [root] Data Directory 지정
vi /etc/postgresql-common/createcluster.conf
... 생략 ...
# Default data directory.
data_directory = '/PostgreSQL_Data'
... 생략 ...

## [root] PostgreSQL Database Install
dpkg -i libllvm14_1%3a14.0.0-1ubuntu1_amd64.deb
dpkg -i postgresql-14_14.5-1.pgdg22.04+1_amd64.deb
## postgresql-14 package가 설치 되면서 앞에 설정했던 PostgreSQL_Data 디렉토리가 생성 됨
더보기
root@ubuntu:~# vi /etc/postgresql-common/createcluster.conf
... 생략 ...
# Default data directory.
#data_directory = '/var/lib/postgresql/%v/%c'
data_directory = '/PostgreSQL_Data'
... 생략 ...

root@ubuntu:~# mkdir /PostgreSQL_Data
root@ubuntu:~# chown postgres:postgres /PostgreSQL_Data

root@ubuntu:~# dpkg -i libllvm14_1%3a14.0.0-1ubuntu1_amd64.deb
Selecting previously unselected package libllvm14:amd64.
(Reading database ... 73836 files and directories currently installed.)
Preparing to unpack libllvm14_1%3a14.0.0-1ubuntu1_amd64.deb ...
Unpacking libllvm14:amd64 (1:14.0.0-1ubuntu1) ...
Setting up libllvm14:amd64 (1:14.0.0-1ubuntu1) ...
Processing triggers for libc-bin (2.35-0ubuntu3) ...

root@ubuntu:~# dpkg -i postgresql-14_14.5-1.pgdg22.04+1_amd64.deb
Selecting previously unselected package postgresql-14.
(Reading database ... 73843 files and directories currently installed.)
Preparing to unpack postgresql-14_14.5-1.pgdg22.04+1_amd64.deb ...
Unpacking postgresql-14 (14.5-1.pgdg22.04+1) ...
Setting up postgresql-14 (14.5-1.pgdg22.04+1) ...
Creating new PostgreSQL cluster 14/main ...
/usr/lib/postgresql/14/bin/initdb -D /PostgreSQL_Data --auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /PostgreSQL_Data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
update-alternatives: using /usr/share/postgresql/14/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Processing triggers for postgresql-common (242.pgdg22.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:

 

4. PostgreSQL 접속 및 확인

## [postgres]
su - postgres
psql -c "SELECT VERSION()"
더보기
root@ubuntu:~# su - postgres
postgres@ubuntu:~$ psql -c "SELECT VERSION()"
                                                           version
------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 (Ubuntu 14.5-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
(1 row)

 

Install PostgreSQL on Linux (Source File) 목차

Source File을 통해 설치 파일을 Build 하여 PostgreSQL 설치를 할 수 있습니다. gcc, make, readline(생략가능), zlib(생략가능) 등 Build를 위해 필요한 OS Package가 우선 설치되어 있어야 합니다.

 

1. PostgreSQL Source File Download

PostgreSQL 다운로드센터(www.postgresql.org/ftp/source/)에서 PostgreSQL 버전별로 Source File을 다운로드할 수 있습니다. 테스트에 사용된 Source File은 postgresql-14.5.tar.gz입니다.

 

2. PostgreSQL OS계정 생성

## [root] postgres group 및 user 생성
groupadd postgres
useradd -d /home/postgres -g postgres -m postgres

 

3. PostgreSQL Engine Directory 생성

## [postgres] PostgreSQL Engine Directory 생성
su - postgres
mkdir /home/postgres/PostgreSQL_Engine

 

4. PostgreSQL 설치 파일 압축해제

## [postgres] PostgreSQL 설치파일 압축해제
tar -zxvf postgresql-14.5.tar.gz

 

5. PostgreSQL Install Configure 설정

PostgreSQL Install Configure 설정은 PostgreSQL Server 설치 시 필요한 내용을 Build 하는 부분으로 이 단계에서 필요한 기능을 포함하여 Build 할 수 있습니다. 예를 들어 JIT 기능을 포함하고자 할 경우 --with-llvm 옵션을 추가하고, LZ4압축 기능을 포함하고자 할 경우 --with-lz4 옵션을 추가하여 Install File을 Build 할 수 있습니다. configure의 옵션은 configure --help로 확인할 수 있습니다.

## [postgres] PostgreSQL Engine Directory에서 PostgreSQL Install Configure 설정
cd /home/postgres/PostgreSQL_Engine
/home/postgres/postgresql-14.5/configure --prefix=/home/postgres/PostgreSQL_Engine --without-readline --without-zlib

## --prefix 옵션 : PostgreSQL Engine 설치 경로(기본값:/usr/local/pgsql)
## Configure 파일 설정관련 로그는 --prefix지정경로/config.log 에서 확인가능
더보기
[postgres@centos PostgreSQL_Engine]$ /home/postgres/postgresql-14.5/configure --prefix=/home/postgres/PostgreSQL_Engine --without-readline --without-zlib
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking which template to use... linux
checking whether NLS is wanted... no
checking for default port number... 5432
checking for block size... 8kB
checking for segment size... 1GB
checking for WAL block size... 8kB
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for gcc option to accept ISO C99... -std=gnu99
checking for g++... g++
checking whether we are using the GNU C++ compiler... yes
checking whether g++ accepts -g... yes
checking for gawk... gawk
checking whether gcc -std=gnu99 supports -Wdeclaration-after-statement, for CFLAGS... yes
checking whether gcc -std=gnu99 supports -Werror=vla, for CFLAGS... yes
checking whether gcc -std=gnu99 supports -Werror=unguarded-availability-new, for CFLAGS... no
checking whether g++ supports -Werror=unguarded-availability-new, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -Wendif-labels, for CFLAGS... yes
checking whether g++ supports -Wendif-labels, for CXXFLAGS... yes
checking whether gcc -std=gnu99 supports -Wmissing-format-attribute, for CFLAGS... yes
checking whether g++ supports -Wmissing-format-attribute, for CXXFLAGS... yes
checking whether gcc -std=gnu99 supports -Wimplicit-fallthrough=3, for CFLAGS... no
checking whether g++ supports -Wimplicit-fallthrough=3, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -Wcast-function-type, for CFLAGS... no
checking whether g++ supports -Wcast-function-type, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -Wformat-security, for CFLAGS... yes
checking whether g++ supports -Wformat-security, for CXXFLAGS... yes
checking whether gcc -std=gnu99 supports -fno-strict-aliasing, for CFLAGS... yes
checking whether g++ supports -fno-strict-aliasing, for CXXFLAGS... yes
checking whether gcc -std=gnu99 supports -fwrapv, for CFLAGS... yes
checking whether g++ supports -fwrapv, for CXXFLAGS... yes
checking whether gcc -std=gnu99 supports -fexcess-precision=standard, for CFLAGS... yes
checking whether g++ supports -fexcess-precision=standard, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -funroll-loops, for CFLAGS_UNROLL_LOOPS... yes
checking whether gcc -std=gnu99 supports -ftree-vectorize, for CFLAGS_VECTORIZE... yes
checking whether gcc -std=gnu99 supports -Wunused-command-line-argument, for NOT_THE_CFLAGS... no
checking whether gcc -std=gnu99 supports -Wcompound-token-split-by-macro, for NOT_THE_CFLAGS... no
checking whether gcc -std=gnu99 supports -Wformat-truncation, for NOT_THE_CFLAGS... no
checking whether gcc -std=gnu99 supports -Wstringop-truncation, for NOT_THE_CFLAGS... no
checking whether the C compiler still works... yes
checking how to run the C preprocessor... gcc -std=gnu99 -E
checking for pkg-config... /bin/pkg-config
checking pkg-config is at least version 0.9.0... yes
checking allow thread-safe client libraries... yes
checking whether to build with ICU support... no
checking whether to build with Tcl... no
checking whether to build Perl modules... no
checking whether to build Python modules... no
checking whether to build with GSSAPI support... no
checking whether to build with PAM support... no
checking whether to build with BSD Authentication support... no
checking whether to build with LDAP support... no
checking whether to build with Bonjour support... no
checking whether to build with SELinux support... no
checking whether to build with systemd support... no
checking whether to build with XML support... no
checking whether to build with LZ4 support... no
checking for ld used by GCC... /bin/ld
checking if the linker (/bin/ld) is GNU ld... yes
checking for ranlib... ranlib
checking for strip... strip
checking whether it is possible to strip libraries... yes
checking for ar... ar
checking for a BSD-compatible install... /bin/install -c
checking for tar... /bin/tar
checking whether ln -s works... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for bison... /bin/bison
configure: using bison (GNU Bison) 3.0.4
checking for flex... /bin/flex
configure: using flex 2.5.37
checking for perl... /bin/perl
configure: using perl 5.16.3
checking for a sed that does not truncate output... /bin/sed
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking whether gcc -std=gnu99 is Clang... no
checking whether pthreads work with -pthread... yes
checking for joinable pthread attribute... PTHREAD_CREATE_JOINABLE
checking whether more special flags are required for pthreads... no
checking for PTHREAD_PRIO_INHERIT... yes
checking pthread.h usability... yes
checking pthread.h presence... yes
checking for pthread.h... yes
checking for strerror_r... yes
checking for getpwuid_r... yes
checking for gethostbyname_r... yes
checking whether strerror_r returns int... no
checking for main in -lm... yes
checking for library containing setproctitle... no
checking for library containing dlsym... -ldl
checking for library containing socket... none required
checking for library containing shl_load... no
checking for library containing getopt_long... none required
checking for library containing shm_open... -lrt
checking for library containing shm_unlink... none required
checking for library containing clock_gettime... none required
checking for library containing fdatasync... none required
checking for library containing shmget... none required
checking for library containing backtrace_symbols... none required
checking for library containing gethostbyname_r... none required
checking for library containing pthread_barrier_wait... -lpthread
checking for stdbool.h that conforms to C99... yes
checking for _Bool... yes
checking atomic.h usability... no
checking atomic.h presence... no
checking for atomic.h... no
checking copyfile.h usability... no
checking copyfile.h presence... no
checking for copyfile.h... no
checking execinfo.h usability... yes
checking execinfo.h presence... yes
checking for execinfo.h... yes
checking getopt.h usability... yes
checking getopt.h presence... yes
checking for getopt.h... yes
checking ifaddrs.h usability... yes
checking ifaddrs.h presence... yes
checking for ifaddrs.h... yes
checking langinfo.h usability... yes
checking langinfo.h presence... yes
checking for langinfo.h... yes
checking mbarrier.h usability... no
checking mbarrier.h presence... no
checking for mbarrier.h... no
checking poll.h usability... yes
checking poll.h presence... yes
checking for poll.h... yes
checking sys/epoll.h usability... yes
checking sys/epoll.h presence... yes
checking for sys/epoll.h... yes
checking sys/event.h usability... no
checking sys/event.h presence... no
checking for sys/event.h... no
checking sys/ipc.h usability... yes
checking sys/ipc.h presence... yes
checking for sys/ipc.h... yes
checking sys/prctl.h usability... yes
checking sys/prctl.h presence... yes
checking for sys/prctl.h... yes
checking sys/procctl.h usability... no
checking sys/procctl.h presence... no
checking for sys/procctl.h... no
checking sys/pstat.h usability... no
checking sys/pstat.h presence... no
checking for sys/pstat.h... no
checking sys/resource.h usability... yes
checking sys/resource.h presence... yes
checking for sys/resource.h... yes
checking sys/select.h usability... yes
checking sys/select.h presence... yes
checking for sys/select.h... yes
checking sys/sem.h usability... yes
checking sys/sem.h presence... yes
checking for sys/sem.h... yes
checking sys/shm.h usability... yes
checking sys/shm.h presence... yes
checking for sys/shm.h... yes
checking sys/signalfd.h usability... yes
checking sys/signalfd.h presence... yes
checking for sys/signalfd.h... yes
checking sys/sockio.h usability... no
checking sys/sockio.h presence... no
checking for sys/sockio.h... no
checking sys/tas.h usability... no
checking sys/tas.h presence... no
checking for sys/tas.h... no
checking sys/uio.h usability... yes
checking sys/uio.h presence... yes
checking for sys/uio.h... yes
checking sys/un.h usability... yes
checking sys/un.h presence... yes
checking for sys/un.h... yes
checking termios.h usability... yes
checking termios.h presence... yes
checking for termios.h... yes
checking ucred.h usability... no
checking ucred.h presence... no
checking for ucred.h... no
checking wctype.h usability... yes
checking wctype.h presence... yes
checking for wctype.h... yes
checking for net/if.h... yes
checking for sys/ucred.h... no
checking for netinet/tcp.h... yes
checking whether byte ordering is bigendian... no
checking for inline... inline
checking for printf format archetype... gnu_printf
checking for __func__... yes
checking for _Static_assert... yes
checking for typeof... typeof
checking for __builtin_types_compatible_p... yes
checking for __builtin_constant_p... yes
checking for __builtin_unreachable... yes
checking for computed goto support... yes
checking for struct tm.tm_zone... yes
checking for union semun... no
checking for struct sockaddr_un... yes
checking for struct sockaddr_storage... yes
checking for struct sockaddr_storage.ss_family... yes
checking for struct sockaddr_storage.__ss_family... no
checking for struct sockaddr_storage.ss_len... no
checking for struct sockaddr_storage.__ss_len... no
checking for struct sockaddr.sa_len... no
checking for struct addrinfo... yes
checking for locale_t... yes
checking for C/C++ restrict keyword... __restrict
checking for struct cmsgcred... no
checking for struct option... yes
checking whether assembler supports x86_64 popcntq... yes
checking for special C compiler options needed for large files... no
checking for _FILE_OFFSET_BITS value needed for large files... no
checking size of off_t... 8
checking size of bool... 1
checking for int timezone... yes
checking types of arguments for accept()... int, int, struct sockaddr *, socklen_t *
checking whether gettimeofday takes only one argument... no
checking for wcstombs_l declaration... no
checking for backtrace_symbols... yes
checking for clock_gettime... yes
checking for copyfile... no
checking for fdatasync... yes
checking for getifaddrs... yes
checking for getpeerucred... no
checking for getrlimit... yes
checking for kqueue... no
checking for mbstowcs_l... no
checking for memset_s... no
checking for poll... yes
checking for posix_fallocate... yes
checking for ppoll... yes
checking for pstat... no
checking for pthread_is_threaded_np... no
checking for readlink... yes
checking for readv... yes
checking for setproctitle... no
checking for setproctitle_fast... no
checking for setsid... yes
checking for shm_open... yes
checking for strchrnul... yes
checking for strsignal... yes
checking for symlink... yes
checking for syncfs... yes
checking for sync_file_range... yes
checking for uselocale... yes
checking for wcstombs_l... no
checking for writev... yes
checking for __builtin_bswap16... yes
checking for __builtin_bswap32... yes
checking for __builtin_bswap64... yes
checking for __builtin_clz... yes
checking for __builtin_ctz... yes
checking for __builtin_popcount... yes
checking for __builtin_frame_address... yes
checking for _LARGEFILE_SOURCE value needed for large files... no
checking how gcc -std=gnu99 reports undeclared, standard C functions... error
checking for posix_fadvise... yes
checking whether posix_fadvise is declared... yes
checking whether fdatasync is declared... yes
checking whether strlcat is declared... no
checking whether strlcpy is declared... no
checking whether strnlen is declared... yes
checking whether preadv is declared... yes
checking whether pwritev is declared... yes
checking whether F_FULLFSYNC is declared... no
checking whether RTLD_GLOBAL is declared... yes
checking whether RTLD_NOW is declared... yes
checking for struct sockaddr_in6... yes
checking for PS_STRINGS... no
checking for dlopen... yes
checking for explicit_bzero... no
checking for fls... no
checking for getopt... yes
checking for getpeereid... no
checking for getrusage... yes
checking for inet_aton... yes
checking for link... yes
checking for mkdtemp... yes
checking for pread... yes
checking for pwrite... yes
checking for random... yes
checking for srandom... yes
checking for strlcat... no
checking for strlcpy... no
checking for strnlen... yes
checking for strtof... yes
checking for pthread_barrier_wait... yes
checking for setenv... yes
checking for unsetenv... yes
checking for getaddrinfo... yes
checking for getopt_long... yes
checking for syslog... yes
checking syslog.h usability... yes
checking syslog.h presence... yes
checking for syslog.h... yes
checking for opterr... yes
checking for optreset... no
checking for strtoll... yes
checking for strtoull... yes
checking whether strtoll is declared... yes
checking whether strtoull is declared... yes
checking test program... ok
checking whether long int is 64 bits... yes
checking for __builtin_mul_overflow... no
checking size of void *... 8
checking size of size_t... 8
checking size of long... 8
checking alignment of short... 2
checking alignment of int... 4
checking alignment of long... 8
checking alignment of double... 8
checking for int8... no
checking for uint8... no
checking for int64... no
checking for uint64... no
checking for __int128... yes
checking for __int128 alignment bug... ok
checking alignment of PG_INT128_TYPE... 16
checking for builtin __sync char locking functions... yes
checking for builtin __sync int32 locking functions... yes
checking for builtin __sync int32 atomic operations... yes
checking for builtin __sync int64 atomic operations... yes
checking for builtin __atomic int32 atomic operations... yes
checking for builtin __atomic int64 atomic operations... yes
checking for __get_cpuid... yes
checking for __cpuid... no
checking for _mm_crc32_u8 and _mm_crc32_u32 with CFLAGS=... no
checking for _mm_crc32_u8 and _mm_crc32_u32 with CFLAGS=-msse4.2... yes
checking for __crc32cb, __crc32ch, __crc32cw, and __crc32cd with CFLAGS=... no
checking for __crc32cb, __crc32ch, __crc32cw, and __crc32cd with CFLAGS=-march=armv8-a+crc... no
checking which CRC-32C implementation to use... SSE 4.2 with runtime check
checking for library containing sem_init... none required
checking which semaphore API to use... unnamed POSIX
checking which random number source to use... /dev/urandom
checking for /dev/urandom... yes
checking for xmllint... /bin/xmllint
checking for xsltproc... /bin/xsltproc
checking for fop... no
checking for dbtoepub... no
checking whether gcc -std=gnu99 supports -Wl,--as-needed... yes
configure: using compiler=gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44)
configure: using CFLAGS=-Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
configure: using CPPFLAGS= -D_GNU_SOURCE
configure: using LDFLAGS=  -Wl,--as-needed
preparing build tree... done
configure: creating ./config.status
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
config.status: creating src/include/pg_config_ext.h
config.status: creating src/interfaces/ecpg/include/ecpg_config.h
config.status: linking /home/postgres/postgresql-14.5/src/backend/port/tas/dummy.s to src/backend/port/tas.s
config.status: linking /home/postgres/postgresql-14.5/src/backend/port/posix_sema.c to src/backend/port/pg_sema.c
config.status: linking /home/postgres/postgresql-14.5/src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking /home/postgres/postgresql-14.5/src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking /home/postgres/postgresql-14.5/src/makefiles/Makefile.linux to src/Makefile.port
더보기
## gcc Library 부재
configure: error: no acceptable C compiler found in $PATH
## libc6-dev Library 부재
configure: error: C compiler cannot create executables
## readline Library 부재(Skip 시 --without-readline 옵션 지정)
configure: error: readline library not found
## zlib Library 부재(Skip 시 --without-zlib 옵션 지정)
configure: error: zlib library not found
더보기
[postgres@centos postgresql-14.5]$ pwd
/home/postgres/postgresql-14.5

[postgres@centos postgresql-14.5]$ ./configure --help
`configure' configures PostgreSQL 14.5 to adapt to many kinds of systems.

Usage: ./configure [OPTION]... [VAR=VALUE]...

To assign environment variables (e.g., CC, CFLAGS...), specify them as
VAR=VALUE.  See below for descriptions of some of the useful variables.

Defaults for the options are specified in brackets.

Configuration:
  -h, --help              display this help and exit
      --help=short        display options specific to this package
      --help=recursive    display the short help of all the included packages
  -V, --version           display version information and exit
  -q, --quiet, --silent   do not print `checking ...' messages
      --cache-file=FILE   cache test results in FILE [disabled]
  -C, --config-cache      alias for `--cache-file=config.cache'
  -n, --no-create         do not create output files
      --srcdir=DIR        find the sources in DIR [configure dir or `..']

Installation directories:
  --prefix=PREFIX         install architecture-independent files in PREFIX
                          [/usr/local/pgsql]
  --exec-prefix=EPREFIX   install architecture-dependent files in EPREFIX
                          [PREFIX]

By default, `make install' will install all the files in
`/usr/local/pgsql/bin', `/usr/local/pgsql/lib' etc.  You can specify
an installation prefix other than `/usr/local/pgsql' using `--prefix',
for instance `--prefix=$HOME'.

For better control, use the options below.

Fine tuning of the installation directories:
  --bindir=DIR            user executables [EPREFIX/bin]
  --sbindir=DIR           system admin executables [EPREFIX/sbin]
  --libexecdir=DIR        program executables [EPREFIX/libexec]
  --sysconfdir=DIR        read-only single-machine data [PREFIX/etc]
  --sharedstatedir=DIR    modifiable architecture-independent data [PREFIX/com]
  --localstatedir=DIR     modifiable single-machine data [PREFIX/var]
  --libdir=DIR            object code libraries [EPREFIX/lib]
  --includedir=DIR        C header files [PREFIX/include]
  --oldincludedir=DIR     C header files for non-gcc [/usr/include]
  --datarootdir=DIR       read-only arch.-independent data root [PREFIX/share]
  --datadir=DIR           read-only architecture-independent data [DATAROOTDIR]
  --infodir=DIR           info documentation [DATAROOTDIR/info]
  --localedir=DIR         locale-dependent data [DATAROOTDIR/locale]
  --mandir=DIR            man documentation [DATAROOTDIR/man]
  --docdir=DIR            documentation root [DATAROOTDIR/doc/postgresql]
  --htmldir=DIR           html documentation [DOCDIR]
  --dvidir=DIR            dvi documentation [DOCDIR]
  --pdfdir=DIR            pdf documentation [DOCDIR]
  --psdir=DIR             ps documentation [DOCDIR]

System types:
  --build=BUILD     configure for building on BUILD [guessed]
  --host=HOST       cross-compile to build programs to run on HOST [BUILD]

Optional Features:
  --disable-option-checking  ignore unrecognized --enable/--with options
  --disable-FEATURE       do not include FEATURE (same as --enable-FEATURE=no)
  --enable-FEATURE[=ARG]  include FEATURE [ARG=yes]
  --disable-integer-datetimes
                          obsolete option, no longer supported
  --enable-nls[=LANGUAGES]
                          enable Native Language Support
  --disable-rpath         do not embed shared library search path in
                          executables
  --disable-spinlocks     do not use spinlocks
  --disable-atomics       do not use atomic operations
  --enable-debug          build with debugging symbols (-g)
  --enable-profiling      build with profiling enabled
  --enable-coverage       build with coverage testing instrumentation
  --enable-dtrace         build with DTrace support
  --enable-tap-tests      enable TAP tests (requires Perl and IPC::Run)
  --enable-depend         turn on automatic dependency tracking
  --enable-cassert        enable assertion checks (for debugging)
  --disable-thread-safety disable thread-safety in client libraries
  --disable-largefile     omit support for large files

Optional Packages:
  --with-PACKAGE[=ARG]    use PACKAGE [ARG=yes]
  --without-PACKAGE       do not use PACKAGE (same as --with-PACKAGE=no)
  --with-extra-version=STRING
                          append STRING to version
  --with-template=NAME    override operating system template
  --with-includes=DIRS    look for additional header files in DIRS
  --with-libraries=DIRS   look for additional libraries in DIRS
  --with-libs=DIRS        alternative spelling of --with-libraries
  --with-pgport=PORTNUM   set default port number [5432]
  --with-blocksize=BLOCKSIZE
                          set table block size in kB [8]
  --with-segsize=SEGSIZE  set table segment size in GB [1]
  --with-wal-blocksize=BLOCKSIZE
                          set WAL block size in kB [8]
  --with-CC=CMD           set compiler (deprecated)
  --with-llvm             build with LLVM based JIT support
  --with-icu              build with ICU support
  --with-tcl              build Tcl modules (PL/Tcl)
  --with-tclconfig=DIR    tclConfig.sh is in DIR
  --with-perl             build Perl modules (PL/Perl)
  --with-python           build Python modules (PL/Python)
  --with-gssapi           build with GSSAPI support
  --with-krb-srvnam=NAME  default service principal name in Kerberos (GSSAPI)
                          [postgres]
  --with-pam              build with PAM support
  --with-bsd-auth         build with BSD Authentication support
  --with-ldap             build with LDAP support
  --with-bonjour          build with Bonjour support
  --with-selinux          build with SELinux support
  --with-systemd          build with systemd support
  --without-readline      do not use GNU Readline nor BSD Libedit for editing
  --with-libedit-preferred
                          prefer BSD Libedit over GNU Readline
  --with-uuid=LIB         build contrib/uuid-ossp using LIB (bsd,e2fs,ossp)
  --with-ossp-uuid        obsolete spelling of --with-uuid=ossp
  --with-libxml           build with XML support
  --with-libxslt          use XSLT support when building contrib/xml2
  --with-system-tzdata=DIR
                          use system time zone data in DIR
  --without-zlib          do not use Zlib
  --with-lz4              build with LZ4 support
  --with-gnu-ld           assume the C compiler uses GNU ld [default=no]
  --with-ssl=LIB          use LIB for SSL/TLS support (openssl)
  --with-openssl          obsolete spelling of --with-ssl=openssl

Some influential environment variables:
  CC          C compiler command
  CFLAGS      C compiler flags
  LDFLAGS     linker flags, e.g. -L if you have libraries in a
              nonstandard directory 
  LIBS        libraries to pass to the linker, e.g. -l
  CPPFLAGS    (Objective) C/C++ preprocessor flags, e.g. -I if
              you have headers in a nonstandard directory 
  CXX         C++ compiler command
  CXXFLAGS    C++ compiler flags
  LLVM_CONFIG path to llvm-config command
  CLANG       path to clang compiler to generate bitcode
  CPP         C preprocessor
  PKG_CONFIG  path to pkg-config utility
  PKG_CONFIG_PATH
              directories to add to pkg-config's search path
  PKG_CONFIG_LIBDIR
              path overriding pkg-config's built-in search path
  ICU_CFLAGS  C compiler flags for ICU, overriding pkg-config
  ICU_LIBS    linker flags for ICU, overriding pkg-config
  XML2_CONFIG path to xml2-config utility
  XML2_CFLAGS C compiler flags for XML2, overriding pkg-config
  XML2_LIBS   linker flags for XML2, overriding pkg-config
  LZ4_CFLAGS  C compiler flags for LZ4, overriding pkg-config
  LZ4_LIBS    linker flags for LZ4, overriding pkg-config
  LDFLAGS_EX  extra linker flags for linking executables only
  LDFLAGS_SL  extra linker flags for linking shared libraries only
  PERL        Perl program
  PYTHON      Python program
  MSGFMT      msgfmt program for NLS
  TCLSH       Tcl interpreter program (tclsh)

Use these variables to override the choices made by `configure' or to help
it to find libraries and programs with nonstandard names/locations.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>.

 

6. PostgreSQL Server 설치

## [postgres] Configure 설정 후 생성된 Makefile을 이용하여 PostgreSQL Server 설치
make && make install

 

7. PostgreSQL Database Initialization

## [postgres] Data Directory를 생성하고 Database Initialization
mkdir /home/postgres/PostgreSQL_Data
/home/postgres/PostgreSQL_Engine/bin/initdb -D /home/postgres/PostgreSQL_Data
더보기
[postgres@centos PostgreSQL_Engine]$ mkdir /home/postgres/PostgreSQL_Data
[postgres@centos PostgreSQL_Engine]$ /home/postgres/PostgreSQL_Engine/bin/initdb -D /home/postgres/PostgreSQL_Data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /home/postgres/PostgreSQL_Data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Seoul
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /home/postgres/PostgreSQL_Engine/bin/pg_ctl -D /home/postgres/PostgreSQL_Data -l logfile start

 

8. PostgreSQL 기동

## [postgres] 
/home/postgres/PostgreSQL_Engine/bin/pg_ctl -D /home/postgres/PostgreSQL_Data start
더보기
[postgres@centos PostgreSQL_Engine]$ /home/postgres/PostgreSQL_Engine/bin/pg_ctl -D /home/postgres/PostgreSQL_Data start
waiting for server to start....2022-08-23 16:44:18.590 KST [13696] LOG:  starting PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-08-23 16:44:18.593 KST [13696] LOG:  listening on IPv6 address "::1", port 5432
2022-08-23 16:44:18.593 KST [13696] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-08-23 16:44:18.594 KST [13696] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-08-23 16:44:18.597 KST [13697] LOG:  database system was shut down at 2022-08-23 16:44:04 KST
2022-08-23 16:44:18.598 KST [13696] LOG:  database system is ready to accept connections
 done
server started

 

9. PostgreSQL 접속 및 확인

## [postgres]
/home/postgres/PostgreSQL_Engine/bin/psql -c "SELECT VERSION()"
더보기
[postgres@centos PostgreSQL_Engine]$ /home/postgres/PostgreSQL_Engine/bin/psql -c "SELECT VERSION()"
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

 

10. PostgreSQL Service 등록

1) 서비스 등록

## [root] postgresql.service 등록
vi /etc/systemd/system/postgresql.service

## postgresql.service 내용 기입
[Unit]
Description=PostgreSQL Database Server

[Service]
Type=forking
User=postgres
Environment=PGDATA=/home/postgres/PostgreSQL_Data
ExecStart=/home/postgres/PostgreSQL_Engine/bin/pg_ctl start -D "${PGDATA}"
ExecStop=/home/postgres/PostgreSQL_Engine/bin/pg_ctl stop -D "${PGDATA}"
ExecReload=/home/postgres/PostgreSQL_Engine/bin/pg_ctl reload -D "${PGDATA}"
TimeoutSec=0

[Install]
WantedBy=multi-user.target

2) 서비스 기동 제어

## [root] PostgreSQL 서비스 기동제어
systemctl enable postgresql    ## 서비스 자동시작
systemctl start postgresql     ## 서비스 기동
systemctl stop postgresql      ## 서비스 종료

 

Install PostgreSQL on Windows (설치관리자) 목차

Windows에 PostgreSQL 설치는 설치관리자를 통해 매우 쉽게 수행할 수 있습니다. 설치 프로그램으로 PostgreSQL 서버, pgAdmin을 설치할 수 있으며, PostgreSQL 도구 및 드라이버 등을 다운로드하고 설치할 수 있는 StackBuilder도 설치할 수 있습니다. StackBuilder에는 Management, Integration, Migration, Replication, Geospatial, Connector 등의 도구가 포함되어 있습니다.

 

1. 설치 파일 준비

EDB(www.enterprisedb.com/downloads/postgres-postgresql-downloads)에서 PostgreSQL 14.5 설치 파일을 다운로드합니다.

 

2. PostgreSQL Install

설치관리자(GUI)를 통해 아래 단계로 PostgreSQL을 설치합니다.

더보기

Step 1. 다운받은 PostgreSQL 14.5 설치파일 실행

더보기

Step 2. PostgreSQL Engine 설치 경로 지정 후 Next 버튼 클릭

PostgreSQL Engine 설치경로이며, 기본값은 C:\Program Files\PostgreSQL\{VERSION} 입니다.

더보기

Step 3. 설치할 구성요소를 선택(or 선택해제) 후 Next 버튼 클릭

  - PostgreSQL Server : PostgreSQL Database Server 포함

  - pgAdmin4 : PostgreSQL GUI Management Tool 포함

  - Stack Builder : Management, integration, Migration, Replication, Geospatial 등 포함

  - Command Line Tools : psql, pg_dump, pg_restore와 같은 Command Line Interface 포함

더보기

Step 4. PostgreSQL Data Directory 지정 후 Next 버튼 클릭

Data 저장경로로, 기본값은 C:\Program Files\PostgreSQL\{VERSION}\data 입니다.

더보기

Step 5. Database Superuser인 postgres 계정의 비밀번호 지정 후 Next 버튼 클릭

더보기

Step 6. PostgreSQL Server에서 사용할 Port 지정 후 Next 버튼 클릭

더보기

Step 7. Database에서 사용하는 기본 Locale 선택 후 Next 버튼 클릭

PostgreSQL 사용 기본언어로 기본값은 Default locale이며, 이는 OS설정 값을 따릅니다.

더보기

Step 8. Step 2부터 Step 7까지 설정 한 내용 확인 후 Next 버튼 클릭

더보기

Step 9. Next 버튼 클릭 하여 설치 실행

더보기

Step 10. Stack Builder를 실행 할지 여부 선택 후 Finish 버튼 클릭

 

3. PostgreSQL 접속 및 확인

pgAdmin이나 psql과 같은 Client 프로그램을 통해 설치한 PostgreSQL의 접속 및 버전을 확인합니다.

## postgres 계정으로 psql 접속
CMD> "C:\Program Files\PostgreSQL\14\bin\psql.exe" -U postgres -c "SELECT VERSION()"
                          version
------------------------------------------------------------
 PostgreSQL 14.5, compiled by Visual C++ build 1914, 64-bit

 

Install PostgreSQL on Windows (Binary File) 목차

설치관리자를 통하지 않는 Binary File(Zip Archive)에 의한 PostgreSQL 설치는 Linux Platform의 Source File과 비슷하지만, 설치 파일을 위한 Build는 필요 없습니다. 단순히 Binary File을 압축 해제하여, Initialzation만 수행하는 것으로 PostgreSQL을 사용할 수 있습니다.

 

1. 설치 파일 준비

EDB(https://www.enterprisedb.com/download-postgresql-binaries)에서 PostgreSQL 14.4 Binary File을 다운로드합니다.

 

2. PostgreSQL Install

아래 단계 순으로 PostgreSQL을 설치합니다.

더보기

Step 1. 다운받은 Binary File 압축 해제

더보기

Step 2. 압축 해제 된 폴더 하위에 있는 pgsql 폴더를 원하는 위치로 이동 및 폴더명 변경

더보기

Step 3. PostgreSQL Data Directory로 사용할 폴더 생성

더보기

Step 4. PostgreSQL Cluster 구성을 위한 초기화(initdb)

## -D Option : PostgreSQL Data Directory
## -U Option : Database Superuser 이름
## -W Option : 새로운 Superuser 암호 설정
CMD> "C:\PostgreSQL_14\bin\initdb.exe" -D "C:\PostgreSQL_Data" -U postgres -W
📢 "VCRUNTIME140.dll이(가) 없어 코드 실행을 진행할 수 없습니다. 프로그램을 다시 설치하면 이 문제가 해결될 수 있습니다."
Microsoft Visual Studio 2015의 VC++ 런타임 라이브러리가 없을 경우 위와 같은 에러가 발생합니다. Microsoft 다운로드 센터(https://www.microsoft.com/ko-kr/download/details.aspx?id=48145)에서 Visual Studio 2015용 Visual C++ 재배포 가능 패키지를 다운로드받아 설치하면 해결됩니다.
더보기

Step 5. PostgreSQL 서비스 등록 및 서비스 실행(명령 프롬프트 관리자권한으로 실행)

## -N Option : 서비스에 표시될 이름
## -D Option : PostgreSQL Data Directory
CMD> "C:\PostgreSQL_14\bin\pg_ctl.exe" register -N PostgreSQL_14 -D "C:\PostgreSQL_Data"

 

3. PostgreSQL 접속 및 확인

pgAdmin이나 psql과 같은 Client 프로그램을 통해 설치한 PostgreSQL의 접속 및 버전을 확인합니다.

## postgres 계정으로 psql 접속
CMD> "C:\PostgreSQL_14\bin\psql.exe" -U postgres -c "SELECT VERSION()"
                           version
------------------------------------------------------------
 PostgreSQL 14.4, compiled by Visual C++ build 1914, 64-bit
(1개 행)

 

부록 1. 외부 접속을 위한 설정 파일 변경 목차

PostgreSQL을 설치했다면, 기본적으로 외부 접속은 불가능하게 설정되어 있습니다. 즉, PostgreSQL Server가 설치된 Local에서만 접속이 가능합니다. 외부 접속을 가능하게 하기 위해서는 PostgreSQL Configuration 파일을 수정한 후 서비스를 재기동해야 합니다. Configuration File의 기본 위치는 아래와 같으며 Install Overview에 설명되어 있습니다.

  • CentOS : /var/lib/pgsql/{VERSION}/data
  • Ubuntu : /var/lib/postgresql/{VERSION}/main
  • Windows : C:\Program Files\PostgreSQL\{VERSION}\data

 

1. 방화벽(Firewalld) 개방

방화벽에서 PostgreSQL Server가 사용하는 Port를 개방합니다.

## [root] PostgreSQL Server 사용 Port(5432) 방화벽 개방
firewall-cmd --zone=public --permanent --add-port=5432/tcp

## [root] 방화벽 서비스갱신
firewall-cmd --reload

## [root] 방화벽 개방 확인
firewall-cmd --list-ports
더보기
[root@centos ~]# firewall-cmd --zone=public --permanent --add-port=5432/tcp
success
[root@centos ~]# firewall-cmd --reload
success
[root@centos ~]# firewall-cmd --list-ports
5432/tcp

 

2. 사용자(Role) 비밀번호 설정

## [postgres] psql 접속
su - postgres
psql

## postgres 비밀번호 변경 (예시 비밀번호 : pgadmin)
\password postgres
또는
ALTER ROLE postgres PASSWORD 'pgadmin' ;
더보기
[root@centos ~]# su - postgres
[postgres@centos ~]$ psql
psql (14.5)
Type "help" for help.

postgres=# \password postgres
Enter new password for user "postgres":
Enter it again:

postgres=# ALTER ROLE postgres PASSWORD 'pgadmin' ;
ALTER ROLE

 

3. postgresql.conf 변경

vi /var/lib/pgsql/14/data/postgresql.conf
... 생략 ...
listen_addresses = '*'
... 생략 ...
📢 PostgreSQL 참고 자료 postgresql.conf(blog.ex-em.com/1649) 내용을 확인할 수 있습니다.

 

4. pg_hba.conf 변경

vi /var/lib/pgsql/14/data/pg_hba.conf
... 생략 ...
## md5 인증방식으로 연결 허용
host     all     all     0.0.0.0/0    md5
... 생략 ...
📢 PostgreSQL 참고 자료 pg_hba.conf(blog.ex-em.com/1650) 내용을 확인할 수 있습니다.

 

5. PostgreSQL 재기동

## [root]
systemctl restart postgresql-14

 

부록 2. Extension 사용을 위한 준비 목차

본 부록에서는 pg_stat_statements와 pgcrypto Extension을 설치하는 과정을 설명합니다.

 

CentOS - PostgreSQL 설치 시 Extension 설치

1. PostgreSQL Contrib Package 설치

** yum을 통한 Contrib Package 설치

## [root] yum install -y postgresql{VERSION}-contrib
## postgresql Contrib Package : PostgreSQL과 함께 배포되는 Source 및 바이너리
yum install -y postgresql14-contrib
더보기
[root@centos ~]# yum install -y postgresql14-contrib
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirror.kakao.com
 * extras: mirror.kakao.com
 * updates: mirror.kakao.com
Resolving Dependencies
--> Running transaction check
---> Package postgresql14-contrib.x86_64 0:14.5-1PGDG.rhel7 will be installed
--> Processing Dependency: libpython3.6m.so.1.0()(64bit) for package: postgresql14-contrib-14.5-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package python3-libs.x86_64 0:3.6.8-18.el7 will be installed
--> Processing Dependency: python(abi) = 3.6 for package: python3-libs-3.6.8-18.el7.x86_64
--> Running transaction check
---> Package python3.x86_64 0:3.6.8-18.el7 will be installed
--> Processing Dependency: python3-setuptools for package: python3-3.6.8-18.el7.x86_64
--> Processing Dependency: python3-pip for package: python3-3.6.8-18.el7.x86_64
--> Running transaction check
---> Package python3-pip.noarch 0:9.0.3-8.el7 will be installed
---> Package python3-setuptools.noarch 0:39.2.0-10.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================
 Package                              Arch                   Version                           Repository               Size
=============================================================================================================================
Installing:
 postgresql14-contrib                 x86_64                 14.5-1PGDG.rhel7                  pgdg14                  686 k
Installing for dependencies:
 python3                              x86_64                 3.6.8-18.el7                      updates                  70 k
 python3-libs                         x86_64                 3.6.8-18.el7                      updates                 6.9 M
 python3-pip                          noarch                 9.0.3-8.el7                       base                    1.6 M
 python3-setuptools                   noarch                 39.2.0-10.el7                     base                    629 k

Transaction Summary
=============================================================================================================================
Install  1 Package (+4 Dependent packages)

Total download size: 9.9 M
Installed size: 50 M
Downloading packages:
(1/5): python3-3.6.8-18.el7.x86_64.rpm                                                                |  70 kB  00:00:00
(2/5): python3-setuptools-39.2.0-10.el7.noarch.rpm                                                    | 629 kB  00:00:00
(3/5): python3-pip-9.0.3-8.el7.noarch.rpm                                                             | 1.6 MB  00:00:00
(4/5): python3-libs-3.6.8-18.el7.x86_64.rpm                                                           | 6.9 MB  00:00:01
(5/5): postgresql14-contrib-14.5-1PGDG.rhel7.x86_64.rpm                                               | 686 kB  00:00:04
-----------------------------------------------------------------------------------------------------------------------------
Total                                                                                        2.4 MB/s | 9.9 MB  00:00:04
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : python3-libs-3.6.8-18.el7.x86_64                                                                          1/5
  Installing : python3-3.6.8-18.el7.x86_64                                                                               2/5
  Installing : python3-setuptools-39.2.0-10.el7.noarch                                                                   3/5
  Installing : python3-pip-9.0.3-8.el7.noarch                                                                            4/5
  Installing : postgresql14-contrib-14.5-1PGDG.rhel7.x86_64                                                              5/5
  Verifying  : postgresql14-contrib-14.5-1PGDG.rhel7.x86_64                                                              1/5
  Verifying  : python3-setuptools-39.2.0-10.el7.noarch                                                                   2/5
  Verifying  : python3-3.6.8-18.el7.x86_64                                                                               3/5
  Verifying  : python3-libs-3.6.8-18.el7.x86_64                                                                          4/5
  Verifying  : python3-pip-9.0.3-8.el7.noarch                                                                            5/5

Installed:
  postgresql14-contrib.x86_64 0:14.5-1PGDG.rhel7

Dependency Installed:
  python3.x86_64 0:3.6.8-18.el7                 python3-libs.x86_64 0:3.6.8-18.el7     python3-pip.noarch 0:9.0.3-8.el7
  python3-setuptools.noarch 0:39.2.0-10.el7

Complete!

** rpm을 통한 Contrib Package 설치

1) Contrib Package 다운로드

## [root]
yum install -y --downloadonly --downloaddir=/root/PostgreSQL_Contrib postgresql14-contrib
더보기
[root@centos ~]# yum install -y --downloadonly --downloaddir=/root/PostgreSQL_Contrib postgresql14-contrib
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirror.navercorp.com
 * extras: mirror.navercorp.com
 * updates: mirror.navercorp.com
Resolving Dependencies
--> Running transaction check
---> Package postgresql14-contrib.x86_64 0:14.5-1PGDG.rhel7 will be installed
--> Processing Dependency: libpython3.6m.so.1.0()(64bit) for package: postgresql14-contrib-14.5-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package python3-libs.x86_64 0:3.6.8-18.el7 will be installed
--> Processing Dependency: python(abi) = 3.6 for package: python3-libs-3.6.8-18.el7.x86_64
--> Running transaction check
---> Package python3.x86_64 0:3.6.8-18.el7 will be installed
--> Processing Dependency: python3-setuptools for package: python3-3.6.8-18.el7.x86_64
--> Processing Dependency: python3-pip for package: python3-3.6.8-18.el7.x86_64
--> Running transaction check
---> Package python3-pip.noarch 0:9.0.3-8.el7 will be installed
---> Package python3-setuptools.noarch 0:39.2.0-10.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================
 Package                               Arch                    Version                            Repository                Size
=================================================================================================================================
Installing:
 postgresql14-contrib                  x86_64                  14.5-1PGDG.rhel7                   pgdg14                   686 k
Installing for dependencies:
 python3                               x86_64                  3.6.8-18.el7                       updates                   70 k
 python3-libs                          x86_64                  3.6.8-18.el7                       updates                  6.9 M
 python3-pip                           noarch                  9.0.3-8.el7                        base                     1.6 M
 python3-setuptools                    noarch                  39.2.0-10.el7                      base                     629 k

Transaction Summary
=================================================================================================================================
Install  1 Package (+4 Dependent packages)

Total download size: 9.9 M
Installed size: 50 M
Background downloading packages, then exiting:
warning: /root/PostgreSQL_Contrib/python3-3.6.8-18.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Public key for python3-3.6.8-18.el7.x86_64.rpm is not installed
(1/5): python3-3.6.8-18.el7.x86_64.rpm                                                                    |  70 kB  00:00:00
Public key for python3-setuptools-39.2.0-10.el7.noarch.rpm is not installed
(2/5): python3-setuptools-39.2.0-10.el7.noarch.rpm                                                        | 629 kB  00:00:00
(3/5): python3-pip-9.0.3-8.el7.noarch.rpm                                                                 | 1.6 MB  00:00:00
(4/5): python3-libs-3.6.8-18.el7.x86_64.rpm                                                               | 6.9 MB  00:00:00
warning: /root/PostgreSQL_Contrib/postgresql14-contrib-14.5-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Public key for postgresql14-contrib-14.5-1PGDG.rhel7.x86_64.rpm is not installed
(5/5): postgresql14-contrib-14.5-1PGDG.rhel7.x86_64.rpm                                                   | 686 kB  00:00:02
---------------------------------------------------------------------------------------------------------------------------------
Total                                                                                            4.0 MB/s | 9.9 MB  00:00:02
exiting because "Download Only" specified

[root@centos ~]# ls -al /root/PostgreSQL_Contrib
total 10176
drwxr-xr-x. 2 root root     238 Aug 23 23:56 .
dr-xr-x---. 9 root root     283 Aug 23 23:56 ..
-rw-r--r--. 1 root root  702052 Aug 10 21:11 postgresql14-contrib-14.5-1PGDG.rhel7.x86_64.rpm
-rw-r--r--. 1 root root   71844 Nov 18  2020 python3-3.6.8-18.el7.x86_64.rpm
-rw-r--r--. 1 root root 7286976 Nov 18  2020 python3-libs-3.6.8-18.el7.x86_64.rpm
-rw-r--r--. 1 root root 1702324 Oct 15  2020 python3-pip-9.0.3-8.el7.noarch.rpm
-rw-r--r--. 1 root root  644052 Aug 23  2019 python3-setuptools-39.2.0-10.el7.noarch.rpm

2) Contrib Package 설치

## [root]
cd /root/PostgreSQL_Contrib
rpm -ivh *.rpm
더보기
[root@centos ~]# cd PostgreSQL_Contrib
[root@centos PostgreSQL_Contrib]# rpm -ivh *.rpm
warning: postgresql14-contrib-14.5-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
warning: python3-3.6.8-18.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:python3-libs-3.6.8-18.el7        ################################# [ 20%]
   2:python3-pip-9.0.3-8.el7          ################################# [ 40%]
   3:python3-setuptools-39.2.0-10.el7 ################################# [ 60%]
   4:python3-3.6.8-18.el7             ################################# [ 80%]
   5:postgresql14-contrib-14.5-1PGDG.r################################# [100%]

 

2. pg_stat_statements, pgcrypto Extension 설치

## [postgres] PostgreSQL에 접속하여 Extension 설치
psql
CREATE EXTENSION pg_stat_statements ;
CREATE EXTENSION pgcrypto ;
더보기
[postgres@centos]$ psql
psql (14.5)
Type "help" for help.

postgres=# CREATE EXTENSION pg_stat_statements ;
CREATE EXTENSION
postgres=# CREATE EXTENSION pgcrypto ;
CREATE EXTENSION

 

3. Extension 설치확인

## [postgres] PostgreSQL에 접속하여 Extension 설치확인
psql
\dx
또는 
SELECT * FROM pg_extension ;
더보기
[postgres@centos]$ psql
psql (14.5)
Type "help" for help.

postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 pg_stat_statements | 1.9     | public     | track planning and execution statistics of all SQL statements executed
 pgcrypto           | 1.3     | public     | cryptographic functions
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# SELECT * FROM pg_extension ;
  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 14472 | plpgsql            |       10 |           11 | f              | 1.0        |           |
 16384 | pg_stat_statements |       10 |         2200 | t              | 1.9        |           |
 16409 | pgcrypto           |       10 |         2200 | t              | 1.3        |           |
(3 rows)

 

 

Ubuntu - PostgreSQL 설치 시 Extension 설치

1. PostgreSQL Contrib Package 설치

** apt를 통한 Contrib Package 설치

## [root] apt install -y postgresql-contrib
## postgresql Contrib Package : PostgreSQL과 함께 배포되는 Source 및 바이너리
apt install -y postgresql-contrib
더보기
root@ubuntu:~# apt install -y postgresql-contrib
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following NEW packages will be installed:
  postgresql-contrib
0 upgraded, 1 newly installed, 0 to remove and 34 not upgraded.
Need to get 66.7 kB of archives.
After this operation, 71.7 kB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-contrib all 14+241.pgdg22.04+1 [66.7 kB]
Fetched 66.7 kB in 10s (6,715 B/s)
Selecting previously unselected package postgresql-contrib.
(Reading database ... 75579 files and directories currently installed.)
Preparing to unpack .../postgresql-contrib_14+241.pgdg22.04+1_all.deb ...
Unpacking postgresql-contrib (14+241.pgdg22.04+1) ...
Setting up postgresql-contrib (14+241.pgdg22.04+1) ...
Scanning processes...
Scanning linux images...

Running kernel seems to be up-to-date.

No services need to be restarted.

No containers need to be restarted.

No user sessions are running outdated binaries.

No VM guests are running outdated hypervisor (qemu) binaries on this host.

** dpkg를 통한 Contrib Package 설치

1) Contrib package 다운로드

## [root]
apt install --download-only -y postgresql-contrib
더보기
root@ubuntu:~# apt install --download-only -y postgresql-contrib
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following NEW packages will be installed:
  postgresql-contrib
0 upgraded, 1 newly installed, 0 to remove and 87 not upgraded.
Need to get 66.8 kB of archives.
After this operation, 71.7 kB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-contrib all 14+242.pgdg22.04+1 [66.8 kB]
Fetched 66.8 kB in 1s (59.4 kB/s)
Download complete and in download only mode

root@ubuntu:~# cd /var/cache/apt/archives
root@ubuntu:/var/cache/apt/archives# ls -al
total 68
drwxr-xr-x 3 root root    86 Aug 23 15:04 .
drwxr-xr-x 3 root root    65 Aug 23 15:04 ..
-rw-r----- 1 root root     0 Apr 21 01:01 lock
drwx------ 2 _apt root     6 Aug 23 15:04 partial
-rw-r--r-- 1 root root 66772 Aug 11 10:46 postgresql-contrib_14+242.pgdg22.04+1_all.deb

2) Contrib package 설치

## [root]
dpkg -i postgresql-contrib_14+242.pgdg22.04+1_all.deb
더보기
root@ubuntu:/var/cache/apt/archives# dpkg -i postgresql-contrib_14+242.pgdg22.04+1_all.deb
Selecting previously unselected package postgresql-contrib.
(Reading database ... 75383 files and directories currently installed.)
Preparing to unpack postgresql-contrib_14+242.pgdg22.04+1_all.deb ...
Unpacking postgresql-contrib (14+242.pgdg22.04+1) ...
Setting up postgresql-contrib (14+242.pgdg22.04+1) ...

 

2. pg_stat_statements, pgcrypto Extension 설치

## [postgres] PostgreSQL에 접속하여 Extension 설치
psql
CREATE EXTENSION pg_stat_statements ;
CREATE EXTENSION pgcrypto ;
더보기
postgres@ubuntu:~ psql
psql (14.5)
Type "help" for help.

postgres=# CREATE EXTENSION pg_stat_statements ;
CREATE EXTENSION
postgres=# CREATE EXTENSION pgcrypto ;
CREATE EXTENSION

 

3. Extension 설치확인

## [postgres] PostgreSQL에 접속하여 Extension 설치확인
psql
\dx
또는 
SELECT * FROM pg_extension ;
더보기
postgres@ubuntu:~ psql
psql (14.5)
Type "help" for help.

postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 pg_stat_statements | 1.9     | public     | track planning and execution statistics of all SQL statements executed
 pgcrypto           | 1.3     | public     | cryptographic functions
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# SELECT * FROM pg_extension ;
  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 14472 | plpgsql            |       10 |           11 | f              | 1.0        |           |
 16384 | pg_stat_statements |       10 |         2200 | t              | 1.9        |           |
 16409 | pgcrypto           |       10 |         2200 | t              | 1.3        |           |
(3 rows)

 

 

Source File로 PostgreSQL 설치 시 Extension 설치

PostgreSQL을 Source File에는 PostgreSQL에서 기본으로 제공하는 Extension Source File이 같이 제공됩니다. PostgreSQL가 설치된 디렉토리 contrib 디렉토리에 Extension Source File이 존재합니다.

[postgres@centos contrib]$ pwd
/home/postgres/PostgreSQL_Engine/contrib/pg_stat_statements

[postgres@centos contrib]$ ls -al
total 92
drwxrwxr-x. 58 postgres postgres 4096 Aug  9 05:57 .
drwxrwxr-x.  6 postgres postgres 4096 Aug 23 16:39 ..
drwxrwxr-x.  4 postgres postgres  230 Aug  9 05:57 adminpack
... 중략
-rw-r--r--.  1 postgres postgres 1478 Aug  9 05:44 Makefile
drwxrwxr-x.  3 postgres postgres   67 Aug  9 05:57 oid2name
drwxrwxr-x.  2 postgres postgres  101 Aug  9 05:57 old_snapshot
drwxrwxr-x.  4 postgres postgres 4096 Aug  9 05:57 pageinspect
drwxrwxr-x.  4 postgres postgres   90 Aug  9 05:57 passwordcheck
drwxrwxr-x.  2 postgres postgres  221 Aug  9 05:57 pg_buffercache
drwxrwxr-x.  4 postgres postgres 4096 Aug  9 05:57 pgcrypto
drwxrwxr-x.  2 postgres postgres  184 Aug  9 05:57 pg_freespacemap
drwxrwxr-x.  2 postgres postgres  180 Aug  9 05:57 pg_prewarm
drwxrwxr-x.  2 postgres postgres  159 Aug  9 05:57 pgrowlocks
drwxrwxr-x.  4 postgres postgres 4096 Aug  9 05:57 pg_stat_statements
... 이하생략 ...

Makefile을 참조하여 Extension을 설치할 수 있습니다. Makefile은 각 디렉토리마다 존재합니다. contrib 디렉토리에 존재하는 Makefile을 참조하여 모든 Extension을 설치하거나, 각 Extension 디렉토리에 존재하는 Makefile을 참조하여 해당 Extension만 설치할 수 있습니다.

 

1. pg_stat_statements Extension 준비

## [postgres] pg_stat_statements Extension Source Build 및 설치
cd /home/postgres/PostgreSQL_Engine/contrib/pg_stat_statements
make && make install
더보기
[postgres@centos pg_stat_statements]$ pwd
/home/postgres/PostgreSQL_Engine/contrib/pg_stat_statements

[postgres@centos contrib]$ cd pg_stat_statements/
[postgres@centos pg_stat_statements]$ make && make install
make -C ../../src/backend generated-headers
make[1]: Entering directory `/home/postgres/PostgreSQL_Engine/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/PostgreSQL_Engine/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/PostgreSQL_Engine/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/PostgreSQL_Engine/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/PostgreSQL_Engine/src/backend/utils'
make[1]: Leaving directory `/home/postgres/PostgreSQL_Engine/src/backend'
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pg_stat_statements -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pg_stat_statements.o /home/postgres/postgresql-14.5/contrib/pg_stat_statements/pg_stat_statements.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pg_stat_statements.so  pg_stat_statements.o -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/home/postgres/PostgreSQL_Engine/lib',--enable-new-dtags -lm
make -C ../../src/backend generated-headers
make[1]: Entering directory `/home/postgres/PostgreSQL_Engine/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/PostgreSQL_Engine/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/PostgreSQL_Engine/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/PostgreSQL_Engine/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/PostgreSQL_Engine/src/backend/utils'
make[1]: Leaving directory `/home/postgres/PostgreSQL_Engine/src/backend'
/bin/mkdir -p '/home/postgres/PostgreSQL_Engine/lib'
/bin/mkdir -p '/home/postgres/PostgreSQL_Engine/share/extension'
/bin/mkdir -p '/home/postgres/PostgreSQL_Engine/share/extension'
/bin/install -c -m 755  pg_stat_statements.so '/home/postgres/PostgreSQL_Engine/lib/pg_stat_statements.so'
/bin/install -c -m 644 /home/postgres/postgresql-14.5/contrib/pg_stat_statements/pg_stat_statements.control '/home/postgres/PostgreSQL_Engine/share/extension/'
/bin/install -c -m 644 /home/postgres/postgresql-14.5/contrib/pg_stat_statements/pg_stat_statements--1.4.sql /home/postgres/postgresql-14.5/contrib/pg_stat_statements/pg_stat_statements--1.8--1.9.sql /home/postgres/postgresql-14.5/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql /home/postgres/postgresql-14.5/contrib/pg_stat_statements/pg_stat_statements--1.6--1.7.sql /home/postgres/postgresql-14.5/contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql /home/postgres/postgresql-14.5/contrib/pg_stat_statements/pg_stat_statements--1.4--1.5.sql /home/postgres/postgresql-14.5/contrib/pg_stat_statements/pg_stat_statements--1.3--1.4.sql /home/postgres/postgresql-14.5/contrib/pg_stat_statements/pg_stat_statements--1.2--1.3.sql /home/postgres/postgresql-14.5/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql /home/postgres/postgresql-14.5/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql  '/home/postgres/PostgreSQL_Engine/share/extension/'

 

2. pgcrypto Extension 준비

## [postgres] pgcrypto Extension Source Build 및 설치
cd /home/postgres/PostgreSQL_Engine/contrib/pgcrypto
make && make install
더보기
[postgres@centos pgcrypto]$ pwd
/home/postgres/PostgreSQL_Engine/contrib/pgcrypto

[postgres@centos pgcrypto]$ make && make install
make -C ../../src/backend generated-headers
make[1]: Entering directory `/home/postgres/PostgreSQL_Engine/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/PostgreSQL_Engine/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/PostgreSQL_Engine/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/PostgreSQL_Engine/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/PostgreSQL_Engine/src/backend/utils'
make[1]: Leaving directory `/home/postgres/PostgreSQL_Engine/src/backend'
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o internal.o /home/postgres/postgresql-14.5/contrib/pgcrypto/internal.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o internal-sha2.o /home/postgres/postgresql-14.5/contrib/pgcrypto/internal-sha2.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o blf.o /home/postgres/postgresql-14.5/contrib/pgcrypto/blf.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o rijndael.o /home/postgres/postgresql-14.5/contrib/pgcrypto/rijndael.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp-mpi-internal.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp-mpi-internal.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -Wno-declaration-after-statement -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o imath.o /home/postgres/postgresql-14.5/contrib/pgcrypto/imath.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o crypt-blowfish.o /home/postgres/postgresql-14.5/contrib/pgcrypto/crypt-blowfish.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o crypt-des.o /home/postgres/postgresql-14.5/contrib/pgcrypto/crypt-des.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o crypt-gensalt.o /home/postgres/postgresql-14.5/contrib/pgcrypto/crypt-gensalt.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o crypt-md5.o /home/postgres/postgresql-14.5/contrib/pgcrypto/crypt-md5.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o mbuf.o /home/postgres/postgresql-14.5/contrib/pgcrypto/mbuf.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgcrypto.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgcrypto.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp-armor.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp-armor.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp-cfb.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp-cfb.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp-compress.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp-compress.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp-decrypt.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp-decrypt.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp-encrypt.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp-encrypt.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp-info.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp-info.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp-mpi.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp-mpi.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp-pgsql.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp-pgsql.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp-pubdec.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp-pubdec.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp-pubenc.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp-pubenc.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp-pubkey.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp-pubkey.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp-s2k.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp-s2k.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o pgp.o /home/postgres/postgresql-14.5/contrib/pgcrypto/pgp.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o px-crypt.o /home/postgres/postgresql-14.5/contrib/pgcrypto/px-crypt.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o px-hmac.o /home/postgres/postgresql-14.5/contrib/pgcrypto/px-hmac.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I/home/postgres/postgresql-14.5/contrib/pgcrypto -I../../src/include -I/home/postgres/postgresql-14.5/src/include  -D_GNU_SOURCE   -c -o px.o /home/postgres/postgresql-14.5/contrib/pgcrypto/px.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pgcrypto.so internal.o internal-sha2.o blf.o rijndael.o pgp-mpi-internal.o imath.o crypt-blowfish.o crypt-des.o crypt-gensalt.o crypt-md5.o mbuf.o pgcrypto.o pgp-armor.o pgp-cfb.o pgp-compress.o pgp-decrypt.o pgp-encrypt.o pgp-info.o pgp-mpi.o pgp-pgsql.o pgp-pubdec.o pgp-pubenc.o pgp-pubkey.o pgp-s2k.o pgp.o px-crypt.o px-hmac.o px.o  -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/home/postgres/PostgreSQL_Engine/lib',--enable-new-dtags
make -C ../../src/backend generated-headers
make[1]: Entering directory `/home/postgres/PostgreSQL_Engine/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/PostgreSQL_Engine/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/PostgreSQL_Engine/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/PostgreSQL_Engine/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/PostgreSQL_Engine/src/backend/utils'
make[1]: Leaving directory `/home/postgres/PostgreSQL_Engine/src/backend'
/bin/mkdir -p '/home/postgres/PostgreSQL_Engine/lib'
/bin/mkdir -p '/home/postgres/PostgreSQL_Engine/share/extension'
/bin/mkdir -p '/home/postgres/PostgreSQL_Engine/share/extension'
/bin/install -c -m 755  pgcrypto.so '/home/postgres/PostgreSQL_Engine/lib/pgcrypto.so'
/bin/install -c -m 644 /home/postgres/postgresql-14.5/contrib/pgcrypto/pgcrypto.control '/home/postgres/PostgreSQL_Engine/share/extension/'
/bin/install -c -m 644 /home/postgres/postgresql-14.5/contrib/pgcrypto/pgcrypto--1.3.sql /home/postgres/postgresql-14.5/contrib/pgcrypto/pgcrypto--1.2--1.3.sql /home/postgres/postgresql-14.5/contrib/pgcrypto/pgcrypto--1.1--1.2.sql /home/postgres/postgresql-14.5/contrib/pgcrypto/pgcrypto--1.0--1.1.sql  '/home/postgres/PostgreSQL_Engine/share/extension/'

 

3. pg_stat_statements, pgcrypto Extension 설치

## [postgres] PostgreSQL에 접속하여 Extension 설치
psql
CREATE EXTENSION pg_stat_statements ;
CREATE EXTENSION pgcrypto ;
더보기
[postgres@centos]$ psql
psql (14.5)
Type "help" for help.

postgres=# CREATE EXTENSION pg_stat_statements ;
CREATE EXTENSION
postgres=# CREATE EXTENSION pgcrypto ;
CREATE EXTENSION

 

4. Extension 설치확인

## [postgres] PostgreSQL에 접속하여 Extension 설치확인
psql
\dx
또는 
SELECT * FROM pg_extension ;
더보기
[postgres@centos]$ psql
psql (14.5)
Type "help" for help.

postgres=# \dx
                                    List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 pg_stat_statements | 1.9     | public     | track planning and execution statistics of all SQL statements executed
 pgcrypto           | 1.3     | public     | cryptographic functions
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# SELECT * FROM pg_extension ;
  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 13878 | plpgsql            |       10 |           11 | f              | 1.0        |           |
 16384 | pg_stat_statements |       10 |         2200 | t              | 1.9        |           |
 16409 | pgcrypto           |       10 |         2200 | t              | 1.3        |           |
(3 rows)

 

부록 3. Windows Stack Builder 설치 목차

PostgreSQL 설치 마지막 단계 "Completing the PostgreSQL Setup Wizard"에서 Stack Builder 설치를 체크하고 종료하게 되면, Stack Builder에 포함된 추가 모듈을 설치할 수 있습니다.

 

Stack Builder로 설치 가능한 모듈 (PostgreSQL 14.5 기준)

카테고리 선택가능항목
Add-ons, tools, and Utilities - EDB Language Pack
- pgAgent(64bit) for PostgreSQL
- pgBouncer
Database Drivers - Npgsql
- pgJDBC
- psqlODBC (32bit/64bit)
Database Server 32bit/64bit의 PostgreSQL Server (9.6~14버전까지의 최신 Minor 버전)
Registration-required and trial products
(EnterpriseDB Tools)
- Migration Toolkit
- PEM SQL Profiler Plugin
- PostgreSQL Enterprise Manager Agent
- PostgreSQL Enterprise Manager Server
- Replication Server
Spatial Extensions - PostGIS 3.1, 3.2
Web Development - PEM_HTTPD
더보기

Step 1. 설치된 PostgreSQL 선택

더보기

Step 2. Stack Builder를 통해 설치할 모듈 선택

더보기

Step 3. 선택한 모듈 설치 파일 다운로드 경로 설정

더보기

Step 4. 설치할 것인지 다운만 받을 것인지 선택

더보기

Step 5-1. 설치했을 경우

더보기

Step 5-2. 다운로드만 했을 경우

 


 

 

 

기획 및 글 | 기술기획팀

 

 

 

 

댓글