1. PostgreSQL 설치

서버 기본설정

서버 기본환경 설정을 선행한다.

PostgreSQL 설치

PostgreSQL Repository 세팅

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

패키지 확인 & 설치

sudo apt show postgresql-16
sudo apt install postgresql-16

테스트 및 패스워드 초기화

sudo passwd postgres
sudo -i -u postgres
psql
 
postgres=# select 1;
postgres=# ALTER USER postgres with PASSWORD '비밀번호';
postgres=# \q
 
su ubuntu

외부접속 설정

파일 세팅

pg_hba.conf 수정

sudo vi /etc/postgresql/16/main/pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                peer
 
# TYPE  DATABASE        USER            ADDRESS                 METHOD
 
# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
#host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            scram-sha-256
#host    replication     all             ::1/128                 scram-sha-256

postgresql.conf 수정

sudo vi /etc/postgresql/16/main/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
 
# - Connection Settings -
listen_addresses = '*'
#listen_addresses = 'localhost'         # what IP address(es) to listen on;

서비스 재시작 및 확인

sudo systemctl restart postgresql
sudo systemctl status postgresql

방화벽 세팅

sudo apt install firewalld
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-all

iptables 세팅

sudo iptables -I INPUT -p tcp --dport 5432 -j ACCEPT

서버 재부팅