PostgreSQL準備和部署

MySQL和PostgreSQL——兩個開放源碼的關系資料庫管理系統，每個系統都有自己的粉絲。多年來，MySQL一直是PostgreSQL社群的樂趣之源——預設的、非常寬松的SQL模式、缺乏對更複雜連接配接的支援、沒有位圖索引等等。另一方面，MySQL愛好者指出了MySQL的健壯性，以及通過複制向外擴充是多麼容易。

您可能會問——為什麼要在一個環境中同時使用這兩個資料庫?在Galera叢集旁邊運作一個複制的PostgreSQL設定有什麼價值嗎?在這篇部落格文章中，我們将從MySQL DBA的角度來回答這個問題。我們還将讨論部署PostgreSQL的不同方法。

為什麼是PostgreSQL?

複雜查詢的性能

PostgreSQL的一個優點是它能夠很好地處理複雜的查詢，而且是以一種有效的方式。PostgreSQL能夠使用不同的連接配接算法(比如散列連接配接)，并且在建構最佳執行計劃方面非常靈活。另一方面，MySQL隻支援嵌套循環連接配接，這可能不是每個查詢的最佳選擇。

PostgreSQL可能比MySQL更快的另一個原因是子查詢處理。在執行子查詢方面，MySQL優化器遠非完美。在過去，它将大部分查詢作為依賴子查詢執行，并且需要手動重寫查詢來加快這類查詢的速度。在MySQL 5.6中，已經做了一些改進，現在可以實作子查詢的物化。MySQL 5.7進一步改進了對這類查詢的處理，但是它仍然可以産生一個不如PostgreSQL那麼理想的查詢執行計劃。

PL/pgSQL

在MySQL中建立程式非常困難。它中可用的程式設計語言是有限的，而且遠不如其他RDBMS系統(包括PostgreSQL)中可用的語言靈活。在PostgreSQL中建構存儲過程要比在MySQL中容易得多——它可以建構一組存儲過程，甚至可以将複雜的操作轉移到資料庫中。使用MySQL，由于MySQL存儲過程語言的限制，您很可能不得不将一些邏輯轉移到應用程式中。使用PostgreSQL更好的是，PL/pgSQL不是惟一可以編寫過程的語言—它可以是C，也可以是PL/Tcl、PL/Perl和PL/Python，這些都包含在核心發行版中。還可以添加許多其他選項。

JSON和GIS支援

PostgreSQL支援JSON和GIS資料——如果您使用其中一種類型的資料，您可能希望在您的環境中包含PostgreSQL。說到MySQL, GIS資料是通過MyISAM表來支援的，而且直到最近才被引入到MySQL 5.7的InnoDB中。JSON資料類型在MySQL 5.7中也可用。在編寫本文時，Galera不支援5.7，是以Galera使用者可以選擇使用單獨的5.7執行個體或PostgreSQL來處理這類資料。使用PostgreSQL的另一個理由是，InnoDB中對GIS和JSON的支援是相當新的。這些特性還不成熟，可能需要一些時間來解決早期的問題。PostgreSQL為您提供了一個經過良好測試的替代方案

PostgreSQL的部署

PostgreSQL在大多數Linux的發行版本中都可用，你也可以簡單的通過yum或者apt-get進行安裝。

當安裝完成後，PostgreSQL提供了安全的通路的方式-你隻能在本地進行通路，直到你切換到postgres使用者下。此時，你可以通過如下方式通路。(譯者注:當到PG10的時候，預設的通路方式已經改變，任何使用者都可以在本地通路)

[root@localhost ~]# su - postgres
上一次登入：五 7月 12 16:11:26 CST 2019pts/0 上
[postgres@localhost ~]$ psql
psql (10.1)
Type "help" for help.

postgres=#            

你可以使用這種方式通路，但是更好的方式通過配置檔案控制用戶端的通路。

配置檔案為pg_hba.conf(HBA stands for host-based authentication.) ，檔案一般在$PGDATA目錄下。

PostgreSQL 10 安裝完成後，預設的pg_hba.conf檔案如下，有六條定義好的規則條目。

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust           

第一列為主機的類型：

• local(unix-domain的socket連接配接通路，即通過運作在同一台主機的程序間進行通信)
• host(TCP/IP socket連接配接)
• hostssl(SSL加密的TCP/IP socket連接配接)

第二列為DATABASE表示資料庫名稱

• all

  比對所有資料庫

• sameuse

  比對請求的使用者和資料庫一緻的情況

• samerole

  比對請求的使用者所在角色與資料庫一緻的情況

• replication

  比對實體複制的連接配接請求

• 資料庫名稱

  ,或者多個資料庫名稱用

  逗号

注意ALL不比對 replication

第三清單示使用者名稱

• all

• 一個使用者名

• 一組使用者名

  ，多個使用者時，可以用

  ,

  逗号隔開

第四清單示用戶端的IP

• 可以是一個位址，10.110.9.155/32
• 位址範圍,10.110.9.155/28
• 通配的位址,0.0.0.0/0

第五列(最後一列)表示認證方式

經常使用的有三種

• peer 作業系統使用者名稱和資料庫的使用者名稱一緻
• md5 使用md5的方式驗證密碼登入
• trust 無需驗證，允許所有用戶端連接配接

建立postgres以外的使用者，如下所示

[postgres@localhost data]$ createuser -P --interactive
Enter name of role to add: s9suser
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
[postgres@localhost data]$ createuser -P --interactive
Enter name of role to add: s9sadmin
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) y           

在上面的示例中，我們建立了兩個使用者

• s9suser,作為普通使用者;
• s9sadmin，作為超級使用者

接下來，我們可以用新建立的使用者連接配接資料庫

[postgres@localhost data]$ psql -h 127.0.0.1 postgres s9suser
psql (10.1)
Type "help" for help.

postgres=>            

雖然此時可以連接配接。但不幸的是，我們無法從本地主機外部連接配接到PostgreSQL執行個體。要改變這一點，需要幾個步驟。

首先，我們需要讓PostgreSQL監聽環回以外的接口。我們需要找到并編輯postgresql.conf檔案。它位于與pg_hba相同的目錄中。

當你使用選擇的文本編輯器打開它時，您将看到以下條目:

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)           

從這裡可以看到，預設情況下，PostgreSQL隻監聽本地主機，為了接受外部連接配接，我們需要将這個值更改為其他值。這種更改需要重新啟動服務。我們将其更改為本地的IP位址:10.110.9.155。并重新啟動PostgreSQL。當它重新出現時，我們可以确認它确實在聽:

[root@localhost ~]# netstat -lnp | grep 5432
tcp        0      0 10.110.9.155:5432       0.0.0.0:*               LISTEN      4646/postmaster     
unix  2      [ ACC ]     STREAM     LISTENING     39440    4646/postmaster      /tmp/.s.PGSQL.5432           

當我們通過其他主機通路時，仍然會遇到如下的錯誤

[postgres@localhost data]$ psql -h 10.110.9.155 postgres s9suser
psql: FATAL:  no pg_hba.conf entry for host "10.110.9.154", user "s9suser", database "postgres"
[postgres@localhost data]$            

我們需要在pg_hba.conf中新增條目,允許這個IP可以連接配接

host    all             all             10.110.9.154/32         md5
           

[postgres@localhost data]$ psql -h 10.110.9.155 postgres s9suser
Password for user s9suser: 
psql (10.1)
Type "help" for help.

postgres=>            

資料庫可正常通路。