Troubleshoot AnalyticDB for PostgreSQL connection issues - AnalyticDB

This topic describes how to resolve connection issues with an AnalyticDB for PostgreSQL instance.

Symptoms

When you connect to an AnalyticDB for PostgreSQL instance, you may receive the following error message:

The error message is ERROR: could not connect to server, with details such as could not connect to server: Connection timed out. Is the server running on host <host_address> and accepting TCP/IP connections on port 5432?

Causes

This issue occurs when the client cannot communicate with the AnalyticDB for PostgreSQL instance. Possible causes include:

The client is connecting from a private IP address on a local area network (LAN).
The AnalyticDB for PostgreSQL for the AnalyticDB for PostgreSQL instance does not include the client's public IP address.

Resolution

How do I find the local client IP address and add it to the AnalyticDB for PostgreSQL instance's whitelist?
- If you are unsure of your on-premises client's public IP address, follow this procedure to find it.
  
  Procedure
  1. Temporarily add 0.0.0.0/0 to the ip address whitelist of your AnalyticDB for PostgreSQL instance.
    1. Log on to the AnalyticDB for PostgreSQL console.
    2. In the upper-left corner of the console, select a region.
    3. Find the instance that you want to manage and click the instance ID.
    4. In the navigation pane on the left, click Security Controls.
    5. On the Whitelist Settings tab, click Modify to the right of the default whitelist group. The Modify Whitelist panel opens.
    6. In the IP Addresses field, delete the default entry 127.0.0.1 and enter 0.0.0.0/0.
      
      Note
      The entry 0.0.0.0/0 allows access from any IP address and poses a significant security risk. Remove it as soon as you complete the configuration.
    7. Click OK.
  2. Connect to the AnalyticDB for PostgreSQL instance by using a psql client. If you need to install a psql client, see Client connections. Use the following command:
```
psql -h yourgpdbaddress.gpdb.rds.aliyuncs.com -p 3432 -d postgres -U gpdbaccount
```
    Parameter descriptions:
    - -h: Specifies the host address.
    - -p: Specifies the port number.
    - -d: Specifies the database to connect to. Defaults to postgres.
    - -U: Specifies the username for the connection.
    - For more options, run the psql --help command. Inside psql, run the \? command for a list of psql-specific commands.
  3. After connecting to the database, run the following command to find the IP address of your on-premises client:
```
select * from pg_stat_activity;
```
    In the results, the value in the CLIENT_ADDR column is your on-premises client's IP address.
```
postgres=> select * from pg_stat_activity;
 datid | datname  | procpid | sess_id | usesysid | usename |        current_query        | waiting |          query_start          |         backend_start          | client_addr | client_port | application_name |          xact_start           | waiting_reason
-------+----------+---------+---------+----------+---------+-----------------------------+---------+-------------------------------+--------------------------------+-------------+-------------+------------------+-------------------------------+----------------
 10902 | postgres |   45909 |  254025 |    17239 | test123 | select * from pg_stat_activity; | f       | 2019-02-19 14:07:00.37242+08 | 2019-02-19 14:06:26.170467+08 | xxx.xx.xx.x |       31964 | psql             | 2019-02-19 14:07:00.37242+08 |
(1 row)
postgres=>
```
  4. Return to the AnalyticDB for PostgreSQL console. In the ip address whitelist, remove the 0.0.0.0/0 entry and replace it with your client's IP address.