如何在Debian上使用PostgreSQL進行開發

1. 更新系統并安裝PostgreSQL
在Debian上使用PostgreSQL前，需先更新系統包列表以確保依賴項最新。通過APT包管理器安裝PostgreSQL及附加工具（如postgresql-contrib，提供額外功能如全文搜索、UUID生成等）：

sudo apt update && sudo apt upgrade -y
sudo apt install postgresql postgresql-contrib -y

安裝完成后，PostgreSQL服務會自動啟動，可通過sudo systemctl status postgresql驗證服務狀態（顯示“active (running)”即為正常）。

2. 訪問PostgreSQL命令行工具（psql）
默認情況下，PostgreSQL創建了一個名為postgres的超級用戶。切換至該用戶并進入psql終端（PostgreSQL的交互式SQL命令行）：

sudo -i -u postgres
psql

退出psql終端可使用\q命令，退出postgres用戶則用exit。

3. 創建數據庫與用戶（角色）
PostgreSQL采用“角色”管理用戶權限，需為用戶分配數據庫所有權。以下命令創建一個名為mydb的數據庫、一個名為myuser的用戶（密碼為mypassword），并授予其對數據庫的所有權限：

-- 在psql終端中執行
CREATE DATABASE mydb;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
\q  -- 退出psql

注意：避免使用弱密碼（如123456），建議使用包含大小寫字母、數字和符號的復雜密碼。

4. 配置PostgreSQL以允許遠程訪問（可選）
若需從其他機器訪問PostgreSQL，需修改以下兩個配置文件：

• 修改監聽地址：編輯postgresql.conf（路徑為/etc/postgresql/<版本>/main/postgresql.conf），找到listen_addresses并將其設置為'*'（允許所有IP連接）或指定IP（如'192.168.1.100'）：

  sudo nano /etc/postgresql/15/main/postgresql.conf
  # 將 listen_addresses = 'localhost' 改為 listen_addresses = '*'
  

• 配置客戶端認證：編輯pg_hba.conf（路徑為/etc/postgresql/<版本>/main/pg_hba.conf），在文件末尾添加允許遠程連接的規則（如允許192.168.1.0/24網段通過密碼認證訪問所有數據庫）：

  sudo nano /etc/postgresql/15/main/pg_hba.conf
  # 添加以下行（method改為scram-sha-256更安全）
  host    all             all             192.168.1.0/24            scram-sha-256
  

修改完成后，重啟PostgreSQL服務使配置生效：

sudo systemctl restart postgresql

若系統啟用了防火墻（如ufw），需允許PostgreSQL默認端口（5432）的流量：

sudo ufw allow 5432/tcp
```。


**5. 基本數據庫操作（psql常用命令）**  
連接到指定數據庫（如`mydb`）：  
```bash
psql -U myuser -d mydb -h localhost

常用命令：

• \l：列出所有數據庫；
• \c mydb：連接到mydb數據庫；
• \dt：列出當前數據庫中的所有表；
• \q：退出psql。

6. 備份與恢復數據庫

• 備份數據庫：使用pg_dump工具導出數據庫結構和數據為SQL腳本（如備份mydb到/backups目錄）：

  sudo -u postgres pg_dump -U postgres -W -F c -b -v -f "/backups/mydb_backup.backup" mydb
  

  （-W提示輸入密碼，-F c表示壓縮格式，-b包含大對象，-v顯示詳細信息）
• 恢復數據庫：使用psql工具將備份文件導入目標數據庫（如恢復到mydb）：

  sudo -u postgres psql -U postgres -d mydb -f "/backups/mydb_backup.backup"
  ```。
  
  
  

7. 安全設置

• 修改默認密碼：首次安裝后，postgres用戶的默認密碼是隨機生成的，需及時修改：

  sudo -u postgres psql
  ALTER USER postgres WITH PASSWORD 'your_new_password';
  \q
  

• 使用強密碼策略：在postgresql.conf中設置password_encryption = 'scram-sha-256'（默認已啟用），強制用戶使用加密密碼。

8. 擴展開發（可選）
若需擴展PostgreSQL功能（如創建自定義函數、數據類型），可通過以下步驟進行：

• 安裝開發庫：PostgreSQL擴展開發需要libpq-dev庫（包含頭文件和工具）：

  sudo apt install libpq-dev -y
  

• 創建擴展目錄：新建擴展目錄（如my_extension），并創建control文件（描述擴展信息）和c文件（實現功能）：

  mkdir -p my_extension/src
  touch my_extension/my_extension.control
  touch my_extension/src/my_extension.c
  

• 編寫擴展代碼：在my_extension.control中填寫擴展元數據（如名稱、版本），在src/my_extension.c中編寫C語言函數（如簡單的加法函數）：

  // my_extension.c
  #include "postgres.h"
  #include "fmgr.h"
  PG_MODULE_MAGIC;
  
  PG_FUNCTION_INFO_V1(add_numbers);
  
  Datum add_numbers(PG_FUNCTION_ARGS) {
      int32 arg1 = PG_GETARG_INT32(0);
      int32 arg2 = PG_GETARG_INT32(1);
      PG_RETURN_INT32(arg1 + arg2);
  }
  

• 編譯與安裝擴展：使用gcc編譯C代碼為共享庫（.so文件），并將庫文件復制到PostgreSQL的lib目錄：

  gcc -fpic -I/usr/include/postgresql/server/ -shared -o my_extension.so my_extension/src/my_extension.c
  sudo cp my_extension.so /usr/lib/postgresql/15/lib/
  

• 加載擴展：在psql中創建擴展，即可使用自定義函數：

  CREATE EXTENSION my_extension;
  SELECT add_numbers(1, 2);  -- 返回3
  ```。