[PostgresQL] 우분투 PostgreSQL 설치

1. 기본 설치 확인

kim@kim-ThinkPad-X1-Carbon-Gen-8:~$ aptitude show postgresql | grep State

State: not installed

2. postgreSQL 설치

kim@kim-ThinkPad-X1-Carbon-Gen-8:~$ sudo apt-get install postgresql
패키지 목록을 읽는 중입니다... 완료
의존성 트리를 만드는 중입니다       
상태 정보를 읽는 중입니다... 완료
다음 패키지가 자동으로 설치되었지만 더 이상 필요하지 않습니다:
  libfwupdplugin1 libllvm11 linux-headers-5.14.0-1027-oem
  linux-headers-5.4.0-104 linux-headers-5.4.0-104-generic
  linux-image-5.14.0-1027-oem linux-image-5.4.0-104-generic
  linux-modules-5.14.0-1027-oem linux-modules-5.4.0-104-generic
  linux-modules-extra-5.4.0-104-generic linux-oem-5.14-headers-5.14.0-1027
'sudo apt autoremove'를 이용하여 제거하십시오.
다음의 추가 패키지가 설치될 것입니다 :
  libllvm10 libpq5 postgresql-12 postgresql-client-12 postgresql-client-common
  postgresql-common sysstat

3. 설치 확인

kim@kim-ThinkPad-X1-Carbon-Gen-8:~$ dpkg -l | grep postgres
ii  postgresql                                 12+214ubuntu0.1                     all          object-relational SQL database (supported version)
ii  postgresql-12                              12.9-0ubuntu0.20.04.1               amd64        object-relational SQL database, version 12 server
ii  postgresql-client-12                       12.9-0ubuntu0.20.04.1               amd64        front-end programs for PostgreSQL 12
ii  postgresql-client-common                   214ubuntu0.1                        all          manager for multiple PostgreSQL client versions
ii  postgresql-common                          214ubuntu0.1                        all          PostgreSQL database-cluster manager

kim@kim-ThinkPad-X1-Carbon-Gen-8:~$ cat /etc/passwd | grep postgres
postgres:x:127:134:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

-> PostgreSQL 관리자 계정 postgres가 생성 

kim@kim-ThinkPad-X1-Carbon-Gen-8:~$ /etc/init.d/postgresql status
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sun 2022-04-03 23:09:50 KST; 1min 59s ago
   Main PID: 120919 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 18887)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

 4월 03 23:09:50 kim-ThinkPad-X1-Carbon-Gen-8 systemd[1]: Starting PostgreSQ…..
 4월 03 23:09:50 kim-ThinkPad-X1-Carbon-Gen-8 systemd[1]: Finished PostgreSQ…S.
Hint: Some lines were ellipsized, use -l to show in full.

root@zetawiki:~# /etc/init.d/postgresql status
9.3/main (port 5432): online

root@zetawiki:~# netstat -tnlp | grep postgres
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      23680/postgres

안나올시 아래 명령어 실행

$ netstat -ntlp | grep 5432

4.패스워드 설정

kim@kim-ThinkPad-X1-Carbon-Gen-8:/$ sudo -u postgres psql template1
[sudo] kim 암호: 
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

template1=# 
template1=# ALTER USER postgres with encrypted password 'P@sswOrd';
ALTER ROLE
template1=# \q

5.접속 대여 IP 설정

kim@kim-ThinkPad-X1-Carbon-Gen-8:/$ ps -ef | grep postgresql.conf | grep -v grep
postgres  123877       1  0 23:15 ?        00:00:00 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf

• 여기서 테스트를 위한 모든 IP는 허용되지만, 실제로 접근시 허용할 IP만 한정하는 것을 권장
• 설정 파일 편집

kim@kim-ThinkPad-X1-Carbon-Gen-8:/$ sudo nano /etc/postgresql/12/main/postgresql.conf

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
listen_addresses = '*'                          # defaults to 'localhost'; use '*' for all

-> 모든 IP(*)에서 5432 포트로 접근할 수 있도록 설정 

• hba 설정 파일 편집

sudo nano /etc/postgresql/12/main/pg_hba.conf

host    all             all             0.0.0.0/0               md5

-> 모든 IP(0.0.0.0/0)에서 모든 DB(all)에 대한 모든 계정(all)로그인 허용

• PostgreSQL 재시작 후 LISTEN 포트 확인

kim@kim-ThinkPad-X1-Carbon-Gen-8:/$ /etc/init.d/postgresql restart
Restarting postgresql (via systemctl): postgresql.service.

kim@kim-ThinkPad-X1-Carbon-Gen-8:/$ netstat -ntlp | grep 5432
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      -                   
tcp6       0      0 :::5432                 :::*                    LISTEN      -

참고 자료

https://zetawiki.com/wiki/%EB%B6%84%EB%A5%98:PostgreSQL

분류:PostgreSQL - 제타위키