백엔드 데이터 인프라 6편 — PostgreSQL 데이터베이스 접속·전환

이 글은 백엔드 데이터 인프라 시리즈 70편 중 6편이에요. 5편 CREATE DATABASE 에서 새 DB를 만들었으니, 이번 6편은 그 DB로 접속하고 다른 DB·다른 사용자로 자유롭게 전환 하는 흐름을 풀어 가요.

접속이 복잡하게 느껴지는 이유

PG 접속은 "호스트·포트·사용자·DB·비밀번호·인증 방식" 6가지 변수의 조합이에요. 어떤 게 기본값이고 어떤 게 명시 필수인지가 헷갈리죠.

이 글은 3가지 시나리오로 풀어요. 첫째는 가장 단순한 로컬 개발 PC, 둘째는 psql 안에서 \c 메타 명령으로 이동하는 경우, 셋째는 명령행에서 매번 옵션·URI·환경 변수를 박는 경우예요.

시나리오 1 — 로컬 PC 단순 접속

2편 설치 방법별로 정리해 볼게요.

Docker

docker exec -it postgres-dev psql -U postgres

macOS Homebrew

psql -d postgres        # 현재 macOS 사용자 = postgres 슈퍼유저

Ubuntu apt

sudo -u postgres psql   # OS의 postgres 사용자로 SWITCH

명시적 형태

psql -h localhost -p 5432 -U appuser -d myappdb -W

• -h = host (기본 = /var/run/postgresql Unix 소켓(파일 기반 로컬 통신) 또는 localhost)
• -p = port (기본 = 5432)
• -U = user (기본 = OS 현재 사용자명)
• -d = database (기본 = user 이름과 같은 DB)
• -W = 비밀번호 프롬프트 강제

시나리오 2 — psql 안에서 전환

이미 psql에 들어와 있는 상태예요.

DB 전환 — \c <DB>

postgres=# \c myappdb
You are now connected to database "myappdb" as user "postgres".
myappdb=#

프롬프트가 바뀝니다. 새 backend process(서버가 클라이언트마다 띄우는 처리 프로세스)가 fork(프로세스 복제 생성)되면서 기존 트랜잭션은 자동 종료돼요.

사용자 전환 — \c <DB> <user>

postgres=# \c myappdb appuser
Password for user appuser:
You are now connected to database "myappdb" as user "appuser".
myappdb=>

프롬프트 끝이 # → > 로 바뀌어요 (슈퍼유저 → 일반 사용자).

같은 DB 다른 사용자

myappdb=# \c - appuser    -- '-' 는 "현재 DB 유지"

시나리오 3 — 명령행 다양한 표현

같은 접속을 표현하는 4가지 방법이에요.

(1) 옵션 나열

psql -h db.example.com -p 5432 -U appuser -d myappdb

(2) Connection URI (권장)

psql "postgresql://appuser@db.example.com:5432/myappdb"

URI(접속 정보를 한 줄 문자열로 표현하는 형식)는 호스트·포트·사용자·DB를 한 줄에 다 담아요. 스크립트나 CI 환경에서 쓰기 아주 편해요.

비밀번호 포함하면 이렇게 돼요.

psql "postgresql://appuser:strongpass@db.example.com:5432/myappdb"

URL-encoded(URL에서 못 쓰는 문자를 %XX로 바꿔 표기) 특수문자도 가능해요.

psql "postgresql://appuser:p%40ss@db.example.com/myappdb"   # @는 %40

(3) keyword=value 형태

psql "host=db.example.com port=5432 user=appuser dbname=myappdb password=strongpass"

(4) 환경 변수

export PGHOST=db.example.com
export PGPORT=5432
export PGUSER=appuser
export PGDATABASE=myappdb
export PGPASSWORD=strongpass

psql   # 모든 변수 자동 사용

운영 스크립트에서 가장 흔히 봐요. 다만 PGPASSWORD를 평문으로 박는 건 위험해서 .pgpass 파일을 쓰는 게 더 안전해요.

.pgpass — 비밀번호 안전 저장

홈 디렉토리 ~/.pgpass 에 박아 두면 psql이 자동으로 읽어요.

# hostname:port:database:username:password
db.example.com:5432:myappdb:appuser:strongpass
*:5432:*:dev_*:dev_password           # 와일드카드 가능

권한은 chmod 600 ~/.pgpass (다른 사람이 못 읽게). 권한을 안 박으면 psql이 그냥 무시해요.

.psqlrc·.pgpass 한 세트

운영 서버 첫 셋업은 이 두 파일을 박는 데서 시작해요.

# ~/.psqlrc
\timing on
\set HISTSIZE 5000
\pset null '[NULL]'
\set PROMPT1 '%n@%~%R%# '

# ~/.pgpass
db1.example.com:5432:*:*:pass1
db2.example.com:5432:*:*:pass2

외부 서버 접속 시 — pg_hba.conf 통과해야

3편 아키텍처 의 인증 흐름을 떠올려 보세요. 외부 클라이언트는 서버의 pg_hba.conf 룰에서 "허용된 IP" 와 "허용된 인증 방식" 을 둘 다 만족할 때만 접속돼요.

# pg_hba.conf 한 줄 예
host    myappdb     appuser     192.168.1.0/24    scram-sha-256

TYPE   DATABASE   USER       ADDRESS          METHOD
호스트  DB 이름    사용자      허용 IP CIDR     인증 방식

운영 서버에 접속이 안 되면 첫 의심은 pg_hba.conf예요. 두 번째 의심은 listen_addresses (postgresql.conf 에서 localhost 만 listen하면 외부 접속이 차단돼요).

인증 방식 5가지

신규는 scram-sha-256 무조건이에요. md5는 deprecated(곧 제거 예정) 상태예요.

Spring Boot 연결

자바 백엔드 입문 13편 application.yml + Profiles 패턴 그대로예요.

# application-local.yml
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/myappdb
    username: appuser
    password: ${DB_PASSWORD:devpass}

# application-prod.yml
spring:
  datasource:
    url: jdbc:postgresql://${DB_HOST}:${DB_PORT:5432}/${DB_NAME}
    username: ${DB_USER}
    password: ${DB_PASSWORD}     # AWS Secrets Manager 같은 비밀 관리에서 주입

운영 환경은 환경 변수 + Secrets Manager 조합이에요. 비밀번호 평문은 절대 금지.

함정 5가지

(1) psql 단독 = 현재 OS 사용자명으로 시도

psql        # OS 사용자명과 같은 PG 사용자·DB가 없으면 실패
psql -U postgres -d postgres   # 명시

(2) localhost vs Unix 소켓

psql                          # Unix 소켓 (Linux 기본)
psql -h localhost             # TCP localhost — pg_hba.conf 다른 룰 적용 가능

같은 "localhost" 라도 둘이 다르게 인증될 수 있어요.

(3) URI 안 특수문자

psql "postgresql://appuser:p@ssword@host/db"   # ❌ @ 가 비밀번호로 안 잡힘
psql "postgresql://appuser:p%40ssword@host/db" # ✅ URL-encoded

(4) PGPASSWORD 환경 변수 노출

ps aux | grep PGPASSWORD     # 다른 사용자가 볼 수 있음

.pgpass 또는 --password-file 을 쓰세요.

(5) \c - 의 -

- 는 "현재 값 유지" 라는 뜻이에요. \c - appuser = "DB 유지, 사용자만 변경".

\c mydb -                 -- 사용자 유지, DB 변경
\c - appuser              -- DB 유지, 사용자 변경
\c mydb appuser           -- 둘 다 변경

한 줄 정리 — psql 접속 = -h -p -U -d -W 5가지 옵션 또는 connection URI. psql 안에서는 \c db user. 운영 = .pgpass + scram-sha-256 + pg_hba.conf 화이트리스트. Spring은 환경 변수 + Secrets Manager.

시험 직전 한 번 더 — PostgreSQL 접속 입문자가 매번 헷갈리는 것

• 5가지 접속 옵션 = -h(host)·-p(port)·-U(user)·-d(database)·-W(prompt)
• 기본값 = 호스트 Unix 소켓, 포트 5432, 사용자 = OS 사용자명, DB = 사용자명
• \c <DB> = psql 안에서 DB 변경
• \c <DB> <user> = DB + 사용자
• \c - = 현재 값 유지 (와일드카드)
• Connection URI = postgresql://user:pass@host:port/db (운영 권장)
• keyword=value = host=... user=... dbname=...
• 환경 변수 = PGHOST·PGPORT·PGUSER·PGDATABASE·PGPASSWORD
• .pgpass = 비밀번호 안전 저장 (chmod 600 필수)
• pg_hba.conf = 인증 룰 (운영 접속 차단의 첫 의심처)
• listen_addresses = postgresql.conf 에서 외부 접속 허용
• 인증 방식 = scram-sha-256 무조건 (md5 deprecated)
• trust = 개발만, 운영 X
• peer = OS 사용자 매칭 (Linux 로컬)
• 평문 PGPASSWORD = ps aux 노출 — 위험
• URI 특수문자 = URL-encoded (@ → %40)
• localhost vs Unix 소켓 = pg_hba.conf 룰 다를 수 있음
• Spring jdbc:postgresql://host:port/db
• 운영 = 환경 변수 + Secrets Manager 조합
• --single-transaction = 파일 전체를 한 트랜잭션
• \conninfo = 현재 접속 정보 확인
• \password = psql 안에서 비밀번호 변경

시리즈 다른 편 (앞뒤 글 모음)

이전 글:

• 1편 — PostgreSQL이란 + MySQL과의 비교
• 2편 — PostgreSQL 설치 (Docker·brew·apt 3가지)
• 3편 — PostgreSQL 아키텍처 (클라이언트·서버·DB·테이블)
• 4편 — psql 첫 접속과 기본 명령
• 5편 — 데이터베이스 만들기 CREATE DATABASE

다음 글:

• 7편 — PostgreSQL 관계형 모델 핵심 개념
• 8편 — SQL 기초 5가지 동사
• 9편 — CREATE TABLE 첫 테이블 만들기
• 10편 — INSERT 데이터 입력 표준 패턴
• 11편 — SELECT 데이터 조회 표준 패턴