Connect to an ApsaraDB RDS for PostgreSQL instance

Prerequisites

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.

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.

psql -h <Endpoint> -U <Username> -p <Port number> -d <Database name>

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.

Use an application to connect to an RDS instance

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.