本文面向想要練習 PostgreSQL 中數(shù)據(jù)庫復制基礎(chǔ)知識但可能無法訪問遠程服務(wù)器的初學者。我認為學習新技術(shù)時，在自己的機器上運行示例以鞏固概念是至關(guān)重要的。對于副本來說，這可能很困難，因為許多可用的資源假設(shè)用戶具有一定的 PostgreSQL 經(jīng)驗并且可以訪問另一臺運行副本的服務(wù)器。我們不會在這里做任何假設(shè)，唯一的先決條件是您已經(jīng)安裝了 Postgres 并且可以登錄到 shell。

準備工作

對于這些示例，我們使用 Ubuntu 22.10 來運行 PostgreSQL 16（開發(fā)版本）。

$HOME/pg/data

將$HOME/pg/data 作為我們的數(shù)據(jù)目錄。第一個示例將介紹使用流式物理復制，然后是使用 日志傳輸 (shipping)進行物理復制的簡短示例，然后是邏輯復制的示例，最后總結(jié)不同的復制類型及其功能。

流式物理復制

首先，我們以超級用戶身份登錄主服務(wù)器，通常這是默認用戶“postgres”。

psql -U postgres

進入 shell 后，為復制創(chuàng)建一個新角色，在主服務(wù)器中創(chuàng)建一個表，向該表中插入一些數(shù)據(jù)，然后退出。

postgres=# CREATE ROLE rep_user WITH REPLICATION LOGIN PASSWORD 'rep_pass';
postgres=# create table t1(a int, b int);
postgres=# insert into t1 values (1,2);
postgres=# \q

打開 $HOME/pg/data/postgres.conf 并確保

listen_addresses = 'localhost'

然后，將以下內(nèi)容附加到 $HOME/pg/data/pg_hba.conf 的末尾

host  replication   rep_user  localhost   md5

運行以下命令創(chuàng)建主服務(wù)器的備份，然后將其用作副本服務(wù)器的數(shù)據(jù)目錄（位于 $HOME/pg/rep）。

$ pg_basebackup -h localhost -U rep_user -X stream -C -S replica_1 -v -R -W -D $HOME/pg/rep

創(chuàng)建此目錄后，打開 $HOME/pg/rep/postgres.conf 并將端口號設(shè)置為 PostgreSQL 默認 5432 以外的其他值。

port = 5433

然后在 postgres.conf 中編輯以primary_conninfo開始的 行，使其如下所示：

primary_conninfo = 'dbname=postgres user=postgres host=localgost port=5432 sslmode=disable'

在啟動服務(wù)器之前我們必須做的最后一件事是在副本服務(wù)器的數(shù)據(jù)文件夾中創(chuàng)建一個 空的standby.signal文件。在命令行中輸入以下內(nèi)容：

$ touch $HOME/pg/rep/standby.signal

打開第二個終端，因為我們將在主機上啟動兩個獨立的 PostgreSQL 實例，每個實例位于不同的端口上。

在第一個終端（主服務(wù)器）中，輸入：

pg_ctl -D $HOME/pg/data start

在第二個終端（副本服務(wù)器）中，輸入：

pg_ctl -D $HOME/pg/rep start

每個都應(yīng)該給出一個輸出，表明服務(wù)器已啟動。我們可以通過輸入以下內(nèi)容來確認這一點：

$ ps -aux | grep postgres
tristen    29088  0.0  0.1 175596 18580 ?        Ss   15:42   0:00 /home/tristen/disk/pgapp/bin/postgres -D /home/tristen/pg/data
tristen    29089  0.0  0.0 175728  2388 ?        Ss   15:42   0:00 postgres: checkpointer 
tristen    29090  0.0  0.0 175752  2364 ?        Ss   15:42   0:00 postgres: background writer 
tristen    29092  0.0  0.0 175596  7424 ?        Ss   15:42   0:00 postgres: walwriter 
tristen    29093  0.0  0.0 177196  4892 ?        Ss   15:42   0:00 postgres: autovacuum launcher 
tristen    29094  0.0  0.0 177176  4984 ?        Ss   15:42   0:00 postgres: logical replication launcher 
tristen    29112  0.0  0.1 175596 18456 ?        Ss   15:42   0:00 /home/tristen/disk/pgapp/bin/postgres -D /home/tristen/pg/rep
tristen    29113  0.0  0.0 175728  2564 ?        Ss   15:42   0:00 postgres: checkpointer 
tristen    29114  0.0  0.0 175596  2564 ?        Ss   15:42   0:00 postgres: background writer 
tristen    29115  0.0  0.0 176348  4488 ?        Ss   15:42   0:00 postgres: startup recovering 000000010000000000000014
tristen    29116  0.0  0.0 176136  3664 ?        Ss   15:42   0:00 postgres: walreceiver streaming 0/14000110
tristen    29117  0.0  0.0 177336  7060 ?        Ss   15:42   0:00 postgres: walsender rep_user 127.0.0.1(39650) streaming 0/14000110
tristen    29126  0.0  0.0  17580  2252 pts/2    S+   15:42   0:00 grep --color=auto postgres

可以看到有兩個進程 $HOME/disk/pgapp/bin/postgres 在不同的兩個目錄中運行。

現(xiàn)在，我們將連接到兩個實例中的每一個。首先是第一個終端上的主服務(wù)器：

psql -U postgres -p 5432

然后是第二個終端上的副本服務(wù)器：

psql -U postgres -p 5433

-p 標志用于指定端口。在主服務(wù)器上使用它是多余的，因為它使用默認端口。然而，這很好地表明我們確實連接到兩個不同的端口，從而連接到兩個不同的 PostgreSQL 實例。

如果我們在 Postgres shell 中輸入 \d，可以獲得數(shù)據(jù)庫中的表的列表。在副本服務(wù)器相對應(yīng)的第二個終端中，可以看到表 t1 確實被復制了。

postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)

現(xiàn)在讓在主服務(wù)器上創(chuàng)建一個新表，以表明它將自動復制到副本服務(wù)器。

在第一個終端上輸入：

postgres=# create table t2(c int, d text);
CREATE TABLE
postgres=# insert into t2 values (3, 'hello');
INSERT 0 1

在第二個終端中輸入：

postgres=# select * from t2;
 c |   d   
---+-------
 3 | hello
(1 row)

可以看到，不僅新表是從主服務(wù)器復制過來的，而且剛剛插入的數(shù)據(jù)也是如此。

值得注意的是，這種復制關(guān)系只是單向的。也就是說，副本服務(wù)器只能從主服務(wù)器的復制。如果嘗試在副本服務(wù)器中創(chuàng)建表，則會收到一條錯誤消息：

ERROR:  cannot execute CREATE TABLE in a read-only transaction

從邏輯上講，這是因為我們希望副本是只讀的，以使來自 主服務(wù)器的數(shù)據(jù)更容易可用。

日志傳輸物理復制

日志傳輸(Shipping)物理復制 與 流式物理復制 有類似的設(shè)置，主要只是更改一些配置文件。在主服務(wù)器上編輯 postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

如果服務(wù)器正在運行，請重新啟動服務(wù)器并創(chuàng)建主服務(wù)器的備份：

$ pg_basebackup -h localhost -U rep_user -X fetch -v -R -W -D $HOME/pg/rep

打開 $HOME/pg/rep/postgresql.conf 并編輯兩行。注釋掉以 archive_command 開頭的行，并取消注釋以 Restore_command 開頭的行，添加在 = 之后進行恢復時要使用的命令。這些行應(yīng)如下所示：

#archive_command = 'cp %p /path/to/archive/%f'

restore_command = 'cp /path/to/archive/%f %p'

像 流式物理復制 示例中一樣啟動兩臺服務(wù)器，應(yīng)該可以看到副本正在復制主服務(wù)器。請注意，日志傳輸更改(shipping changes)可能不會像在流式物理復制中那樣實時。日志傳輸復制具有較高的延遲，并且僅在 WAL 文件填充到副本時才發(fā)送更改。

邏輯復制

邏輯復制與物理復制具有與服務(wù)器相同的初始設(shè)置。它涉及一些不同的配置，還涉及一些手動編輯以使表正常工作。

在主服務(wù)器上編輯 postgresql.conf

wal_level = logical

確保主服務(wù)器上的 pga_hba.conf 包含復制用戶的正確連接權(quán)限。

host     all     rep_user     localhost     md5

一旦所有配置準備就緒，要啟動兩臺服務(wù)器并登錄 shell。回想一下，在我們的示例中，每個服務(wù)器都使用不同的端口號，因此請確保您沒有兩次使用同一服務(wù)器！在每個服務(wù)器各自的 shell 中，我們需要在每個服務(wù)器中創(chuàng)建一個具有 相同名稱和結(jié)構(gòu) 的表。如果表完全不同，副本將無法找到它，將收到錯誤。也就是說，在兩個終端中輸入以下內(nèi)容：

postgres=# create table t1(id int, val text);

現(xiàn)在我們在兩個數(shù)據(jù)庫中都有相同的空表，繼續(xù)以下命令復制該表。在主服務(wù)器上輸入以下命令為表 t1 創(chuàng)建發(fā)布：

postgres=# CREATE PUBLICATION pub_t1 FOR TABLE t1;

這將為表 t1 創(chuàng)建一個發(fā)布，這意味著表 t1 將被復制到副本服務(wù)器。可以復制任意數(shù)量的表作為發(fā)布的一部分。

創(chuàng)建發(fā)布后，將副本數(shù)據(jù)庫中的表 t1 訂閱到主數(shù)據(jù)庫中的表 t1。為此，必須在副本數(shù)據(jù)庫中創(chuàng)建訂閱，如下所示：

postgres=# CREATE SUBSCRIPTION sub_t1 CONNECTION 'dbname=postgres host=localhost port=5432 user=postgres' PUBLICATION pub_t1;

注意，在本示例中，我們同時使用了默認用戶 postgres 和默認數(shù)據(jù)庫 postgres，但這不是必需的。

現(xiàn)在已經(jīng)創(chuàng)建了訂閱，將看到主服務(wù)器表 t1 中的所有更新都出現(xiàn)在副本服務(wù)器的表 t1 中。主服務(wù)器上沒有做發(fā)布的任何其他表都不會復制到副本?，F(xiàn)在做一個測試，在主數(shù)據(jù)庫終端中向表 t1 中插入一些數(shù)據(jù)：

postgres=# insert into t1 values (1, 'hello');
INSERT 0 1
postgres=# insert into t1 values (2, 'world');
INSERT 0 1

現(xiàn)在讓我們看看該表是否實際上已復制到副本服務(wù)器中：

postgres=# select * from t1;
 id |  val  
----+-------
  1 | hello
  2 | world
(2 rows)

現(xiàn)在，數(shù)據(jù)已成功復制到副本服務(wù)器。但我們怎么區(qū)分這是不是物理復制呢？好吧，讓我們向主數(shù)據(jù)庫添加一個新表并插入一些數(shù)據(jù)：

postgres=# create table t2(c int, d int);
CREATE TABLE
postgres=# insert into t2 values(3,4);
INSERT 0 1
postgres=# insert into t2 values(5,6);
INSERT 0 1

再檢查一下副本：

postgres=# select * from t2;
ERROR:  relation "t2" does not exist
LINE 1: select * from t2;

可以看到表 t2 沒有被復制，因為它沒有訂閱，因此不存在于副本中。這允許將某些表從中央數(shù)據(jù)庫公開給遠程數(shù)據(jù)庫，同時保持其他表的私有性。

但是對 表結(jié)構(gòu) 的 修改 在副本數(shù)據(jù)庫中 結(jié)果會如何？好吧，讓我們測試一下。在主數(shù)據(jù)庫終端中，更改表結(jié)構(gòu)并添加一些數(shù)據(jù)：

postgres=# alter table t1 add column x int;
ALTER TABLE
postgres=# insert into t1 values (3, 'foo', 42);
INSERT 0 1
postgres=# insert into t1 values (4, 'bar', 10);
INSERT 0 1
postgres=# select * from t1;
 id |  val  | x  
----+-------+----
  1 | hello |   
  2 | world |   
  3 | foo   | 42
  4 | bar   | 10
(4 rows)

現(xiàn)在檢查一下副本服務(wù)器：

postgres=# select * from t1;
 id |  val  
----+-------
  1 | hello
  2 | world
(2 rows)

可以看到副本服務(wù)器不再復制主服務(wù)器。即使將同一架構(gòu)中的新數(shù)據(jù)添加到主數(shù)據(jù)庫中，它也不會顯示在副本中：

postgres=# insert into t1 values (15, 'test');
INSERT 0 1

postgres=# select * from t1;
 id |  val  
----+-------
  1 | hello
  2 | world
(2 rows)

由于我們更改了 t1 的 架構(gòu)，副本不再從中復制數(shù)據(jù)，因為它現(xiàn)在實際上是一個不同的表。如果您更改了表的架構(gòu)并將副本運行在其他地方，記住這一點很重要。

如果不在副本中創(chuàng)建與主服務(wù)器中的表 相匹配的表，會發(fā)生什么情況？

postgres=# CREATE PUBLICATION pub_t2 FOR TABLE t2;
CREATE PUBLICATION

postgres=# CREATE SUBSCRIPTION sub_t2 CONNECTION 'dbname=postgres host=localhost port=5432 user=postgres' PUBLICATION pub_t2;
ERROR:  relation "public.t2" does not exist

這樣收到一條錯誤消息，指出我們想要訂閱的 關(guān)系 不存在。這可能會非常令人困惑，因為我們給了它所有連接信息，并且我們知道表 t2 存在于該位置。但是，此錯誤是指它在我們的副本上找不到表 t2，因此無法創(chuàng)建訂閱。因此，請務(wù)必記住，我們需要兩個數(shù)據(jù)庫包含具有相同架構(gòu)的相同表，以便邏輯復制正常工作。

對比

以下是我們從高層次角度討論的所有主題的摘要。如果您仍在嘗試決定要使用哪種復制方法，那么此摘要可能有助于做出決定。

邏輯復制與物理復制

邏輯復制與物理復制的比較

日志傳輸 與 流式復制

流式物理復制與日志傳輸物理復制的比較

異步流與同步流 復制

異步流與同步流的比較

總結(jié)

關(guān)于使用 PostgreSQL 設(shè)置服務(wù)器復制以實現(xiàn)各種復制方法的文章到此結(jié)束。首先，我們回顧了設(shè)置和運行每種方法的實際步驟，然后查看了每種方法功能的高級摘要。

References 參考

27.2. log-shipping standby servers. PostgreSQL Documentation. (2023, February 9). Retrieved March 29, 2023, from https://www.postgresql.org/docs/current/warm-standby.html

31.11. quick setup. PostgreSQL Documentation. (2023, February 9). Retrieved March 29, 2023, from https://www.postgresql.org/docs/current/logical-replication-quick-setup.html

B, A. (2022, April 8). [web log]. Retrieved March 29, 2023, from https://scalegrid.io/blog/comparing-logical-streaming-replication-postgresql/.

Levinas, M. (2022, October 10). [web log]. Retrieved March 29, 2023, from https://www.cherryservers.com/blog/how-to-set-up-postgresql-database-replication.

Ravoof, S. (2023, February 17). [web log]. Retrieved March 29, 2023, from https://kinsta.com/blog/postgresql-replication/.

原文地址Setting Up a PostgreSQL Replica Server Locally - Highgo Software Inc.文章來源地址http://www.zghlxwxcb.cn/news/detail-791096.html

到了這里，關(guān)于postgresql16 物理復制與邏輯復制的實現(xiàn)和對比的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！