This topic describes how to create an Apsara PolarDB cluster, specify basic configurations, and connect to the cluster. It allows you to familiarize yourself with the entire process about how to purchase and use an Apsara PolarDB cluster.

Procedure

To purchase and use an Apsara PolarDB cluster, you must perform the following steps:

  1. Step 1: Create a PolarDB-O cluster
  2. Step 2: Configure the cluster whitelist
  3. Step 3: Create a database account
  4. Step 4: Connect to a database cluster
  5. Step 5: Create a database
  6. Step 6: Query data

Step 1: Create a PolarDB-O cluster

  1. Log on to the PolarDB console.
  2. On the upper-left corner of the page, click Create Cluster.
  3. Select Subscription or Pay-As-You-Go as Product Type.
    Note
    • Subscription: An upfront payment is required for the compute nodes when you create the cluster. The compute nodes include one primary node and one read-only node. The storage fee is charged based on the used storage space on an hourly basis and is deducted from your account on an hourly basis. The Subscription billing method is more cost-effective than the pay-as-you-go billing method if you want to use the new cluster for a long period of time. You are offered larger discounts for longer subscription periods.
    • Pay-As-You-Go: An upfront payment is not required. Both the compute node fee and the storage fee are charged on an hourly basis. The storage fee is charged based on the used storage space. Fees are deducted from your account on an hourly basis. We recommend that you select the pay-as-you-go billing method for short term use. You can save costs by releasing the cluster as needed.
  4. Specify the following parameters.
    Section Parameter Description
    Basic Configuration Region The region where the cluster is deployed. You cannot change the region after you purchase the instance.
    Note Make sure that you deploy your PolarDB-O cluster in the same region as the Elastic Compute Service (ECS) instance to which you want to connect. Otherwise, the instances cannot communicate through the internal network and optimal performance cannot be achieved.
    Create Type The create type of a Polar-O cluster. Valid values:
    • Create Primary Cluster: create a new Apsara PolarDB cluster.
    • Restore from Recycle: create a new cluster by restoring a backup of a deleted cluster from the recycle bin.
      • Source Version: the version of the cluster that has been deleted.
      • Deleted Clusters: the name of the cluster that has been deleted.
      • Backup History: select the backup that you want to restore.

    You can select other options to create databases of other engines.

    Primary Availability Zone The zone of the cluster.
    • Each zone is an independent geographical location within a region. The zones that are deployed in the same region are similar.
    • You can deploy your Apsara PolarDB cluster and the ECS instance in the same zone or in different zones.
    • You only need to select the primary zone. The system automatically selects a secondary zone.
    Network type

    By default, this parameter is set to VPC. You do not need to specify this parameter.

    VPC

    VSwitch

    Make sure that the Apsara PolarDB cluster and ECS instance are deployed in the same VPC network. Otherwise, the cluster and the ECS instance cannot communicate with each other over the internal network and achieve optimal performance.
    • If you have created a VPC network that meets your network plan, select the VPC. For example, if you have created an ECS instance and the VPC network where it is deployed meets your network plan, select this VPC.
    • Otherwise, use the default VPC and VSwitch.
      • Default VPC:
        • It is a unique VPC in the selected zone.
        • The default VPC uses a 16-bit subnet mask. For example, the CIDR block 172.31.0.0/16 provides up to 65,536 internal IP addresses
        • It is not included in the total number of VPC networks that you can create.
      • Default VSwitch:
        • It is a unique VSwitch in your selected zone.
        • The default VSwitch uses a 20-bit subnet mask. For example, the CIDR block 172.16.0.0/20 provides up to 4,096 internal IP addresses
        • The default VSwitch is not included in the total number of VSwitches that you can create in a VPC network.
    • If the default VPC and VSwitch cannot meet your business requirements, you can create your own VPC and VSwitch.
    Instance Compatibility Select Compatibility with Oracle Syntax.

    PolarDB-O is highly compatible with Oracle syntax. For more information, see Oracle compatibility.

    Node Specification Select the specifications as needed. All nodes in the Apsara PolarDB cluster are dedicated nodes with stable and reliable performance. For more information about specifications, see Specifications and pricing.
    Number of Nodes
    • The number of nodes in the cluster. You do not need to specify this parameter. The system automatically creates a read-only node with the same specification as that of the primary node.
    • If the primary node fails, the system automatically switches the read-only node as the primary node, and creates a new read-only node.
    • For more information about read-only nodes, see Architecture.
    Storage Cost The fee incurred by the storage. You do not need to specify this parameter. The system charges you on an hourly basis based on the actual data usage. For more information, see Specifications and pricing.
    Note You do not need to select a storage capacity when you purchase a cluster. The storage capacity can automatically resize based on your data usage.
    Enable TDE Specify whether to enable Transparent Data Encryption (TDE). After TDE is enabled, Apsara PolarDB encrypts cluster data files. This may reduce the performance by 5% to 10%.
    Note You cannot disable TDE after it is enabled.
  5. Specify Purchase Plan if the billing method is subscription, specify Number, and click Buy Now in the lower-right corner.
    Note You can create a maximum of 50 clusters at a time. For example, you can create multiple clusters in scenarios such as enabling multiple game servers at a time.
  6. On the Confirm Order page, confirm your order information. Read the terms of service, select the check box, and click Buy Now.
  7. After you complete the payment, it takes 10 to 15 minutes to create the cluster. Then, the newly created cluster is displayed on the Clusters page in the console.
    Note
    • If some nodes in the cluster are still in the Creating state, the cluster is still being created and unavailable. The cluster is available only when the cluster is in the Running state.
    • Make sure that you have selected the region where the cluster is deployed. Otherwise, you cannot view your cluster.

Step 2: Configure the cluster whitelist

  1. Log on to the Apsara PolarDB console.
  2. In the upper-left corner of the page, select the region where the instance is deployed.
  3. Click the cluster ID to go to the Basic Information page.
  4. Choose Settings and Management > Whitelists.
  5. On the Whitelists page, find the whitelist that you want to manage, and click Modify in the Actions column for the whitelist to modify the whitelist. You can also click Add IP Whitelist to add a whitelist.
    Whitelists
    • Click Modify in the Actions column to configure the IP whitelist.
    • Click Add IP Whitelist to add an IP whitelist.
  6. In the Add IP Whitelist pane, configure the information of the IP whitelist and click Submit.
    • If you want to connect your ECS instance to the Apsara PolarDB cluster, you can retrieve IP addresses of the ECS instance from the Configuration Information section on the Instance Details page. Then you can add these IP addresses to the IP whitelist.
      Note If the ECS instance is in the same region as the Apsara PolarDB cluster such as the China (Hangzhou) region, use the private IP address of the ECS instance. If the ECS instance is in a different region from the Apsara PolarDB cluster, use the Elastic IP address of the ECS instance. You can also migrate the ECS instance to the region where the Apsara PolarDB cluster is located. Then, you can use the private IP address of the ECS instance.
    • If you want to connect your on-premises server, computer, or other cloud server to the Apsara PolarDB cluster, add the IP address to the IP whitelist.

Step 3: Create a database account

  1. Log on to the PolarDB console.
  2. On the top of the page, select the region where the target cluster is located.
  3. Find the target cluster and click the cluster ID to go to the Overview page.
  4. In the left-side navigation pane, choose Settings and Management > Accounts.
  5. On the page that appears, click Create Account.
  6. In the Create Account pane, configure the following parameters.
    Parameter Description
    Account Name

    Enter an account name. The account name must meet the following requirements:

    • It must start with a lowercase letter and end with a letter or a digit.
    • It can contain lowercase letters, digits, and underscores (_).
    • It must be 2 to 16 characters in length.
    • It cannot be a system reserved username, such as root or admin.
    Account Type
    • To create a privileged account, select Privileged Account.
    • To create a standard account, select Standard Account.
    Password Enter an account password. The password must meet the following requirements:
    • It must contain at least three of the following character types: uppercase letters, lowercase letters, digits, and special characters.
    • It must be 8 to 32 characters in length.
    • It can contain the following special characters:

      ! @#$%^&*()_+

    Confirm Password Enter the password again.
    Description Enter the information about the account to facilitate subsequent account management. The description must meet the following requirements:
    • It cannot start with http:// or https://.
    • It must start with a letter.
    • It can contain letters, digits, underscores (_), and hyphens (-).
    • It must be 2 to 256 characters in length.
  7. Click OK.
  1. Log on to the ApsaraDB for PolarDB console.
  2. Find the target cluster and click the cluster ID.
  3. In the left-side navigation pane, click Accounts.
  4. Click Create Account.
  5. In the dialog box that appears, set the following parameters.
    Parameter Description
    Account Name

    Enter the account name. The requirements are as follows:

    • It must start with a lowercase letter and end with a letter or digit.
    • It can contain lowercase letters, digits, and underscores (_).
    • It must be 2 to 16 characters in length.
    • It cannot be a system reserved username, such as root and admin.
    Account Type Select Privileged Account or Standard Account.
    Password Enter the password of the privileged account. The requirements are as follows:
    • The password must contain at least three of the following character types: uppercase letters, lowercase letters, digits, and special characters.
    • The password must be 8 to 32 characters in length.
    • Special characters include ! @ # $ % ^ & * ( ) _ + - =
    Confirm Password Enter the password again.
    Description Enter information about the account to facilitate future use. The requirements are as follows:
    • It cannot start with http:// or https://.
    • The description must start with an uppercase or lowercase letter.
    • The description can contain uppercase or lowercase letters, digits, underscores (_), and hyphens (-).
    • The description must be 2 to 256 characters in length.

Step 4: Connect to a database cluster

Use a client to connect to a PolarDB-O cluster. In this example, pgAdmin 4 is used. Perform the following steps:

  1. Launch the pgAdmin 4 client.
  2. Right-click Servers and choose Create > Server, as shown in the following figure.
    Create a server
  3. On the General tab of the Create - Server dialog box that appears, enter the name of the server, as shown in the following figure.
    pgadmin4-General
  4. Click the Connection tab and specify the information of the destination instance. These parameters are listed in the following table.
    pgadmin-Connection
    Table 1. Parameters
    Parameter Description
    Host name/address

    Enter the endpoint of the PolarDB-O cluster. To view the endpoint and port information of the PolarDB-O cluster, perform the following steps:

    1. Log on to the Apsara PolarDB console.
    2. In the upper-left corner of the console, select the region where the cluster is deployed.
    3. Find the target cluster and click the cluster ID.
    4. In the Endpoints section, view the endpoint and port information.
    Port The port of the cluster. Default value: 1521.
    Maintenance database Enter the maintenance database. Default value: postgres.
    Username The username of the account of the PolarDB-O cluster. For more information, see Create database accounts.
    Password The password of the account for the PolarDB-O cluster.
  5. Click Save.
  6. If the connection information is valid, the following page appears after you click the database name. This indicates that the connection is established.
    Connection established
  7. Right-click the target database and click Query Tool.... On the following page, you can add, delete, update, and query data in the database.
    Query Editor

Use DMS to log on to an Apsara PolarDB cluster:

  1. Log on to the PolarDB console.
  2. On the top of the page, select the region where the target cluster is located.
  3. Find the target cluster and click the cluster ID to go to the Overview page.
  4. In the upper right corner of the Overview page, click Log On to Database to go to the RDS Database Logon page.
    Use DMS to connect to a PolarDB-O cluster
  5. On the RDS Database Logon page, enter the primary endpoint and the port number, and separate them with a colon (:). Then, enter the username and password of the privileged or standard account, and click Log On.
    When you connect to a cluster by using DMS, you can only use a primary endpoint. DMS does not support cluster endpoints.

Step 5: Create a database

  • The following example shows how to create tables: dept, EMP, and jobhist.
    CREATE TABLE dept (
        deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
        dname           VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
        loc             VARCHAR2(13)
    );
    CREATE TABLE emp (
        empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
        ename           VARCHAR2(10),
        job             VARCHAR2(9),
        mgr             NUMBER(4),
        hiredate        DATE,
        sal             NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
        comm            NUMBER(7,2),
        deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                            REFERENCES dept(deptno)
    );
    CREATE TABLE jobhist (
        empno           NUMBER(4) NOT NULL,
        startdate       DATE NOT NULL,
        enddate         DATE,
        job             VARCHAR2(9),
        sal             NUMBER(7,2),
        comm            NUMBER(7,2),
        deptno          NUMBER(2),
        chgdesc         VARCHAR2(80),
        CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate),
        CONSTRAINT jobhist_ref_emp_fk FOREIGN KEY (empno)
            REFERENCES emp(empno) ON DELETE CASCADE,
        CONSTRAINT jobhist_ref_dept_fk FOREIGN KEY (deptno)
            REFERENCES dept (deptno) ON DELETE SET NULL,
        CONSTRAINT jobhist_date_chk CHECK (startdate <= enddate)
    );
  • The following example shows how to insert data into a table.
    INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
    INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
    INSERT INTO dept VALUES (30,'SALES','CHICAGO');
    INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
    INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
    INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
    INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
    INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
    INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
    INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
    INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
    INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
    INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
    INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
    INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
    INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
    INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
    INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
    INSERT INTO jobhist VALUES (7369,'17-DEC-80',NULL,'CLERK',800,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7499,'20-FEB-81',NULL,'SALESMAN',1600,300,30,'New Hire');
    INSERT INTO jobhist VALUES (7521,'22-FEB-81',NULL,'SALESMAN',1250,500,30,'New Hire');
    INSERT INTO jobhist VALUES (7566,'02-APR-81',NULL,'MANAGER',2975,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7654,'28-SEP-81',NULL,'SALESMAN',1250,1400,30,'New Hire');
    INSERT INTO jobhist VALUES (7698,'01-MAY-81',NULL,'MANAGER',2850,NULL,30,'New Hire');
    INSERT INTO jobhist VALUES (7782,'09-JUN-81',NULL,'MANAGER',2450,NULL,10,'New Hire');
    INSERT INTO jobhist VALUES (7788,'19-APR-87','12-APR-88','CLERK',1000,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7788,'13-APR-88','04-MAY-89','CLERK',1040,NULL,20,'Raise');
    INSERT INTO jobhist VALUES (7788,'05-MAY-90',NULL,'ANALYST',3000,NULL,20,'Promoted to Analyst');
    INSERT INTO jobhist VALUES (7839,'17-NOV-81',NULL,'PRESIDENT',5000,NULL,10,'New Hire');
    INSERT INTO jobhist VALUES (7844,'08-SEP-81',NULL,'SALESMAN',1500,0,30,'New Hire');
    INSERT INTO jobhist VALUES (7876,'23-MAY-87',NULL,'CLERK',1100,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7900,'03-DEC-81','14-JAN-83','CLERK',950,NULL,10,'New Hire');
    INSERT INTO jobhist VALUES (7900,'15-JAN-83',NULL,'CLERK',950,NULL,30,'Changed to Dept 30');
    INSERT INTO jobhist VALUES (7902,'03-DEC-81',NULL,'ANALYST',3000,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7934,'23-JAN-82',NULL,'CLERK',1300,NULL,10,'New Hire');

Step 6: Query data

You can execute SELECT statements to query data from tables. The following example shows how to query data of all columns in the emp table.

SELECT * FROM emp;

 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+--------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
(14 rows)

Execute the following SQL statement to query data of specified columns:

SELECT ename, sal, sal * 24 AS yearly_salary, deptno FROM emp;

 ename  |   sal   | yearly_salary | deptno
--------+---------+---------------+--------
 SMITH  |  800.00 |      19200.00 |     20
 ALLEN  | 1600.00 |      38400.00 |     30
 WARD   | 1250.00 |      30000.00 |     30
 JONES  | 2975.00 |      71400.00 |     20
 MARTIN | 1250.00 |      30000.00 |     30
 BLAKE  | 2850.00 |      68400.00 |     30
 CLARK  | 2450.00 |      58800.00 |     10
 SCOTT  | 3000.00 |      72000.00 |     20
 KING   | 5000.00 |     120000.00 |     10
 TURNER | 1500.00 |      36000.00 |     30
 ADAMS  | 1100.00 |      26400.00 |     20
 JAMES  |  950.00 |      22800.00 |     30
 FORD   | 3000.00 |      72000.00 |     20
 MILLER | 1300.00 |      31200.00 |     10
(14 rows)

Execute the following SQL statement to query employees whose salary exceed 1,000 in department 20:

SELECT ename, sal, deptno FROM emp WHERE deptno = 20 AND sal > 1000;

 ename |   sal   | deptno
-------+---------+--------
 JONES | 2975.00 |     20
 SCOTT | 3000.00 |     20
 ADAMS | 1100.00 |     20
 FORD  | 3000.00 |     20
(4 rows)

Execute the following SQL statement to query data in a specific order by using ORDER BY. For example, query data in order of names:

SELECT ename, sal, deptno FROM emp ORDER BY ename;

 ename  |   sal   | deptno
--------+---------+--------
 ADAMS  | 1100.00 |     20
 ALLEN  | 1600.00 |     30
 BLAKE  | 2850.00 |     30
 CLARK  | 2450.00 |     10
 FORD   | 3000.00 |     20
 JAMES  |  950.00 |     30
 JONES  | 2975.00 |     20
 KING   | 5000.00 |     10
 MARTIN | 1250.00 |     30
 MILLER | 1300.00 |     10
 SCOTT  | 3000.00 |     20
 SMITH  |  800.00 |     20
 TURNER | 1500.00 |     30
 WARD   | 1250.00 |     30
(14 rows)

Execute the following SQL statement to query distinct values in a column. For example, query distinct job titles in the job column.

SELECT DISTINCT job FROM emp;

    job
-----------
 ANALYST
 CLERK
 MANAGER
 PRESIDENT
 SALESMAN
(5 rows)