If your local server still can't connect to an ApsaraDB RDS for PostgreSQL instance after you add its public IP address to the IP address whitelist, the most likely cause is that the IP address you added is incorrect. This topic describes how to find the actual public IP address of your local server using a SQL query, then update the whitelist with the correct address.
This solution applies only to connections from a local server. If you're connecting from an Alibaba Cloud Elastic Compute Service (ECS) instance, get the public and private IP addresses of the ECS instance directly from the ECS console.
Prerequisites
Before you begin, ensure that you have:
An ApsaraDB RDS for PostgreSQL instance
pgAdmin 4 installed on your local server
Find the correct IP address and update the whitelist
Step 1: Temporarily allow all connections.
Add 0.0.0.0/0 to the IP address whitelist of your RDS instance. This lets your local server connect regardless of its IP address. For instructions, see Configure an IP address whitelist.
Step 2: Connect to the RDS instance using pgAdmin 4.
For instructions, see Connect to an ApsaraDB RDS for PostgreSQL instance.
Step 3: Open the Query Tool.
Click Database, then click postgres. In the top navigation bar, choose Tools > Query Tool.
Step 4: Run the following SQL query to find your public IP address.
select datname, pid, usename, client_addr, client_hostname, client_port, query from pg_stat_activity;In the results, find the row where the query column shows SELECT. The value in the client_addr column is the public IP address of your local server.
Sample output:
Step 5: Update the whitelist with the correct IP address.
Remove 0.0.0.0/0 from the whitelist, then add the client_addr value you found in the previous step.
Dynamic IP address considerations
If your local server has a dynamic public IP address and this connection is used in a production environment, use one of the following approaches to avoid repeated disconnections when the IP changes:
Connect to the RDS instance over an internal network.
Add an appropriate public CIDR block to the IP address whitelist instead of a single IP address.
Applicable scope
ApsaraDB RDS for PostgreSQL