ApsaraDB RDS:Access an external database over the Internet from an RDS for PostgreSQL instance

Step 1: Create a NAT gateway

1. Log on to the NAT Gateway console.

2. On the Internet NAT Gateway page, click Create Internet NAT Gateway.

3. (Optional) If this is your first time using NAT Gateway, click Create Service-linked Role in the Service-linked Role Creation section. The button appears on the Internet NAT Gateway page.

4. On the Create Internet NAT Gateway page, set the following parameters and click Buy Now.

   The table below lists key parameters only. For all parameters, see Use the SNAT feature of an Internet NAT gateway to access the Internet.

   Parameter	Description
   Region	Select the same region as your RDS for PostgreSQL instance
   Network And Zone	Select the VPC and vSwitch of your RDS for PostgreSQL instance. To find the VPC, go to the Database Connection page in the RDS console
   Network Type	Select Internet NAT Gateway
   EIP	Select Configure Later

5. On the Confirm page, review the configuration and click Activate Now.

After the gateway is created, it appears on the Internet NAT Gateway page.

Step 2: Associate an Elastic IP Address (EIP)

1. In the NAT Gateway console, click the instance ID of the NAT gateway you created.

2. On the Associated EIP tab, click Associate EIP.

3. In the Associate EIP dialog box, select Purchase and Associate EIP.

   

4. Click OK.

The EIP appears in the Associated EIP section.

Step 3: Create an SNAT entry

1. In the NAT Gateway console, click the instance ID of the NAT gateway you created.

2. On the SNAT tab, click Create SNAT Entry.

3. Configure the following parameters and click OK.

   Parameter	Description
   SNAT Entry	Select vSwitch Granularity. All instances in the vSwitch use the EIP to access the internet
   Select vSwitch	Select the vSwitch of your RDS for PostgreSQL instance
   Select EIP	Select the EIP associated in the previous step

The SNAT entry appears in the SNAT Entry List.

Step 1: Connect to the RDS instance

Connect to your RDS for PostgreSQL instance. See Connect to a PostgreSQL instance.

Step 2: Install the FDW extension

Run the following command:

CREATE EXTENSION mysql_fdw;

Step 3: Create a server definition

Create a server definition that points to the target database. Replace the placeholders with actual values:

CREATE SERVER <server_name>
FOREIGN DATA WRAPPER mysql_fdw OPTIONS (
  host '<public_IP_of_the_target_database>',
  port '<port_of_the_target_database>'
);

Example for a MySQL database:

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

Expected output:

CREATE SERVER

Step 4: Create a user mapping

Map the server definition to a user in the RDS instance. This lets the user authenticate to the target MySQL database:

CREATE USER MAPPING
FOR <RDS_for_PostgreSQL_username> SERVER <created_server_name> OPTIONS (
  username '<username_for_the_target_database>',
  password '<password_for_the_target_database>'
);

Example:

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

Expected output:

CREATE USER MAPPING

Step 5: Create a foreign table

Create a foreign table that maps to a table in the target database. The column names and types must match the remote table:

CREATE FOREIGN TABLE <foreign_table_name> (
  id int,
  name varchar(10)
)
SERVER <created_server_name> OPTIONS (
  dbname '<database_name_of_the_target_database>',
  table_name '<table_name_of_the_target_database>'
);

Example:

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

Expected output:

CREATE FOREIGN TABLE

Step 6: Verify the connection

Query the foreign table to confirm data is returned from the target MySQL database:

SELECT * FROM mysql_fdw_test;

If the query returns rows from the target database, the configuration is complete.