ApsaraDB RDS:Use an ApsaraDB RDS for PostgreSQL instance to access an external database over the Internet

Prerequisites

• An external database that has public IP addresses is available. The database can run MySQL, SQL Server, PostgreSQL, or Redis.
• An RDS instance is created. For more information, see Create an ApsaraDB RDS for PostgreSQL instance.
  Important The following extensions are required to enable your RDS instance to access the external database. Make sure that your RDS instance supports the extensions. For more information about the extensions that are supported by each PostgreSQL version, see Supported extensions.

  • MySQL: mysql_fdw
  • SQL Server: tds_fdw
  • PostgreSQL: postgres_fdw
  • Redis: redis_fdw
• Accounts are created for the external database and your RDS instance.
  • For more information about how to create an account for the external database, see the official documentation of each database engine.
  • For more information about how to create an account for your RDS instance, see Create an account on an ApsaraDB RDS for PostgreSQL instance.
• Data is created for the external database.

Procedure

Configure an Internet NAT gateway

1. Create an Internet NAT gateway.
   1. Log on to the NAT Gateway console.
   2. On the Internet NAT Gateway page, click Create NAT Gateway.
   3. Optional. In the Create Service-Linked Role section of the Internet NAT Gateway page, click Create Service-Linked Role to create a service-linked role. If this is the first time you create an Internet NAT gateway, this step is required. After the service-linked role is created, you can create NAT gateways.
   4. On the Internet NAT Gateway page, configure the parameters and click Buy Now.
      Note The following table describes only key parameters. For more information about all parameters, see Use the SNAT feature of an Internet NAT gateway to access the Internet.

      Parameter	Description
      Region	Select the region in which you want to create the Internet NAT gateway. The region must be the same as the region of your RDS instance.
      VPC	Select the VPC to which the Internet NAT gateway belongs. The VPC must be the same as the VPC of your RDS instance. You can go to the Database Connection page of the ApsaraDB RDS console to view the VPC of your RDS instance.
      Associate vSwitch	Select the vSwitch to which the Internet NAT gateway belongs. The vSwitch must be the same as the vSwitch of your RDS instance. You can go to the Database Connection page of the ApsaraDB RDS console to view the vSwitch of your RDS instance.
      Access Mode	In this example, Configure Later is selected.

   5. On the Confirm page, confirm the configuration, read and select Terms of Service, and then click Confirm.
      You can find the new Internet NAT gateway on the Internet NAT Gateway page.
2. Associate an EIP with the Internet NAT gateway.
   1. On the Internet NAT Gateway page, find the new Internet NAT gateway and click its ID to go to the Basic Information tab.
   2. On the Associated EIP tab, click Bind Elastic IP Address.
   3. In the Associate EIP dialog box, select Purchase and Associate EIP.
   4. Click OK.
      After you associate an EIP with the Internet NAT gateway, the EIP is displayed on the Associated EIP tab.
3. Create an SNAT entry.
   1. On the Internet NAT Gateway page, find the new Internet NAT gateway and click its ID to go to the Basic Information tab.
   2. On the SNAT management tab, click Create SNAT Entry.
   3. On the Create SNAT Entry page, configure the parameters and click Confirm.

      Parameter	Description
      SNAT Entry	Specify whether to create an SNAT entry for a VPC, a vSwitch, an ECS instance, or a custom CIDR block. In this example, Specify vSwitch is selected. This option specifies that only RDS instances that are attached to a specified vSwitch can access the Internet by using a specified public IP address.
      Select VSwitch	Select the vSwitch of your RDS instance from the drop-down list.
      Select Public IP Address	Select one or more public IP addresses to access the Internet. In this example, Use One IP Address is selected, and an EIP is selected from the drop-down list.

      After the SNAT entry is created, you can view the SNAT entry in the Used in SNAT Entry section.

Configure the external database

Configure your RDS instance

1. Connect to your RDS instance. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance.
2. Create an extension.
   Note In this example, a mysql_fdw extension is created for the external database that runs MySQL.

   CREATE EXTENSION mysql_fdw;

3. Create a server definition for the external database.

   CREATE SERVER <Server name>
   FOREIGN DATA WRAPPER mysql_fdw OPTIONS (
     host '<Public IP address of the external database>',
     port '<Port number of the external database>'
   );

   In this example, the external database runs MySQL, and you must execute the following statement:

   CREATE SERVER mysql_server80
   FOREIGN DATA WRAPPER mysql_fdw OPTIONS (
     host 'XX.XX.XX.XX',
     port '3306'
   );

4. Create a user mapping to map the MySQL server definition to a user of your RDS instance. Use the user to access the external MySQL database.

   CREATE USER MAPPING
   FOR <Username of your RDS instance> SERVER <Name of the created server> OPTIONS (
     username '<Username of the external database>',
     password '<Password of the external database>'
   );

   Sample statement:

   CREATE USER MAPPING
   FOR pg_client SERVER mysql_server80  OPTIONS (
     username 'testuser',
     password 'U123456!'
   );

5. Create a foreign table.

   CREATE FOREIGN TABLE <Name of the foreign table> (
     id int,
     name varchar(10)
   )
   SERVER <Name of the created server> OPTIONS (
     dbname '<Name of the external database>',
     table_name '<Name of the table in the external database>'
   );

   Sample statement:

   CREATE FOREIGN TABLE mysql_fdw_test (
     id int,
     name varchar(10)
   )
   SERVER mysql_server80 OPTIONS (
     dbname 'testdb',
     table_name 'test'
   );

6. Test the connectivity.
   After you complete the preceding configuration, your RDS instance can access the table in the external database.

   In this example, a foreign table named mysql_fdw_test is created. You can directly query the foreign table from your RDS instance to obtain the data in the external database.

   SELECT * FROM mysql_fdw_test;