ApsaraDB RDS:Connect to an ApsaraDB RDS for PostgreSQL instance

Use DMS to connect to the RDS instance

Data Management (DMS) is a browser-based tool that covers data management, schema management, server management, user authorization, security audit, trend analysis, data tracking, BI reporting, and performance optimization. No client installation is required.

1. Log on to the ApsaraDB RDS console. In the top navigation bar, select the region where the instance resides. Find the instance and click its ID.

2. On the Basic Information page, click Log On to Database.

   

3. In the Log on to Database Instance dialog box, configure the Access mode and Control Mode parameters. Access mode options:

   • Security Hosting - Automatic (Recommended): DMS enables security hosting and creates a database account automatically.

   • Account + password login: use a database account that has permissions on the target database.

   • Login with KMS Secret: DMS enables security hosting, but you must manually select the RDS credentials from Key Management Service (KMS).

   Parameter	Description
   Access mode	The method used to connect to the RDS instance from DMS. Security Hosting - Automatic (Recommended) and Login with KMS Secret both enable security hosting, which activates logon-free mode for the instance. Account + password login requires a database account with the necessary permissions.
   Control Mode	The instance-level control mode: Flexible Management, Stable Change, or Security Collaboration. Choose based on your business requirements.

4. Refresh the page. In the left-side navigation pane, click Instances Connected to view the connected RDS instances and their databases. Keep the following in mind:

   • To switch databases in DMS, double-click the target database. The USE command is not supported.

   • If the instance appears but the database is missing, either the account lacks permissions to that database, or the metadata needs a refresh. To refresh, hover over the instance name and click the icon.

   • To quickly synchronize database and table schemas, use the empty database initialization feature.

   If you enabled security hosting, the instances appear under Logon-free Instances instead.

   

To add an RDS instance directly from the DMS console, see Register an Alibaba Cloud database instance. After resetting your account password, log on to DMS again.

Use pgAdmin to connect to the RDS instance

pgAdmin is a desktop GUI client for PostgreSQL. pgAdmin 4 is bundled with the PostgreSQL installer from the PostgreSQL official website. To install pgAdmin without PostgreSQL, download it separately from pgadmin.org.

The following steps use pgAdmin 4 V6.2.0 as an example.

1. Start pgAdmin 4.

   On first launch of newer pgAdmin versions, set a master password to protect saved credentials.

2. Right-click Servers and choose Register > Server.

   

3. On the General tab, enter a name for the server.

   

4. Click the Connection tab and configure the connection parameters.

   Parameter	Description
   Host name/address	The instance endpoint. Use the internal endpoint for VPC connections, or the public endpoint for Internet connections.
   Port	The port corresponding to the endpoint type (internal or public).
   Username	The database account username.
   Password	The database account password. For more information, see Create a database and an account.

   

5. Click Save. A successful connection shows the server tree in the left panel.

   

Use the PostgreSQL CLI to connect to the RDS instance

The PostgreSQL CLI (psql, also called Command Line Tools) is installed with PostgreSQL from the PostgreSQL official website.

Run the following command to connect:

psql -h <endpoint> -U <username> -p <port> [-d <database-name>]

Connect from an application

The following example uses Java Database Connectivity (JDBC) in a Maven project. The approach is similar for other programming languages.

Step 1: Add the PostgreSQL JDBC dependency to pom.xml:

<dependency>
  <groupId>postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>8.2-504.jdbc3</version>
</dependency>

Step 2: Connect to the instance using JDBC:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseConnection {
    public static void main(String[] args) {
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        // Endpoint of the RDS instance
        String hostname = "pgm-bp1i3kkq7321o9****.pg.rds.aliyuncs.com";
        // Port number of the RDS instance
        int port = 5432;
        // Database name
        String dbname = "postgres";
        // Username
        String username = "username";
        // Password
        String password = "password";

        String dbUrl = "jdbc:postgresql://" + hostname + ":" + port + "/" + dbname + "?binaryTransfer=true";

        try {
            Connection dbConnection = DriverManager.getConnection(dbUrl, username, password);
            Statement statement = dbConnection.createStatement();
            String selectSql = "SELECT * FROM information_schema.sql_features LIMIT 10";
            ResultSet resultSet = statement.executeQuery(selectSql);
            while (resultSet.next()) {
                System.out.println(resultSet.getString("feature_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}