TimescaleDB Docker → native macOS · สูตรย้ายฐานข้อมูล

Source and publishing rule

บทนี้มาจาก Discord #road-to-dev message 1514823323740012715 โดย nazt_ เวลา 2026-06-12T02:47:04Z พร้อม attachment message.txt.

The attachment contained a local password-like value in a connection table. The public website keeps the operational knowledge and redacts secrets.

กฎของ update ต่อไป: เรื่องใหม่เพิ่ม chapter ใหม่ท้ายเล่ม เพื่อให้ Wind อ่านเฉพาะของใหม่ได้ ไม่ต้องย้อนหาใน chapter เดิม.

What changed

หัวข้อหลักคือการย้าย TimescaleDB จาก Docker ไปเป็น native PostgreSQL 17 บน macOS Tahoe / Apple Silicon โดย build TimescaleDB 2.27.2 จาก source เพราะ Homebrew formula ยังพังกับ SDK path ของ Xcode 26.

The core move is Docker TimescaleDB → native PostgreSQL 17 on macOS, with TimescaleDB 2.27.2 built from source because the Homebrew formula failed against the Xcode 26 SDK path.

Install PostgreSQL 17

brew install postgresql@17
brew services start postgresql@17
/opt/homebrew/opt/postgresql@17/bin/psql -d postgres -c "SELECT version();"

Build TimescaleDB from source

export SDKROOT=$(xcrun --sdk macosx --show-sdk-path)

mkdir -p /tmp/timescaledb-build && cd /tmp/timescaledb-build
git clone --depth 1 --branch 2.27.2 https://github.com/timescale/timescaledb.git
cd timescaledb

./bootstrap \
  -DPG_CONFIG=$(brew --prefix postgresql@17)/bin/pg_config \
  -DREGRESS_CHECKS=OFF \
  -DCMAKE_OSX_SYSROOT="$SDKROOT"

cd build && make

Observed trap: make may report Error 2, while the three required .dylib files have already been produced. Verify files before assuming the build failed.

Copy extension files using pg_config

PG_LIB=$(/opt/homebrew/opt/postgresql@17/bin/pg_config --pkglibdir)
PG_SHARE=$(/opt/homebrew/opt/postgresql@17/bin/pg_config --sharedir)/extension
BUILD=/tmp/timescaledb-build/timescaledb/build

cp "$BUILD/src/loader/timescaledb.dylib" "$PG_LIB/"
cp "$BUILD/src/timescaledb-2.27.2.dylib" "$PG_LIB/"
cp "$BUILD/tsl/src/timescaledb-tsl-2.27.2.dylib" "$PG_LIB/"

cp "$BUILD/timescaledb.control" "$PG_SHARE/"
cp $BUILD/sql/timescaledb--*.sql "$PG_SHARE/"

Do not guess the Homebrew library path. Use pg_config --pkglibdir and pg_config --sharedir.

Enable and create the extension

echo "shared_preload_libraries = 'timescaledb'" >> /opt/homebrew/var/postgresql@17/postgresql.conf
brew services restart postgresql@17

/opt/homebrew/opt/postgresql@17/bin/psql -d postgres -c "CREATE DATABASE phd_pm25;"
/opt/homebrew/opt/postgresql@17/bin/psql -d phd_pm25 -c "CREATE EXTENSION IF NOT EXISTS timescaledb;"

Dump and restore

Small tables can move through a direct pipe from Docker pg_dump into native psql.

for tbl in collocated_pairs dustboy_daily pcd_daily bam_daily gems_training; do
  docker exec phd-timescaledb pg_dump -U phd -d phd_pm25 \
    --table="$tbl" --no-owner --no-privileges --format=plain \
    | /opt/homebrew/opt/postgresql@17/bin/psql -d phd_pm25 -q
done

For very large compressed hypertables, raise Docker Desktop RAM first because decompression happens during dump/restore.

TimescaleDB 2.27 compression syntax

ALTER TABLE dustboy_sensor SET (
  timescaledb.enable_columnstore = true,
  timescaledb.segmentby = 'model,nickname'
);

ALTER TABLE pcd_daily SET (
  timescaledb.enable_columnstore = true,
  timescaledb.segmentby = 'station_id'
);

Important version trap: TimescaleDB 2.27+ uses timescaledb.enable_columnstore, not the older timescaledb.compress setting.

Docker Desktop RAM

python3 -c "
import json
path = '$HOME/Library/Group Containers/group.com.docker/settings-store.json'
with open(path) as f: d = json.load(f)
d['MemoryMiB'] = 16384
with open(path, 'w') as f: json.dump(d, f, indent=2)
"

The key is MemoryMiB with a capital M. In the observed session, 32 GB was the sweet spot for chunk compression speed; more RAM was not automatically faster.

Trap table

Connection notes