PostgreSQL

解決問題 PostgreSQL (3)

教學目標

初步了解如何透過參數調整解決 PostgreSQL 資料庫效能的問題。

重點概念

首先當我們系統上線之後,經常會遇到系統效能的問題,此時我們要如何解決問題呢?任何問題時主要會透過以下六個步驟進行故障排除,分別為:

  1. 識別問題。
  2. 建立可能原因的理論。
  3. 測試理論以利確定原因。
  4. 建立解決問題和實施解決方案的行動計劃。
  5. 驗證完整的系統功能,並在適用時實施預防措施。
  6. 記錄分析結果文件。

因此當我們識別問題可能是 PostgreSQL 資料庫的效能問題時,則我們就會建立可能原因的理論為 PostgreSQL 資料庫的參數設定值不夠處理大量的連線數。

接著我們要如何測試理論以利確定原因呢?主要可以透過系統的記錄檔查看是否有相關資訊,像是下述記錄檔關鍵資訊,此時我們根據記錄檔關鍵資訊開始要建立解決問題和實施解決方案的行動計劃,一般來說,我們主要除了調整系統參數之外,更會調整 PostgreSQL 資料庫參數,並且當完成參數調整之外,我們就會重新啟動服務。

記錄檔關鍵資訊

1
2
3
...
Could not get JDBC Connection; nested exception is com.atomikos.jdbc.AtomikosSQLException: Connection pool exhausted - try increasing 'maxPoolSize' and/or 'borrowConnectionTimeout' on the DataSourceBean.
...

再來並且驗證完整的系統功能,並在適用時實施預防措施,針對實施預防措施則建議深入了解 PostgreSQL 資料庫參數,以利資料庫優化相關設定,以利預防問題在短時間內又會再次發生。此時我們主要會開啟 postgresql.conf 設定檔調整參數,請參考下表:

設定檔參數調整

參數 說明
max_connections 設定允許客戶端連接的最大數目。
shared_buffers 設定有多少記憶體可以被用於快取資料,建議為記憶體 1/4。
work_mem 設定有多少記憶體可以被用於執行複雜的查詢和排序。
maintenance_work_mem 設定有多少記憶體可以被用於維護作業,通常這這些指令將會消耗比轉多的資源,因此應該盡快執行完畢。
effective_cache_size 設定有多少快取能夠被使用,建議為記憶體 1/2。
wal_buffers 設定記錄檔快取的大小。
checkpoint_segments 設定記錄檔的最大數量。
checkpoint_timeout 設定兩次檢查點的逾時時間。
checkpoint_completion_target 設定兩個檢查點完成時間為逾時時間的多少比例。

最後我們則必須記錄分析結果文件,以利後績歸檔和進行變更管理。同時若發現是系統的限制,則建議查看是否有相關的應用程式更新已經釋出解決此次的問題,若有則建議除了調整系統或資料庫參數之外,更能夠更新應用程式的功能,以利從根本原因解決問題。

相關資源

解決問題 PostgreSQL (2)

教學目標

初步了解如何解決 Windows 排程工作無法直接定期執行 PostgreSQL 之 SQL 指令操作的問題。

重點概念

首先雖然在 Windows 伺服器平台中我們能夠透過 psql 工具透過命令提示列的指令方式登入 PostgreSQL 資料庫執行 SQL 指令操作,但是需要手動輸入 PostgreSQL 帳號的密碼,此時將會無法設定 Windows 排程工作定期執行 PostgreSQL 的 SQL 指令操作。此外雖然能夠設定「PGPASSWORD」環境變數為 PostgreSQL 使用者預設密碼,但此密碼將會是明碼顯示,所以將會被企業或組織中資安單位所挑戰此方式非常不安全。

接著我們該如何先解決 PGPASSWORD 環境變數以明碼設定密碼的問題呢?最簡單的方式就是撰寫加解密的 程式解決此問題,其中程式主要有兩個執行步驟如下:

  1. 企業中的系統安全人員透過程式建立 pgpass 加密密碼檔案。
  2. 企業中的系統管理人員透過程式以 pgpass 加密密碼檔案直接執行 psql 工具。

再來我們開始需要撰寫程式,此篇主要以 Java 程式語言撰寫範例程式為主,但是為了避免 Oracle JAVA 授權的問題,所以建議透過 OpenJDK 進行程式開發,其中需要特別注意的是 CRYPT_KEY 加密金鑰需要為 16 個字元,否則加密結果會為 NULL,以及需要建立「psql.bat」批次檔主要執行 psql 工具。

撰寫 JAVA 範例程式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
import javax.crypto.Cipher;  
import javax.crypto.spec.SecretKeySpec;
import java.io.*;
public class PGSQL {
private static final String AES = "AES";
private static final String CRYPT_KEY = "LEOYEH1234567890";
public static byte[] encrypt(byte[] src, String key) throws Exception {
Cipher cipher = Cipher.getInstance(AES);
SecretKeySpec securekey = new SecretKeySpec(key.getBytes(), AES);
cipher.init(Cipher.ENCRYPT_MODE, securekey);
return cipher.doFinal(src);
}
public static byte[] decrypt(byte[] src, String key) throws Exception {
Cipher cipher = Cipher.getInstance(AES);
SecretKeySpec securekey = new SecretKeySpec(key.getBytes(), AES);
cipher.init(Cipher.DECRYPT_MODE, securekey);
return cipher.doFinal(src);
}
public static String byte2hex(byte[] b) {
String hs = "";
String stmp = "";
for (int n = 0; n < b.length; n++) {
stmp = (java.lang.Integer.toHexString(b[n] & 0XFF));
if (stmp.length() == 1)
hs = hs + "0" + stmp;
else
hs = hs + stmp;
}
return hs.toUpperCase();
}
public static byte[] hex2byte(byte[] b) {
if ((b.length % 2) != 0)
throw new IllegalArgumentException("");
byte[] b2 = new byte[b.length / 2];
for (int n = 0; n < b.length; n += 2) {
String item = new String(b, n, 2);
b2[n / 2] = (byte) Integer.parseInt(item, 16);
}
return b2;
}
public final static String decrypt(String data) {
try {
return new String(decrypt(hex2byte(data.getBytes()),CRYPT_KEY));
} catch (Exception e) {
System.out.println(e.toString());
}
return null;
}
public final static String encrypt(String data) {
try {
return byte2hex(encrypt(data.getBytes(), CRYPT_KEY));
} catch (Exception e) {
System.out.println(e.toString());
}
return null;
}
public static void main(String[] args) {
try {
if (args[0].equals("-c")) {
String pw = args[1];
System.out.println("PostgreSQL Default Password:" + pw);
String pwEncrypt = encrypt(pw);
System.out.println("PostgreSQL Encrypt Password:" + pwEncrypt);
OutputStream fos = new FileOutputStream("pgpass");
fos.write(pwEncrypt.getBytes());
fos.close();
} else if (args[0].equals("-r")) {
File keyfile = new File("pgpass");
FileInputStream fis = new FileInputStream("pgpass");
int length = (int)keyfile.length();
byte[] cipherText = new byte [length];
fis.read(cipherText);
fis.close();
String pwDecrypt = decrypt(new String(cipherText, "UTF-8"));
ProcessBuilder builder = new ProcessBuilder("cmd.exe", "/k", "SET PGPASSWORD=" + pwDecrypt + "& call psql.bat");
builder.redirectErrorStream(true);
Process p = builder.start();
BufferedReader r = new BufferedReader(new InputStreamReader(p.getInputStream()));
String line;
while (true) {
line = r.readLine();
if (line == null) { break; }
System.out.println(line);
}
}
} catch (Exception e) {
System.out.println(e.toString());
}
}
}

最後當我們根據企業或組織的需求撰寫完成 Java 範例程式,就能夠以 pgpass 加密密碼檔案直接執行 psql 工具,並且就能夠設定 Windows 排程工作定期執行 PostgreSQL 的 SQL 指令操作。此時就能夠不以明碼的方式設定 PostgreSQL 預設密碼至環境變數中,以利解決 Windows 排程工作無法直接定期執行 PostgreSQL 之 SQL 指令操作的問題。至於 pgpass 加密密碼檔案要如何進行保護呢?理應能夠透過企業或組織中 Windows 伺服器的檔案權限控管即可。

編譯 Java 範例程式

1
> javac PGSQL.java

透過程式建立 pgpass 加密密碼檔案

1
> java PGSQL -c [PostgreSQL 預設密碼]

透過程式以 pgpass 加密密碼檔案直接執行 psql 工具

1
> java PGSQL -r

總結 PostgreSQL 資料庫若需要定期進行資料清理時,將會面臨 Windows 排程工作無法直接定期執行 PostgreSQL 之 SQL 指令操作的問題,此時我們就能夠透過 JAVA 範例程式解決此問題。

相關資源

解決問題 PostgreSQL (1)

教學目標

主要解決如何在 Python 中使用 PostgreSQL 資料庫的問題。

重點概念

首先 PostgreSQL 主要是強大的開發源碼物件關係資料庫系統,它已經有 15 年以上的積極發展和經過驗證的架構,在可靠性、資料完整性和正確性方面皆有很不錯的評價。同時它完全符合 ACID 原則,完全支持 Foreign Keys、Joins、Views、Triggers 、Stored Procdures、:等,以及包括大多數 SQL 的資料類型,像是 INTEGER、NUMERIC、BOOLEAN、CHAR、VARCHAR、DATE、INTERVAL、TIMESTAMP、…等,當然也支援以二進位的方式儲存龐大物件,包括圖片、聲音或視頻。

接著 PostgreSQL 做為一個企業級資料庫,它主要擁有複雜的功能,像是多版本平行控管、時間恢復點、資料表空間、非同步複製、巢狀交易、線上熱備份、複雜的查詢規劃和優化器、寫入前記錄容錯、… 等。同時它也支援 UNICODE 國際字元集編碼,以及具備高度可擴展性能夠有效管理資料量和同時在線使用者量,但還是會有限制,請參考下表。

限制 最大值
資料庫大小 無限制
資料表大小 32 TB
資料列大小 1.6 TB
資料欄大小 1 GB
每個資料表的列數 無限制
每個資料表的欄數 250 ~ 1600 (取決於欄位類型)
每個資料表的索引數 無限制

再來 PostgreSQL 符合 ANSI SQL:2008 標準,它完全支援子查詢,讀取提交和可序列化的交易隔離層級,同時 PostegreSQL 有一個完整關係的系統目錄支援每個資料庫的多個模式,以及目錄也可以透過 SQL 標準中定義的資訊綱要進行存取。此外它針對資料完整性主要包括 Primary Keys、Foreign Keys、Check Contraints、Unique Contraints、Not Null Contraints、…等,當然它除了能夠有效確保資料完整性之外,還具備大量擴展和進階的功能,像是 LIMIT 和 OFFSET 允許回傳部份結果集,它支援複合、唯一、部份、功能索引以及任何 B-tree、R-tree、hash 或 GiST 儲存方法加速存取速度。

最後 PostgreSQL 支援許多程式語言,像是 ODBC、Java (JDBC)、Python、C++、…等,本篇主要介紹如何在 Python 程式語言透過 psycopg2 模組操作 PostgreSQL 資料庫,主要有幾個步驟,分別為:

  1. 安裝 psycopg2 模組。
  2. 載入 psycopg2 模組。
  3. 建立 PostgreSQL 資料庫連線。
  4. 針對 PostgreSQL 資料庫執行 SQL 指令
  5. 關閉 PostgreSQL 資料庫連線

安裝 psycopg2 模組

1
$ yum install python-psycopg2

載入 psycopg2 模組

1
2
# 載入 psycopg2 模組
import psycopg2

建立 PostgreSQL 資料庫連線

1
2
# 連線至存在的資料庫
conn = psycopg2.connect(database="testdb", user="postgres", password="xxx", host="127.0.0.1", port="5432")

針對 PostgreSQL 資料庫執行 SQL 指令

1
2
3
4
5
6
# 取得指標執行資料庫操作
cur = conn.cursor()
# 執行 SQL 指令
cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
# 提交目前的交易至資料庫中進行修改
conn.commit()

關閉 PostgreSQL 資料庫連線

1
2
# 關閉  PostgreSQL 資料庫連線
conn.close()

總結我們透過 psycopg2 模組就能夠解決透過 Python 操作 PostgreSQL 資料庫的問題,至於資料庫最基本的 CRUD 操作,僅需更改 SQL 指令為 INSERT、SELECT、UPDATE和 DELETE 即可,其中 SELECT 比較特別需要將「conn.commit()」換成「rows = cur.fetchall()」才能夠讀取 SELECT 指令回傳的全部內容。

相關資源

SAS 系統管理 (49)

教學目標

初步了解如何查看 SAS 解決方案中的 PostgreSQL 資料庫內容與設定。

重點概念

首先當我們安裝 SAS 解決方案時,預設會透過 PostgreSQL 資料庫儲存 SAS 伺服器的內容。一般來說,SAS Web Infrastructure Platform Service 主要被設定使用 SAS Web Infrastructure Platform Data Server 儲存 SAS 9.4 Middle-Tier 伺服器的相關資訊和建立 SAS 解決方案的相關資訊,像是 SAS Visual Analytics、SAS Environment Manager、SAS Deployment Backup and Recovery Tool、…,預設資料庫的版本為 PostgreSQL 9.1.9,此時我們要如何查看 PostgreSQL 資料庫的內容,主要可以透過 pgAdmin4 工具進行操作,所以我們需要先安裝 pgAdmin4 工具。

接著當我們安裝 pgAdmin4 工具完成之後,新增「Server」,接著點選「Connection」頁籤輸入下述資訊。

屬性名稱 屬性值
Host name/address SAS Server-Tier 伺服器的主機名稱或 IP 位置
Port 9432
Maintenance database postgres
Username dbmsowner

再來按下「Save」之後,點選該伺服器,然後輸入「Password」,按下「OK」。此時我們就能夠看到六個預設的資料庫,分別為:

  1. Administration
  2. EVManager
  3. SharedService
  4. VDBService
  5. postgres
  6. transportsvcs_db

其中我們主要透過「dbmsowner」使用者管理 PostgreSQL 資料庫,此外針對 SAS 伺服器主要的內容,我們則可以點選「SharedServices」資料庫中的「Schemas」->「public」->「Tables」查看 SAS 伺服器相關資料表的內容。

最後我們可以針對「SharedService」資料庫設定其它資料庫,目前已測試的資料庫如下所示:

  1. DB2 Version 9.7 FP3
  2. MySQL 5.5
  3. Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  4. PostgreSQL 9.1.9
  5. SQL Server 2008 R2

此外請注意 SAS Web Infrastructure Platform Data Server 為了支援 SAS Environment Manager 和 SAS Deployment Backup and Recovery Tool 則還是必須進行內建安裝 PostgreSQL 9.1.9 並無法支援其它資料庫,至於如何修改則總共有三個步驟,分別為:

  1. 確認資料庫版本已被測試。
  2. 新增使用者和角色。
  3. 設定時選擇自訂。
  4. 取消勾選「Use SAS Web Infrastructure Platform Data Server」選項。
  5. 選擇資料庫類型進行設定。

總結我們安裝 SAS 解決方案時預設會透過 PostgreSQL 資料庫儲存 SAS 伺服器的內容,主要資料庫為「SharedServices」,同時我們可以透過 pgAdmin4 工具查看「SharedServices」資料庫中的資料表內容,以及針對「SharedServices」資料庫設定其它類型的資料庫。

相關資源