MathJax

MathJax-2

MathJax-3

Google Code Prettify

置頂入手筆記

EnterproseDB Quickstart — 快速入門筆記

由於考慮採用 EnterpriseDB 或是直接用 PostgreSQL 的人，通常需要一些入手的資料。這邊紀錄便提供相關快速上手的簡單筆記 ～ 這篇筆記以 資料庫安裝完畢後的快速使用 為目標，基本紀錄登入使用的範例：

2016年12月14日 星期三

pgBouncer 簡介

PostgreSQL 對於一個連線的 Session，就是一個 postgres 程序，會開一個 Unix Socket 來進行。所以若是頻繁的斷線/連線，對 PostgreSQL 來說，開銷比較大。

這也使的 PostgreSQL 一直到 9.5 版為止，對於一個處在 Idle 狀態的 Session，沒有自動逾時斷線的機制，除非手動踢掉它。直到 9.6 版，才多設計一個預設不啟用 Transaction Timeout 的參數，idle_in_transaction_session_timeout，才比較「接近」逾時中止交易的機制。這些都算是傾向保留住 Session 的設計。
（註 1：嚴格來說，idle_in_transaction_session_timeout 這個參數不是逾時斷線，而是交易卡住強制中斷）
（註 2：idle_in_transaction_session_timeout 參數可以針對 Database 或 User 進行個別設置。）

因此對於短暫交易的活動型態，在 PostgreSQL 中，便會採用 Connection Pooling 程式，咬住一些 PostgreSQL 的 Session，然後負責應付外部連線，由它進行分配。

目前 Postgres 的 Connection Pooling 中介程式的專案，以 pgBouncer 與 pgPool-2 為主流。前一陣子的維護活動比較歇緩，不過兩者都正常維護中。
上述兩者都能作為中介的連線管理程式，那兩者有哪些不同？
閱讀更多 »
於 沒有留言:
標籤：

2016年12月11日 星期日

postgresql.conf 的 listen_addresses 到底在幹麻的？

在 PostgreSQL 的設定檔 postgresql.conf 裡面，第一項會遇到的參數就是 listen_address：
listen_addresses = 'localhost'
listen_addresses = '*'
listen_addresses = '0.0.0.0/0'
listen_addresses = 'localhost,127.0.0.1,10.140.0.2'

上面四種設置有何不同？

閱讀更多 »
於 沒有留言:
標籤：

2016年12月8日 星期四

mongo_fdw 筆記

MongoDB 資料庫是現在知名的 NoSQL 資料庫，他讓程式開發部門「避開」與資料庫管理人員申請資料欄位的往返溝通，直接塞想要的資料存起來。聽說這在作為網頁後端資料庫很方便。
要是想要把這裡面的資料拉出來分析，就稍微不方便一些，因為他的資料都塞在一份份的 JSON 檔案裡面，要單獨取出大量某個「欄位」（JSON Key）的資料作運算，分析，就有點不方便，尤其要進行普通的關聯式資料庫的 JOIN，尤其不容易。
PostgreSQL 提供的 Foreign Data Wrapper 中，有跨接 MongoDB 的套件，叫做 mongo_fdw，是一個由 EnterpriseDB 公司維護的開源專案。這個模組能夠對 MongoDB 的 JSON 資料，取過來 PostgreSQL 裡面，當成一般表格處理，便能夠和現有資料庫的資料，進行進一步的分析。

以下將示範 mongo_fdw 的套件使用流程。

閱讀更多 »
於 沒有留言:
標籤：

2016年12月5日 星期一

PostgreSQL 的稽核紀錄（Audit Log）功能套件 - pgAudit

稽核日誌（Audit Log）泛指用來紀錄程式活動的紀錄，在企業中很常被應用。在資料庫使用也是重要的功能。

一般要紀錄資料庫活動，可以自己用 Trigger 設計。不過在 EnterpriseDB 提供的企業版 PostgreSQL 裡面也有 edb_audit 的功能，可以不用自己設計 Trigger 就能進行紀錄。

而使用原生的 PostgreSQL，則可以用 PGAudit 這個專案。

這篇便紀錄在 CentOS7 下使用 PGAudit 套件的練習。

以下安裝流程，對於 EnterpriseDB 發行的 PostgreSQL 發行版亦適用：惟需要將相關路徑社製作些許調整。
閱讀更多 »
於 沒有留言:
標籤：

2016年12月1日 星期四

EFM 2.1 版的 Active/Passive Switchover 切換功能

EnterpriseDB 的訂閱服務提供的資料庫高可用套件 — EDB Failover Manager（EFM），前一陣子（2016 年九月）釋出更新版，2.1 版。EFM 在先前的筆記已經有紀錄過 2.0 版的操作（之前筆記）。

在這次更新版中，引進一個重要的新功能 — 資料庫 Active/Passive Switchover 功能。這個功能，能夠將 Active 資料庫（Read-Write）/Pssive 資料庫（Read-Only）角色對調。

該功能在維護資料庫上，提供相當方便性：當有主機維護的需要，便可以依照需要，將 Master 資料庫與 Standby 資料庫對調，將 Standby 主機下線，處理完畢再上線便不會影響 Master 運作。

該功能支援 PostgreSQL 9.3 版本以上的資料庫：這是由於自此版本開始，在 recovery.conf 設定檔中，引進了 recovery_target_timeline 參數。該參數允許資料庫交易日誌的「timeline 接軌」的功能。（忘了 recovery.conf 是什麼？請參考之前 Point-in-Time Recovery 或 Streaming Replication 的筆記）


閱讀更多 »
於 沒有留言:
標籤：

2016年11月16日 星期三

檢視當前的 PostgreSQL Log 的偷懶 Script

每次要一直找新的 PostgreSQL Log 檔名有點麻煩，在這裡作個很簡單的紀錄 ...
把下面這個 Shell Script 的資訊 PGUSER（登入帳號）、PGDATA（資料目錄）、PGDATABASE（登入的資料庫）然後執行，可以比較簡單的找出 Log 檔查看。 
#!/bin/bash


PGUSER=enterprisedb
PGDATA=/opt/PostgresPlus/9.5AS/data
PGDATABASE=edb


LATEST_LOG="SELECT pg_ls_dir
 FROM pg_ls_dir('pg_log')
 ORDER BY (pg_stat_file('pg_log/' || pg_ls_dir)).modification DESC
 LIMIT 1;"


CURRENT_PGLOG_FILE=(`psql -U PGUSER -d "${PGDATABASE}" -c "${LATEST_LOG}" --tuples-only`)


tail -f $PGDATA/pg_log/$CURRENT_PGLOG_FILE

顯然，這個 Script 只有在資料庫有在運作的時候才有用處，資料庫要是是關掉的，這就派不上用場了。當然也可以把 Script 的 LATEST_LOG 變數換成在 Shell 裡面列舉最新的 Log 檔案就好了。

參考資料：


於 沒有留言:
標籤：

2016年11月10日 星期四

同一個 Postgres Cluster 的不同資料庫之間的資料怎麼溝通？

在同一個 PostgreSQL Instance（或稱作一個 PostgreSQL Cluster）裡面，不同的 Database 間要交換資料，這看起來是個小小問題，但其實只能走連線交換資料的方式，沒有想像中的直覺。

PostgreSQL 提供以下兩種方式進行：
1. 用 Postgres_FDW 存取外部表
2. 用 DBLink 連到其他資料庫

其中的 postgres_fdw 在最近發展功能越來越豐富，因此一般建議使用 Postgres_FDW 去進行不同 Database 之間，資料的交換。

以下簡單示範在 EDB 公司的 PostgreSQL 9.5 企業版中使用 postgres_fdw 進行存取。
來源的 database 是 edb，以及裡面的範例資料 Schema；然後在另外一個 database，postgres 建立外部表。


以下即操作步驟
閱讀更多 »
於 沒有留言:
標籤：

2016年11月7日 星期一

練習 - 用 pgAgent 定時透過 foreign data wrapper 拉資料

有時會希望透過 PostgreSQL 拉資料來進行分析，來減輕工作中資料庫的負擔。這一般是利用所謂 ETL 工具定時拉資料達成。不過由於 PostgreSQL 本身就有排程工具的專案，而且在近幾版本的 PostgreSQL9.x 能夠外接資料，因此用此來試試看。

pgAgent 是一個 PostgreSQL 的排程作業工具，而 Foreign Data Wrapper 是 PostgreSQL 的外部資料源存取架構。這兩樣搭起來，加上在 9.5 版引入的UPSERT 功能：INSERT ... ON CONFLICT ... ，就能達成跨資料庫進行資料更新/同步的作業雛型。

以下雛型的練習，方便起見，將從同一個 PostgreSQL Instance 的兩個 Database 之間用 Foreign Data Wrapper 更新資料

閱讀更多 »
於 6 則留言:
標籤：

2016年10月5日 星期三

社群 YUM Repo 維護的 PostgreSQL 9.5 升版 9.6

最近新出 PostgreSQL 9.6 版，把練習環境更新一下

以下紀錄升級的流水帳，適用於 9.x 版之間的升版。

使用的環境是 CentOS 7.2 64-bit，使用 PostgreSQL 維護的 RPM 發行版。

如果使用 EnterpriseDB 公司發行的 PostgreSQL 安裝套件（可以用圖形界面安裝的），步驟是相似的。差別只在安裝程式的位置不在 /usr/pgsql-9.x/ 而是在 /opt/PostgreSQL/9.x/ 底下。對 EnterpriseDB 的企業版，則安裝位置在 /opt/PostgresPlus/9.xAS/。

PostgreSQL 升級工具 pg_upgrade 會把實體資料檔「複製」到新的資料夾，以及作相關的變動調整。

升級需求：PostgreSQL 升級程式成功升級之後，舊資料會保留著，標記成 Old，所以需要兩倍的空間；當然有需要也有沿用舊的 Cluster DIR 的方式。若要初估時間，可以用 I/O 讀寫速率來除資料大小，因為基本上資料升版大部分是以複製作業進行。

進一步升級使用方式，請參考手冊頁面 PostgreSQL: Documentation: 9.6: pg_upgrade 。
例如，升級工具提供下列額外功能：
  • Dry-Run 模式：檢查有沒有升級問題。常發生的是就資料庫裡面安裝的套件（例如 postgresql96-contrib modules 或自己編譯的套件），在新版資料庫上面忘記裝上去，在移轉中便會出現沒升級成功。於是可以先用此模式去檢查。
  • Parallel Jobs 模式：指定執行緒加速移轉時間
  • 使用 Hard Link 模式：
  • 保留資料庫 Log 選項
就可以在手冊頁面找到使用方式。

以下開始流水帳紀錄：

閱讀更多 »
於 沒有留言:
標籤：

2016年10月4日 星期二

列舉 PostgreSQL 表格所佔的空間大小

 要列舉表格大小（不包含全部的 Relation 物件），可以用下列方式查詢：

SELECT
   schemaname as "Schema",
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_all_tables
   WHERE schemaname <> 'pg_catalog'
   AND schemaname <> 'information_schema'
   AND schemaname <> 'sys'
   AND schemaname <> 'dbo'
   AND schemaname !~ '^pg_toast'
   ORDER BY pg_total_relation_size(relid) DESC;


其中
  • Schema 為 Schema 名稱
  • Table 為表格名稱
  • Size 為表格大小
  • External Size 為依附在表格的其他物件（Index 或其他物件）的大小：也就是該表格相關的 Index、Primary Key、Sequence ... 等物件的總大小；也就是用 \d+ 列表格裡面比 \d 多列出來的部份。

最後補充，在此用到的 pg_statio_all_tables 為系統統計的 View，pg_stat* 裡面的其中一個功能。

參考：
於 沒有留言:
標籤：

psql 的 Meta-Commands 等同執行了什麼 SQL Query?

想要找出 psql meta-command 對照到什麼樣的 SQL 指令，可以藉由變更 log_min_duration_statement 參數（預設 -1），就能夠從資料庫的 log 看出送到資料庫的是什麼指令了。

首先先在這次操作的 Session 設定上述 Log 紀錄參數
edb=# SET log_min_duration_statement TO 0;

對照一下 Log （位在 $PGDATA/pg_log/ 底下）內容，可以確認上面變更
2016-08-17 03:57:16 UTC LOG: duration: 0.164 ms statement: set log_min_duration_statement to 0;


接著可以開始觀察了，下舉四個例子：


閱讀更多 »
於 沒有留言:
標籤：

2016年10月2日 星期日

查看 Foreign data wrapper 相關資訊

使用 Foreign Data Wrapper，如果想要找出相關資訊怎麼辦？
Foreign Data Wrapper 分三部份：Foreign Serbver、User Mapping 以及 Foreign Tables。這些有關資訊都被紀錄到 Information Schema 裡面了

  • 列舉已經設置的 User Mapping

edb=# \deu+
                 List of user mappings
 Server |  User name   |          FDW Options           
--------+--------------+--------------------------------
 orabg5 | enterprisedb | (username 'hr', password 'hr')
 to_ora | enterprisedb | ("user" 'hr', password 'hr')
(2 rows)

edb=# select * from pg_user_mapping;                                 
 umuser | umserver |         umoptions         
--------+----------+---------------------------
     10 |    16757 | {user=hr,password=hr}
     10 |    16768 | {username=hr,password=hr}
(2 rows)

edb=# -- 上面用 Super User，把密碼也列出來了
edb=# \c edb aaa
Password for user aaa:
You are now connected to database "edb" as user "aaa".
edb=> select * from pg_user_mapping;
ERROR:  permission denied for relation pg_user_mapping
edb=> \deu+
        List of user mappings
 Server |  User name   | FDW Options
--------+--------------+-------------
 orabg5 | enterprisedb |
 to_ora | enterprisedb |
(2 rows)
edb=>

上面可以看到，如果當初在設置遠端資料庫的帳號時，也多存密碼的話，使用資料庫的 Super User 會看到相關密碼，而其他沒有權限的帳戶則不會看到。

  • 列舉 Foreign Servers

edb=# select srvname, srvoptions from pg_foreign_server;             
 srvname |                                                                             srvoptions                                                                             
---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 to_ora  | {dbserver=//10.140.0.18/xe}
 to_ora2 | {dbserver=//localhost/xe}
 orabg5  | {drivername=oracle.jdbc.driver.OracleDriver,url=jdbc:oracle:thin:@10.140.0.18:1521:xe,querytimeout=15,jarfile=/usr/lib/jvm/jre/lib/ext/ojdbc7.jar,maxheapsize=600}
(3 rows)

列舉 Foreign Table

edb=# select * from information_schema.foreign_tables;
 foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name
-----------------------+----------------------+--------------------+------------------------+---------------------
 edb                   | enterprisedb         | tst2ftw            | edb                    | to_ora
 edb                   | enterprisedb         | testjfdw           | edb                    | orabg5
(2 rows)

當然，使用者對應（User Mapping）、外部伺服器（Foreign Servers）和外部表（Foreign Table）等物件，也能分別用 psql 裡面的 \deu、\des、\det 等 meta-command 查詢出來。



參考：
於 沒有留言:
標籤：

2016年9月6日 星期二

EDB 功能 - Dynatune 動態效能調整模組

EnterpriseDB 公司的企業版資料庫，可以設定資源控管模式，進行效能的動態的調整，這功能稱作 Dynatune。在安裝過程中，就會進行相關的設定。安裝之後，也可以事後於 $PGDATA/postgresql.conf 設定檔裡面找到相關設定值。

在安裝過程中，會出現以下選項：
image32.png
（圖一）

，以及
 image33.png
（圖二）

這兩項設置，分別對應到以下兩項 postgresql.conf 內的設置參數（GUC）：

edb_dynatune
設定的是要允許 EDB 使用多少作業系統的資源（0~100）（圖一）
edb_dynatune_profile
設定的是 EDB 要用來進行交易處理還是分析處理（oltp|mixed|reporting）（圖二）

 
因此，在企業版 PostgreSQL 裡面，有以下的效能組合模式：
edb_dynatune

edb_dynatune_profile
development machine
（1 - 33）
mixed
（33 - 66）
dedicated
（66 - 100）
oltp
VV
V(一般建議)
mixed
V
V(預設值)
V
reporting
VVV

進一步的資訊，Dynatune 參考的相關作業系統資訊有
System Shared Memory
sys_shared_mem;
Current allocated Shared Buffer
Current MaxFSMRelations settings;
Number of Buffers;
Total Memory.

進而被 Dynatune 調整的功能有（截至 EPAS 9.5 版為止）
max_connections
work_mem (動態調整)
maintenance_work_mem (動態調整)
shared_buffers
max_fsm_relations
max_fsm_pages
wal_buffers
effective_cache_size (動態調整)
random_page_cost
checkpoint_segments (Not in 9.5)
autovacuum_vacuum_threshold
autovacuum_vacuum_scale_factor
autovacuum_analyze_threshold
autovacuum_analyze_scale_factor
autovacuum_naptime
stats_row_level (Not in 9.x)
Autovacuum

如果需要調整效能模式，只要將 $PGDATA/postgresql.conf 相關的參數修改好，再重新啟動資料庫即可。

參考：
於 沒有留言:
標籤：

2016年8月22日 星期一

EDB 功能 - Optimizer Hints

在 PostgreSQL 中要影響執行計畫，就是透過 PostgreSQL 的 GUC 參數，對 Query Planner 選用執行計畫優先順序影響。

而 PostgreSQL 企業版則提供直接影響 Query Plan 的工具 — Optimizer hints，能夠以用 Oracle 的 Optimizer Hints 工具的使用方式，去影響 Postgres Query Planner 的行為。

Optimizer hints 將 Postgres 裡面的可行的設置方式，對照成 「Oracle-Style」 的 Optimizer Hint，直接對執行計畫進行動作的開關。如果 Query Planner 對於所指令的 Hint 找不到符合的執行計畫，這個 Hint 就會被忽略。

由於 Hint 是包在註解中的，於是如果 Hint 沒有被理會，也不會跳出任何錯誤的。

使用方式，是採用多行註解進行 Hint 的指定，適用於 SELECT、INSERT、UPDATE、DELETE 等 SQL 指令：
SELECT
/*+
放置 Hint 內容
*/
col_list FROM tbl;

以下利用企業版內建範例表格 emp，作一個簡單示範：
edb=# explain SELECT /*+ FIRST_ROWS(1) */ * FROM emp ORDER BY empno;               
                            QUERY PLAN                            
---------------------------------------------------------------------
 Index Scan using emp_pk on emp  (cost=0.14..10.85 rows=14 width=45)
(1 row)

edb=# explain SELECT /*+ FULL(emp) */ * FROM emp ORDER BY empno;
                       QUERY PLAN                       
-----------------------------------------------------------
 Sort  (cost=1.41..1.44 rows=14 width=45)
   Sort Key: empno
   ->  Seq Scan on emp  (cost=0.00..1.14 rows=14 width=45)
(3 rows)

根據使用手冊，目前 9.5 企業版可以指定與 Oracle Optimizer Hint 相容的的有：
類型
Hint
Row Number Correction
CHOOSE
ALL_ROWS
FIRST_ROWS
FIRST_ROWS_10
FIRST_ROWS_100
FIRST_ROWS_1000
FIRST_ROWS(數量)
Scan Method
FULL(表格名稱)
INDEX(表格名稱 INDEX名稱)
NO_INDEX(表格名稱 INDEX名稱)
Join Method
USE_HASH(表格名稱)
NO_USE_HASH(表格名稱)
USE_MERGE(表格名稱)
NO_USE_MERGE(表格名稱)
USE_NL(表格名稱)
NO_USE_NL(表格名稱)
Join Order
ORDERED


最後，社群也有針對 postgreSQL 開發出 Hint 套件，稱為 pg_hint_plan。不過該套件和 EDB 企業版內建的 Optimizer Hints 相比較，企業版仍然想辦法對應 Oracle 提供的 Hints 種類為主；而 pg_hint_plan 則是以 postgresql.conf 設定檔內的 Planner Method Configuration 小節對應的 Planner Methode 為主（詳見 Hint 支援清單），對於 Oracle 習慣用戶來說，還是從 Postgres 企業版比較親切一些。

參考

Postgres Query Optimizer 基本觀念：Hinting at PostgreSQL | Blog - 2ndQuadrant



其他資料：


社群版相似工具： pg_hint_plan
相關資料：
於 沒有留言:
標籤：
訂閱： 文章 (Atom)