PostgreSQL 데이터 이관: 비우는 순서와 같은 DB 가드

로컬에 둔 영업 DB 가 원본보다 뒤처져 있었습니다. 리드가 254건인데 원본은 282건이었습니다. 활동 기록은 171건 대 281건으로 더 벌어져 있었습니다.

문제는 그것만이 아니었습니다. 기능을 확인하느라 API 를 눌러 보는 동안 제가 남긴 통화 기록이 그 안에 섞여 있었습니다. 실제 고객 리드의 단계를 「거절」로 바꿔 놓은 것도 있었습니다. 한 번 되돌리긴 했지만, 되돌리는 일 자체가 원본과 한 줄씩 맞춰 보는 작업이었습니다.

그래서 증분으로 따라잡는 대신 통째로 지우고 다시 가져오기로 했습니다. 이 글은 그 작업에서 걸렸던 것들입니다.

증분 갱신이 아니라 초기화를 고른 이유

원본에서 새로 생긴 28건만 가져오면 될 것처럼 보입니다. 실제로는 그게 더 어렵습니다.

제가 남긴 흔적이 어느 것인지 가려내야 하기 때문입니다. 활동 기록에는 만든 사람이 적혀 있지 않습니다. 시각으로 가르려 해도 원본에도 같은 시간대에 진짜 통화 기록이 있었습니다. 실제로 되돌릴 때 원본 DB 에 붙어 활동 id 를 하나씩 대조해야 했습니다.

지우고 다시 넣으면 그 판단이 통째로 사라집니다. 원본이 정본이고 우리 쪽은 사본이라는 관계가 명확하면, 사본을 고치는 것보다 다시 만드는 편이 언제나 싸다는 뜻입니다.

같은 DB 를 가리키면 되돌릴 수 없습니다

이 스크립트는 대상 표를 비웁니다. 원본과 대상이 같은 곳을 가리키면 운영 데이터가 그 자리에서 사라집니다.

그래서 처음에 이런 가드를 넣어 두었습니다.

SELECT inet_server_addr()::text || '/' || current_database()

두 쪽에서 이 값을 받아 같으면 멈추게 했습니다. 나중에 확인해 보니 이 가드는 아무 일도 하지 않고 있었습니다.

inet_server_addr() 는 유닉스 소켓으로 붙었을 때 NULL 을 돌려줍니다. Postgres 에서 NULL 이 섞인 문자열 연결은 통째로 NULL 이 됩니다. 즉 두 값이 다 NULL 이 되고, NULL = NULL 은 참이 아니라 NULL 이라 비교가 영영 성립하지 않습니다. 같은 DB 를 넣어도 통과하는 가드였습니다.

지금은 이걸 씁니다.

SELECT system_identifier::text || '/' || current_database() FROM pg_control_system()

system_identifier 는 initdb 때 정해지는 값이라 같은 클러스터면 반드시 같습니다. 접속 방법과 무관합니다. 실제 실행에서는 이렇게 나옵니다.

원본 7669456479325499436/원본DB
대상 7675341779911888940/사본DB
(서로 다름을 확인했습니다)

가드를 짤 때 「무엇을 비교할까」보다 「그 값이 언제 비는가」를 먼저 봤어야 했습니다.

표를 하나씩 비우면 막힙니다

비우는 코드를 처음에는 표마다 한 줄씩 돌렸습니다. 일곱 번째에서 멈췄습니다.

ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "LeadProjectField" references "LeadProject".
HINT: Truncate table "LeadProjectField" at the same time, or use TRUNCATE ... CASCADE.

LeadProjectField 는 그때 0행이었습니다. 그런데도 막힙니다. Postgres 는 자식 표에 실제로 행이 있는지 보지 않고 참조 관계 자체를 봅니다. 비어 있으니 괜찮겠지 하는 기대가 틀렸습니다.

힌트가 CASCADE 를 권하지만 쓰지 않았습니다. CASCADE 는 우리가 적지 않은 표까지 따라가며 지웁니다. 스키마가 바뀌어 새 참조가 생기면 목록에 없는 표가 조용히 비워집니다. 그게 언제 일어났는지도 모릅니다.

대신 관련 표를 한 문장에 다 적었습니다.

TRUNCATE TABLE "Holiday", "LeadSearchRegion", "NpsWorkplace", ..., "LeadMapPlace"

그 집합이 외래키로 닫혀 있으면 Postgres 가 허용합니다. 무엇이 비워지는지가 코드에 그대로 드러난다는 게 요점입니다. 스크립트를 읽는 사람이 목록만 보면 됩니다.

채우는 순서는 외래키 의존 순서입니다

비우는 것과 달리 채우는 것은 순서가 있습니다. Lead 보다 LeadActivity 를 먼저 넣으면 외래키 위반으로 멈춥니다.

표 배열의 순서가 곧 그 순서입니다.

1  참조 없는 것        Holiday · LeadSearchRegion · NpsWorkplace · BizItem …
2  LeadProject         LeadProjectField·Lead 가 가리킨다
3  Lead                활동·견적·메일·공공장소·수집장소가 가리킨다
4  Lead 를 가리키는 것   LeadActivity · MailDraft · Quote …
5  Quote 를 가리키는 것  QuoteItem · Proposal
6  LeadMapRun          셀·장소가 가리킨다

순서가 틀려도 조용히 넘어가지 않습니다. 그 자리에서 멈추므로 잘못을 늦게 알 일은 없습니다. 순서를 지키는 것보다 틀렸을 때 시끄러운 것이 중요합니다.

pg_dump 를 파이프로 넘깁니다

행이 72만 개입니다. 대부분이 국민연금 사업장 54만 7천 건과 공공 장소 14만 8천 건입니다.

ORM 으로 읽어서 쓰면 몇 시간이 걸립니다. COPY 는 몇 분에 끝납니다. 그래서 pg_dump 의 출력을 파일로 떨어뜨리지 않고 바로 psql 에 넘깁니다.

pg_dump --data-only --no-owner --no-privileges --table="public.\"$t\"" "$SRC" \
  | psql -v ON_ERROR_STOP=1 "$DST"

--data-only 인 이유는 스키마를 이미 Prisma 가 만들어 두었기 때문입니다. --no-owner 와 --no-privileges 는 원본의 롤 이름이 대상에 없어서 넣었습니다. 두 쪽 다 Postgres 라 형 변환이 없다는 점도 이 방법을 고른 이유입니다. 원본은 16.14 이고 대상은 18.6 인데, pg_dump 는 자기보다 낮은 서버를 읽을 수 있으므로 18 쪽 도구를 쓰면 됩니다.

ON_ERROR_STOP=1 이 없으면 psql 은 실패한 줄을 건너뛰고 계속 갑니다. 그러면 절반만 들어간 표를 성공으로 착각합니다.

표 하나는 일부러 뺐습니다

목록을 만들고 첫 확인을 돌렸더니 대상 쪽이 원본보다 많은 표가 하나 있었습니다.

Setting    원본 4    대상 6

그 두 줄이 이 앱의 설정이었습니다. 기반 지도를 네이버로 쓸지 오픈소스로 쓸지, 어떤 색으로 그릴지를 거기 넣어 두었습니다. 원본 쪽 Setting 은 다른 앱의 것이라 이 앱이 읽지 않습니다.

그대로 덮었으면 지도 설정이 사라지고 기본값인 네이버로 돌아갔을 겁니다. 네이버 지도는 콘솔에 등록된 주소에서만 타일이 나오는데, 지금 쓰는 포트는 등록돼 있지 않습니다. 데이터를 성공적으로 옮기고 나서 지도가 통째로 안 보이는 상태가 됐을 겁니다.

그래서 Setting 을 목록에서 빼고 이유를 코드 옆에 적어 두었습니다.

「데이터를 초기화한다」의 대상은 영업 데이터이지 이 앱의 설정이 아닙니다.

이 구분은 표 이름만 봐서는 안 보입니다. 행 수를 나란히 찍어 보지 않았으면 놓쳤을 자리입니다.

검증은 옮기면서 합니다

표를 하나 넣을 때마다 원본과 대상의 행 수를 세어 비교합니다. 어긋나면 그 자리에서 멈춥니다.

Lead               282행
LeadActivity       281행
PublicPlace        148323행
NpsWorkplace       547443행

끝나고 한 번에 세는 것과 다릅니다. 중간에 멈추면 어느 표에서 어긋났는지가 바로 나옵니다. 다 넣고 나서 총합만 비교하면 어디를 다시 봐야 하는지 알 수 없습니다.

행 수 말고 하나 더 봅니다.

리드에 연결된 수집 장소 92
리드에 연결된 공공 장소 138

지도에서 모은 장소와 등록한 리드를 잇는 열쇠입니다. 이게 끊기면 행 수는 다 맞는데 지도에서 같은 업체가 「새 업체」로 보입니다. 행 수만 세면 못 잡는 종류의 손상이라 따로 셉니다.

지우기 전에 두 번 볼 수 있게 했습니다

되돌릴 수 없는 스크립트라 아무것도 쓰지 않는 모드를 둘 두었습니다.

--check 는 연결과 행 수만 보여 줍니다. --dry-run 은 옮길 양을 세어 보여 줍니다. 둘 다 대상에 손대지 않습니다.

실제로 이번 작업에서 --check 를 먼저 돌렸기 때문에 Setting 표의 차이를 발견했습니다. 바로 실행했으면 지도 설정이 사라진 뒤에 알았을 겁니다.

결과

18개 표, 72만 550행이 원본과 정확히 일치합니다.

리드        254 → 282
활동        171 → 281
메일 초안     3 → 10
공휴일        0 → 16

공휴일이 0행이었던 것은 이관 목록에 그 표를 넣지 않았던 탓입니다. 영업일 계산이 그 표를 읽는데, 비어 있으면 공휴일 없이 셉니다. 다음 연락일이 공휴일에 잡히고 있었다는 뜻입니다. 이번에 16건이 들어왔습니다.

제가 검증하며 남긴 흔적은 사라졌습니다. 이름이 smoke- 로 시작하는 리드는 0건이고, 남아 있는 수집 하나는 8월 6일자로 원본에 있던 것입니다.

남은 문제

이관은 단방향입니다. 원본은 지금도 쓰이고 있으므로 내일이면 또 앞서 나갑니다. 이번에 28건 차이였던 것이 다음에는 더 벌어질 겁니다.

그때 또 통째로 지울지, 아니면 그때는 증분을 만들지 아직 정하지 않았습니다. 사본에서 하는 작업이 늘어나면 통째로 지우는 선택지가 없어집니다. 그 시점이 오기 전에 방향을 정해야 한다는 것만 알고 있습니다.