檢視當前的 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 檔案就好了。

參考資料：

postgresql - How can I dynamically determine the current Postgres log file? - Stack Overflow

ls - Get the last file name in a variable in bash? - Super User

同一個 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 建立外部表。

以下即操作步驟

練習 - 用 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 更新資料

社群 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 選項

就可以在手冊頁面找到使用方式。

以下開始流水帳紀錄：

列舉 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* 裡面的其中一個功能。

參考：

Check size of tables and objects in PostgreSQL database - General IT Documentation - D-BSSE Wiki

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;

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

查看 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 查詢出來。

參考：

oracle10g - Show linked servers in PostgreSQL - Stack Overflow

information_schema with PostgreSQL · Cogs and Levers