05. 데이터베이스 (PostgreSQL)
PostgreSQL 16. 접속 정보는 DATABASE_URL(libpq URI, 예: postgresql://acn:***@127.0.0.1:5432/acn).
애플리케이션은 커넥션 풀(psycopg 3, 최대 20)을 쓰고, 스키마는 기동 시 db.py의 SCHEMA와
office/schema.py의 OFFICE_SCHEMA가 CREATE TABLE IF NOT EXISTS로 적용한다.
2026-09-08 이전까지는 SQLite(
backend/data/alice.db) 였다. 이전 절차와 남은 SQLite 파일의 취급은 아래 SQLite에서 이전 참고.
트랜잭션 모델
db.get_conn()이 세 가지 모드를 준다. SQLite 시절의 보장을 그대로 옮긴 것이다.
| 호출 | PostgreSQL | 대체한 SQLite 동작 |
|---|---|---|
get_conn(write=True) | pg_advisory_xact_lock 획득 | BEGIN IMMEDIATE |
get_conn(snapshot=True) | REPEATABLE READ | deferred BEGIN(WAL 스냅샷) |
get_conn() | 오토커밋 읽기 | 동일 |
advisory lock이 왜 필요한가. PostgreSQL은 MVCC라 writer끼리 서로 막지 않는다. 그런데 Office 서비스 계층은 전부 "읽고-검사하고-쓰는" 코드이고(잔액, 노드 재고, 상태 전이), SQLite에서는 단일 writer 락이 그 사이를 지켜주고 있었다. 그대로 옮기면 두 요청이 같은 잔액 검사를 동시에 통과해 이중 출금이 가능해진다. 트랜잭션 범위 advisory lock으로 "한 번에 한 writer"를 복원했고, 덕분에 정산 로직은 한 줄도 바뀌지 않았다. 상태 전이의 조건부 UPDATE와 부분 유니크 인덱스는 그 위에 겹쳐진 두 번째·세 번째 방어선으로 그대로 남아 있다.
읽기는 락과 무관하다. 조직도 조회(snapshot=True)는 writer가 락을 쥐고 있어도 즉시 응답한다.
테이블
transfers — ACN Transfer 이벤트
| 컬럼 | 타입 | 설명 |
|---|---|---|
tx_hash | TEXT | 트랜잭션 해시(소문자 0x) |
log_index | BIGINT | 영수증 내 로그 인덱스. 한 tx에 Transfer가 여러 개일 수 있어 PK에 포함 |
block_number | BIGINT | 블록 번호 |
from_address | TEXT | 보낸 주소(소문자) |
to_address | TEXT | 받은 주소(소문자) |
amount_wei | TEXT | 수량(wei). uint256은 64bit 정수를 넘을 수 있어 TEXT |
block_time | BIGINT | 블록 타임스탬프(unix). 없으면 NULL |
source | TEXT | indexer 또는 client(POST /api/transfers로 기록) |
created_at | BIGINT | 저장 시각(unix), 기본값 EXTRACT(EPOCH FROM now())::bigint |
- PK:
(tx_hash, log_index) - 인덱스:
from_address,to_address,block_number DESC - upsert 규칙: 충돌 시
block_number갱신,block_time은 NULL이 아닌 값 우선 유지.
indexer_state — 인덱서 체크포인트 겸 운영 키·값
| 컬럼 | 타입 | 설명 |
|---|---|---|
key | TEXT PK | last_indexed_block, office_swap_rate_micro, office_airdrop_backfill 등 |
value | TEXT | 값 |
Office office_* 테이블은 16-office.md에 있다.
설계 메모
- 주소는 전부 소문자로 정규화해 저장·비교한다(EIP-55 체크섬은 표시용).
- 금액 계산은 DB가 아니라 애플리케이션(파이썬
int, 프론트bigint)에서 한다. DB에는 정수로만 저장한다(USD는 센트, ACN·USDT는 milli). - 집계는
::bigint로 캐스팅한다. PostgreSQL의SUM(bigint)은numeric을 돌려주고 psycopg가 이를Decimal로 넘기는데, 그대로 두면 원장 금액이 JSON에 숫자가 아닌 문자열로 나간다. 장부 집계 8곳은 전부COALESCE(SUM(...),0)::bigint형태다. 새 집계를 추가할 때도 같은 규칙을 지킨다. - id는
BIGINT GENERATED BY DEFAULT AS IDENTITY다.ALWAYS가 아닌 이유는 이전 도구가 원래 id를 그대로 넣어야 하기 때문이다(추천 보상·에어드랍 배분·조직이동 감사기록이 같은 회원/노드를 계속 가리켜야 한다). - 애플리케이션 SQL은 SQLite 문법(
?자리표시자)을 그대로 쓴다.db.PgConnection이 문자열 리터럴·주석 밖의?만%s로 치환한다. 방언 차가 실제로 있는 곳만 손댔다:INSERT OR IGNORE→ON CONFLICT DO NOTHING,COLLATE NOCASE→LOWER(...),IS ?→IS NOT DISTINCT FROM ?::bigint,lastrowid→RETURNING id. - 마이그레이션 도구 없이 시작한다. 스키마 변경이 잦아지면 Alembic 도입을 검토한다.
백업
pg_dump가 매일 03:17 UTC(12:17 KST)에 돈다(/etc/cron.d/acn-backup).
| 항목 | 값 |
|---|---|
| 스크립트 | /usr/local/bin/acn-backup |
| 저장 위치 | /var/lib/acn/backup/acn-<UTC타임스탬프>.dump |
| 형식 | pg_dump -Fc --compress=9 (선택 복원 가능) |
| 보관 | 14일 (ACN_BACKUP_KEEP_DAYS) |
| 로그 | /var/log/acn-backup.log (월 단위 rotate, 12개월) |
| 권한 | 디렉터리·파일 모두 root 전용 0600 |
권한이 root 전용인 이유는 덤프에 비밀번호 해시와 TOTP 비밀키가 들어 있기 때문이다. 이 파일을 읽을 수 있으면 회원의 2단계 인증을 재현할 수 있다. 외부로 복사할 때도 같은 취급을 해야 한다.
스크립트는 덤프를 .tmp로 쓴 뒤 원자적으로 옮기고(중단된 실행이 백업처럼 보이지 않도록),
pg_restore --list로 목차를 읽어 최소한의 무결성을 확인한 다음 오래된 덤프를 지운다.
복원 검증
복원해 본 적 없는 백업은 백업이 아니다. acn-restore-check가 최신 덤프를 임시 DB에 복원해
스키마·행 수를 운영 DB와 대조하고 임시 DB를 지운다. 운영 DB에는 쓰지 않는다.
acn-restore-check # 최신 덤프
acn-restore-check /path/to/x.dump # 특정 덤프
실제 복원
systemctl stop acn-api
sudo -u postgres dropdb acn && sudo -u postgres createdb -O acn acn
sudo -u postgres pg_restore --dbname=acn --no-owner --role=acn < /var/lib/acn/backup/acn-<타임스탬프>.dump
systemctl start acn-api
덤프가 root 전용이라 postgres 사용자가 직접 열 수 없다. 위처럼 root가 읽어 stdin으로 넘긴다.
SQLite에서 이전
backend/tools/migrate_sqlite_to_pg.py가 옛 alice.db를 PostgreSQL로 옮긴다.
cd backend
.venv/bin/python tools/migrate_sqlite_to_pg.py --dry-run # 무엇이 옮겨지는지만 출력
.venv/bin/python tools/migrate_sqlite_to_pg.py --sqlite /var/lib/acn/alice.db
원장은 오프체인이라 어디서도 재구축할 수 없으므로 도구는 관대하지 않고 엄격하게 만들었다.
- 대상 스키마는 앱 자신(
app.db.init_db)이 만든다. API가 기대하는 모양과 데이터가 들어가는 모양이 어긋날 수 없다. - 소스에 대상에 없는 컬럼이 있으면 중단한다. TOTP·USDT 이전 시절의 SQLite 파일을 필드가 빠진 채로 조용히 밀어넣지 않는다. 그런 파일은 구버전을 한 번 띄워 자체 마이그레이션을 돌린 뒤 옮긴다.
- 행 id를 그대로 보존하고, 끝나면 각 identity 시퀀스를 최대 id 뒤로 밀어 둔다.
office_users.referrer_id는 자기 참조 FK라 회원을 추천인 없이 먼저 넣고 2패스로 연결한다. 트리의 id 순서와 무관하게 동작한다.- 전체가 한 트랜잭션이고, 테이블별 행 수를 대조해 불일치하면 실패한다.
- 비어 있지 않은 대상은
--force없이는 건드리지 않는다.
이전 검증은 구버전(SQLite)과 신버전(PostgreSQL)에서 전 회원의 장부·노드·추천·에어드랍·조직도를 JSON으로 덤프해 비교하는 방식으로 했다. 결과는 바이트 단위로 동일했다.
옛 /var/lib/acn/alice.db는 참고용으로 남아 있고 애플리케이션은 열지 않는다. 이전이 확정되면
지워도 된다(백업 디렉터리에 사본이 있다).