Back to blog

Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ ์„ค์ • ๊ฐ€์ด๋“œ: PostgreSQL 17 ์‹ค์Šต๊ณผ ๋™์ž‘ ์›๋ฆฌ

August 10, 2026

์ž‘์„ฑ์ž: EDB Korea Team
2025๋…„ 1์›” 17์ผ

Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ๋Š” ๋ฐ์ดํ„ฐ ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ๊ฒŒ์‹œ์ž์—์„œ ๊ตฌ๋…์ž๋กœ ์ „๋‹ฌํ•˜๋Š” ๊ธฐ๋Šฅ์ž…๋‹ˆ๋‹ค. ์ด ๊ฐ€์ด๋“œ์—์„œ๋Š” PostgreSQL 17 ์†Œ์Šค ์ฝ”๋“œ๋ฅผ ๋นŒ๋“œํ•˜๊ณ  ๋‘ ๊ฐœ์˜ Postgres ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์‚ฌ์ด์— ๊ฒŒ์‹œ(publication)์™€ ๊ตฌ๋…(subscription)์„ ๊ตฌ์„ฑํ•˜๋Š” ๋ฐฉ๋ฒ•์„ ๋‹จ๊ณ„๋ณ„๋กœ ์‚ดํŽด๋ด…๋‹ˆ๋‹ค.

๋˜ํ•œ walsender, apply worker, pgoutput์ด ๋…ผ๋ฆฌ์  ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ์ „์†กํ•˜๊ณ  ์ ์šฉํ•˜๋Š” ๋‚ด๋ถ€ ๊ณผ์ •๋„ ์†Œ์Šค ์ฝ”๋“œ์™€ ๋กœ๊ทธ๋ฅผ ํ†ตํ•ด ํ™•์ธํ•ฉ๋‹ˆ๋‹ค.

Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ ์„ค์ • ๋ฐ ๋™์ž‘ ์›๋ฆฌ

Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ์˜ ๋ฐฐ๊ฒฝ

Postgres๋Š” ์ฒ˜์Œ์— ๋ฌผ๋ฆฌ์  ๋ณต์ œ๋ฅผ ๊ตฌํ˜„ํ–ˆ์Šต๋‹ˆ๋‹ค. ๋ฌผ๋ฆฌ์  ๋ณต์ œ๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค A์—์„œ B๋กœ ๋””์Šคํฌ์˜ ๋ฐ”์ดํŠธ ๋ฐ์ดํ„ฐ๋ฅผ ์ „์†กํ•˜๊ณ , ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค B๊ฐ€ ์ด ๋ฐ”์ดํŠธ๋ฅผ ๋””์Šคํฌ์— ๊ทธ๋Œ€๋กœ ๊ธฐ๋กํ•˜๋Š” ๋ฐฉ์‹์ž…๋‹ˆ๋‹ค. ํ•˜์ง€๋งŒ ๋™์ผํ•œ Postgres ๋ฒ„์ „์„ ์‹คํ–‰ํ•˜๋Š” ์ธ์Šคํ„ด์Šค ๊ฐ„์—์„œ๋งŒ ์ž‘๋™ํ•˜๋ฉฐ, ๋™์ผํ•œ CPU ์•„ํ‚คํ…์ฒ˜์™€ ํŽ˜์ด์ง€ ํฌ๊ธฐ ๊ฐ™์€ ์šด์˜ ์ฒด์ œ ์„ค์ •๋„ ์š”๊ตฌํ•œ๋‹ค๋Š” ์ œํ•œ์ด ์žˆ์Šต๋‹ˆ๋‹ค.

๋…ผ๋ฆฌ์  ๋ณต์ œ๋Š” ์‚ฝ์ž…(insert), ์ˆ˜์ •(update), ์‚ญ์ œ(delete), ํŠธ๋ ์ผ€์ดํŠธ(truncate) ๊ฐ™์€ ํŠน์ • ๋ฉ”์‹œ์ง€๋ฅผ ๋Œ€๋ถ€๋ถ„ ์•ˆ์ •์ ์ธ ํ˜•์‹์œผ๋กœ ๋„คํŠธ์›Œํฌ๋ฅผ ํ†ตํ•ด ๋ณต์ œํ•ฉ๋‹ˆ๋‹ค. ์ด๋ฅผ ํ†ตํ•ด ์„œ๋กœ ๋‹ค๋ฅธ Postgres ๋ฒ„์ „์„ ์‹คํ–‰ํ•˜๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๊ฐ„์— ๋ฐ์ดํ„ฐ๋ฅผ ๋ณต์ œํ•  ์ˆ˜ ์žˆ์œผ๋ฉฐ, ๋ณต์ œํ•  ๋•Œ ํ–‰์ด๋‚˜ ์—ด์„ ํ•„ํ„ฐ๋งํ•  ์ˆ˜๋„ ์žˆ์Šต๋‹ˆ๋‹ค.

๊ทธ๋Ÿฌ๋‚˜ ๋…ผ๋ฆฌ์  ๋ณต์ œ๋Š” ๋ฌผ๋ฆฌ์  ๋ณต์ œ๋ณด๋‹ค ๋น„๊ต์  ์ƒˆ๋กญ๊ฒŒ ๋„์ž…๋œ ๊ธฐ์ˆ ์ด๊ธฐ ๋•Œ๋ฌธ์— ์•„์ง ์„ฑ์ˆ™ ๋‹จ๊ณ„์— ์žˆ์Šต๋‹ˆ๋‹ค. ์˜ˆ๋ฅผ ๋“ค์–ด, ํ˜„์žฌ๋Š” DDL ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ๋…ผ๋ฆฌ์  ๋ณต์ œ๋ฅผ ํ†ตํ•ด ์–ป์„ ์ˆ˜ ์—†์Šต๋‹ˆ๋‹ค.

ํ•„์ž๋Š” ์ด์ „์— Postgres์—์„œ ๊ธฐ๋ณธ์ ์ธ ๋…ผ๋ฆฌ์  ๋ณต์ œ๋ฅผ ์ง์ ‘ ์„ค์ •ํ•ด ๋ณธ ์ ์ด ์—†์—ˆ๊ธฐ ๋•Œ๋ฌธ์—, ์ด๋ฒˆ ๊ธ€์—์„œ ๊ทธ ๊ณผ์ •์„ ์‹ค์Šตํ•ฉ๋‹ˆ๋‹ค.


PostgreSQL 17 ์‹ค์Šต ํ™˜๊ฒฝ ์„ค์ •

๋จผ์ € Postgres 17์„ ์†Œ์Šค ์ฝ”๋“œ์—์„œ ๋นŒ๋“œํ•ฉ๋‹ˆ๋‹ค. ์ดํ›„ ๋…ผ๋ฆฌ์  ๋ณต์ œ๊ฐ€ ์ž‘๋™ํ•˜๋Š” ๋ฐฉ์‹์„ ํ™•์ธํ•˜๊ธฐ ์œ„ํ•ด ๋ช‡ ๊ฐ€์ง€ ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ์ ์šฉํ•  ์˜ˆ์ •์ด๋ฏ€๋กœ ํ•„์š”ํ•œ ๊ณผ์ •์ž…๋‹ˆ๋‹ค.

$ git clone https://github.com/postgres/postgres
$ cd postgres
$ git checkout REL_17_STABLE
$ ./configure --with-libxml --without-icu --prefix=$(pwd)/build --libdir=$(pwd)/build/lib --enable-cassert --enable-debug
$ make -j8
$ make install

๋‹ค์Œ์œผ๋กœ ๋‘ ๊ฐœ์˜ Postgres ์ธ์Šคํ„ด์Šค๋ฅผ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค.

$ ./build/bin/initdb testdb1
$ ./build/bin/initdb testdb2

๋‘ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ๊ณ ์œ ํ•œ ํฌํŠธ๋ฅผ ์„ค์ •ํ•˜๊ณ , ๋…ผ๋ฆฌ์  ๋ณต์ œ๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋„๋ก wal_level์„ logical๋กœ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.

$ printf '\nport = 6001\nwal_level = logical' >> testdb1/postgresql.conf
$ printf '\nport = 6002\nwal_level = logical' >> testdb2/postgresql.conf

๊ทธ๋Ÿฐ ๋‹ค์Œ ๋‘ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์‹œ์ž‘ํ•ฉ๋‹ˆ๋‹ค.

$ ./build/bin/pg_ctl -D testdb1 -l logfile1 start
$ ./build/bin/pg_ctl -D testdb2 -l logfile2 start

Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ ์„ค์ • ๋ฐฉ๋ฒ•

๋จผ์ € testdb1์— ๋‹จ์ผ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๊ณ  ๋ช‡ ๊ฐœ์˜ ํ–‰์„ ์‚ฝ์ž…ํ•ฉ๋‹ˆ๋‹ค. ์ด ํ–‰๋“ค์ด testdb2์˜ ๋™์ผํ•œ ํ…Œ์ด๋ธ”์—๋„ ๋ฐ˜์˜๋˜๋„๋ก ์„ค์ •ํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.

๋…ผ๋ฆฌ์  ๋ณต์ œ๋กœ๋Š” DDL์„ ๋ณต์ œํ•  ์ˆ˜ ์—†์œผ๋ฏ€๋กœ CREATE TABLE ๋ฌธ์€ ๋‘ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ๊ฐ๊ฐ ์‹คํ–‰ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

$ psql -h localhost -p 6001 postgres -c "CREATE TABLE users (name TEXT PRIMARY KEY, age INT);"
CREATE TABLE
$ psql -h localhost -p 6002 postgres -c "CREATE TABLE users (name TEXT PRIMARY KEY, age INT);"
CREATE TABLE

์ด์ œ testdb1(ํฌํŠธ 6001)์ด ์ด ํ…Œ์ด๋ธ”์˜ ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ๊ฒŒ์‹œํ•˜๋„๋ก ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค. ์ด๋ฅผ pub์ด๋ผ๋Š” ๊ฒŒ์‹œ(publication)๋กœ ์ •์˜ํ•ฉ๋‹ˆ๋‹ค.


$ psql -h localhost -p 6001 postgres -c "CREATE PUBLICATION pub FOR TABLE users;"
CREATE PUBLICATION

์ด์–ด์„œ testdb2(ํฌํŠธ 6002)๊ฐ€ testdb1์˜ ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ๊ตฌ๋…ํ•˜๋„๋ก ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค. ์—ฌ๊ธฐ์„œ ๊ตฌ๋…(subscription)์€ testdb1์— ์ƒ์„ฑ๋œ pub ๊ฒŒ์‹œ๋ฅผ ์ฐธ์กฐํ•ฉ๋‹ˆ๋‹ค.

$ psql -h localhost -p 6002 postgres -c "CREATE SUBSCRIPTION sub CONNECTION 'port=6001 dbname=postgres' PUBLICATION pub;"
NOTICE:  created replication slot "sub" on publisher
CREATE SUBSCRIPTION

ํ˜„์žฌ ๋‘ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ํ…Œ์ด๋ธ”์€ ๋น„์–ด ์žˆ์Šต๋‹ˆ๋‹ค.

$ psql -h localhost -p 6001 postgres -c "SELECT * FROM users;"
 name  | age
-------+-----
(0 rows)
$ psql -h localhost -p 6002 postgres -c "SELECT * FROM users;"
 name  | age
-------+-----
(0 rows)

์ด์ œ testdb1์— ๋‘ ๊ฐœ์˜ ํ–‰์„ ์‚ฝ์ž…ํ•ฉ๋‹ˆ๋‹ค.

$ psql -h localhost -p 6001 postgres -c "INSERT INTO users VALUES ('deb', 12);"
INSERT 0 1
$ psql -h localhost -p 6001 postgres -c "INSERT INTO users VALUES ('kevin', 13);"
INSERT 0 1

๊ทธ๋Ÿฐ ๋‹ค์Œ testdb2์—์„œ ํ…Œ์ด๋ธ”์„ ์กฐํšŒํ•ฉ๋‹ˆ๋‹ค.

$ psql -h localhost -p 6002 postgres -c "SELECT * FROM users;"
 name  | age
-------+-----
 deb   |  12
 kevin |  13
(2 rows)

testdb1์— ์‚ฝ์ž…ํ•œ ๋‘ ๊ฐœ์˜ ํ–‰์ด testdb2์—๋„ ๋ฐ˜์˜๋˜์—ˆ์Šต๋‹ˆ๋‹ค. ์ด์ œ ๋ฐฑ๊ทธ๋ผ์šด๋“œ์—์„œ ๋…ผ๋ฆฌ์  ๋ณต์ œ๊ฐ€ ์–ด๋–ป๊ฒŒ ์ž‘๋™ํ•˜๋Š”์ง€ ์‚ดํŽด๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.


Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ ์•„ํ‚คํ…์ฒ˜

Postgres์˜ ํ”„๋กœ์„ธ์Šค ๋ชจ๋ธ์€ ๋‚ด๋ถ€ ๋™์ž‘์„ ์ดํ•ดํ•˜๊ธฐ ์‰ฝ๊ฒŒ ๊ตฌ์„ฑ๋˜์–ด ์žˆ์Šต๋‹ˆ๋‹ค. ๋‹ค์Œ ํ”„๋กœ์„ธ์Šค ํŠธ๋ฆฌ์—์„œ ๊ฒŒ์‹œ ์ธก๊ณผ ๊ตฌ๋… ์ธก์— ์ƒ์„ฑ๋œ ๋…ผ๋ฆฌ์  ๋ณต์ œ ํ”„๋กœ์„ธ์Šค๋ฅผ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

$ pstree -s postgres
-+= 00001 root /sbin/launchd
 |-+= 87091 phil /Users/phil/edb/postgres17/build/bin/postgres -D testdb2
 | |--= 87092 phil postgres: checkpointer
 | |--= 87093 phil postgres: background writer
 | |--= 87095 phil postgres: walwriter
 | |--= 87096 phil postgres: autovacuum launcher
 | |--= 87097 phil postgres: logical replication launcher
 | \--= 89689 phil postgres: logical replication apply worker for subscription 16407
 \-+= 87135 phil /Users/phil/edb/postgres17/build/bin/postgres -D testdb1
   |--= 87136 phil postgres: checkpointer
   |--= 87137 phil postgres: background writer
   |--= 87139 phil postgres: walwriter
   |--= 87140 phil postgres: autovacuum launcher
   |--= 87141 phil postgres: logical replication launcher
   \--= 89696 phil postgres: walsender phil postgres [local] START_REPLICATION

๊ฒŒ์‹œ ์ธก์˜ walsender ํ”„๋กœ์„ธ์Šค

๊ฒŒ์‹œ ์ธก(Postgres testdb1)์—์„œ๋Š” walsender๋ผ๋Š” ์ƒˆ๋กœ์šด ํ”„๋กœ์„ธ์Šค๊ฐ€ ์ƒ์„ฑ๋ฉ๋‹ˆ๋‹ค.

walsender๋ผ๋Š” ์ด๋ฆ„์€ Postgres์—์„œ ๋ฌผ๋ฆฌ์  ๋ณต์ œ๊ฐ€ ๋จผ์ € ๊ตฌํ˜„๋˜์—ˆ๋˜ ์—ญ์‚ฌ์  ๋ฐฐ๊ฒฝ์˜ ํ”์ ์œผ๋กœ ๋ณด์ž…๋‹ˆ๋‹ค. ๋ฌผ๋ฆฌ์  ๋ณต์ œ์—์„œ๋Š” ๋ณดํ†ต 16MB ํฌ๊ธฐ์˜ WAL(WAL Segment)์„ ํ•œ ๋ฒˆ์— ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๊ฐ„์— ์ „์†กํ–ˆ์Šต๋‹ˆ๋‹ค. ์‹ค์ œ๋กœ walsender.c ์ฝ”๋“œ์—์„œ๋Š” ๋ฌผ๋ฆฌ์  ๋ณต์ œ์™€ ๋…ผ๋ฆฌ์  ๋ณต์ œ๋ฅผ ๋ชจ๋‘ ์ฒ˜๋ฆฌํ•ฉ๋‹ˆ๋‹ค.

walsender ํ”„๋กœ์„ธ์Šค๋Š” CREATE PUBLICATION ๋ช…๋ น๊ณผ ๊ฐ™์ด ๊ฒŒ์‹œ(publication)๊ฐ€ ๊ตฌ์„ฑ๋  ๋•Œ ์‹œ์ž‘๋ฉ๋‹ˆ๋‹ค. ๊ฐ ๊ฒŒ์‹œ๋งˆ๋‹ค ๊ณ ์œ ํ•œ walsender ํ”„๋กœ์„ธ์Šค๊ฐ€ ์ƒ์„ฑ๋ฉ๋‹ˆ๋‹ค.

๊ตฌ๋… ์ธก์˜ apply worker ํ”„๋กœ์„ธ์Šค

๊ตฌ๋… ์ธก(Postgres testdb2)์—์„œ๋Š” apply worker๋ผ๋Š” ์ƒˆ๋กœ์šด ํ”„๋กœ์„ธ์Šค๊ฐ€ ์ƒ์„ฑ๋ฉ๋‹ˆ๋‹ค.

apply worker์˜ ์†Œ์Šค ์ฝ”๋“œ๋Š” worker.c ํŒŒ์ผ์— ์žˆ์œผ๋ฉฐ, ๋…ผ๋ฆฌ์  ๋ณต์ œ๋ฅผ ์œ„ํ•ด ์„ค๊ณ„๋œ ์ฝ”๋“œ์ž…๋‹ˆ๋‹ค. ์ด ํ”„๋กœ์„ธ์Šค๋Š” ๊ฒŒ์‹œ(Postgres testdb1) ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ์—ฐ๊ฒฐํ•˜์—ฌ walsender ํ”„๋กœ์„ธ์Šค์™€ ํ†ต์‹ ํ•ฉ๋‹ˆ๋‹ค.

apply worker ํ”„๋กœ์„ธ์Šค๋Š” CREATE SUBSCRIPTION ๋ช…๋ น๊ณผ ๊ฐ™์€ ๊ตฌ๋…(subscription)์ด ๊ตฌ์„ฑ๋  ๋•Œ ์‹œ์ž‘๋ฉ๋‹ˆ๋‹ค. ๊ฐ ๊ตฌ๋…๋งˆ๋‹ค ๊ณ ์œ ํ•œ apply worker ํ”„๋กœ์„ธ์Šค๊ฐ€ ์ƒ์„ฑ๋ฉ๋‹ˆ๋‹ค.

๋…ผ๋ฆฌ์  ๋ณต์ œ ํ†ต์‹  ํ”„๋กœํ† ์ฝœ

apply worker์™€ walsender ์‚ฌ์ด์˜ ํ†ต์‹ ์€ ๋…ผ๋ฆฌ์  ๋ณต์ œ ํ”„๋กœํ† ์ฝœ(Logical Replication Protocol)์„ ํ†ตํ•ด ์ด๋ฃจ์–ด์ง‘๋‹ˆ๋‹ค.

์†Œ์Šค ์ฝ”๋“œ๋ฅผ ์ด์šฉํ•œ ์‹ค์Šต

๋” ๊ตฌ์ฒด์ ์œผ๋กœ ์ดํ•ดํ•˜๊ธฐ ์œ„ํ•ด Postgres ์†Œ์Šค ์ฝ”๋“œ๋ฅผ ํŽธ์ง‘ํ•ด ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

  • walsender ํ”„๋กœ์„ธ์Šค์—์„œ INSERT ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ๋ณด๋‚ผ ๋•Œ ๋กœ๊ทธ๋ฅผ ์ถ”๊ฐ€ํ•ฉ๋‹ˆ๋‹ค.
  • apply worker ํ”„๋กœ์„ธ์Šค์—์„œ INSERT ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ๋ฐ›์„ ๋•Œ ๋กœ๊ทธ๋ฅผ ์ถ”๊ฐ€ํ•ฉ๋‹ˆ๋‹ค.

์ „์†ก ๋‹จ๊ณ„์— ๋กœ๊ทธ ์ถ”๊ฐ€ํ•˜๊ธฐ

walsender.c ์ž์ฒด๋Š” ์–ด๋–ค WAL ๋ ˆ์ฝ”๋“œ๋„ ์ฒ˜๋ฆฌํ•˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค. WAL ๋ ˆ์ฝ”๋“œ๋ฅผ ์ฒ˜๋ฆฌํ•˜๋Š” ๊ฒƒ์€ walsender ํ”„๋กœ์„ธ์Šค ๋‚ด์—์„œ ์‹คํ–‰๋˜๋Š” ์ถœ๋ ฅ ํ”Œ๋Ÿฌ๊ทธ์ธ(output plugin)์ž…๋‹ˆ๋‹ค.

walsender์™€ apply worker ์‚ฌ์ด์— ์ „์†ก๋˜๋Š” WAL ๋ ˆ์ฝ”๋“œ์˜ ๊ธฐ๋ณธ ํ˜•์‹์€ pgoutput์ด๋ฉฐ, ์ด๋Š” pgoutput.c์— ๊ตฌํ˜„๋˜์–ด ์žˆ์Šต๋‹ˆ๋‹ค. ์ด ํ˜•์‹์€ libpqwalreceiver.c์—์„œ ๊ธฐ๋ณธ๊ฐ’์œผ๋กœ ์„ค์ •๋ฉ๋‹ˆ๋‹ค.

์ผ๋ฐ˜์ ์ธ ์‚ฌ์šฉ์ž๋ผ๋ฉด ์ด ํ˜•์‹์„ ๋ณ„๋„๋กœ ๊ณ ๋ คํ•  ํ•„์š”๊ฐ€ ์—†์Šต๋‹ˆ๋‹ค. CREATE SUBSCRIPTION ๋ช…๋ น์€ ํ˜•์‹์„ ์„ ํƒํ•  ์ˆ˜ ์žˆ๋Š” ์˜ต์…˜์„ ์ œ๊ณตํ•˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค. ๊ธฐ๋ณธ ํ˜•์‹์„ ์žฌ์ •์˜ํ•˜๋ ค๋ฉด pg_create_logical_replication_slot ํ•จ์ˆ˜๋ฅผ ํ˜ธ์ถœํ•˜์—ฌ ๋‹ค๋ฅธ ์ถœ๋ ฅ ํ”Œ๋Ÿฌ๊ทธ์ธ์„ ์ „๋‹ฌํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

๋˜ํ•œ CREATE SUBSCRIPTION์— ์ƒˆ๋กœ์šด ์Šฌ๋กฏ์„ ์ƒ์„ฑํ•˜์ง€ ์•Š๊ณ , pg_create_logical_replication_slot๋กœ ์ˆ˜๋™ ์ƒ์„ฑํ•œ ์Šฌ๋กฏ์„ ์‚ฌ์šฉํ•˜๋„๋ก ์ง€์ •ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. ์ถœ๋ ฅ ํ”Œ๋Ÿฌ๊ทธ์ธ์„ ์žฌ์ •์˜ํ•  ํ•„์š”๊ฐ€ ์—†๋‹ค๋ฉด pg_create_logical_replication_slot์„ ์ง์ ‘ ํ˜ธ์ถœํ•  ํ•„์š”๋„ ์—†์Šต๋‹ˆ๋‹ค.

๊ฐ ์ถœ๋ ฅ ํ”Œ๋Ÿฌ๊ทธ์ธ์€ ์ž์ฒด _PG_output_plugin_init ๊ตฌํ˜„์—์„œ WAL ๋””์ฝ”๋”๋ฅผ ์œ„ํ•œ ์ฝœ๋ฐฑ์„ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค. ์ด ์ฝœ๋ฐฑ ์ค‘ ํ•˜๋‚˜๋Š” change_cb๋กœ, INSERT, UPDATE, DELETE๋ฅผ ์ฒ˜๋ฆฌํ•ฉ๋‹ˆ๋‹ค.

INSERT, UPDATE, DELETE๋ฅผ ๋””์ฝ”๋”ฉํ•ด ๊ตฌ๋…์ž์—๊ฒŒ ๋…ผ๋ฆฌ์  ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ์ „์†กํ•˜๋Š” ๊ณผ์ •์„ ํ™•์ธํ•˜๊ธฐ ์œ„ํ•ด pgoutput_change์— elog๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๋กœ๊ทธ ๋ผ์ธ์„ ์ถ”๊ฐ€ํ•ฉ๋‹ˆ๋‹ค.

$ diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 99518c6b6d..7719d5a622 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1556,14 +1556,17 @@ pgoutput_change(LogicalDecodingContext *ctx, ReorderBufferTXN *txn,
        switch (action)
        {
                case REORDER_BUFFER_CHANGE_INSERT:
+                       elog(LOG, "SENDING INSERT!");
                        logicalrep_write_insert(ctx->out, xid, targetrel, new_slot,
                                                                        data->binary, relentry->columns);
                        break;
                case REORDER_BUFFER_CHANGE_UPDATE:
+                       elog(LOG, "SENDING UPDATE!");
                        logicalrep_write_update(ctx->out, xid, targetrel, old_slot,
                                                                        new_slot, data->binary, relentry->columns);
                        break;
                case REORDER_BUFFER_CHANGE_DELETE:
+                       elog(LOG, "SENDING DELETE!");
                        logicalrep_write_delete(ctx->out, xid, targetrel, old_slot,
                                                                        data->binary, relentry->columns);
                        break;

์ˆ˜์‹  ๋‹จ๊ณ„์— ๋กœ๊ทธ ์ถ”๊ฐ€ํ•˜๊ธฐ

apply worker ํ”„๋กœ์„ธ์Šค๊ฐ€ ๋™์ž‘ํ•˜๋Š” ๊ตฌ๋… ์ธก์€ ๋” ๊ฐ„๋‹จํ•ฉ๋‹ˆ๋‹ค. ๊ตฌ๋… ์ž‘์—…์ž ํ”„๋กœ์„ธ์Šค์—๋Š” ๊ฒŒ์‹œ์ž๊ฐ€ ๋ณด๋‚ธ ๊ฐ ๋ฉ”์‹œ์ง€๋ฅผ ํ•ด์„ํ•˜๋Š” ๋ฃจํ”„๊ฐ€ ์žˆ์œผ๋ฉฐ, INSERT๋Š” ๋ฉ”์‹œ์ง€ ์œ ํ˜• ์ค‘ ํ•˜๋‚˜์ž…๋‹ˆ๋‹ค.

์ž‘์—…์ž๋Š” INSERT ๋ฉ”์‹œ์ง€๋ฅผ ์ฒ˜๋ฆฌํ•˜๊ธฐ ์œ„ํ•ด apply_handle_insert๋ฅผ ํ˜ธ์ถœํ•ฉ๋‹ˆ๋‹ค. ๊ตฌ๋…์ž ์ธก์—์„œ INSERT, UPDATE, DELETE๋ฅผ ๋ฐ›์„ ๋•Œ๋งˆ๋‹ค ์ด๋ฅผ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋„๋ก ํ•ด๋‹น ๋ฉ”์„œ๋“œ์— ๋กœ๊ทธ๋ฅผ ์ถ”๊ฐ€ํ•ฉ๋‹ˆ๋‹ค.

๋˜ํ•œ apply_handle_delete์™€ apply_handle_update ๋ฉ”์„œ๋“œ์—๋„ ๋กœ๊ทธ๋ฅผ ์ถ”๊ฐ€ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

$ diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c
index d091a1dd27..44d921ba5a 100644
--- a/src/backend/replication/logical/worker.c
+++ b/src/backend/replication/logical/worker.c
@@ -2427,6 +2427,7 @@ apply_handle_insert(StringInfo s)
  slot_fill_defaults(rel, estate, remoteslot);
  MemoryContextSwitchTo(oldctx);
 
+ elog(LOG, "GOT AN INSERT!");
  /* For a partitioned table, insert the tuple into a partition. */
  if (rel->localrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
  apply_handle_tuple_routing(edata,
@@ -2607,6 +2608,7 @@ apply_handle_update(StringInfo s)
  has_oldtup ? &oldtup : &newtup);
  MemoryContextSwitchTo(oldctx);
 
+ elog(LOG, "GOT AN UPDATE!");
  /* For a partitioned table, apply update to correct partition. */
  if (rel->localrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
  apply_handle_tuple_routing(edata,
@@ -2762,6 +2764,7 @@ apply_handle_delete(StringInfo s)
  slot_store_data(remoteslot, rel, &oldtup);
  MemoryContextSwitchTo(oldctx);
 
+ elog(LOG, "GOT A DELETE!");
  /* For a partitioned table, apply delete to correct partition. */
  if (rel->localrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
  apply_handle_tuple_routing(edata,

๊ทธ๋Ÿฐ ๋‹ค์Œ ๋กœ๊ทธ๋ฅผ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋„๋ก make && make install๋กœ Postgres๋ฅผ ๋‹ค์‹œ ๋นŒ๋“œํ•ฉ๋‹ˆ๋‹ค.


๋ณ€๊ฒฝ๋œ ๋…ผ๋ฆฌ์  ๋ณต์ œ ์ฝ”๋“œ ์‹คํ–‰ํ•˜๊ธฐ

๋‘ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์ข…๋ฃŒํ•˜๊ณ  ์‚ญ์ œํ•œ ๋‹ค์Œ ์žฌ์ƒ์„ฑํ•˜์—ฌ ๋‹ค์‹œ ์‹œ์ž‘ํ•ฉ๋‹ˆ๋‹ค.

# Shut down both databases
$ ./build/bin/pg_ctl -D testdb1 -l logfile1 stop
waiting for server to shut down.... done
server stopped
$ ./build/bin/pg_ctl -D testdb2 -l logfile2 stop
waiting for server to shut down.... done
server stopped
# Delete both databases
$ rm -rf testdb1 testdb2 logfile1 logfile2
# Recreate both databases
$ ./build/bin/initdb testdb1
$ ./build/bin/initdb testdb2
$ printf 'port = 6002\nwal_level = logical' >> testdb2/postgresql.conf
$ printf 'port = 6001\nwal_level = logical' >> testdb1/postgresql.conf
# Start both databases
$./build/bin/pg_ctl -D testdb2 -l logfile2 start
waiting for server to start.... done
server started
$ ./build/bin/pg_ctl -D testdb1 -l logfile1 start
waiting for server to start.... done
server started

์ด์ œ ๋‘ ๋กœ๊ทธ ํŒŒ์ผ์„ ๋ชจ๋‹ˆํ„ฐ๋งํ•˜์—ฌ ์ถ”๊ฐ€ํ•œ ์ƒˆ๋กœ์šด ๋กœ๊ทธ๋ฅผ ํ™•์ธํ•ฉ๋‹ˆ๋‹ค.

$ tail -f logfile1 logfile2
==> logfile1 <==
2025-01-16 20:43:37.240 EST [93365] LOG: starting PostgreSQL 17.2 on aarch64-apple-darwin23.6.0, compiled by Apple clang version 16.0.0 (clang-1600.0.26.6), 64-bit
2025-01-16 20:43:37.245 EST [93365] LOG: listening on IPv6 address "::1", port 6001
2025-01-16 20:43:37.248 EST [93365] LOG: listening on IPv4 address "127.0.0.1", port 6001
2025-01-16 20:43:37.253 EST [93365] LOG: listening on Unix socket "/tmp/.s.PGSQL.6001"
2025-01-16 20:43:37.256 EST [93368] LOG: database system was shut down at 2025-01-16 20:42:08 EST
2025-01-16 20:43:37.263 EST [93365] LOG: database system is ready to accept connections
==> logfile2 <==
2025-01-16 20:43:33.662 EST [93334] LOG: starting PostgreSQL 17.2 on aarch64-apple-darwin23.6.0, compiled by Apple clang version 16.0.0 (clang-1600.0.26.6), 64-bit
2025-01-16 20:43:33.663 EST [93334] LOG: listening on IPv6 address "::1", port 6002
2025-01-16 20:43:33.663 EST [93334] LOG: listening on IPv4 address "127.0.0.1", port 6002
2025-01-16 20:43:33.664 EST [93334] LOG: listening on Unix socket "/tmp/.s.PGSQL.6002"
2025-01-16 20:43:33.666 EST [93337] LOG: database system was shut down at 2025-01-16 20:42:11 EST
2025-01-16 20:43:33.670 EST [93334] LOG: database system is ready to accept connections

๋‘ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค.

$ psql -h localhost -p 6001 postgres -c "CREATE TABLE users (name TEXT PRIMARY KEY, age INT);"
CREATE TABLE
$ psql -h localhost -p 6002 postgres -c "CREATE TABLE users (name TEXT PRIMARY KEY, age INT);"
CREATE TABLE

testdb1์—์„œ ๊ฒŒ์‹œ๋ฅผ ์„ค์ •ํ•˜๊ณ  testdb2์—์„œ ๊ตฌ๋…์„ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.

$ psql -h localhost -p 6001 postgres -c "CREATE PUBLICATION pub FOR TABLE users;"
CREATE PUBLICATION
$ psql -h localhost -p 6002 postgres -c "CREATE SUBSCRIPTION sub CONNECTION 'port=6001 dbname=postgres' PUBLICATION pub;"
NOTICE: created replication slot "sub" on publisher
CREATE SUBSCRIPTION

์ด์–ด์„œ testdb1์— ๋ฐ์ดํ„ฐ๋ฅผ ์‚ฝ์ž…ํ•˜๊ณ  ๋กœ๊ทธ ํŒŒ์ผ์„ ํ™•์ธํ•ฉ๋‹ˆ๋‹ค.

$ psql -h localhost -p 6001 postgres -c "INSERT INTO users VALUES ('deb', 12);"
INSERT 0 1

tail ํ”„๋กœ์„ธ์Šค์—๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์ด ํ‘œ์‹œ๋ฉ๋‹ˆ๋‹ค.

==> logfile1 <==
2025-01-16 20:46:02.764 EST [93544] LOG:  SENDING INSERT!
2025-01-16 20:46:02.764 EST [93544] CONTEXT:  slot "sub", output plugin "pgoutput", in the change callback, associated LSN 0/15037E0
2025-01-16 20:46:02.764 EST [93544] STATEMENT:  START_REPLICATION SLOT "sub" LOGICAL 0/0 (proto_version '4', origin 'any', publication_names '"pub"')
==> logfile2 <==
2025-01-16 20:46:02.764 EST [93536] LOG:  GOT AN INSERT!
2025-01-16 20:46:02.764 EST [93536] CONTEXT:  processing remote data for replication origin "pg_16395" during message type "INSERT" for replication target relation "public.users" in transaction 751, finished at 0/15038C0

logfile1์€ ๊ฒŒ์‹œ์ž testdb1์— ํ•ด๋‹นํ•˜๋ฉฐ SENDING INSERT! ๋กœ๊ทธ๋ฅผ ๊ธฐ๋กํ–ˆ์Šต๋‹ˆ๋‹ค. logfile2๋Š” ๊ตฌ๋…์ž testdb2์— ํ•ด๋‹นํ•˜๋ฉฐ GOT AN INSERT! ๋กœ๊ทธ๋ฅผ ๊ธฐ๋กํ–ˆ์Šต๋‹ˆ๋‹ค.


Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ์˜ ํ–ฅํ›„ ๊ณผ์ œ

WAL(Write-Ahead Logging)์„ ๋””์ฝ”๋”ฉํ•˜์—ฌ ๋…ผ๋ฆฌ์  ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ๋ณต์ œํ•˜๋Š” ๊ฒƒ์€ ํ•˜๋‚˜์˜ ์‚ฌ์šฉ ์‚ฌ๋ก€์— ๋ถˆ๊ณผํ•ฉ๋‹ˆ๋‹ค. ์•ž์œผ๋กœ์˜ ๊ธ€์—์„œ๋Š” WAL์„ ๋ชฉ์ ์— ๋งž๊ฒŒ ๋””์ฝ”๋”ฉํ•˜๋Š” ๋ฐฉ๋ฒ•์„ ์‹ค์ œ ์ฝ”๋“œ๋ฅผ ํ†ตํ•ด ์„ค๋ช…ํ•˜๊ณ ์ž ํ•ฉ๋‹ˆ๋‹ค. Rust๋กœ ์ž‘์„ฑ๋œ pgrx ์†Œ์Šค ์ฝ”๋“œ์—๋Š” ์ด์™€ ๊ด€๋ จ๋œ ์ข‹์€ ์˜ˆ์ œ๊ฐ€ ํฌํ•จ๋˜์–ด ์žˆ๋Š”๋ฐ, ํ•„์ž๊ฐ€ ์ƒ๊ฐํ•˜๋Š” ๋ฐฉํ–ฅ๊ณผ ์ผ์น˜ํ•  ์ˆ˜๋„ ์žˆ์ง€๋งŒ ์•„์ง ์ž์„ธํžˆ ์‚ดํŽด๋ณด์ง€๋Š” ์•Š์•˜์Šต๋‹ˆ๋‹ค.

Postgres๋Š” ๋ถ„์‚ฐ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์ž…๋‹ˆ๋‹ค. ํ•˜์ง€๋งŒ ๋…ผ๋ฆฌ์  ๋ณต์ œ์˜ ์ผ๋ถ€ ๊ธฐ๋Šฅ์€ ์•„์ง ์ปค๋ฎค๋‹ˆํ‹ฐ ๋ฒ„์ „์˜ Postgres์—์„œ ์™„์ „ํžˆ ๊ตฌํ˜„๋˜์ง€ ์•Š์•˜์œผ๋ฉฐ, ํŠนํžˆ DDL ๋ณต์ œ๊ฐ€ ์ง€์›๋˜์ง€ ์•Š๋Š” ์ ์ด ํฐ ๊ณผ์ œ๋กœ ๋‚จ์•„ ์žˆ์Šต๋‹ˆ๋‹ค. ํ•„์ž๊ฐ€ ์ž‘์—… ์ค‘์ธ ๋…์  ์ œํ’ˆ๊ณผ ๊ฐ™์€ ์ผ๋ถ€ ์†”๋ฃจ์…˜์€ ์ด๋Ÿฌํ•œ ๋ฌธ์ œ๋ฅผ ํ•ด๊ฒฐํ•˜๊ธฐ ์œ„ํ•œ ๋Œ€์•ˆ์„ ์ œ๊ณตํ•˜์ง€๋งŒ, ์ด๋Ÿฌํ•œ ์ธํ”„๋ผ๋ฅผ ์ง์ ‘ ๊ตฌ์ถ•ํ•˜๋Š” ๊ฒƒ๋„ ๊ฐ€๋Šฅํ•œ ๋ฐฉ๋ฒ•์ž…๋‹ˆ๋‹ค.


Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ FAQ

Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ๋ž€ ๋ฌด์—‡์ธ๊ฐ€์š”?

Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ๋Š” INSERT, UPDATE, DELETE, TRUNCATE์™€ ๊ฐ™์€ ๋ณ€๊ฒฝ ๋ฉ”์‹œ์ง€๋ฅผ ๋„คํŠธ์›Œํฌ๋ฅผ ํ†ตํ•ด ๋ณต์ œํ•˜๋Š” ๋ฐฉ์‹์ž…๋‹ˆ๋‹ค. ์„œ๋กœ ๋‹ค๋ฅธ Postgres ๋ฒ„์ „ ๊ฐ„์— ๋ฐ์ดํ„ฐ๋ฅผ ๋ณต์ œํ•  ์ˆ˜ ์žˆ์œผ๋ฉฐ, ๋ณต์ œํ•  ํ–‰์ด๋‚˜ ์—ด์„ ํ•„ํ„ฐ๋งํ•  ์ˆ˜๋„ ์žˆ์Šต๋‹ˆ๋‹ค.

Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ๋ฅผ ์‚ฌ์šฉํ•˜๋ ค๋ฉด ์–ด๋–ค ์„ค์ •์ด ํ•„์š”ํ•œ๊ฐ€์š”?

๊ฒŒ์‹œ์ž์™€ ๊ตฌ๋…์ž ์—ญํ• ์„ ์ˆ˜ํ–‰ํ•  Postgres ์ธ์Šคํ„ด์Šค๊ฐ€ ํ•„์š”ํ•˜๋ฉฐ, ๋…ผ๋ฆฌ์  ๋ณต์ œ๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋„๋ก wal_level์„ logical๋กœ ์„ค์ •ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. ์ดํ›„ ๊ฒŒ์‹œ์ž์—์„œ PUBLICATION์„ ๋งŒ๋“ค๊ณ  ๊ตฌ๋…์ž์—์„œ ํ•ด๋‹น ๊ฒŒ์‹œ๋ฅผ ์ฐธ์กฐํ•˜๋Š” SUBSCRIPTION์„ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค.

Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ๋กœ DDL๋„ ๋ณต์ œํ•  ์ˆ˜ ์žˆ๋‚˜์š”?

์ด ๊ธ€์—์„œ ๋‹ค๋ฃจ๋Š” Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ๋Š” DDL ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ๋ณต์ œํ•˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค. ๋”ฐ๋ผ์„œ CREATE TABLE๊ณผ ๊ฐ™์€ DDL ๋ฌธ์€ ๊ฒŒ์‹œ์ž์™€ ๊ตฌ๋…์ž ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ๊ฐ๊ฐ ์‹คํ–‰ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

๋…ผ๋ฆฌ์  ๋ณต์ œ์—์„œ walsender์™€ apply worker๋Š” ์–ด๋–ค ์—ญํ• ์„ ํ•˜๋‚˜์š”?

๊ฒŒ์‹œ ์ธก์˜ walsender ํ”„๋กœ์„ธ์Šค๋Š” ๋…ผ๋ฆฌ์  ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ๊ตฌ๋…์ž์—๊ฒŒ ์ „์†กํ•ฉ๋‹ˆ๋‹ค. ๊ตฌ๋… ์ธก์˜ apply worker ํ”„๋กœ์„ธ์Šค๋Š” ๊ฒŒ์‹œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ์—ฐ๊ฒฐํ•ด ๋ฉ”์‹œ์ง€๋ฅผ ๋ฐ›๊ณ  ๋Œ€์ƒ ํ…Œ์ด๋ธ”์— ๋ณ€๊ฒฝ ์‚ฌํ•ญ์„ ์ ์šฉํ•ฉ๋‹ˆ๋‹ค.

Postgres ๋…ผ๋ฆฌ์  ๋ณต์ œ์˜ ๊ธฐ๋ณธ ์ถœ๋ ฅ ํ”Œ๋Ÿฌ๊ทธ์ธ์€ ๋ฌด์—‡์ธ๊ฐ€์š”?

walsender์™€ apply worker ์‚ฌ์ด์—์„œ WAL ๋ ˆ์ฝ”๋“œ๋ฅผ ์ „์†กํ•  ๋•Œ ์‚ฌ์šฉํ•˜๋Š” ๊ธฐ๋ณธ ํ˜•์‹์€ pgoutput์ž…๋‹ˆ๋‹ค. ์ผ๋ฐ˜์ ์ธ ์‚ฌ์šฉ์ž๋Š” CREATE SUBSCRIPTION์„ ์ด์šฉํ•  ๋•Œ ์ด ํ˜•์‹์„ ๋ณ„๋„๋กœ ์ง€์ •ํ•  ํ•„์š”๊ฐ€ ์—†์Šต๋‹ˆ๋‹ค.


๋ณธ๋ฌธ: Logical replication in Postgres: Basics

์ด๋ฉ”์ผ: salesinquiry@enterprisedb.com

PostgreSQL ๋„์ž…๋ถ€ํ„ฐ ์šด์˜๊นŒ์ง€, EDB์™€ ํ•จ๊ป˜ํ•˜์„ธ์š”

PostgreSQL ๋„์ž…, ์šด์˜, ์„ฑ๋Šฅ ์ตœ์ ํ™” ๋ฐ ์—…๊ทธ๋ ˆ์ด๋“œ์— ๋Œ€ํ•ด EDB ์ „๋ฌธ๊ฐ€์™€ ์ƒ๋‹ดํ•ด ๋ณด์„ธ์š”.

๋ฌธ์˜ํ•˜๊ธฐ

Share this