：使用pg_dump和pg_restore將自建PostgreSQL資料庫遷移至RDS PostgreSQL

操作步驟

步驟一：確認待遷移資料庫的相關資訊

串連自建PostgreSQL資料庫，在psql命令列中，使用\l查詢資料庫列表資訊，確認待遷移資料庫的Owner、Encoding、Collate和Ctype資訊。

本文以遷移testdb資料庫為例，Owner為testuser、Encoding為UTF8、Collate為en_US.UTF-8、Ctype為en_US.UTF-8。

                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =Tc/postgres         +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 testdb    | testuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
(4 rows)

步驟二：遷移使用者

1. 串連自建PostgreSQL資料庫所在的伺服器，使用如下命令匯出待遷移資料庫的Owner使用者testuser。

   pg_dumpall -g -h 127.0.0.1 -p 5432 -U testuser

   返回結果樣本：

   說明

   在返回結果中的Roles部分，即可看到匯出使用者testuser的相關許可權語句。

   --
   -- PostgreSQL database cluster dump
   --
   
   SET default_transaction_read_only = off;
   
   SET client_encoding = 'UTF8';
   SET standard_conforming_strings = on;
   
   --
   -- Roles
   --
   
   CREATE ROLE postgres;
   ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
   CREATE ROLE testuser;
   ALTER ROLE testuser WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:0IEvNqWupQsGcaoDS3SX****3U=';
   
   --
   -- User Configurations
   --
   
   --
   -- PostgreSQL database cluster dump complete
   --

2. 使用高許可權帳號串連RDS PostgreSQL執行個體，具體操作，請參見串連PostgreSQL執行個體。

3. 修改擷取的testuser使用者的相關許可權SQL語句（將SUPERUSER修改為rds_SUPERUSER），在RDS PostgreSQL執行個體中建立與自建PostgreSQL許可權相同的帳號。

   CREATE ROLE testuser;
   ALTER ROLE testuser WITH rds_SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:0IEvNqWupQsGcaoDS3SX****3U=';

步驟三：在RDS PostgreSQL中建立目標資料庫

1. 使用高許可權帳號串連RDS PostgreSQL執行個體。

2. 使用如下SQL語句，建立目標資料庫，需要確保與自建PostgreSQL中待遷移資料庫具有相同字元集、Collate和Ctype。例如步驟一中擷取的Encoding為UTF8、Collate為en_US.UTF-8、Ctype為en_US.UTF-8。

   CREATE DATABASE <目標資料庫的名稱> WITH OWNER <資料庫Owner名稱> TEMPLATE template0 ENCODING 'UTF8' LC_CTYPE 'en_US.UTF-8' LC_COLLATE 'en_US.UTF-8';

   說明

   • 目標資料庫名稱可以與自建PostgreSQL中待遷移資料庫名稱不同。

   • 目標資料庫的Owner必須與自建PostgreSQL中遷移資料庫Owner相同。

步驟四：資料移轉

本文提供如下資料移轉方案，任選其一即可。

• 如果您的自建PostgreSQL資料庫所在伺服器可以直接連接RDS PostgreSQL資料庫，則推薦使用線上遷移方式。

• 如果您的自建PostgreSQL資料庫所在伺服器無法直接連接RDS PostgreSQL資料庫，則推薦使用離線遷移方式，先使用pg_dump匯出檔案，然後將檔案拷貝到能串連RDS PostgreSQL的主機，最後使用pg_restore匯入。

相關文檔

本文涉及的命令均可根據業務需求配置更多相關參數，具體詳見如下官方文檔：

• pg_dump

• pg_restore

• pg_dumpall