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