再不瞭解PostgreSQL,你就晚了之PostgreSQL主從流複製部署

穆書偉發表於2019-03-18
SQL

背景圖片

前言

在MySQL被收購之後，雖然有其替代品為: MariaDB,但是總感覺心裡有點膈應。大家發現了另一款開源的資料庫: PostgreSQL。

雖然centos自帶版本9.2也可以用，但是最近的幾次升級支援了更多更新的內容，比如，PostgreSQL有一個MySQL無法比擬的優勢，那就是PostGIS，PostGIS可以完美支援空間資料儲存和空間分析；從PostgreSQL9.3起就內建了JSON資料型別，而9.4又開始支援JSONB，這標誌著PostgreSQL實際上已經是一個關係型資料庫和NoSQL資料庫的結合體了。

雖然postgresql是一個關係型資料庫，但是近幾次更新PostgreSQL的NoSQL效能有益到甚至超過了MongoDB。我們可以從下圖資料庫Rank榜上觀察到PostgreSQL排在第四位,和MongoDB的位置不相上下。可見PostgreSQL在開發人員的喜愛度上,可信賴度上和社群文件的查詢上都是不錯的。

DB-Engines_Ranking

Centos7 安裝最新版postgresql10

更新源

雲伺服器系統: CentOS 7.2 x86_64架構

地址: www.postgresql.org/download/li…

PostgreSQL-Yum_Repository

這裡我選擇PostgreSQL10,CentOS 7平臺, x86_64架構,就會出現相應的yum源了。

在centos系統中執行以下命令:

yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
複製程式碼

安裝PostgreSQL

先檢視PostgreSQL源

Yum-Repository_List

我們需要安裝的是這三個個。postgresql10-devel.x86_64, postgresql10-contrib.x86_64,postgresql10-server.x86_64

 yum install postgresql10-client postgresql10-server postgresql10-contrib postgresql10-devel
複製程式碼

初始化資料庫

PostgreSQL安裝目錄是/usr/pgsql-10,而PostgreSQL的資料目錄是/var/lib/pgsql/版本號(這裡為10)

在這裡，如果在裝系統開始分配var空間足夠大則可以繼續，如果分配var空間不夠，我們需要更改資料目錄，在這裡，我們假設var空間足夠大。直接開始初始化。

/usr/pgsql-10/bin/postgresql-10-setup initdb
複製程式碼

initdb

顯示這個代表初始化成功

啟功資料庫並設定開機啟動

sudo systemctl start postgresql-10
sudo systemctl enable postgresql-10.service
複製程式碼

登入PostgreSQL並設定密碼

postgresql在安裝時預設新增使用者postgres

輸入

su - postgres
psql
複製程式碼

進入資料庫

命令介面長這樣

PostgreSQL-Terminal

我們來設定密碼:

alter-password

退出: \q

列出所有庫 \l

列出所有使用者 \du

列出庫下所有表 \d

支援密碼和遠端登陸

修改密碼驗證

預設情況下postgresql是不用密碼不支援遠端登入的。我們需要修改配置檔案

vi /var/lib/pgsql/10/data/pg_hba.conf
複製程式碼

原本長成這樣

hba

我們需要改成

update_hba

儲存退出

關於退出vim的問題，這裡還有一個小趣聞：

StackOverflow驕傲的宣佈： 已經幫助187萬不知如何從VIM退出的程式設計師

view_exit_vim

在這裡我引用上面的高贊回答，幫助大家脫離這個187萬群體。

QA_exit_vim

修改遠端訪問

vi /var/lib/pgsql/10/data/postgresql.conf
複製程式碼

往下拉我們會看到

postgresql_conf

需要改成

after_postgresql_conf

在vim中搜尋想要查詢的文字，可以用 /***, n 表示下翻頁，N表示上翻頁

重啟postgresql

systemctl restart postgresql-10
複製程式碼

登入postgresql

postgresql在安裝時預設新增使用者postgres

輸入

su - postgres
psql
複製程式碼

進入資料庫

verify_password

此時已經可以進行驗證密碼

遠端連線資料庫

我們使用Navicat Premium 12來驗證連線

postgresql_test

此時我們可以看到相關資料了

PostgreSQL_View

PostgreSQL主從流複製部署

簡介

postgres在9.0之後引入了主從的流複製機制，所謂流複製，就是從伺服器通過tcp流從主伺服器中同步相應的資料。這樣當主伺服器資料丟失時從伺服器中仍有備份。

與基於檔案日誌傳送相比，流複製允許保持從伺服器更新。 從伺服器連線主伺服器，其產生的流WAL記錄到從伺服器， 而不需要等待主伺服器寫完WAL檔案。

PostgreSQL流複製預設是非同步的。在主伺服器上提交事務和從伺服器上變化可見之間有一個小的延遲，這個延遲遠小於基於檔案日誌傳送，通常1秒能完成。如果主伺服器突然崩潰，可能會有少量資料丟失。

同步複製必須等主伺服器和從伺服器都寫完WAL後才能提交事務。這樣在一定程度上會增加事務的響應時間。

配置同步複製僅需要一個額外的配置步驟： synchronous_standby_names必須設定為一個非空值。synchronous_commit也必須設定為on。

這裡部署的是非同步的流複製。

主從伺服器所在節點的系統、環境等最好一致。PostgreSQL版本也最好一致，否則可能會有問題。

安裝部署

先假定在192.168.20.93和192.168.20.94均安裝PostgreSQL,具體情況按照實際IP地址。

具體安裝部署步驟見：上述步驟

2.1 主伺服器

主伺服器為192.168.20.93

1.首先需要建立一個資料庫使用者進行主從同步。建立使用者replica，並賦予登入和複製的許可權。

postgres# CREATE ROLE replica login replication encrypted password 'replica'；

複製程式碼

2.修改pg_hba.conf，允許replica使用者來同步。

在pg_hba.conf裡增加兩行：

host     all             all          192.168.20.94/32          trust   #允許94連線到主伺服器
host   replication      replica       192.168.20.94/32          md5   #允許94使用replica使用者來複制

複製程式碼

這樣，就設定了replica這個使用者可以從192.168.20.93進行流複製請求。

注： 第二個欄位必須要填replication

4.修改postgresql.conf

listen_addresses = '*'   # 監聽所有IP
archive_mode = on  # 允許歸檔
archive_command = 'cp %p /opt/pgsql/pg_archive/%f'  # 用該命令來歸檔logfile segment
wal_level = hot_standby 
max_wal_senders = 32 # 這個設定了可以最多有幾個流複製連線，差不多有幾個從，就設定幾個wal_keep_segments = 256 ＃ 設定流複製保留的最多的xlog數目
wal_sender_timeout = 60s ＃ 設定流複製主機傳送資料的超時時間
max_connections = 100 # 這個設定要注意下，從庫的max_connections必須要大於主庫的

複製程式碼

配置完兩個檔案後重啟伺服器。

systemctl restart postgresql-10

複製程式碼

3.測試94能否連線93資料庫。在94上執行如下命令：

psql -h 192.168.20.93 -U postgres

複製程式碼

看看是否能進入資料庫。若可以，則正常。

2.2 從伺服器

1.從主節點拷貝資料到從節點

su - postgres
rm -rf /var/lib/pgsql/10/data/*   #先將data目錄下的資料都清空
pg_basebackup -h 192.168.20.93 -U replica  -X stream -P  # 從93拷貝資料到94（基礎備份）

複製程式碼

2.配置recovery.conf

複製/usr/pgsql-9.4/share/recovery.conf.sample 到 /opt/pgsql/data/recovery.conf

cp /usr/pgsql-10/share/recovery.conf.sample /usr/pgsql-10/share/recovery.conf

複製程式碼

修改recovery.conf

standby_mode = on    # 說明該節點是從伺服器
primary_conninfo = 'host=192.168.20.93 port=5432 user=replica password=replica'  # 主伺服器的資訊以及連線的使用者
recovery_target_timeline = 'latest'

複製程式碼

3.配置postgresql.conf

wal_level = hot_standby
max_connections = 1000 ＃ 一般查多於寫的應用從庫的最大連線數要比較大
hot_standby = on ＃ 說明這臺機器不僅僅是用於資料歸檔，也用於資料查詢
max_standby_streaming_delay = 30s # 資料流備份的最大延遲時間
wal_receiver_status_interval = 10s # 多久向主報告一次從的狀態，當然從每次資料複製都會向主報告狀態，這裡只是設定最長的間隔時間
hot_standby_feedback = on # 如果有錯誤的資料複製，是否向主進行反饋

複製程式碼

配置完後重啟從伺服器

systemctl restart postgresql-10

複製程式碼

3. 驗證是否部署成功

在主節點上執行：

select client_addr,sync_state from pg_stat_replication;

複製程式碼

結果如下：

postgres=# select client_addr,sync_state from pg_stat_replication;
  client_addr  | sync_state
---------------+------------
 192.168.20.94 | async
(1 行記錄)

複製程式碼

說明94是從伺服器，在接收流，而且是非同步流複製。

此外，還可以分別在主、從節點上執行 ps aux | grep postgres 來檢視程式：

主伺服器（93）上：

postgres 262270  0.0  0.0 337844  2832 ?        Ss   10:14   0:00 postgres: wal sender process replica 192.168.20.94(13059) streaming 0/A002A88

複製程式碼

可以看到有一個 wal sender 程式。

從伺服器（94）上：

postgres 569868  0.0  0.0 384604  2960 ?        Ss   10:14   0:02 postgres: wal receiver process   streaming 0/A002B60

複製程式碼

可以看到有一個 wal receiver 程式。

至此，PostgreSQL主從流複製安裝部署完成。

在主伺服器上插入資料或刪除資料，在從伺服器上能看到相應的變化。從伺服器上只能查詢，不能插入或刪除。

版權宣告: 作者：穆書偉

github出處：github.com/sanshengshu…    

個人部落格出處：www.mushuwei.cn/

相關文章