[PostgreSQL 설치와 운영] #1. Ubuntu 22.04 PostgreSQL 설치

■ Ubuntu 22.04 버전 PostgreSQL(v 14.x) 설치(CLI에서 apt 사용)

 : 2022년에 발표된 22.04 LTS(Long-Term Support) 버전의 기본 PostgreSQL의 설치 버전은 14.x 버전 입니다.

 

1. OS에 설치된 패키지 최신화

 : Ubuntu에서 중요 소프트웨어 설치 전에는 기존 설치된 패키지들을 최신화 하는 것이 안정적입니다.

 1) 패키지 업데이트 확인 및 다운로드
root@raonyn:~# sudo apt update
sudo: unable to resolve host raonyn: Name or service not known
Hit:1 http://kr.archive.ubuntu.com/ubuntu jammy InRelease
Get:2 http://kr.archive.ubuntu.com/ubuntu jammy-updates InRelease [114 kB]
Get:3 http://kr.archive.ubuntu.com/ubuntu jammy-backports InRelease [99.8 kB]
Get:4 http://kr.archive.ubuntu.com/ubuntu jammy-security InRelease [110 kB]
Get:5 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main amd64 Packages [631 kB]
Get:6 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main Translation-en [146 kB]
Get:7 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main amd64 c-n-f Metadata [8,964 B]
Get:8 http://kr.archive.ubuntu.com/ubuntu jammy-updates/restricted amd64 Packages [373 kB]
Get:9 http://kr.archive.ubuntu.com/ubuntu jammy-updates/restricted Translation-en [57.4 kB]                                                                   
Get:10 http://kr.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 Packages [428 kB]                                                                     
Get:11 http://kr.archive.ubuntu.com/ubuntu jammy-updates/universe Translation-en [108 kB]                                                                     
Get:12 http://kr.archive.ubuntu.com/ubuntu jammy-security/main amd64 Packages [370 kB]                                                                        
Get:13 http://kr.archive.ubuntu.com/ubuntu jammy-security/main Translation-en [84.1 kB]                                                                       
Get:14 http://kr.archive.ubuntu.com/ubuntu jammy-security/restricted amd64 Packages [334 kB]                                                                  
Get:15 http://kr.archive.ubuntu.com/ubuntu jamm  .................

 2) 패키지 업그레이드
  : 패키지 업그레이드 완료 된 후에는 일부 서비스를 재시작 하라는 메뉴가 나옵니다. 
   서비스에 운영에 지장이 없다면 서버 재부팅이 안정적일 수 있습니다.
  : atp -y upgrade, apt install -y 패키지 : -y 옵션은 설치여부 선택에 대한 "yes"의 의미로 옵션이 들어가면 설치 진행 여부를 묻지 않습니다.
root@raonyn:~# sudo apt upgrade
sudo: unable to resolve host raonyn: Name or service not known
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Calculating upgrade... Done
The following NEW packages will be installed:
  linux-headers-5.15.0-50 linux-headers-5.15.0-50-generic linux-image-5.15.0-50-generic linux-modules-5.15.0-50-generic linux-modules-extra-5.15.0-50-generic
  systemd-hwe-hwdb
The following packages will be upgraded:
  apt apt-utils cryptsetup cryptsetup-bin cryptsetup-initramfs dmidecode gzip libapt-pkg6.0 libcryptsetup12 libldap-2.5-0 libldap-common libnftables1
  libnss-systemd libpam-systemd libpython3-stdlib libpython3.10 libpython3.10-minimal libpython3.10-stdlib libsystemd0 libudev1 linux-generic
  linux-headers-generic linux-image-generic nftables python3 python3-distupgrade python3-distutils python3-gdbm python3-lib2to3 python3-minimal
  python3-software-properties python3.10 python3.10-minimal software-properties-common systemd systemd-sysv systemd-timesyncd ubuntu-advantage-tools
  ubuntu-release-upgrader-core udev zlib1g
41 upgraded, 6 newly installed, 0 to remove and 0 not upgraded.
3 standard security updates
Need to get 132 MB of archives.
After this operation, 583 MB of additional disk space will be used.
Do you want to continue? [Y/n] y   <== 진행 할 것인지 물어 봄
Get:1 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main amd64 gzip amd64 1.10-4ubuntu4.1 [96.0 kB]
Get:2 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libnss-systemd amd64 249.11-0ubuntu3.6 [133 kB]
Get:3 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libsystemd0 amd64 249.11-0ubuntu3.6 [318 kB]
Get:4 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main amd64 systemd-timesyncd amd64 249.11-0ubuntu3.6 [31.2 kB]
Get:5 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main amd64 systemd-sysv amd64 249.11-0ubuntu3.6 [10.5 kB]
Get:6 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libpam-systemd amd64 249.11-0ubuntu3.6 [203 kB]
Get:7 http://kr.archive.ubuntu.com/ubuntu jammy-updates/main amd64 systemd amd64 249.11-0ubuntu3.6 [4,580 kB]
4% [7 systemd 2,118 kB/4,580 kB 46%]  ............................

 

2. PostgreSQL 설치

 : Ubuntu 22.04에서 postgresql를 설치하면 필요한 패키지인 postgresql-client-14, , postgresql-common 등이 같이 설치 됩니다.

 1) 패키지 설치
root@raonyn:~# sudo apt install -y postgresql
sudo: unable to resolve host raonyn: Name or service not known
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 postgresql-14
  postgresql-client-14 postgresql-client-common postgresql-common ssl-cert sysstat
Suggested packages:
  lm-sensors postgresql-doc 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 postgresql postgresql-14
  postgresql-client-14 postgresql-client-common postgresql-common ssl-cert sysstat
0 upgraded, 15 newly installed, 0 to remove and 0 not upgraded.
Need to get 42.4 MB of archives.
After this operation, 161 MB of additional disk space will be used.
.........................

 2) 참고: 특정 버전을 설치할 경우에는 버전 정보를 넣어 줍니다.
sudo apt install postgresql-12

3. PostgreSQL 설치 완료 확인

 : postgresql 설치가 완료되면 아래와 같은 방법으로 정상 설치 되었는지 확인해 봅니다.

Ubuntu 설치된 패키지 및 프로세스 확인

 1) 프로세스 확인

root@raonyn:~# ps -ef | grep post postgres   35692       1  0 11:36 ?        00:00:00 /usr/lib/postgresql/14/bin/postgres -D /data/postgres14-data/main -c config_file=/etc/postgresql/14/main/postgresql.conf postgres   35693   35692  0 11:36 ?        00:00:00 postgres: 14/main: logger  postgres   35695   35692  0 11:36 ?        00:00:00 postgres: 14/main: checkpointer  postgres   35696   35692  0 11:36 ?        00:00:00 postgres: 14/main: background writer  postgres   35697   35692  0 11:36 ?        00:00:00 postgres: 14/main: walwriter  postgres   35698   35692  0 11:36 ?        00:00:00 postgres: 14/main: autovacuum launcher  postgres   35699   35692  0 11:36 ?        00:00:00 postgres: 14/main: stats collector  postgres   35700   35692  0 11:36 ?        00:00:00 postgres: 14/main: logical replication launcher  root       35972    1913  0 11:36 pts/1    00:00:00 grep --color=auto post

 => 메인 프로세스가 기동 되며, 추가로 logger, checkpointer 등 총 8개의 프로세스가 기동됨.

 2) 서비스 데몬 LISTEN 확인

root@raonyn:~# ss -lntp State           Recv-Q          Send-Q                   Local Address:Port                      Peer Address:Port          Process                                        LISTEN          0               511                          127.0.0.1:34221                          0.0.0.0:*              users:(("node",pid=1453,fd=18))               LISTEN          0               128                            0.0.0.0:22                             0.0.0.0:*              users:(("sshd",pid=32583,fd=3))               LISTEN          0               244                            0.0.0.0:5432                           0.0.0.0:*              users:(("postgres",pid=35692,fd=6))           LISTEN          0               128                               [::]:22                                [::]:*              users:(("sshd",pid=32583,fd=4))               LISTEN          0               244                               [::]:5432                              [::]:*              users:(("postgres",pid=35692,fd=7))

 => PostgreSQL은 기본 TCP 5432 포트로 LISTEN 함

 3) OS상의 서비스 등록 및 활성화 확인

root@raonyn:~# systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS      Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)      Active: active (exited) since Tue 2022-10-11 11:36:36 KST; 1min 0s ago     Process: 35711 ExecStart=/bin/true (code=exited, status=0/SUCCESS)    Main PID: 35711 (code=exited, status=0/SUCCESS)         CPU: 1ms Oct 11 11:36:36 raonyn systemd[1]: Starting PostgreSQL RDBMS... Oct 11 11:36:36 raonyn systemd[1]: Finished PostgreSQL RDBMS.

 => OS에는 postgresql.service로 등록되며, 설치 완료 후 자동으로  Active 상태가 활성화됨.

4. 설치된 패키지 정보 확인

 : 어떤 postgresql 패키지가 설치 되었는지 아래 방법으로 확인 가능 합니다.

Ubuntu 설치된 패키지 정보 확인

 1) dpkg 명령

root@raonyn:~# dpkg -l | grep postgresql* ii  postgresql                            14+238                                  all          object-relational SQL database (supported version) ii  postgresql-14                         14.5-0ubuntu0.22.04.1                   amd64        The World's Most Advanced Open Source Relational Database ii  postgresql-client-14                  14.5-0ubuntu0.22.04.1                   amd64        front-end programs for PostgreSQL 14 ii  postgresql-client-common              238                                     all          manager for multiple PostgreSQL client versions ii  postgresql-common                     238                                     all          PostgreSQL database-cluster manager

 2) apt 명령

root@raonyn:~# apt list --installed postgresql* Listing... Done postgresql-14/jammy-updates,jammy-security,now 14.5-0ubuntu0.22.04.1 amd64 [installed,automatic] postgresql-client-14/jammy-updates,jammy-security,now 14.5-0ubuntu0.22.04.1 amd64 [installed,automatic] postgresql-client-common/jammy,now 238 all [installed,automatic] postgresql-common/jammy,now 238 all [installed,automatic] postgresql/jammy,now 14+238 all [installed]

 

5. PostgreSQL 관리계정 

 1) 관리계정 확인

postgres 계정 확인

 => postgresql이 설치 완료되면 dbms 관리용 id(postgres)가 자동 생성됩니다.

6. PostgreSQL 서버 중지 및 시작 명령

 1) 서버 중지
sudo systemctl stop postgresql

 2) 서버 시작
sudo systemctl start postgresql

 3) 서버 재시작
sudo systemctl restart postgresql

 4) 서버 다시 로드
sudo systemctl reload postgresql