#!/usr/bin/env bash # # postgres_fdw COPY batch insert benchmark. # # This script runs on the SOURCE machine. It: # 1. Inits and starts a local PostgreSQL instance (source) # 2. Connects to TARGET (remote host:port) via psql to create the base table # 3. Creates a foreign table on source pointing to target (fdw on source) # 4. Generates a TSV data file on source # 5. Runs COPY FROM into foreign table (source pushes data to target via fdw) # 6. Runs pgbench INSERT into foreign table # # The SOURCE (this machine) has the patched binaries — that's where fdw COPY lives. # The TARGET just needs a running PostgreSQL with a table. # # Usage: # ./fdw_bench.sh --target-host=127.0.0.1 --target-port=5533 [--source-port=5532] [--rows=1000000] # # Run twice: once with HEAD binaries, once with patched binaries. Compare. set -euo pipefail # --- defaults --- SOURCE_PORT=5532 TARGET_HOST="" TARGET_PORT="" PGBIN="${PGBIN:-}" PGUSER="${PGUSER:-$(whoami)}" DATA_DIR="${DATA_DIR:-/tmp/fdw_bench}" ROWS="${ROWS:-1000000}" # --- parse args --- for arg in "$@"; do case "$arg" in --target-host=*) TARGET_HOST="${arg#*=}" ;; --target-port=*) TARGET_PORT="${arg#*=}" ;; --source-port=*) SOURCE_PORT="${arg#*=}" ;; --rows=*) ROWS="${arg#*=}" ;; --pgbin=*) PGBIN="${arg#*=}" ;; *) echo "Unknown arg: $arg"; exit 1 ;; esac done if [ -z "$TARGET_HOST" ] || [ -z "$TARGET_PORT" ]; then echo "Usage: $0 --target-host=HOST --target-port=PORT [--source-port=$SOURCE_PORT] [--rows=$ROWS]" echo "" echo "This script runs on the SOURCE machine (with patched binaries)." echo "It initializes a local PG instance and connects to TARGET via psql" echo "to create the base table. Then it creates a foreign table on source" echo "pointing to target, and benchmarks COPY FROM and INSERT." exit 1 fi # --- resolve binaries --- if [ -z "$PGBIN" ]; then PGBIN=$(dirname "$(command -v initdb 2>/dev/null || echo /usr/local/pgsql/bin/initdb)") fi INITDB="$PGBIN/initdb" PG_CTL="$PGBIN/pg_ctl" PSQL="$PGBIN/psql" PGBENCH="$PGBIN/pgbench" # --- get source IP (best guess) --- SOURCE_HOST=$(hostname -I 2>/dev/null | awk '{print $1}' || echo 127.0.0.1) echo "=== Configuration ===" echo " PGBIN: $PGBIN" echo " SOURCE: $SOURCE_HOST:$SOURCE_PORT (local, initdb, has fdw patch)" echo " TARGET: $TARGET_HOST:$TARGET_PORT (remote, just needs a table)" echo " ROWS: $ROWS" echo " PGUSER: $PGUSER" echo " DATA_DIR: $DATA_DIR" echo "" SRC_DIR="$DATA_DIR/source" mkdir -p "$DATA_DIR" # --- helpers --- psql_src() { "$PSQL" -h 127.0.0.1 -p "$SOURCE_PORT" -U "$PGUSER" -d postgres -v ON_ERROR_STOP=1 "$@"; } psql_tgt() { "$PSQL" -h "$TARGET_HOST" -p "$TARGET_PORT" -U "$PGUSER" -d postgres -v ON_ERROR_STOP=1 "$@"; } RESULTS="$DATA_DIR/results.txt" echo "" > "$RESULTS" # --- step 1: initdb + start source instance --- echo "=== Initializing source instance (port $SOURCE_PORT) ===" if [ ! -d "$SRC_DIR" ]; then "$INITDB" -D "$SRC_DIR" --no-locale -E UTF8 -U "$PGUSER" fi cat > "$SRC_DIR/postgresql.conf" </dev/null; then echo "host all all 0.0.0.0/0 trust" >> "$SRC_DIR/pg_hba.conf" fi "$PG_CTL" -D "$SRC_DIR" -l "$DATA_DIR/source.log" start sleep 3 echo " Source started on port $SOURCE_PORT" echo "" # --- step 2: create table on TARGET (the remote side) --- echo "=== Creating table on target ($TARGET_HOST:$TARGET_PORT) ===" psql_tgt -c "DROP TABLE IF EXISTS t CASCADE; CREATE TABLE t (id int, value int, note text);" echo "" # --- step 3: setup foreign table on SOURCE (local, pointing to target) --- echo "=== Setting up foreign table on source (fdw -> target) ===" psql_src -c "CREATE EXTENSION IF NOT EXISTS postgres_fdw;" 2>/dev/null || true psql_src -c "DROP FOREIGN TABLE IF EXISTS ft; DROP SERVER IF EXISTS remote CASCADE; DROP USER MAPPING IF EXISTS $PGUSER SERVER remote;" 2>/dev/null || true psql_src -c "CREATE SERVER remote FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '$TARGET_HOST', port '$TARGET_PORT', dbname 'postgres');" psql_src -c "CREATE USER MAPPING FOR $PGUSER SERVER remote OPTIONS (user '$PGUSER');" psql_src -c "CREATE FOREIGN TABLE ft (id int, value int, note text) SERVER remote OPTIONS (table_name 't', batch_size '1000');" # Verify connectivity echo " Testing connectivity..." psql_src -c "INSERT INTO ft VALUES (0, 0, 'connectivity test'); SELECT count(*) FROM ft;" psql_tgt -c "TRUNCATE t;" echo "" # --- step 4: generate data file on source --- DATA_FILE="$DATA_DIR/copy_data_${ROWS}.tsv" if [ ! -f "$DATA_FILE" ]; then echo "=== Generating $ROWS rows of TSV data ===" seq 0 $((ROWS - 1)) | awk -v OFS='\t' '{print $1, $1*100, "batch copy test data " $1}' > "$DATA_FILE" echo " File: $DATA_FILE ($(du -h "$DATA_FILE" | cut -f1))" fi echo "" # --- step 5: COPY FROM benchmarks (run on source, data file is local) --- echo "=== COPY FROM $ROWS rows (local file -> fdw -> target) ===" echo "=== COPY FROM $ROWS rows ===" >> "$RESULTS" for BS in 100 1000 10000 100000; do psql_tgt -c "TRUNCATE t;" 2>/dev/null psql_src -c "DROP FOREIGN TABLE ft; CREATE FOREIGN TABLE ft (id int, value int, note text) SERVER remote OPTIONS (table_name 't', batch_size '$BS');" 2>/dev/null sleep 1 echo -n " batch_size=$BS: " START=$(date +%s%N) psql_src -c "COPY ft FROM '$DATA_FILE'" > /dev/null 2>&1 END=$(date +%s%N) ELAPSED_MS=$(( (END - START) / 1000000 )) ELAPSED_S=$(echo "scale=2; $ELAPSED_MS / 1000" | bc 2>/dev/null || echo "${ELAPSED_MS}ms") COUNT=$(psql_tgt -tA -c "SELECT count(*) FROM t;" 2>/dev/null || echo "ERROR") echo "${ELAPSED_S}s (${COUNT} rows)" echo " batch_size=$BS: ${ELAPSED_S}s (${COUNT} rows)" >> "$RESULTS" done echo "" # --- step 6: pgbench INSERT benchmarks (run on source) --- echo "=== pgbench INSERT ===" echo "=== pgbench INSERT ===" >> "$RESULTS" BENCH_SQL="$DATA_DIR/fdw_bench.sql" cat > "$BENCH_SQL" </dev/null psql_src -c "DROP FOREIGN TABLE ft; CREATE FOREIGN TABLE ft (id int, value int, note text) SERVER remote OPTIONS (table_name 't', batch_size '$BS');" 2>/dev/null sleep 1 echo -n " batch_size=$BS: " TPS=$("$PGBENCH" -n -c 10 -j 10 -t 10 -f "$BENCH_SQL" -h 127.0.0.1 -p "$SOURCE_PORT" -U "$PGUSER" -d postgres 2>&1 | grep "tps =" | sed 's/.*tps = //;s/ .*//') echo "TPS=$TPS" echo " batch_size=$BS: TPS=$TPS" >> "$RESULTS" done echo "" # --- results --- echo "============================================" echo "=== RESULTS ===" echo "============================================" cat "$RESULTS" echo "" echo "Results saved to: $RESULTS" # --- cleanup --- psql_src -c "DROP FOREIGN TABLE ft; DROP SERVER remote CASCADE;" 2>/dev/null || true psql_tgt -c "DROP TABLE t;" 2>/dev/null || true "$PG_CTL" -D "$SRC_DIR" stop -m immediate 2>/dev/null || true