在我的日常工作中，我與許多將他們的數據遷移到 Postgres 的客戶一起工作。我與從同源 (PostgreSQL) 以及異構數據庫源（如 Oracle 和 Redshift）遷移的客戶合作。人們為什么選擇 Postgres？由于 PostgreSQL 的豐富性以及存儲過程、JSONB、用于地理空間工作負載的 PostGIS 等功能，以及許多有用的 Postgres 擴展，包括我個人最喜歡的：Citus。

我幫助人們進行的大部分遷移是同質的 Postgres 到 Postgres 數據到云的遷移。由于 Azure Database for PostgreSQL 運行開源 Postgres，因此在許多情況下，應用程序遷移可以直接進行，不需要大量工作。大部分工作通常用于決定和實施正確的策略來執行數據遷移。對于那些在 Postgres 遷移過程中無法承受任何停機時間的人，當然有數據遷移服務可以提供幫助。但是，如果您可以在特定的維護窗口（例如周末、晚上等）期間為遷移提供一些停機時間，那么可以使用簡單的 Postgres 實用程序，例如 pg_dump 和 pg_restore。

在這篇文章中，讓我們討論一下在使用 pg_dump 和 pg_restore 進行 Postgres 數據庫遷移時要考慮的權衡 - 以及如何優化遷移以提高速度。讓我們也探索一下需要遷移非常大的 Postgres 表的場景。對于大型表，使用 pg_dump 和 pg_restore 遷移數據庫可能不是最佳方法。好消息是我們將介紹一個用于在 Postgres 中遷移大型數據庫表的漂亮 Python 工具。使用此工具，我們觀察到大型 Postgres 表（~1.4TB）的遷移在 7 小時內完成。 45 分鐘與 pg_dump/pg_restore 超過 1 天。

使用 pg_dump 和 pg_restore 實現更 快的遷移

pg_dump 是用于備份 PostgreSQL 數據庫的標準和傳統實用程序。 pg_dump 對您的 Postgres 數據庫進行一致的快照，即使該數據庫正在被積極使用。 pg_dump 為您提供了多個命令行選項（我稱它們為標志），您可以使用它們來控制正在備份的數據的格式和內容。 pg_dump 的一些常見和最有用的命令行選項使您能夠執行以下操作：

• 對轉儲特定模式、特定表、僅數據等的細粒度控制。
• 控制轉儲的格式；選項包括純文本或自定義或目錄格式，默認情況下是壓縮的。
• 使用 --jobs/-j 命令行選項，它提供了指定用于轉儲的并發線程數的能力。每個線程轉儲一個特定的表，此命令行選項控制同時轉儲多少個表。

您可以使用 pg_restore 實用程序從 pg_dump 創建的存檔中恢復 PostgreSQL 數據庫。與 pg_dump 類似，pg_restore 還提供了對如何恢復存檔的大量控制。例如，您可以將還原限制為特定的數據庫對象/實體，為還原指定并行作業等。

提示：將執行 pg_dump/pg_restore 的客戶端計算機放置在盡可能靠近源和目標數據庫的位置，以避免因網絡延遲不良而導致的性能問題。如果兩者中只有一個是可能的，您可以選擇其中一個。只要確保將客戶端計算機盡可能靠近目標數據庫或源數據庫，或兩者兼而有之。

總之，pg_dump 和 pg_restore 是用于同構（Postgres 到 Postgres）數據庫遷移的最常用的、本機的、健壯的和經過驗證的實用程序。當您可以承受停機時間（在一些可接受的維護窗口內）時，使用這些實用程序是執行數據遷移的默認方式。

借助 pg_dump 和 pg_restore 提供的大量命令行選項，重要的是根據手頭的場景以最佳方式使用這些選項。讓我們來看看您可能面臨的一些場景，以了解如何最好地使用 pg_dump 和 pg_restore。

如果您需要遷移超過 5 個大型 Postgres 表怎么辦？

假設您的 Postgres 數據庫有多個（例如，超過 5 個）大小適中（大于 5GB）的表。您可以使用 -j 標志來指定執行 pg_dump 和 pg_restore 時要使用的線程數。這樣做不僅可以最大限度地利用源服務器和目標服務器上的資源（計算/內存/磁盤），而且還可以擴展可用的網絡帶寬。 （但是您應該小心，pg_dump 和 pg_restore 不會成為網絡霸主，也不會影響您的其他工作負載。）因此，使用 pg_dump 和 pg_restore 可以提供顯著的性能提升。

如果您在 Postgres 服務器上執行離線遷移，沒有其他負載，您可以指定作業數是系統中核心數的倍數，這將最大限度地提高服務器上的計算利用率。但是，如果您只是出于備份/恢復原因在具有生產負載的服務器上執行轉儲/恢復，請務必指定一些不會影響現有負載性能的作業。

您可以使用目錄格式 (-Fd)，它會固有地提供壓縮轉儲（使用 gzip）。我們有時會在使用 -Fd 標志時看到超過 5 倍的壓縮。對于較大的數據庫（例如超過 1 TB），壓縮轉儲可以減少磁盤 IOP 在您從中捕獲轉儲的服務器上遇到瓶頸的影響。

下面是示例 pg_dump 和 pg_restore 命令，它們分別使用 5 個作業進行轉儲和恢復：

pg_dump -d 'postgres://username:password@hostname:port/database' -Fd -j 5 -f dump_dir

pg_restore --no-acl --no-owner -d 'postgres://username:password@hostname:port/database' --data-only -Fd -j5 dump_dir

如果您的大多數表都很小，但您的一張表非常大，如何遷移？

假設您的數據庫有一個大表（超過 5GB），而其余表都很小（小于 1GB）。 您可以將 pg_dump 的輸出通過管道傳輸到 pg_restore，這樣您就無需等待轉儲完成后再開始恢復； 兩者可以同時運行。 這避免了將轉儲存儲在客戶端，這是一件好事，因為避免將轉儲存儲在客戶端可以顯著減少將轉儲寫入磁盤所需的 IOP 開銷。

在這種情況下，-j 標志可能沒有幫助，因為 pg_dump/pg_restore 每個表只運行一個線程。 實用程序將在轉儲和恢復最大的表時受到限制。 此外，不幸的是，當您使用 -j 標志時，您無法將 pg_dump 的輸出通過管道傳輸到 pg_restore。 下面是一個顯示用法的示例命令：

pg_dump -d 'postgres://username:password@hostname:port/source_database' -Fc | pg_restore --no-acl --no-owner -d 'postgres://username:password@hostname:port/target_database' --data-only

上述 2 節中的技術可以使用 pg_dump 和 pg_restore 顯著縮短數據遷移時間，尤其是在涉及一個或多個大型表時。此外，這篇關于加快 Postgres 恢復速度的文章介紹了類似的技術，并為您提供了有關如何使用 pg_dump/pg_restore 實現約 100% 性能提升的分步指導。這是我最喜歡的關于 pg_dump 和 pg_restore 的 Postgres 博客之一，因此分享以供參考。

pg_dump/pg_restore 在單表級別是單線程的，這會減慢遷移速度

即使您使用上述優化，由于 pg_dump 和 pg_restore 在遷移單個表時每個都只能使用一個線程，因此整個遷移可能會在一組特定的非常大的表上出現瓶頸。對于超過 1 TB 且有幾個表代表大部分數據的數據庫，我們已經看到 pg_dump 和 pg_restore 需要數天時間，這導致了以下問題。

如何使用多個線程遷移 PostgreSQL 中的單個大表？

您可以利用多個線程來遷移單個大表，方法是在邏輯上將 Postgres 表分塊/分區為多個部分，然后使用一對線程——一個從源讀取，一個從每個部分寫入目標。您可以根據水印列對表進行分塊。水印列可以是單調遞增的列（例如，id 列）（或）時間戳列（例如，created_at、updated_at 等）。

有許多商業工具可以實現上述邏輯。本著分享的精神，下面是一個名為 Parallel Loader 的 Python 腳本，它是上述邏輯的示例實現。如果您想自己使用，可以在 GitHub 上找到 Parallel Loader 腳本。

#suppose the filename is parallel_migrate.py

import os
import sys

#source info
source_url = sys.argv[1]
source_table = sys.argv[2]
                             
#dest info
dest_url = sys.argv[3]
dest_table = sys.argv[4]

#others
total_threads=int(sys.argv[5]);
size=int(sys.argv[6]);

interval=size/total_threads;
start=0;
end=start+interval;

for i in range(0,total_threads):
        if(i!=total_threads-1):
                select_query = '\"\COPY (SELECT * from ' + source_table + ' WHERE id>='+str(start)+' AND id<'+str(end)+") TO STDOUT\"";
                read_query = "psql \"" + source_url + "\" -c " + select_query
                write_query = "psql \"" + dest_url + "\" -c \"\COPY " + dest_table +" FROM STDIN\""
                os.system(read_query+'|'+write_query + ' &')
        else:
                select_query = '\"\COPY (SELECT * from '+ source_table +' WHERE id>='+str(start)+") TO STDOUT\"";
                read_query = "psql \"" + source_url + "\" -c " + select_query
                write_query = "psql \"" + dest_url + "\" -c \"\COPY " + dest_table +" FROM STDIN\""
                os.system(read_query+'|'+write_query)
        start=end;
        end=start+interval;

如何調用并行加載程序腳本

python parallel_migrate.py "source_connection_string" source_table "destination_connection_string" destination_table number_of_threads count_of_table

使用 Parallel Loader 腳本，您還可以控制用于遷移大表的線程數。 在上述調用中，number_of_threads 參數控制并行度因子。

并行加載程序腳本的示例調用

python parallel_migrate.py "host=test_src.postgres.database.azure.com port=5432 dbname=postgres user=test@test_src password=xxxx sslmode=require" test_table "host=test_dest.postgres.database.azure.com port=5432 dbname=postgres user=test@test_dest password=xxxx sslmode=require" test_table 8 411187501

上述實現使用表的單調遞增的 id 列將其分塊并使用并行線程將數據從源表流式傳輸到目標表。 您可以在此 GitHub 存儲庫中找到使用 Parallel Loader 的一些先決條件和建議。

比較 Parallel Loader 與 pg_dump 和 pg_restore 的性能，用于大型 Postgres 表

為了比較 pg_dump 和 pg_restore 與 Parallel Loader 腳本的性能，我使用這兩種技術將 1.4 TB Postgres 表（帶有索引）從一個 Postgres 數據庫遷移到同一區域的 Azure 中的另一個。

您可以在下表中看到，對于此 Postgres 到 Postgres 數據遷移，Parallel Loader 腳本的執行速度比 pg_dump 和 pg_restore 快了 3 倍以上。

圖 1：我們觀察到遷移的網絡吞吐量為每 5 分鐘約 9.5GB，峰值為每 5 分鐘 27.9GB。

Parallel Loader 使用 COPY 命令來提高性能

請注意，Parallel Loader 在每個線程中使用 COPY 命令從源讀取數據并將數據寫入目標數據庫。 COPY 命令是在 Postgres 中批量攝取的最佳方式。 我們已經看到使用 COPY 命令的攝取吞吐量超過每秒一百萬行。

圖 2：顯示由目標數據庫上的 COPY 命令組成的活動 (pg_stat_activity) 的屏幕截圖。這些 COPY 命令由 Parallel Loader 腳本在遷移大表時生成。每個 COPY 命令都轉換為腳本生成的單個線程。

底線：您可以將 pg_dump/pg_restore 與 Parallel Loader 結合使用，以實現更快的 Postgres 數據遷移

pg_dump/pg_restore 實用程序是從 Postgres 數據庫遷移到另一個 Postgres 數據庫的絕佳工具。但是，當數據庫中有非常大的表時，它們可能會大大減慢。為了解決這個問題，您可以使用本文中介紹的方法：使用 Parallel Loader 腳本將單個大表遷移到 Postgres 并行化。我們已經看到客戶結合使用 Parallel Loader 和 pg_dump/pg_restore 來成功遷移他們的 Postgres 數據庫。 Parallel Loader 可以處理大型表，而 pg_dump/pg_restore 可用于遷移其余的 Postgres 表。

更有用的數據遷移資源：

• 將數據庫存儲對象從 Oracle 遷移到 Postgres
• 新的 Oracle 到 Postgres 遷移指南
• pg_dump 的 Postgres 文檔
• pg_restore 的 Postgres 文檔
• GitHub 上的 Parallel Loader 實用程序
• 用于在 Azure 上將數據遷移到/從 PostgreSQL 遷移的 Azure 數據工廠文檔
• 加速 Postgres 恢復，2016 年的老歌

原文標題：Faster Data Migrations in Postgres
原文作者：Sai Krishna Srirampur
原文地址：https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/faster-data-migrations-in-postgres/ba-p/2150850