将 PostgreSQL 扩展与 Amazon RDS for PostgreSQL 结合使用

使用 orafce 扩展

1. 使用用于创建数据库实例的主用户名连接到数据库实例。

   如果要为同一数据库实例中的不同数据库启用 orafce，请使用 /c dbname psql 命令。使用此命令，您可以在启动连接后从主数据库进行更改。

2. 使用 CREATE EXTENSION 语句启用 orafce 扩展。

   CREATE EXTENSION orafce;

3. 使用 ALTER SCHEMA 语句将 oracle 架构的所有权移交给 rds_superuser 角色。

   ALTER SCHEMA oracle OWNER TO rds_superuser;

   如果要查看 oracle 架构的所有者列表，请使用 \dn psql 命令。

使用 pgactive 扩展配置第一个 RDS for PostgreSQL 数据库实例

以下示例说明如何创建 pgactive 组，以及在 RDS for PostgreSQL 数据库实例上创建 pgactive 扩展所需的其它步骤。

1. 使用 psql 或其它客户端工具连接第一个 RDS for PostgreSQL 数据库实例。

   psql --host=firstinstance.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=master username --password --dbname=postgres
   

2. 使用以下命令在 RDS for PostgreSQL 实例上创建数据库：

   postgres=> CREATE DATABASE app;

3. 使用以下命令将连接切换到新数据库：

   \c app

4. 要检查 shared_preload_libraries 参数是否包含 pgactive，请运行以下命令：

   app=>SELECT setting ~ 'pgactive' FROM pg_catalog.pg_settings WHERE name = 'shared_preload_libraries';

   
    ?column? 
   ----------
    t

5. 使用以下 SQL 语句创建并填充示例表：

   1. 使用以下 SQL 语句创建一个示例表。

      app=> CREATE SCHEMA inventory;
      CREATE TABLE inventory.products (
      id int PRIMARY KEY, product_name text NOT NULL,
      created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP);

   2. 使用以下 SQL 语句用一些示例数据填充表。

      app=> INSERT INTO inventory.products (id, product_name)
      VALUES (1, 'soap'), (2, 'shampoo'), (3, 'conditioner');
      

   3. 使用以下 SQL 语句验证表中是否存在数据。

       app=>SELECT count(*) FROM inventory.products;
      
       count
      -------
       3

6. 在现有数据库上创建 pgactive 扩展。

   app=> CREATE EXTENSION pgactive;

7. 使用以下命令创建并初始化 pgactive 组：

   app=> SELECT pgactive.pgactive_create_group(
       node_name := 'node1-app',
       node_dsn := 'dbname=app host=firstinstance.111122223333.aws-region.rds.amazonaws.com user=master username password=PASSWORD');

   node1-app 是您分配的名称，用于唯一标识 pgactive 组中的节点。

   注意

   要在可公开访问的数据库实例上成功执行此步骤，必须通过将 rds.custom_dns_resolution 参数设置为 1 将其开启。

8. 要检查数据库实例是否就绪，请使用以下命令：

   app=> SELECT pgactive.pgactive_wait_for_node_ready();

   如果命令成功，您可以看到以下输出：

   
   pgactive_wait_for_node_ready 
   ------------------------------ 
   (1 row)
   

配置第二个 RDS for PostgreSQL 实例并将其加入 pgactive 组

以下示例说明如何将 RDS for PostgreSQL 数据库实例加入 pgactive 组，以及在数据库实例上创建 pgactive 扩展所需的其它步骤。

这些步骤假设已使用 pgactive 扩展设置了其它 RDS for PostgreSQL 数据库实例。有关更多信息，请参阅初始化 pgactive 扩展功能。

1. 使用 psql 连接到要从发布者接收更新的实例。

   psql --host=secondinstance.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=master username --password --dbname=postgres
   

2. 使用以下命令在第二个 RDS for PostgreSQL 数据库实例上创建数据库：

   postgres=> CREATE DATABASE app;

3. 使用以下命令将连接切换到新数据库：

   \c app

4. 在现有数据库上创建 pgactive 扩展。

   app=> CREATE EXTENSION pgactive;

5. 将 RDS for PostgreSQL 第二个数据库实例加入 pgactive 组，如下所示。

   app=> SELECT pgactive.pgactive_join_group(
   node_name := 'node2-app',
   node_dsn := 'dbname=app host=secondinstance.111122223333.aws-region.rds.amazonaws.com user=master username password=PASSWORD',
   join_using_dsn := 'dbname=app host=firstinstance.111122223333.aws-region.rds.amazonaws.com user=postgres password=PASSWORD');

   node2-app 是您分配的名称，用于唯一标识 pgactive 组中的节点。

6. 要检查数据库实例是否就绪，请使用以下命令：

   app=> SELECT pgactive.pgactive_wait_for_node_ready(); 

   如果命令成功，您可以看到以下输出：

   
   pgactive_wait_for_node_ready 
   ------------------------------ 
   (1 row)
   

   如果第一个 RDS for PostgreSQL 数据库相对较大，则可以看到 pgactive.pgactive_wait_for_node_ready() 正在发出还原操作的进度报告。输出看上去类似于以下内容：

   NOTICE:  restoring database 'app', 6% of 7483 MB complete
   NOTICE:  restoring database 'app', 42% of 7483 MB complete
   NOTICE:  restoring database 'app', 77% of 7483 MB complete
   NOTICE:  restoring database 'app', 98% of 7483 MB complete
   NOTICE:  successfully restored database 'app' from node node1-app in 00:04:12.274956
    pgactive_wait_for_node_ready 
   ------------------------------ 
   (1 row)
   

   此后，pgactive 将在两个数据库实例之间同步数据。

7. 您可以使用以下命令来验证第二个数据库实例的数据库是否具有数据：

   app=> SELECT count(*) FROM inventory.products;

   如果数据成功同步，您将看到以下输出：

   
    count
   -------
    3

8. 运行以下命令插入新值：

   app=> INSERT INTO inventory.products (id, product_name) VALUES ('lotion');

9. 连接到第一个数据库实例的数据库并运行以下查询：

   app=> SELECT count(*) FROM inventory.products;

   如果已初始化主动-主动复制，则输出类似于以下内容：

   count
   -------
    4

从 pgactive 组中分离和移除数据库实例

您可以使用以下步骤从 pgactive 组中分离和移除数据库实例：

1. 您可以使用以下命令将第二个数据库实例与第一个数据库实例分离：

   app=> SELECT * FROM pgactive.pgactive_detach_nodes(ARRAY[‘node2-app']);

2. 使用以下命令从第二个数据库实例中移除 pgactive 扩展：

   app=> SELECT * FROM pgactive.pgactive_remove();

   要强制移除此扩展，请执行以下操作：

   app=> SELECT * FROM pgactive.pgactive_remove(true);

3. 使用以下命令删除扩展：

   app=> DROP EXTENSION pgactive;

使用 pg_repack 扩展

1. 通过运行以下命令在 RDS for PostgreSQL 数据库实例上安装 pg_repack 扩展。

   CREATE EXTENSION pg_repack;

2. 运行以下命令以授予对由 pg_repack 创建的临时日志表的写入访问权限。

   ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC;
   ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;

3. 使用 pg_repack 客户端实用程序连接到数据库。使用具有 rds_superuser 特权的账户。例如，假设 rds_test 角色有 rds_superuser 特权。以下语法对完整表执行 pg_repack，包括 postgres 数据库中的所有表索引。

   pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test -k postgres

   注意

   必须使用 -k 选项进行连接。不支持 -a 选项。

   来自 pg_repack 客户端的响应提供有关重新打包的数据库实例上的表的信息。

   INFO: repacking table "pgbench_tellers"
   INFO: repacking table "pgbench_accounts"
   INFO: repacking table "pgbench_branches"

4. 以下语法对 postgres 数据库中包含索引的单个表 orders 进行重新打包。

   pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test --table orders -k postgres

   以下语法仅重新打包 postgres 数据库中 orders 表的索引。

   pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test --table orders --only-indexes -k postgres

将目录元数据与新版本的 PLV8 同步

1. 确认您需要更新。为此，请在连接到实例的情况下运行以下命令。

   SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee');

   如果您的结果包含的已安装版本的值低于默认版本，请继续该过程以更新扩展。例如，以下结果集指示您应更新。

   name    | default_version | installed_version |                     comment
   --------+-----------------+-------------------+--------------------------------------------------
   plls    | 2.1.0           | 1.5.3             | PL/LiveScript (v8) trusted procedural language
   plcoffee| 2.1.0           | 1.5.3             | PL/CoffeeScript (v8) trusted procedural language
   plv8    | 2.1.0           | 1.5.3             | PL/JavaScript (v8) trusted procedural language
   (3 rows)

2. 如果尚未创建 RDS for PostgreSQL 数据库实例的快照，请先创建一个。创建快照时，您可以继续下列步骤。

3. 获取数据库实例中 PLV8 函数的数量，以便在升级后验证是否遗漏函数。例如，以下 SQL 查询会返回用 plv8、plcoffee 和 plls 编写的函数数量。

   SELECT proname, nspname, lanname 
   FROM pg_proc p, pg_language l, pg_namespace n
   WHERE p.prolang = l.oid
   AND n.oid = p.pronamespace
   AND lanname IN ('plv8','plcoffee','plls');
   

4. 使用 pg_dump 创建仅架构转储文件。例如，在客户端计算机上的 /tmp 目录中创建文件。

   ./pg_dump -Fc --schema-only -U master postgres >/tmp/test.dmp

   该示例使用以下选项：

   • -Fc - 自定义格式

   • --仅架构 - 仅转储创建架构所需的命令（在本例中为功能）

   • -U - RDS 主用户名

   • database - 数据库实例的数据库名称

   有关 pg_dump 的更多信息，请参阅 PostgreSQL 文档中的 pg_dump。

5. 提取位于转储文件中的“CREATE FUNCTION”DDL 语句。以下示例使用 grep 命令提取创建函数的 DDL 语句并将函数保存到文件中。将在后续步骤中使用该语句重新创建函数。

   ./pg_restore -l /tmp/test.dmp | grep FUNCTION > /tmp/function_list/

   有关 pg_restore 的更多信息，请参阅 PostgreSQL 文档中的 pg_restore。

6. 删掉函数和扩展。以下示例将删除基于 PLV8 的任何对象。级联选项确保删除任何依赖项。

   DROP EXTENSION plv8 CASCADE;

   如果您的 PostgreSQL 实例包含基于 plcoffee 或 plls 的对象，请对这些扩展重复此步骤。

7. 创建扩展。以下示例将创建 plv8、plcoffee 和 plls 扩展。

   CREATE EXTENSION plv8;
   CREATE EXTENSION plcoffee;
   CREATE EXTENSION plls;

8. 使用转储文件和“驱动程序”文件创建函数。

   以下示例将重新创建之前提取的函数。

   ./pg_restore -U master -d postgres -Fc -L /tmp/function_list /tmp/test.dmp

9. 使用以下查询验证是否已重新创建所有函数。

   SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee'); 

   PLV8 版本 2 在结果集中添加以下额外行：

       proname    |  nspname   | lanname
   ---------------+------------+----------
    plv8_version  | pg_catalog | plv8