데이터베이스 · 설치·설정 레시피

PostgreSQL 설치와 제한된 원격 연결

Ubuntu 지원 패키지로 PostgreSQL을 설치하고, 전용 역할·데이터베이스·listen_addresses·pg_hba.conf를 최소 범위로 설정한 뒤 로컬과 사설망 연결을 검증합니다.

PostgreSQL 설치pg_hba.conf5432 포트SCRAM원격 접속

지원 환경Ubuntu Server 24.04 LTS 기본 PostgreSQL 패키지

예상 시간약 40분

난이도중급

검토일2026-08-28

BEFORE YOU START

시작 전에 준비하세요

sudo 권한과 유지보수 가능한 SSH 접속

DB 서버 사설 IP와 접근을 허용할 애플리케이션 서버 CIDR

생성할 데이터베이스·역할 이름과 비밀번호 보관 수단

기존 클러스터가 있다면 논리·물리 백업 및 복원 시험 결과

권장 대상 사설망에서 애플리케이션 DB를 운영하는 개발자·운영자

FOLLOW THE RECIPE

8단계 구성·점검 레시피

확인시스템을 변경하지 않는 점검 단계

지원 환경과 설치 방식 결정

Ubuntu 기본 저장소는 배포판 수명 동안 특정 PostgreSQL 주 버전을 유지합니다. 특정 주 버전이 꼭 필요한 경우에만 PostgreSQL 공식 PGDG 저장소를 별도 변경으로 검토합니다.

운영체제 확인

cat /etc/os-release

아키텍처 확인

dpkg --print-architecture

기본 패키지 후보 확인

apt-cache policy postgresql

호스트 사설 주소 확인

ip -brief address

Ubuntu LTS 지원 기간과 필요한 PostgreSQL 주 버전을 확인했다.
기본 저장소와 PGDG 저장소를 무심코 혼용하지 않는다.
DB 서버와 애플리케이션 서버 사이의 사설망 경로를 확인했다.

결과 읽기

기능 요구보다 장기 패치와 운영 단순성이 중요하면 Ubuntu 기본 패키지가 안전한 출발점입니다. 확장 모듈의 주 버전 호환성도 함께 확인합니다.

다음 판단

기존 클러스터·포트·용량을 점검합니다.

확인시스템을 변경하지 않는 점검 단계

기존 클러스터와 5432 포트 점검

기존 PostgreSQL을 새 클러스터로 오인하지 않도록 패키지·클러스터·포트·디스크를 확인합니다.

설치 패키지 확인

dpkg -l | grep '^ii  postgresql'

클러스터 목록 확인

pg_lsclusters

5432 포트 확인

sudo ss -ltnp | grep ':5432 '

데이터 경로 용량 확인

df -h /var/lib/postgresql

서비스 상태 확인

systemctl status postgresql --no-pager

기존 클러스터의 주 버전·포트·상태·소유 팀을 확인했다.
5432 포트 충돌이 없다.
데이터와 WAL 증가를 감당할 여유 공간이 있다.

결과 읽기

pg_lsclusters에 online 클러스터가 있으면 이미 서비스 중일 수 있습니다. 새 설치보다 변경·마이그레이션 절차가 필요합니다.

다음 판단

충돌이 없으면 Ubuntu 패키지를 설치합니다.

변경패키지·설정·서비스 상태가 달라지는 단계

PostgreSQL 서버 설치

Ubuntu 기본 postgresql 메타패키지를 설치합니다. 메타패키지는 해당 Ubuntu가 지원하는 서버 주 버전을 선택합니다.

변경 단계입니다. 대상 서버, 백업 파일, 서비스 중단 영향과 바로 이전 상태로 돌아가는 방법을 다시 확인하세요.

패키지 인덱스 갱신

sudo apt update

변경사항 미리 보기

apt-get --simulate install postgresql

PostgreSQL 설치

sudo apt-get install -y postgresql

클러스터 확인

pg_lsclusters

서버 버전 확인

sudo -u postgres psql -Atc "SHOW server_version;"

APT가 의도하지 않은 DB 패키지를 제거하지 않는다.
기본 클러스터가 online 상태다.
설치된 주 버전을 애플리케이션 드라이버가 지원한다.

결과 읽기

클러스터가 down이면 바로 재생성하지 말고 journalctl과 pg_lsclusters의 상태를 먼저 확인합니다.

다음 판단

전용 역할·DB와 원격 접속 범위를 설정합니다.

주의권한·연결·중단 영향을 확인할 단계

전용 역할·DB와 허용 CIDR 설정

postgres 슈퍼유저를 애플리케이션에 사용하지 않습니다. 설정 경로를 조회하고 작업마다 고유한 <BACKUP_SUFFIX>로 백업한 뒤, 사설 IP와 특정 DB·사용자·CIDR만 SCRAM 인증으로 허용합니다.

주 설정 파일 경로 확인

sudo -u postgres psql -Atc "SHOW config_file;"

접근 제어 파일 경로 확인

sudo -u postgres psql -Atc "SHOW hba_file;"

고유 백업 접미사 생성

date -u +%Y%m%dT%H%M%SZ

출력값을 이번 작업의 <BACKUP_SUFFIX>로 사용하고 변경 기록에 남깁니다. 관련 설정 백업에는 모두 같은 값을 사용합니다.

postgresql.conf 백업

sudo cp --preserve=all --no-clobber /etc/postgresql/<MAJOR>/main/postgresql.conf /etc/postgresql/<MAJOR>/main/postgresql.conf.<BACKUP_SUFFIX>.bak

<MAJOR>와 <BACKUP_SUFFIX>를 확인된 값으로 교체합니다. 대상이 이미 있으면 덮어쓰지 말고 새 접미사를 만듭니다.

pg_hba.conf 백업

sudo cp --preserve=all --no-clobber /etc/postgresql/<MAJOR>/main/pg_hba.conf /etc/postgresql/<MAJOR>/main/pg_hba.conf.<BACKUP_SUFFIX>.bak

postgresql.conf 백업과 같은 <BACKUP_SUFFIX>를 사용합니다.

postgresql.conf 편집

sudoedit /etc/postgresql/<MAJOR>/main/postgresql.conf

<MAJOR>는 SHOW config_file 결과의 실제 주 버전으로 교체합니다.

pg_hba.conf 편집

sudoedit /etc/postgresql/<MAJOR>/main/pg_hba.conf

<APP_SERVER_CIDR>를 실제 단일 호스트 또는 승인한 사설망 CIDR로 교체하고 규칙 순서를 확인합니다.

설정 예시 · /etc/postgresql/<MAJOR>/main/postgresql.conf

서버 사설 IP만 수신

listen_addresses = '127.0.0.1,<DB_PRIVATE_IP>'
password_encryption = 'scram-sha-256'

<DB_PRIVATE_IP>는 이 서버에 실제로 할당된 사설 IP로 교체합니다. 모든 인터페이스 수신을 기본값으로 사용하지 않습니다.

설정 예시 · /etc/postgresql/<MAJOR>/main/pg_hba.conf

특정 애플리케이션만 SCRAM 인증 허용

# TYPE  DATABASE  USER      ADDRESS              METHOD
host    app_db    app_user  <APP_SERVER_CIDR>    scram-sha-256

<APP_SERVER_CIDR>는 단일 호스트라면 /32, IPv6 단일 호스트라면 /128처럼 가능한 한 좁게 지정합니다. pg_hba.conf는 위에서 처음 일치한 규칙이 적용됩니다.

두 설정 파일의 원본 백업이 있다.
listen_addresses가 실제 사설 IP와 루프백으로 제한된다.
pg_hba.conf가 특정 DB·역할·CIDR만 허용한다.
app_owner는 NOLOGIN, migration_user는 배포 전용, app_user는 DML 런타임 전용으로 분리할 이름과 정책을 확정했다.
trust, 전체 IPv4 대역 허용, 평문 password 인증을 사용하지 않는다.

결과 읽기

listen_addresses 변경은 재시작이 필요합니다. pg_hba.conf 규칙 순서가 넓은 기존 규칙에 가려지지 않는지 반드시 검토합니다.

다음 판단

구문 오류를 검사하고 서비스를 재시작합니다.

변경패키지·설정·서비스 상태가 달라지는 단계

설정 검사 후 서비스 재시작

pg_hba_file_rules에서 오류를 확인한 뒤 재시작하고 password_encryption이 SCRAM인지 검증합니다. 그 다음에만 소유자·마이그레이션·런타임 역할과 비밀번호를 생성합니다.

변경 단계입니다. 대상 서버, 백업 파일, 서비스 중단 영향과 바로 이전 상태로 돌아가는 방법을 다시 확인하세요.

pg_hba.conf 오류 확인

sudo -u postgres psql -x -c "SELECT line_number,type,database,user_name,address,auth_method,error FROM pg_hba_file_rules WHERE error IS NOT NULL;"

행이 반환되지 않는 것이 기대 결과입니다.

부팅 자동 시작과 재시작

sudo systemctl enable postgresql

변경 설정 적용

sudo systemctl restart postgresql

SCRAM 비밀번호 저장 형식 확인

sudo -u postgres psql -Atc "SHOW password_encryption;"

scram-sha-256이 출력된 뒤에만 로그인 역할 비밀번호를 생성합니다.

로그인 불가 소유자 역할 생성

sudo -u postgres createuser --no-login --no-superuser --no-createdb --no-createrole app_owner

마이그레이션 로그인 역할 생성

sudo -u postgres createuser --login --pwprompt --no-superuser --no-createdb --no-createrole migration_user

SCRAM 적용 확인 후 비밀번호를 프롬프트에서 입력하고 배포용 비밀 저장소에만 보관합니다.

소유자 역할을 마이그레이션 역할에 부여

sudo -u postgres psql -c "GRANT app_owner TO migration_user;"

런타임 로그인 역할 생성

sudo -u postgres createuser --login --pwprompt --no-superuser --no-createdb --no-createrole app_user

SCRAM 적용 확인 후 비밀번호를 프롬프트에서 입력하고 애플리케이션 비밀 저장소에만 보관합니다.

로그인 불가 역할 소유의 DB 생성

sudo -u postgres createdb --owner=app_owner app_db

기존 DB 권한 스크립트 백업

sudo cp --preserve=all --no-clobber /etc/postgresql/app_db-privileges.sql /etc/postgresql/app_db-privileges.sql.<BACKUP_SUFFIX>.bak

권한 스크립트가 이미 있을 때만 실행하고 configure 단계에서 기록한 같은 <BACKUP_SUFFIX>를 사용합니다.

DB 권한 스크립트 생성 또는 보존

sudo touch /etc/postgresql/app_db-privileges.sql

touch는 기존 내용을 지우지 않습니다.

DB 권한 스크립트 소유권 지정

sudo chown root:postgres /etc/postgresql/app_db-privileges.sql

DB 권한 스크립트 권한 제한

sudo chmod 640 /etc/postgresql/app_db-privileges.sql

DB 권한 스크립트 편집

sudoedit /etc/postgresql/app_db-privileges.sql

아래 예시를 입력하고 역할·DB·스키마 이름을 실제 환경과 대조합니다.

DB 권한 스크립트 오류 시 중단 적용

sudo -u postgres psql --set=ON_ERROR_STOP=1 -d app_db -f /etc/postgresql/app_db-privileges.sql

서비스 상태 확인

systemctl status postgresql --no-pager

실제 클러스터 유닛 로그 확인

sudo journalctl -u postgresql@<MAJOR>-main -n 100 --no-pager

<MAJOR>를 pg_lsclusters에서 확인한 실제 주 버전으로 교체합니다. postgresql.service 메타 유닛만 보지 않습니다.

클러스터 로그 파일 확인

sudo tail -n 100 /var/log/postgresql/postgresql-<MAJOR>-main.log

패키지 기본 클러스터 이름이 main이 아닐 때는 pg_lsclusters와 설정에서 실제 이름·로그 경로를 확인해 교체합니다.

설정 예시 · /etc/postgresql/app_db-privileges.sql

소유자·마이그레이션·런타임 역할 분리

REVOKE CONNECT ON DATABASE app_db FROM PUBLIC;
GRANT CONNECT ON DATABASE app_db TO migration_user, app_user;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE, CREATE ON SCHEMA public TO app_owner;
GRANT USAGE ON SCHEMA public TO app_user;
ALTER ROLE migration_user IN DATABASE app_db SET role TO 'app_owner';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO app_user;

app_owner의 public 스키마 CREATE 권한을 명시해 PostgreSQL 14와 15+의 기본 스키마 소유권 차이에도 대응합니다. migration_user는 배포 때만 사용하고 자동 SET ROLE로 app_owner 소유 객체를 만듭니다. app_user에는 DDL·소유권을 주지 않으며, 기존 객체가 있으면 ALL TABLES·SEQUENCES GRANT를 다시 검증합니다.

pg_hba_file_rules에 오류가 없다.
postgresql 서비스가 active 상태다.
password_encryption이 scram-sha-256인 상태에서 로그인 역할 비밀번호를 만들었다.
app_owner·migration_user·app_user 역할과 권한을 분리했다.
로그에 주소 바인딩 또는 인증 설정 오류가 없다.

결과 읽기

Address already in use는 포트 충돌, Cannot assign requested address는 서버에 없는 IP를 지정했을 때 주로 발생합니다.

다음 판단

로컬 준비 상태와 실제 원격 로그인을 검증합니다.

확인시스템을 변경하지 않는 점검 단계

로컬·원격 연결과 재부팅 검증

서버 준비 상태, 리스닝 주소, 로컬 쿼리, 허용된 애플리케이션 호스트의 로그인을 각각 확인합니다.

서버 준비 상태 확인

pg_isready -h 127.0.0.1 -p 5432

리스닝 주소 확인

sudo ss -ltnp | grep ':5432 '

로컬 쿼리 확인

sudo -u postgres psql -d postgres -c "SELECT current_timestamp, version();"

허용 호스트에서 원격 로그인

psql -h <DB_PRIVATE_IP> -U app_user -d app_db -W

애플리케이션 서버에서 실행하고 비밀번호는 프롬프트로 입력합니다.

부팅 자동 시작 확인

systemctl is-enabled postgresql

pg_isready가 accepting connections를 반환한다.
5432가 루프백과 지정한 사설 IP에만 열린다.
허용 CIDR에서는 app_user가 app_db에 로그인된다.
허용하지 않은 호스트에서는 연결이 차단된다.
유지보수 재부팅 후 같은 검증을 반복했다.

결과 읽기

Connection refused는 서비스·리스닝 주소 문제, timeout은 네트워크 경계 문제, no pg_hba.conf entry는 접근 규칙 문제, password authentication failed는 계정·비밀번호 문제로 나눠 봅니다.

다음 판단

방화벽·암호화·권한을 최종 확인합니다.

주의권한·연결·중단 영향을 확인할 단계

네트워크·SCRAM·권한 최종 점검

DB 포트는 공개 인터넷에 노출하지 않습니다. 사설망에서도 서버 방화벽을 좁게 열고, 신뢰할 수 없는 구간은 TLS 또는 터널을 사용합니다.

현재 인증 설정 확인

sudo -u postgres psql -Atc "SHOW password_encryption;"

수신 주소 확인

sudo -u postgres psql -Atc "SHOW listen_addresses;"

방화벽 상태 확인

sudo ufw status numbered

UFW가 활성일 때 승인 CIDR만 허용

sudo ufw allow from <APP_SERVER_CIDR> to any port 5432 proto tcp

기존 SSH 허용 규칙을 확인한 뒤, UFW를 실제로 사용하는 서버에서만 실행합니다.

5432는 공인 인터넷에서 접근할 수 없다.
pg_hba.conf에 trust와 불필요하게 넓은 CIDR이 없다.
애플리케이션은 슈퍼유저가 아닌 전용 역할을 사용한다.
SCRAM-SHA-256을 사용하고 비밀번호를 코드·Git·명령줄에 남기지 않는다.
신뢰할 수 없는 네트워크에서는 TLS(hostssl) 또는 SSH/VPN 터널을 적용한다.
백업 파일과 데이터 디렉터리 접근 권한을 제한한다.

결과 읽기

방화벽 허용만으로 암호화가 생기지는 않습니다. 사설망을 벗어나는 연결은 서버 인증서를 포함한 TLS 구성을 별도 검증합니다.

다음 판단

롤백 경로와 장애 가이드를 운영 기록에 연결합니다.

주의권한·연결·중단 영향을 확인할 단계

원격 설정 되돌리기와 안전한 제거

원격 연결 변경이 실패하면 백업 목록과 변경 기록을 대조해 같은 <BACKUP_SUFFIX>의 설정 두 개를 명시적으로 선택하고 로컬 연결부터 회복합니다. 최신 파일을 자동 선택하지 않으며 데이터 디렉터리를 삭제하는 purge 명령은 제공하지 않습니다.

postgresql.conf 백업 후보 확인

sudo find /etc/postgresql/<MAJOR>/main -maxdepth 1 -type f -name 'postgresql.conf.*.bak' -printf '%TY-%Tm-%Td %TH:%TM:%TS %p\n' | sort

변경 기록·시각·내용을 대조해 복원할 한 개의 <BACKUP_SUFFIX>를 고릅니다.

pg_hba.conf 백업 후보 확인

sudo find /etc/postgresql/<MAJOR>/main -maxdepth 1 -type f -name 'pg_hba.conf.*.bak' -printf '%TY-%Tm-%Td %TH:%TM:%TS %p\n' | sort

postgresql.conf와 같은 <BACKUP_SUFFIX>의 백업을 선택합니다.

선택한 주 설정 백업 확인

sudo test -f /etc/postgresql/<MAJOR>/main/postgresql.conf.<BACKUP_SUFFIX>.bak

<MAJOR>와 <BACKUP_SUFFIX>를 명시적으로 선택한 값으로 교체합니다.

선택한 접근 제어 백업 확인

sudo test -f /etc/postgresql/<MAJOR>/main/pg_hba.conf.<BACKUP_SUFFIX>.bak

두 test 명령이 모두 성공한 뒤에만 복원합니다.

주 설정 복원

sudo cp --preserve=all /etc/postgresql/<MAJOR>/main/postgresql.conf.<BACKUP_SUFFIX>.bak /etc/postgresql/<MAJOR>/main/postgresql.conf

접근 제어 설정 복원

sudo cp --preserve=all /etc/postgresql/<MAJOR>/main/pg_hba.conf.<BACKUP_SUFFIX>.bak /etc/postgresql/<MAJOR>/main/pg_hba.conf

복원 설정 적용

sudo systemctl restart postgresql

추가한 방화벽 규칙 제거

sudo ufw delete allow from <APP_SERVER_CIDR> to any port 5432 proto tcp

완전한 신규 설치일 때 패키지만 제거

sudo apt-get remove postgresql

클러스터와 데이터 보존을 먼저 확인하고 purge 또는 데이터 디렉터리 삭제는 하지 않습니다.

복원 전 현재 설정도 별도로 보관했다.
백업 후보 목록과 변경 기록을 대조해 같은 <BACKUP_SUFFIX>의 설정 쌍을 선택했다.
선택한 두 백업의 존재와 내용을 확인한 뒤에만 복원했다.
로컬 pg_isready와 핵심 쿼리가 정상화되었다.
방화벽 규칙 번호와 다른 서비스 영향도를 확인했다.
패키지 제거 전 백업·복원 책임자 승인을 받았다.

결과 읽기

설정 복원 후에도 시작하지 않으면 클러스터 상태와 로그를 우선 확인합니다. 데이터를 새로 초기화하는 명령은 복구가 아니라 파괴적 재구성이므로 실행하지 않습니다.

다음 판단

연결 수·락·장기 쿼리 문제가 있으면 PostgreSQL 장애 대응 가이드로 이동합니다.

SECURITY CHECK

운영 전 마지막 보안 점검

5432를 공인 인터넷에 직접 공개하지 않는다.
listen_addresses와 방화벽을 승인한 사설 IP·CIDR로 제한한다.
pg_hba.conf에서 특정 DB·역할·CIDR과 scram-sha-256을 사용한다.
애플리케이션에 postgres 슈퍼유저를 제공하지 않는다.
NOLOGIN 소유자·배포 전용 migration_user·DML 전용 app_user를 분리한다.
원격 구간의 신뢰 수준에 따라 TLS·VPN·SSH 터널을 적용한다.

COMMON ERRORS

자주 막히는 지점

no pg_hba.conf entry 오류

증상: 서버까지 연결되지만 요청한 호스트·사용자·DB에 맞는 pg_hba.conf 규칙이 없다고 표시됩니다.
가능한 원인: CIDR, DB 이름, 역할 또는 규칙 순서가 실제 요청과 맞지 않습니다.
확인 순서: 접속 원본 IP와 pg_hba_file_rules를 확인하고 필요한 단일 규칙만 추가한 뒤 재로드·재검증합니다.

too many clients 오류

증상: 새 연결이 거부되고 애플리케이션에서 남은 연결 슬롯 오류가 발생합니다.
가능한 원인: 연결 누수, 과도한 풀 크기 또는 장기 유휴 세션으로 max_connections가 소진됐습니다.
확인 순서: 즉시 max_connections만 올리지 말고 세션 상태와 풀 설정을 먼저 분석합니다.

트러블슈팅으로 이어보기

재시작 후 포트가 열리지 않음

증상: 서비스 재시작 뒤 5432가 리스닝되지 않거나 클러스터가 down입니다.
가능한 원인: 존재하지 않는 listen_addresses, 포트 충돌 또는 설정 문법 오류가 흔합니다.
확인 순서: journalctl, pg_lsclusters, ss 순서로 확인하고 백업 설정으로 복원합니다.

트러블슈팅으로 이어보기

PRIMARY REFERENCES

공식 문서

설치 저장소와 지원 버전은 바뀔 수 있습니다. 검토일 이후에는 링크된 공식 문서와 현재 서버의 패키지 후보 버전을 함께 확인하세요.

시작 전에 준비하세요

8단계 구성·점검 레시피

운영 전 마지막 보안 점검

자주 막히는 지점

no pg_hba.conf entry 오류

too many clients 오류

재시작 후 포트가 열리지 않음

공식 문서

설치 후 문제라면 여기서 이어가세요

함께 볼 구성 레시피