ApsaraDB RDS:Connect to an ApsaraDB RDS for PostgreSQL instance

Use DMS to connect to the RDS instance

DMS offers an integrated solution that supports data management, schema management, server management, user authorization, security audit, trend analysis, data tracking, business intelligence (BI) reporting, and performance analysis and optimization. For more information, see What is DMS?

1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.

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

3. In the Log on to Database Instance dialog box of the DMS console, enter the username and password of the account that is used for logon and click Login.

   Note

   The account that is used for logon must have permissions on the required database. Otherwise, the required database is not displayed in the left-side navigation pane.

   

4. Refresh the page. In the left-side navigation pane of the DMS console, click Instances Connected to view databases that are created for the RDS instance.

Use pgAdmin to connect to the RDS instance

pgAdmin is a recommended PostgreSQL client that you can use 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. The following section provides an example on how to use pgAdmin 4 V6.2.0 to connect to an RDS instance.

If you do not want to install PostgreSQL, you can download only pgAdmin for remote connections.

1. Start pgAdmin 4.

   Note

   If this is the first time you log on to pgAdmin of a later version, you must specify a master password that is used to protect saved passwords and other credentials.

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

   

3. On the General tab of the Register - Server dialog box, enter the name of the server on which pgAdmin is installed.

4. Click the Connection tab. Then, configure the parameters that are used to connect to the RDS instance.

   Parameter	Description
   Host name/address	The endpoint and port that are used to connect to the RDS instance. If you want to connect to the RDS instance over an internal network, enter the internal endpoint and internal port of the RDS instance. If you want to connect to the RDS instance over the Internet, enter the public endpoint and public port of the RDS instance. You can view the preceding information on the Database Connection page of the RDS instance. For more information, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.
   Port
   Username	The username and password that are used to log on to the RDS instance. For more information about how to create an account on an RDS instance, see Create a database and an account on an ApsaraDB RDS for PostgreSQL instance.
   Password

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.

   Important

   The postgres database is the default system database. Do not perform operations on the postgres database.

Use the PostgreSQL CLI to connect to the RDS instance

When you download the PostgreSQL software package from the PostgreSQL official website and install PostgreSQL, the PostgreSQL CLI that is named Command Line Tools is automatically downloaded and installed.

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

Use an application to connect to the 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. Use 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 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";
           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();
           }
       }
   }

Use a third-party reporting tool to connect to the RDS instance

ApsaraDB RDS for PostgreSQL allows you to use third-party reporting tools to obtain, cleanse, and visualize data, and create models based on the data. This facilitates data analysis. This section provides an example on how to use Power BI Desktop that is provided by Microsoft to connect to an RDS instance. In this example, Power BI Desktop 2.112.1161.0 64-bit is used.

1. Download and install Power BI Desktop. For more information about the download method, see Obtain Power BI Desktop.

2. Start Power BI Desktop.

3. In the top navigation bar, click the Home tab and choose Get data > More.

4. In the Get Data dialog box, choose Database > PostgreSQL database and click Connect.

5. In the PostgreSQL database dialog box, configure the Server and Database parameters and click OK.

   Parameter	Description
   Server	The endpoint and port of the RDS instance. The value is in the format of Endpoint:Port number. If you want to connect to the RDS instance over an internal network, enter the internal endpoint and internal port of the RDS instance. If you want to connect to the RDS instance over the Internet, enter the public endpoint and public port of the RDS instance. You can view the preceding information on the Database Connection page of the RDS instance. For more information, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.
   Database	The name of the database that you want to connect on the RDS instance. The database named postgres is the default system database. Do not perform operations on the postgres database. We recommend that you use another existing database of the RDS instance. For more information about how to create and view a database on an RDS instance, see Create a database.

6. Configure the User name and Password parameters and click Connect. You must set these parameters to the username and password of the RDS instance.

   Note

   For more information about how to create an account on an RDS instance, see Create a database and an account on an ApsaraDB RDS for PostgreSQL instance.

7. In the Encryption Support dialog box, click OK.

8. In the Navigator window, view information about the tables in the database. You can select the required table and click Load or Transform Data based on your business requirements.

Connect to an RDS instance over SSL connections

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 connections.