[筆記] PostgreSQL on NAS

Posted on |

NAS (Network Attached Storage) 是透過網路來連接的儲存裝置，有點像是小型的雲端伺服器，電腦可以透過網路來存取 NAS 上的檔案。而 NAS 除了以檔案/ 資料夾的形式來共享資料之外，也可以透過建立資料庫來分享資料。以下筆記在 NAS 上面建立 Postgres 資料庫所需要的操作以及如何取得已建立資料庫裡面的檔案。

以下操作 NAS 的型號為 QNAP TS-453Be，本機端作業系統為 Windows 10。

Install PostgreSQL on NAS

在 QNAP TS-453Be 上要安裝 PostgreSQL 很容易，只以 admin 管理者帳號連進 NAS，去官方的 APP Center 下載安裝檔，照著說明安裝就可以了，後續資料庫的建置也可以透過其內建的 phpPgAdmin WebUI 管理介面來操作。不過這時候資料庫的使用權限只開放給 admin 帳號，其他帳戶無法透過遠端來連線 NAS 取得資料庫的服務。要讓其它帳戶也可以遠端使用資料庫內的資料，需要修改幾個 PostgreSQL 的設定檔。

Edit configuration files

  1. 以 admin 管理者帳號登入 NAS，開啟允許 SSH 遠端連線 NAS 的功能 (記得開啟的通訊埠 (port) 編號)

  2. 透過 PuTTY 遠端連線進 NAS 的終端機

  3. 找到 NAS 裡面 PostgreSQL 的路徑，我的在
    /share/CACHEDEV1_DATA/.qpkg/PostgreSQL/postgresDB

  4. 找到裡面的 postgresql.conf (PostgreSQL 設定檔) 以及 pg_hba.conf (Host-Based Authencation) 兩個檔案，並且修改連線部分的設定。

    • ~./postgresql.conf
      開啟 listen_addresses 的設定並修改為 '*'，這樣只要知道 IP 的人都可以遠端進來，雖然方便，不過這樣的設定也會伴隨著一定程度的風險。
      (#listen_addresses = 'localhost' to listen_addresses = '*')

    • ~./pg_hba.conf
      修改 IPv4 local connections，以下是修改的範例

      1
      2
      3
      4
      5
      # IPv4 local connections:
      host    all             all             127.0.0.1/32      trust    # only allow localhost to connect
      host    all             all             localhost          trust    # only allow localhost to connect
      host    all             all             0.0.0.0/0            md5   # allow all public host to connect
      host    all             all             192.168.0.0/24  md5   # allow IPs 192.168.0.* to connect

      我在 terminal 的操作都是用 vim，如果輸入 vim 指令時出現 E437: terminal capability "cm" required，然後 vim 的操作變得不太正常，我找到的解法是輸入 export TERM=xterm 就可以解決了。

  1. 以上設定修改完後，應該就可以在本機端從 psql 連進資料庫了。如果使用原本的 postgres 帳號密碼遠端連線卻遇到 Postgres password authentication fails 的問題的話，可以試著回到 NAS 的圖形化介面，登入 phpPgAdmin 修改密碼，接著用新的密碼遠端應該就可以順利登入惹～

Import data from Postgres in R

R 連接資料庫需要的套件是 RPostgres，若不想把自己的帳號密碼放在 script 裡面，如果是 RStudio 的使用者，可以利用下面 rstudioapi 的方式透過 GUI 畫面輸入帳號密碼。要取得資料庫內的資料可以用 dbGetQuery，會返回 data.frame。

1
2
3
4
5
6
7
8
9
library(RPostgres)
con <- dbConnect(Postgres(), 
                  host="localhost", # 需替換成資料庫的 IP 
                  dbname="dvdrental", # 資料庫的名字
                  user=rstudioapi::askForPassword("Database username"), # posrgres 的帳號
                  password=rstudioapi::askForPassword("Database password")) # 密碼

# 讀取資料                  
data_psql <- dbGetQuery(con, "select * table_1") # 第二個參數為 sql 語法

非 RStudio 用戶的話也可以透過 keyring 來隱藏密碼的資訊，詳細的設定可以參考 這裡

Reference

How to access QNAP NAS by SSH?
QNAP/Linux Tool - PostgreSQL
PostgreSQL database and QNAP
【原创】如何解决 E437: terminal capability ＂cm＂ required
Postgres password authentication fails
R: Working with Databases