제7부 미들웨어·데이터 계층

7.3 PostgreSQL 설치와 활용

7.3.1 설치 — Debian 표준 절차

UNO Q의 MPU는 Debian(Trixie, 3.1)을 구동하므로 PostgreSQL은 여느 Debian 서버와 동일하게 apt로 설치한다. 별도 저장소를 추가할 필요 없이 Debian 기본 저장소만으로 충분하다.

sudo apt update
sudo apt install -y postgresql

Debian 13(Trixie) 기본 저장소는 PostgreSQL 17을 담고 있다(집필 시점 확인 — Debian 릴리스 주기에 따라 이후 버전으로 바뀔 수 있으니 실제 설치되는 버전은 아래 명령으로 재확인). 설치가 끝나면 postgresql 서비스가 자동으로 기동되고 로컬 클러스터가 하나 만들어진다.

sudo systemctl status postgresql
psql --version

최신 버전을 직접 지정해 쓰고 싶다면(Debian 기본 버전보다 앞선 릴리스가 필요한 드문 경우) PGDG(PostgreSQL 공식 apt 저장소)를 추가하는 방법도 있지만, UNO Q처럼 자원이 제한된 보드에서는 Debian 기본 패키지를 그대로 쓰는 편을 권한다 — 배포판이 검증한 조합이라 의존성 충돌 위험이 낮다.

7.3.2 기본 설정 — 계정 생성과 접속

Debian 패키지는 설치 시 postgres라는 OS 계정 겸 DB 슈퍼유저를 만든다. 이 계정을 거쳐 애플리케이션용 역할(role)과 데이터베이스를 새로 만드는 것이 관례다.

# postgres 시스템 계정으로 전환해 psql 접속
sudo -u postgres psql

# psql 안에서 버전 확인
postgres=# SELECT version();

애플리케이션용 사용자·DB를 별도로 만든다(대화형으로 비밀번호까지 설정):

sudo -u postgres createuser --interactive --pwprompt sensoruser
sudo -u postgres createdb sensordb -O sensoruser

이제 그 사용자로 접속할 수 있다.

psql -h localhost -U sensoruser -d sensordb

보드 밖(같은 네트워크의 PC 등)에서 접속하려면 두 파일을 손봐야 한다 — postgresql.conf에서 listen_addresses를 '*'(또는 특정 IP)로 열고, pg_hba.conf에 접속을 허용할 네트워크 대역을 추가한다. Debian 패키지 레이아웃에서는 보통 /etc/postgresql/17/main/(버전 번호는 실제 설치된 버전에 맞춰) 아래에 있다.

sudo nano /etc/postgresql/17/main/postgresql.conf
# listen_addresses = '*'

sudo nano /etc/postgresql/17/main/pg_hba.conf
# host  sensordb  sensoruser  192.168.0.0/24  scram-sha-256

sudo systemctl restart postgresql

⚠️ 네트워크에 열어둔 채 방치하지 않는다. UNO Q가 가정·현장에 상시 연결된 IoT 게이트웨이로 쓰이는 경우가 많은데, 기본 비밀번호·전체 대역 허용(0.0.0.0/0) 상태로 두면 그대로 노출된다. 필요한 대역만 좁혀 허용하고 방화벽(5.1·5.2 참조)도 함께 점검한다.

7.3.3 임베디드 보드에서의 리소스 고려사항

PostgreSQL은 원래 서버급 메모리를 가정해 튜닝값이 정해지는 소프트웨어다. UNO Q(2GB 또는 4GB RAM)에서는 Debian 전체·App Lab·다른 워크로드와 메모리를 나눠 써야 하므로 기본값을 그대로 두기보다 아래 값들을 점검한다. 설정 파일은 /etc/postgresql/<버전>/main/postgresql.conf.

shared_buffers를 15~25% 범위로 설정하는 것은 PostgreSQL 커뮤니티에서 널리 통용되는 경험칙이며, PostgreSQL은 자체 공유 버퍼와 OS의 버퍼드 I/O를 동시에 쓰기 때문에 같은 데이터가 메모리에 두 번 적재되는 이중 버퍼링 현상이 생긴다 — RAM이 2~4GB로 제한적인 UNO Q에서는 이 낭비가 상대적으로 더 크게 작용한다. 값을 높이기 전후로 반드시 free -h와 실제 쿼리 지연을 비교해 보는 것을 권한다.

eMMC 쓰기 마모도 고려사항. UNO Q의 주 저장소는 eMMC(16GB/32GB)로, SSD보다 쓰기 내구성이 낮고 흔히 마모 수명 지표도 공개돼 있지 않다(확인 필요). PostgreSQL은 WAL(Write-Ahead Log)과 체크포인트로 인해 쓰기 입출력이 잦은 편이다. 완화책 몇 가지를 소개한다 — 모두 내구성(durability)과 쓰기량의 트레이드오프이므로 데이터 중요도에 따라 선택한다.

• checkpoint_completion_target을 0.9 근처로 높여 체크포인트를 완만하게 분산시킨다(순간 쓰기 스파이크 완화).
• 실시간 알림용 임시 로그성 테이블처럼 유실돼도 무방한 데이터는 synchronous_commit = off로 커밋 시 강제 fsync를 줄일 수 있다 — 단, 전원이 갑자기 끊기면 최근 커밋 일부가 유실될 수 있다는 점을 감수해야 한다.
• 정말 쓰기가 많은 워크로드라면 데이터 디렉터리를 USB로 연결한 외장 SSD로 옮기는 것도 검토한다(eMMC는 부팅·OS 전용으로 남겨둠).

이 튜닝 값들은 일반적인 저사양 ARM SBC 튜닝 가이드(예: Raspberry Pi 대상 자료)에서 흔히 제시되는 범위를 UNO Q 사양에 맞춰 옮긴 것이며, UNO Q 자체를 대상으로 한 벤치마크 자료는 확인하지 못했다. 실제 배포 전에는 예상 워크로드(초당 insert 수, 동시 접속 수)로 직접 테스트해 보는 것을 권한다.

7.3.4 실습 — 센서 데이터 테이블

간단한 센서 로깅 스키마를 만들어 본다.

-- sensordb에 접속한 상태에서
CREATE TABLE sensor_readings (
    id          BIGSERIAL PRIMARY KEY,
    sensor_name TEXT NOT NULL,
    value       DOUBLE PRECISION NOT NULL,
    recorded_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_sensor_readings_name_time
    ON sensor_readings (sensor_name, recorded_at DESC);

값 삽입과 조회:

INSERT INTO sensor_readings (sensor_name, value) VALUES ('temp_c', 24.7);
INSERT INTO sensor_readings (sensor_name, value) VALUES ('humidity_pct', 58.2);

-- 최근 1시간 평균
SELECT sensor_name, avg(value) AS avg_value, count(*) AS n
FROM sensor_readings
WHERE recorded_at > now() - interval '1 hour'
GROUP BY sensor_name;

Python(psycopg 또는 psycopg2)에서 같은 작업:

import psycopg

with psycopg.connect(
    "host=localhost dbname=sensordb user=sensoruser password=..."
) as conn:
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO sensor_readings (sensor_name, value) VALUES (%s, %s)",
            ("temp_c", 24.7),
        )
        conn.commit()

        cur.execute(
            "SELECT sensor_name, avg(value) FROM sensor_readings "
            "WHERE recorded_at > now() - interval '1 hour' GROUP BY sensor_name"
        )
        for row in cur.fetchall():
            print(row)

psycopg는 sudo apt install python3-psycopg2 또는 pip install "psycopg[binary]"로 설치한다(App Lab의 Python 환경 관리는 4.2 참조).

7.3.5 PostgreSQL이 맞는 경우, 아닌 경우

• 맞는 경우: 여러 프로세스·기기(ROS 2 노드, 웹 대시보드, 외부 클라이언트)가 동시에 같은 데이터베이스에 접속해야 할 때, 스키마·제약조건·트랜잭션 무결성이 중요할 때, 데이터 양이 계속 늘어나 인덱싱·쿼리 최적화가 필요해질 때.
• 과할 수 있는 경우: 단일 프로세스가 로컬 파일 하나에 순차적으로 기록만 하면 되는 소규모 로깅, 부팅 즉시 최소 오버헤드로 시작해야 하는 상황. 이런 경우는 7.4(SQLite)이 더 알맞다 — 두 DB의 트레이드오프는 7.4.2에서 표로 정리한다.

7.3.6 참고자료

• PostgreSQL 공식 apt 저장소 안내
• PostgreSQL Wiki — Tuning Your PostgreSQL Server
• RustProof Labs — PostgreSQL on Raspberry Pi 4: Initial Testing
• 다음: 7.4(SQLite), 12.1(IoT 대시보드 실전 프로젝트)에서 이 두 DB를 함께 활용한다.

7.3.7 백업과 복구

7.3.4에서 만든 sensordb를 예로 논리 백업(logical backup) 절차를 정리한다. PostgreSQL 공식 도구인 pg_dump/pg_dumpall/pg_restore는 서버 규모와 무관하게 동일하게 동작하지만, UNO Q처럼 저장 공간이 eMMC 16GB/32GB로 제한된 보드(5.4)에서는 백업본을 어디에 얼마나 남길지가 서버급 환경보다 훨씬 민감한 문제다.

pg_dump로 데이터베이스 단위 백업. sensoruser 권한으로 sensordb 하나만 백업할 때는 커스텀 포맷(-Fc, 압축되고 pg_restore로 선택적 복구가 가능)을 권장한다.

mkdir -p ~/backups/postgresql

pg_dump -h localhost -U sensoruser -Fc sensordb \
    -f ~/backups/postgresql/sensordb_$(date +%Y%m%d).dump

postgres 슈퍼유저로 서버의 모든 DB를 관리한다면 sudo -u postgres로 실행하고, 저장 위치도 시스템 경로로 지정하는 편이 관리에 유리하다.

sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql

sudo -u postgres pg_dump -Fc sensordb \
    -f /var/backups/postgresql/sensordb_$(date +%Y%m%d).dump

pg_dumpall로 역할·전역 객체까지 포함한 백업. pg_dump는 DB 하나의 스키마·데이터만 담는다 — 사용자 계정(sensoruser 같은 role)이나 비밀번호, 클러스터 전역 설정은 제외된다. 보드 전체를 재설치해야 하는 상황까지 대비하려면 전역 객체도 별도로 받아 둔다.

# 역할·권한 등 전역 객체만 (용량이 작고 자주 받아도 부담 적음)
sudo -u postgres pg_dumpall --globals-only \
    -f /var/backups/postgresql/globals_$(date +%Y%m%d).sql

# 클러스터 전체(모든 DB + 전역 객체) — 텍스트라 용량이 커질 수 있다
sudo -u postgres pg_dumpall \
    -f /var/backups/postgresql/full_cluster_$(date +%Y%m%d).sql

pg_restore/psql로 복구. 커스텀 포맷 덤프는 pg_restore로, pg_dumpall이 만든 평문 SQL은 psql로 되돌린다. DB 자체가 없다면 7.3.2와 동일하게 역할·DB를 먼저 만든 뒤 데이터를 흘려 넣는다.

# 1) 역할 복구 (globals 덤프를 psql로 실행)
sudo -u postgres psql -f /var/backups/postgresql/globals_20260825.sql

# 2) 대상 DB 생성 (7.3.2와 동일한 방식)
sudo -u postgres createdb sensordb -O sensoruser

# 3) 커스텀 포맷 덤프 복구
sudo -u postgres pg_restore -d sensordb \
    /var/backups/postgresql/sensordb_20260825.dump

# 4) 복구 확인
psql -h localhost -U sensoruser -d sensordb \
    -c "SELECT count(*) FROM sensor_readings;"

⚠️ pg_dump/pg_dumpall은 실행 시점의 스냅숏만 만든다. 마지막 백업 이후의 변경분은 복구 대상에서 제외된다 — 이 차이를 줄이려면 백업 주기를 좁히거나(아래 스케줄링 참고), 유실을 감수할 수 없는 데이터라면 WAL 아카이빙 기반 PITR(Point-In-Time Recovery)을 검토해야 한다. 다만 PITR은 운영 복잡도가 상당히 높아지는 기능이라 이 장에서는 다루지 않는다 — UNO Q 규모의 프로젝트에는 대개 과한 선택일 가능성이 높다(확인 필요).

로컬 백업 vs 원격(PC/NAS) 반출 — eMMC 여유 공간과의 트레이드오프

5.4에서 다뤘듯 UNO Q의 eMMC는 여유 공간이 충분하지 않다. 백업 파일을 보드 안에만 계속 쌓으면 ① eMMC 공간을 압박하고 ② 정작 보드가 고장 났을 때(eMMC 손상) 백업본까지 함께 사라지는 이중 위험이 있다. 다음 순서를 권한다.

1. 로컬에는 최근 1~2개만 남긴다. 오래된 백업은 바로 지우거나 반출 후 삭제한다.
2. PC/NAS로 반출한다. 5.2에서 다룬 scp/rsync를 그대로 쓴다 — 방향만 반대로, 보드에서 바깥으로 보낸다.

# 보드에서 실행 — 백업 파일을 같은 네트워크의 PC/NAS로 전송
scp ~/backups/postgresql/sensordb_$(date +%Y%m%d).dump \
    [email protected]:/backups/uno-q/postgresql/

# 여러 파일을 한꺼번에, 증분 전송(이미 있는 파일은 재전송하지 않음)
rsync -avz ~/backups/postgresql/ [email protected]:/backups/uno-q/postgresql/

1. eMMC 쓰기 마모를 고려해 백업 주기를 데이터 중요도에 맞춘다. 7.3.3에서 다룬 것과 같은 이유다 — pg_dump 자체는 읽기 위주 작업이라 WAL만큼 쓰기를 유발하지는 않지만, 로컬에 임시로 쓰는 덤프 파일과 오래된 파일 삭제(rotation)는 그 자체로 eMMC 쓰기다. 센서 로그처럼 유실 허용 범위가 넓은 데이터는 주 1회 정도로 충분할 수 있고, 손실을 감수하기 어려운 설정·계정 데이터는 변경 직후 수동 백업을 정례화하는 편이 오히려 더 안전하고 쓰기 총량도 적다.

cron으로 예약하기. 정기 백업을 cron으로 자동화하는 것은 PostgreSQL·Debian 전반에서 널리 쓰이는 일반적인 관행이지만, UNO Q 보드에서 이 구성을 공식적으로 검증·권장한 자료는 확인하지 못했다(5.3.5에서 OTA 업데이트 자동화를 다룰 때와 같은 종류의 한계다) — 확인 필요. 적용한다면 실패를 조용히 넘기지 않도록 로그를 남기고, 오래된 로컬 백업은 함께 정리한다.

sudo -u postgres crontab -e

# 매일 03:00 백업 후, 로컬에는 7일치만 남기고 정리
0 3 * * * pg_dump -Fc sensordb -f /var/backups/postgresql/sensordb_$(date +\%Y\%m\%d).dump && find /var/backups/postgresql -name "sensordb_*.dump" -mtime +7 -delete

crontab 안에서는 %가 개행으로 해석되므로 date 형식 문자열의 %는 위처럼 \%로 이스케이프해야 한다.

최소 복구 시나리오

시나리오 1 — eMMC 손상으로 보드를 재설치한 경우. 5.4의 방법으로 이미지를 다시 플래시하고 PostgreSQL을 재설치(7.3.1)한 뒤, PC/NAS에 반출해 둔 백업으로 되돌린다.

# 1) PostgreSQL 재설치
sudo apt update && sudo apt install -y postgresql

# 2) 백업 파일을 NAS에서 보드로 가져오기
scp [email protected]:/backups/uno-q/postgresql/sensordb_20260825.dump ~/

# 3) 역할·DB 재생성 후 복구 (앞의 pg_restore 절차와 동일)
sudo -u postgres psql -f globals_20260825.sql
sudo -u postgres createdb sensordb -O sensoruser
sudo -u postgres pg_restore -d sensordb ~/sensordb_20260825.dump

시나리오 2 — PostgreSQL 서비스만 재설치한 경우(OS·데이터 디렉터리는 보존됨). apt purge postgresql처럼 패키지를 완전히 지우면 보통 /var/lib/postgresql 데이터 디렉터리도 함께 삭제되므로, 이 경우는 시나리오 1과 동일하게 백업에서 복구해야 한다. 반면 패키지만 재설치(apt install --reinstall)하고 데이터 디렉터리를 변경하지 않았다면 별도 복구 없이 기존 클러스터가 그대로 인식되는 경우가 많다 — 다만 이는 상황에 따라 달라질 수 있으므로, 어느 쪽이든 작업 전 반드시 최신 백업을 먼저 받아 두고 시작하는 편이 안전하다.

복구 후 확인. 복구가 끝나면 행 수·최근 레코드를 확인해 데이터가 온전한지 확인한다.

SELECT count(*) FROM sensor_readings;
SELECT * FROM sensor_readings ORDER BY recorded_at DESC LIMIT 5;

이 절에서 제시한 백업 주기·보관 개수·cron 예시는 UNO Q를 대상으로 한 공식 벤치마크나 사례가 아니라, PostgreSQL 공식 문서(pg_dump/pg_dumpall/pg_restore 사용법)와 Raspberry Pi 등 일반적인 저사양 SBC 운영에서 통용되는 백업 관행을 UNO Q의 저장 공간·리소스 제약에 맞춰 옮긴 것이다. 실제 배포 전에는 프로젝트의 데이터 중요도와 eMMC 여유 공간에 맞춰 주기·보관 정책을 직접 조정하는 것을 권한다.