Connect to a PostgreSQL instance

Data Management (DMS)

Data Management (DMS) is an all-in-one data management service that integrates data management, schema management, user authorization, security auditing, data trends, data tracing, BI charts, performance optimization, and server management. For more information about DMS, see What is Data Management (DMS)?

1. Log on to the ApsaraDB RDS console and 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 instance ID.

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

3. On the Log on to database instance page in DMS, select an Access Method and a Control Mode.

   Parameter	Description
   Access Method	The method to access the ApsaraDB RDS instance by using DMS. This topic uses Account + Password Login as an example. Account + Password Login: Log on with the username and password of a database account that has permissions for the target database. Log on with KMS credential: DMS automatically enables secure hosting for the instance. You must manually select the RDS credential created in KMS to log on. Note When using the Log on with KMS credential access method, DMS automatically enables secure hosting for the instance at no cost. You can also click One-click enable secure hosting, enter an account and password, and enable secure hosting for the instance for free. This enables secure, controllable, and password-free logon.
   Control Mode	DMS provides three instance-level control modes. You can choose one based on your business scenario. Flexible Management Stable Change Security Collaboration

4. After you log on and refresh the page, the instances and their databases appear in the Instances Connected pane on the left.

   Note

   If you enabled secure hosting, you can see the logged-in instances and their databases in the Password-free instances pane on the left.

   

   • In the DMS console, you can only switch databases by double-clicking the target database. The USE command is not supported.

   • If the instance exists but you cannot find the target database after expanding it, the cause may be one of the following:

     • The logon account does not have access permissions for the target database. Log on with an account that has the required permissions.

     • The catalog is not displayed because metadata is not synchronized. To resolve this, hover over the instance name and click the icon to its right. This refreshes the database list and displays the target database.

     To quickly synchronize the schemas of your databases and tables, you can use the DMS empty database initialization feature.

pgAdmin client

The pgAdmin client is the official recommended tool for connecting to PostgreSQL databases. This section uses pgAdmin 4 v6.2.0 as an example to show how to connect to an ApsaraDB RDS for PostgreSQL instance. When you download and install PostgreSQL from the official PostgreSQL website, the pgAdmin 4 client is automatically installed.

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

1. Start the pgAdmin 4 client.

   Note

   When you log on to a recent version of the client for the first time, you must set a Master Password to protect saved passwords and other credentials.

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

   

3. On the General tab, set a name for the connection.

4. Click the Connection tab and enter the connection information for your instance.

   Parameter	Description
   Host name/address	The endpoint and port of the ApsaraDB RDS for PostgreSQL instance. To connect over an internal network, enter the internal endpoint and port of the instance. To connect over the internet, enter the public endpoint and port of the instance. You can view this information on the Database Connection page of your ApsaraDB RDS for PostgreSQL instance. For more information, see View and change endpoints and ports.
   Port
   Username	The username and password of your ApsaraDB RDS for PostgreSQL instance account. For information about how to create an account, see Create an account and a database.
   Password

5. Click Save.

   If the connection information is correct, the server is successfully added.

   Important

   The postgres database is the default system database for the ApsaraDB RDS instance. Do not perform any operations in this database.

PostgreSQL CLI

When you install PostgreSQL from the official PostgreSQL website, the PostgreSQL CLI is also installed.

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

Application

1. Add the dependency to the pom.xml file.

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

2. The following code provides an example of how to connect to the instance:

   public class DatabaseConnection
   {
       public static void main( String[] args ){
           try {
               Class.forName("org.postgresql.Driver");
           } catch (ClassNotFoundException e) {
               e.printStackTrace();
           }
           // The endpoint of the instance.
           String hostname = "pgm-bp1i3kkq7321o9****.pg.rds.aliyuncs.com";
           // The port of the instance.
           int port = 5432;
           // The name of the database.
           String dbname = "postgres";
           // The username.
           String username = "username";
           // The 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();
               // Enter the SQL statement to be executed.
               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();
           }
       }
   }

Third-party BI tool

SSL encryption

ApsaraDB RDS for PostgreSQL supports SSL encryption to protect data in transit. For specific configuration details, see Connect to an ApsaraDB RDS for PostgreSQL instance over an SSL-encrypted connection.