Raspberry Pi 做 PostgreSQL(Timescale) 讓資料可以寫入到PostgreSQL

先安裝樹梅派系統

https://asps5414.blogspot.com/2026/03/raspberry-pi-os64-bit-litegui.html

sudo apt install postgresql -y

sudo systemctl status postgresql

畫面顯示：

Active: active (running)
status=0/SUCCESS

表示PostgreSQL 已成功安裝並啟動

 Step 1：加入 TimescaleDB repository

sudo sh -c "echo 'deb https://packagecloud.io/timescale/timescaledb/debian/ bookworm main' > /etc/apt/sources.list.d/timescaledb.list"

Step 2：加入金鑰

wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo gpg --dearmor -o /usr/share/keyrings/timescaledb.gpg

Step 3：加入 repository

echo "deb [signed-by=/usr/share/keyrings/timescaledb.gpg] https://packagecloud.io/timescale/timescaledb/debian/ bookworm main" | sudo tee /etc/apt/sources.list.d/timescaledb.list

Step 4：更新套件

sudo apt update

Step 5：安裝 TimescaleDB

sudo apt install timescaledb-2-postgresql-17 -y

 Step 6：自動調校（重要）

sudo timescaledb-tune

全部按 Enter（用預設）

Step 7：重啟 PostgreSQL

sudo systemctl restart postgresql

Step 8：驗證

sudo -u postgres psql

因為我原本前面做過一次驗證所以才跳出的問題

所以這邊輸入y 意思是重新覆蓋 → 正確版本

顯示下面這個畫面代表TimescaleDB 已經安裝完成

有顯示RECOMMENDED: Run 'timescaledb-tune'

沒啟用 TimescaleDB！

sudo timescaledb-tune
全部直接按 Enter（用預設）

sudo systemctl restart postgresql

重啟

sudo -u postgres psql

啟用 extension

CREATE EXTENSION IF NOT EXISTS timescaledb;

Step 1：建立資料庫

CREATE DATABASE iot_db;

Step 2：切換資料庫

\c iot_db

(在iot_db=# 裡輸入：CREATE EXTENSION IF NOT EXISTS timescaledb;)

Step 3：建立資料表（IoT用）

CREATE TABLE sensor_data (

    time TIMESTAMPTZ NOT NULL,

    device_id TEXT,

    temperature DOUBLE PRECISION,

    humidity DOUBLE PRECISION

);

Step 4：轉成 hypertable

SELECT create_hypertable('sensor_data', 'time');

測試寫入資料

INSERT INTO sensor_data VALUES (NOW(), 'ESP32_01', 25.5, 60.2);

查詢

SELECT * FROM sensor_data;

執行sudo 的話 要退出 psql 打 \q 看到是 pi@pi-server:~ $在執行以下指令

修改 postgresql.conf
sudo nano /etc/postgresql/17/main/postgresql.conf

找到:

#listen_addresses = 'localhost'

改成：

listen_addresses = '*'

把 # 拿掉 # = 註解

Ctrl + O   （存檔）按 Enter

Ctrl + X   （離開）

sudo nano /etc/postgresql/17/main/pg_hba.conf

最下面加：

host    all     all     0.0.0.0/0     trust

重啟 PostgreSQL:

sudo systemctl restart postgresql

esp32程式:

#include <SPI.h> #include <Ethernet2.h> #include <ModbusMaster.h> // ===== W5500 ===== #define W5500_CS    6 #define W5500_RST   14 byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; IPAddress dbServer(192, 168, 0, 112);  // 你電腦的 IP EthernetClient client; // ===== RS485 ===== #define RS_TX_PIN          5 #define RS_RX_PIN          4 #define RS_ENAMBLE_232_PIN 21 #define RS_ENAMBLE_422_PIN 16 #define RS_ENAMBLE_485_PIN 15 #define RS_BAUDRATE        9600 ModbusMaster node; // ===== DB 設定 ===== const char* DB_NAME = "iot_db"; const char* DB_USER = "postgres"; // ───────────────────────────────────────────── // PostgreSQL wire protocol helpers // ───────────────────────────────────────────── // 讀掉一個完整的 server message，回傳 type byte char pgReadMessage() {   unsigned long t = millis();   while (!client.available()) {     if (millis() - t > 5000) { Serial.println("PG timeout"); return 0; }     delay(10);   }   char msgType = (char)client.read();   // 讀 4 bytes 長度   uint8_t lb[4] = {0};   int got = 0;   t = millis();   while (got < 4) {     if (client.available()) lb[got++] = client.read();     if (millis() - t > 2000) break;   }   int bodyLen = (((int)lb[0]<<24)|((int)lb[1]<<16)|((int)lb[2]<<8)|(int)lb[3]) - 4;   // 如果是錯誤訊息，印出來   if (msgType == 'E' && bodyLen > 0 && bodyLen < 400) {     uint8_t body[400];     got = 0; t = millis();     while (got < bodyLen) {       if (client.available()) body[got++] = client.read();       if (millis() - t > 2000) break;     }     // PG 錯誤格式：一堆 "field_type + string\0" 組合     // 印出所有可讀字元     Serial.print("PG Error: ");     for (int i = 0; i < got; i++) {       if (body[i] >= 32 && body[i] < 127) Serial.print((char)body[i]);       else if (body[i] == 0) Serial.print(" | ");     }     Serial.println();     return msgType;   }   // 其他訊息：讀掉 body   got = 0; t = millis();   while (got < bodyLen) {     if (client.available()) { client.read(); got++; }     if (millis() - t > 2000) break;   }   return msgType; } // 送出有 type byte 的 message（一般訊息用） void pgSendMessage(char type, const uint8_t* body, int bodyLen) {   int totalLen = bodyLen + 4;   uint8_t hdr[5];   hdr[0] = (uint8_t)type;   hdr[1] = (totalLen >> 24) & 0xFF;   hdr[2] = (totalLen >> 16) & 0xFF;   hdr[3] = (totalLen >>  8) & 0xFF;   hdr[4] = (totalLen      ) & 0xFF;   client.write(hdr, 5);   if (bodyLen > 0) client.write(body, bodyLen); } // 等到收到 ReadyForQuery ('Z') void pgWaitReady() {   char r = 0;   for (int i = 0; i < 15 && r != 'Z'; i++) r = pgReadMessage(); } // ───────────────────────────────────────────── // 連線到 PostgreSQL（Startup → Auth → Ready） // ───────────────────────────────────────────── bool pgConnect() {   Serial.print("TCP -> DB...");   if (!client.connect(dbServer, 5432)) {     Serial.println("FAIL");     return false;   }   Serial.println("OK");   // ── Startup message（沒有 type byte，格式特殊）──   uint8_t buf[128];   int pos = 0;   // Protocol 3.0   buf[pos++]=0x00; buf[pos++]=0x03; buf[pos++]=0x00; buf[pos++]=0x00;   // "user\0<user>\0"   memcpy(buf+pos,"user",4); pos+=4; buf[pos++]=0;   int ul=strlen(DB_USER); memcpy(buf+pos,DB_USER,ul); pos+=ul; buf[pos++]=0;   // "database\0<db>\0"   memcpy(buf+pos,"database",8); pos+=8; buf[pos++]=0;   int dl=strlen(DB_NAME); memcpy(buf+pos,DB_NAME,dl); pos+=dl; buf[pos++]=0;   buf[pos++]=0;  // terminator   int totalLen = pos + 4;   uint8_t lenBytes[4] = {     (uint8_t)(totalLen>>24),(uint8_t)(totalLen>>16),     (uint8_t)(totalLen>>8), (uint8_t)(totalLen)   };   client.write(lenBytes, 4);   client.write(buf, pos);   // 伺服器回 'R'（auth request）或直接 'Z'（trust mode）   char r = pgReadMessage();   Serial.printf("Startup resp: '%c'\n", r);   if (r == 'R') {     // trust mode 下伺服器送 AuthenticationOk 然後一堆 parameter status     // 直接等 ReadyForQuery     pgWaitReady();   } else if (r != 'Z') {     Serial.println("Unexpected startup response");     client.stop();     return false;   }   Serial.println("DB ready!");   return true; } // ───────────────────────────────────────────── // 執行 INSERT SQL // ───────────────────────────────────────────── bool pgInsert(float val, int machine_id, int tag_addr) {   char sql[256];  snprintf(sql, sizeof(sql),   "INSERT INTO sensor_data (time, device_id, temperature, humidity)VALUES (NOW(), 'ESP32_01', 27.3, 60.2);",   machine_id, val );   int sLen = strlen(sql);   uint8_t body[256];   memcpy(body, sql, sLen);   body[sLen] = 0;   pgSendMessage('Q', body, sLen + 1);   char resp = pgReadMessage();   Serial.printf("Insert resp: '%c'\n", resp);   if (resp == 'E') {     Serial.println("SQL Error!");     pgWaitReady();     return false;   }   pgWaitReady();   return true; } // ───────────────────────────────────────────── // Modbus callbacks // ───────────────────────────────────────────── void preTransmission() {   while (Serial2.available()) Serial2.read(); } void postTransmission() {   Serial2.flush();   delayMicroseconds(100);   while (Serial2.available()) Serial2.read(); } // ───────────────────────────────────────────── void setup() {   Serial.begin(115200);   // W5500 reset   pinMode(W5500_RST, OUTPUT);   digitalWrite(W5500_RST, LOW);  delay(20);   digitalWrite(W5500_RST, HIGH); delay(100);   SPI.begin(12, 13, 11, W5500_CS);   Ethernet.init(W5500_CS);   IPAddress ip(192, 168, 0, 50);   IPAddress gateway(192, 168, 0, 1);   IPAddress subnet(255, 255, 255, 0);   Ethernet.begin(mac, ip, gateway, gateway, subnet);   Serial.print("IP: "); Serial.println(Ethernet.localIP());   // RS485   pinMode(RS_ENAMBLE_232_PIN, OUTPUT);   pinMode(RS_ENAMBLE_422_PIN, OUTPUT);   pinMode(RS_ENAMBLE_485_PIN, OUTPUT);   digitalWrite(RS_ENAMBLE_232_PIN, LOW);   digitalWrite(RS_ENAMBLE_422_PIN, LOW);   digitalWrite(RS_ENAMBLE_485_PIN, HIGH);   Serial2.begin(RS_BAUDRATE, SERIAL_8N1, RS_RX_PIN, RS_TX_PIN);   node.begin(1, Serial2);   node.preTransmission(preTransmission);   node.postTransmission(postTransmission);   Serial.println("System Ready");   pgConnect(); } void loop() {   uint8_t result = node.readHoldingRegisters(43, 1);   if (result == node.ku8MBSuccess) {     uint16_t raw = node.getResponseBuffer(0);     float val = raw / 10.0;     Serial.print("Temp: "); Serial.println(val);     // DB 連線斷掉就重連     if (!client.connected()) {       Serial.println("Reconnecting...");       pgConnect();     }     // machine_id=10, tag_addr=43 ( Modbus register)     if (pgInsert(val, 10, 43)) {       Serial.println("Saved to DB!");     }   } else {     Serial.print("Modbus error: 0x");     Serial.println(result, HEX);   }   delay(2000); }

成功後:

離開 (END):按q 就會回到iot_db=#

查詢建議開考筆就不會有(END)問題

SELECT * FROM sensor_data ORDER BY time DESC LIMIT 5;