PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新使用者)

PostgreSQL , Linux , CentOS

新使用者部署PostgreSQL以及空間資料庫插件PostGIS的指南。

如果是内網環境，可以将包全部下載下傳到本地再用rpm安裝。

安裝epel和postgresql yum rpm兩個包後再執行：

1、使用yum-utils的yumdownloader下載下傳需要的安裝包，包括依賴包。

2、其他指令（使用yumdownloader下載下傳包到本地後，下面的可以忽略...）。

例子(安裝epel和postgresql yum rpm兩個包後再執行) , 注意本地已經安裝的包不會被下載下傳，是以假設你就是想下載下傳的話，可以先yum remove一下已安裝的包，然後再執行：

列出要安裝的包的所有依賴，例如:

例子

1、阿裡雲ECS

2、OS：CentOS 7.x x64

3、本地盤（40 GB）

4、雲盤（200GB），建議根據實際情況配置雲盤。建議多塊雲盤做LVM條帶，提高吞吐和IOPS。

5、PostgreSQL 10

6、PostGIS 2.4

1. sysctl

注意某些參數，根據記憶體大小配置（已說明）

含義詳見

<a href="https://github.com/digoal/blog/blob/master/201608/20160803_01.md">《DBA不可不知的作業系統核心參數》</a>

2. 生效配置

最好再關注一下/etc/security/limits.d目錄中的檔案内容，會覆寫/etc/security/limits.conf的配置。

已有程序的ulimit請檢視/proc/pid/limits，例如

如果你要啟動其他程序，建議退出SHELL再進一遍，确認ulimit環境配置已生效，再啟動。

（建議按業務場景設定，這裡先清掉）

配置範例：

如果沒有這方面的需求，建議禁用

關閉不必要的, 例如

1、grub1代采用這種方法：加上前面的預設IO排程，如下：

如果所有盤都是SSD，可以這樣。

2、如果隻是某些盤是SSD，那麼隻對這些盤設定為deadline。或者如果用的是grub2：

2、grub2代，可以使用rc.local。

當場生效：

1、用parted分區，可以自動對齊。

2、如果是多塊雲盤，建議使用LVM條帶，提高讀寫吞吐和IOPS。

例子(注意你的塊裝置名字可能不一樣，建議 lslbk 看一下塊裝置名字)：

1、如果是條帶，注意建立檔案系統時，也要使用條帶。

2、EXT4例子：

3、建議使用的ext4 mount選項

1、安裝EPEL

<a href="http://fedoraproject.org/wiki/EPEL">http://fedoraproject.org/wiki/EPEL</a>

2、安裝PostgreSQL yum

<a href="https://yum.postgresql.org/repopackages.php#pg96">https://yum.postgresql.org/repopackages.php#pg96</a>

查詢軟體目錄在哪裡:

1、postgresql.conf

2、pg_hba.conf (資料庫ACL通路控制清單,防火牆)

連接配接到PG叢集，在需要使用空間資料的DB中，使用超級使用者，建立空間資料庫插件。

<a href="https://github.com/digoal/blog/blob/master/201710/20171026_02.md">《PostgreSQL + PostGIS + SFCGAL 優雅的處理3D資料》</a>

驗證1、空間資料庫PostGIS的使用

驗證2、高效分區的使用

<a href="https://postgrespro.com/docs/postgresproee/9.6/pg-pathman">https://postgrespro.com/docs/postgresproee/9.6/pg-pathman</a>

驗證3、Sharding的使用，參考下一篇文檔。

如果要高效率，就選pg_pathman。（它使用custom scan，避免了inherit中繼資料的LOCK）

如果需要持久的相容，建議使用PostgreSQL 10的原生文法。

pgadmin4較重（采用WEB服務，含監控功能），建議下載下傳pgadmin3。

<a href="https://www.pgadmin.org/download/">https://www.pgadmin.org/download/</a>

1、備份

2、監控

3、容災

4、HA

5、時間點恢複

6、資料遷移

7、資料導入

8、日常維護

請參考

<a href="https://github.com/digoal/blog/blob/master/201706/20170601_02.md">《PostgreSQL、Greenplum 寶典《如來神掌》》</a>

1、簡單性能測試

2、診斷

3、函數性能診斷

<a href="https://github.com/digoal/blog/blob/master/201611/20161121_02.md">《PostgreSQL 函數調試、診斷、優化 &amp; auto_explain》</a>

1、安裝mysql_fdw插件。

2、在資料庫中加載mysql_fdw插件(在需要使用mysql_fdw的database中建立)。

3、使用舉例。

4、小技巧，一次導入目标端的所有表或指定多個表作為本地外部表，結構一樣，本地表名一樣。

克隆，便于下次部署。

1、對于可以預計算的AP應用，使用PostgreSQL 10完全滿足需求，這類應用PostgreSQL可以OLTP和OLAP一把抓。

2、對于不能預計算的AP應用，視資料量和運算量，PostgreSQL結合強悍的硬體（IO能力強，CPU核數多），同樣可以搞定。

3、對于不能預計算，并且運算量和資料量超出了單機硬體能力能承受的範疇，再考慮Greenplum、Citus、PG-XL這樣的産品。

<a href="https://github.com/digoal/blog/blob/master/201708/20170812_01.md">《PostgreSQL on ECS多雲盤的部署、快照備份和恢複》</a>

<a href="https://github.com/digoal/blog/blob/master/201611/20161121_01.md">《PostgreSQL on Linux 最佳部署手冊》</a>

<a href="https://github.com/digoal/blog/blob/master/201609/20160926_01.md">《PostgreSQL 資料庫開發規範》</a>

<a href="https://github.com/digoal/blog/blob/master/201702/20170216_01.md">《PostgreSQL 清理redo(xlog,wal,歸檔)的機制 及 如何手工清理》</a>

mysql_fdw foreign server、user mapping、foreign table的options如下:

mysql_fdw/options.c

mysql與pgsql的類型映射：