This topic describes how to connect to an ApsaraDB RDS for PostgreSQL instance. You can connect to an RDS instance by using Data Management (DMS), a command-line tool, pgAdmin, or an application.

Prerequisites

The operations that are described in the following topics are complete:

Use DMS to connect to an RDS instance

Log on to the ApsaraDB RDS console, find the RDS instance to which you want to connect, and go to the Basic Information page. In the upper-right corner of the page, click Log On to Database. Then, enter the required information to log on to the RDS instance.

Log On to Database button

For more information, see Use DMS to log on to an ApsaraDB RDS for PostgreSQL instance.

Use pgAdmin to connect to an RDS instance

When you download the PostgreSQL software package from the PostgreSQL official website and install PostgreSQL, pgAdmin 4 is automatically downloaded and installed. You can also download the pgAdmin software package from the PostgreSQL official website.

  1. Start pgAdmin 4.
    Note If you use pgAdmin that is in a later version than version 4 and you use pgAdmin for the first time, you must specify a master password to protect your saved logon credentials such as passwords.
  2. Right-click Servers and choose Create > Server....
  3. On the General tab of the Create - Server dialog box, enter the name of the server on which pgAdmin is installed.
  4. Click the Connection tab and enter the information that is used to connect to the RDS instance.
    Parameter Description
    Hostname/address Enter the endpoint of the RDS instance. If you want to connect to the RDS instance over an internal network, enter the internal endpoint of the RDS instance. If you want to connect to the RDS instance over the Internet, enter the public endpoint of the RDS instance. For more information, see View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.
    Port Enter the port number that is associated with the specified endpoint.
    Username Enter the username of the account that is used to log on to the RDS instance. For more information about how to create an account for an RDS instance, see Create a database and an account on an ApsaraDB RDS for PostgreSQL instance.
    Password Enter the password of the account that is used to log on to the RDS instance.
  5. Click Save.
    If the information that you enter is correct, the page that is shown in the following figure appears, which indicates that the connection to the RDS instance is successful.
    Notice The postgres database is a default system database. Do not perform operations on this database.

Use a command-line tool to connect to an RDS instance

When you download the PostgreSQL software package from the PostgreSQL official website and install PostgreSQL, a PostgreSQL command-line tool is automatically downloaded and installed.

Run the following command in the command-line tool to connect to a database of the RDS instance:
psql -h <Endpoint> -U <Username> -p <Port number> -d <Database name>
Run the command in the command-line tool
The following table provides details about how to obtain the values of the parameters from the ApsaraDB RDS console.
Parameter How to obtain
Endpoint. The endpoint that is used to connect to the RDS instance. For more information, see View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.
Username The username of the account that is used to log on to the RDS instance. You can obtain the username from the Accounts page. For more information about how to create an account, see Create an account on an ApsaraDB RDS for PostgreSQL instance.
Port number The port number that is used to connect to the RDS instance. The default port number is 5432. If you have modified the port number, you can obtain the new port number from the Database Connection page. For more information, see View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.
Database name The name of the database that you want to connect in the RDS instance. The postgres database is a default system database. Do not perform operations on this database. You can obtain the name of the database that you want to connect from the Databases Connection page. For more information about how to create a database, see Create a database on an ApsaraDB RDS for PostgreSQL instance.

Use SQL Shell (psql) to connect to an RDS instance

When you download the PostgreSQL software package from the PostgreSQL official website and install PostgreSQL, SQL Shell (psql) is automatically downloaded and installed.

Open the Start menu on your computer and click the SQL Shell (psql). Then, enter the required parameters to connect to the RDS instance.

SQL Shell
The following table provides details about how to obtain the values of the parameters from the ApsaraDB RDS console.
Parameter How to obtain
Server The endpoint that is used to connect to the RDS instance. For more information, see View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.
Database The name of the database that you want to connect in the RDS instance. If you do not specify this parameter, the default value is postgres. The postgres database is a default system database. Do not perform operations on this database. You can obtain the name of the database that you want to connect from the Databases Connection page. For more information about how to create a database, see Create a database on an ApsaraDB RDS for PostgreSQL instance.
Port The port number that is used to connect to the RDS instance. The default port number is 5432. If you have modified the port number, you can obtain the new port number from the Database Connection page. For more information, see View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.
Username The username of the account that is used to log on to the RDS instance. You can obtain the username from the Accounts page. For more information about how to create an account, see Create an account on an ApsaraDB RDS for PostgreSQL instance.

Use an application to connect to an RDS instance

Note In this topic, a Maven project is connected to the RDS instance by using the Java Database Connectivity (JDBC). This connection method is similar to other programming languages.
  1. Add dependencies to the pom.xml file:
    <dependency>
      <groupId>postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>8.2-504.jdbc3</version>
    </dependency>
  2. The following code snippet provides an example on how to use the JDBC to connect to the RDS instance:
    public class DatabaseConnection
    {
        public static void main( String[] args ){
            try {
                Class.forName("org.postgresql.Driver");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            //Endpoint
            String hostname = "pgm-bp1i3kkq7321o9****.pg.rds.aliyuncs.com";
            //Port number
            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";
            Connection dbConnection;
            try {
                dbConnection = DriverManager.getConnection(dbUrl, username, password);
                Statement statement = dbConnection.createStatement();
                //SQL statement that you want to execute 
                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();
            }
        }
    }

Configure SSL encryption for an RDS instance

You can configure SSL encryption for an RDS instance. SSL encryption is used to encrypt the connections to the RDS instance and protect the data that is transmitted over the connections. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance over SSL.

FAQ

How do I use Function Compute to obtain data from my RDS instance?

You can install third-party dependencies on Function Compute. Then, you can use these built-in dependencies to obtain data from ApsaraDB RDS. For more information, see Install third-party dependencies.