This topic describes how to create a PolarDB for PostgreSQL(Compatible with Oracle) cluster, configure the basic settings, and connect to the cluster. You can familiarize yourself with the process of purchasing and using a PolarDB for PostgreSQL(Compatible with Oracle) cluster.

Procedure

To purchase, create, and use a PolarDB for PostgreSQL(Compatible with Oracle) cluster, perform the following steps:

  1. Step 1: Create a PolarDB for PostgreSQL(Compatible with Oracle) cluster
  2. Step 2: Configure the cluster whitelist
  3. Step 3: Create a database account
  4. Step 4: Check the default values of and configure cluster parameters
  5. Step 5: Connect to the cluster
  6. Step 6: Create a database
  7. Step 7: Query data

Step 1: Create a PolarDB for PostgreSQL(Compatible with Oracle) cluster

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the page, click Create Cluster.
  3. Select Subscription or Pay-As-You-Go.
    Note
    • Subscription: If you select this billing method when you create the cluster, you must pay for compute nodes (a primary node and a read-only node) in advance. In addition, you are charged for the consumed storage resources on an hourly basis. The charge of storage resources 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: If you select this billing method when you create the cluster, you do not need to pay in advance. You are charged for compute nodes and the consumed storage resources on an hourly basis. These charges are deducted from your account balance on an hourly basis. We recommend that you select the Pay-As-You-Go billing method for the short-term use. You can reduce costs by releasing the cluster based on your business requirements.
  4. Configure the parameters described in the following table.
    ParameterDescription
    Region
    The region in which you want to create a cluster. After the cluster is created, the region cannot be changed.
    Note Make sure that the PolarDB cluster and the Elastic Compute Service (ECS) instance to which you want to connect are deployed in the same region. Otherwise, the PolarDB cluster and the ECS instance can communicate only over the Internet, which degrades cluster performance.
    Creation Method
    The method used to create the PolarDB cluster.
    • Create Primary Cluster: creates a PolarDB cluster.
    • Restore from Recycle: creates a cluster by restoring a backup of a deleted cluster from the recycle bin.
      • Source Version: the version of the deleted cluster that you want to restore.
      • Deleted Clusters: the name of the deleted cluster that you want to restore.
      • Backup History: the backup that you want to restore.
    Note You can select other options to create databases of other engines.
    Primary Zone
    The primary zone in which the cluster is deployed.
    • A zone is an independent geographical location in a region. All zones in a region provide the same level of service performance.
    • You can deploy your PolarDB cluster and ECS instance in the same zone or in different zones.
    • You must specify only the primary zone. The system automatically selects a secondary zone.
    Network Type
    This parameter can be set only to VPC. You do not need to change this value.
    VPC

    VSwitch

    Make sure that the PolarDB cluster and the ECS instance to which you want to connect are deployed in the same VPC. Otherwise, the cluster and the ECS instance cannot communicate over a VPC, which degrades cluster performance.
    • If an existing VPC meets your network requirements, select the VPC. For example, if you have created an ECS instance and the VPC to which the ECS instance is connected meets your network requirements, select this VPC.
    • Otherwise, use the default VPC and the default vSwitch.
      • Default VPC:
        • Only one VPC is specified as the default VPC in the region that you select.
        • 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.
        • The default VPC does not count towards the quota of VPCs that you can create on Alibaba Cloud.
      • Default vSwitch:
        • Only one vSwitch is specified as the default vSwitch in the zone that you select.
        • The default VPC 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 does not count towards the quota of vSwitches that you can create in a VPC.
    • If the default VPC and vSwitch cannot meet your business requirements, you can create your own VPC and vSwitch. For more information, see Create and manage a VPC.
    Compatibility
    • MySQL 8.0: fully compatible with MySQL 8.0. MySQL 8.0 supports parallel queries. In specific scenarios, the database performance increases by 10 times. For more information, see Overview.
    • MySQL 5.7: fully compatible with MySQL 5.7.
    • MySQL 5.6: fully compatible with MySQL 5.6.
    • PostgreSQL 11: fully compatible with PostgreSQL 11.
    • PostgreSQL 14: fully compatible with PostgreSQL 14.
    • Compatible with Oracle: highly compatible with Oracle. For more information, see Oracle compatibility.
    Note
    • PostgreSQL 11, PostgreSQL 14 and Compatible with Oracle are not supported in the following regions: China (Qingdao), US (Virginia), UK (London), and Australia (Sydney).
    • The PostgreSQL 14 version is in the public preview. To apply for a trial use, click here.
    Edition
    By default, this parameter is set to Cluster Edition (2 to 16 Nodes) (Recommended).
    Specification Type
    By default, this parameter is set to Dedicated Specification.
    Node Specification
    Select node specifications based on your requirements. All nodes in the PolarDB cluster are dedicated nodes with stable and reliable performance.

    For more information about compute node specifications, see Specifications and pricing.

    Nodes
    If the source cluster edition is Cluster Edition (2 to 16 Nodes) (Recommended), the system creates a primary node and a read-only node that have the same specifications. In this case, you do not need to specify this parameter.
    Note If the primary node fails, the system uses the read-only node as the primary node and creates another read-only node. For more information about read-only nodes, see Architecture.
    PolarProxy Type
    By default, this parameter is set to Dedicated Enterprise.
    Storage Type
    PolarDB supports the High-performance Edition and Standard Edition storage types.
    • PSL5: the storage types supported in the historical versions of PolarDB. It delivers higher performance, reliability and availability. This storage type is also called PSL5.
    • PSL4: A new storage type for PolarDB. PSL4 uses the Smart-SSD technology developed by Alibaba Cloud to compress and decompress data at the physical SSD disk level. This can minimize the storage price of data while keep a high disk performance.
    Note The storage type of existing clusters cannot be changed to Standard Edition. To use Standard Edition, we recommend that you purchase a new cluster, set the storage type to Standard Edition, and then migrate data to the new cluster.

    For more information about the two storage types, see Comparison between PSL4 and PSL5.

    Storage Billing Method
    PolarDB supports the Pay-as-you-go and Subscription billing methods for the storage of your clusters.
    • Pay-as-you-go: The storage capacity is provided based on a serverless architecture. This way, you do not need to specify the storage capacity when you purchase clusters. The storage capacity of the clusters can be automatically scaled up as the volume of data increases. You are charged for the actual data volume.
    • Subscription: You must purchase a specific amount of storage capacity when you create the cluster.

    For more information about the two storage billing methods, see billing methods for the storage.

    Note When Product Type is set to Subscription, the Storage Billing Method parameter can be set to Pay-as-you-go or Subscription. When Product Type is set to Pay-as-you-go, the Storage Billing Method parameter is unavailable, and the storage is billed on a pay-as-you-go basis.
    Storage Cost
    The storage cost. You do not need to configure this parameter. The system charges you on an hourly basis based on the amount of storage that is consumed by your data. For more information, see Specifications and pricing.
    Note You do not need to specify the storage capacity when you create a cluster. The system automatically scales storage resources based on data volume.
    Enable TDE
    Specifies whether to enable Transparent Data Encryption (TDE). After TDE is enabled, PolarDB encrypts the data files of your cluster. You do not need to modify the code to allow access to your cluster. However, TDE reduces the performance of your cluster by 5% to 10%.
    Note TDE cannot be disabled after it is enabled.
    Cluster Name
    The name of the cluster. The name must meet the following requirements:
    • It cannot start with http:// or https://.
    • It must be 2 to 256 characters in length.

    If this parameter is left empty, the system automatically generates a cluster name. You can change the cluster name after the cluster is created.

    Resource Group
    Select a resource group from the available resource groups. For more information, see Create a resource group.
    Note A resource group is a group of resources that belong to an Alibaba Cloud account. Resource groups allow you to manage these resources in a centralized manner. A resource belongs to only one resource group. For more information, see Use RAM to create and authorize resource groups.
  5. Specify the Quantity parameter and click Buy Now.
    Note You can create a maximum of 50 clusters at a time. This allows you to create multiple clusters in specific scenarios. For example, you can deploy multiple game servers at a time.
  6. On the Confirm Order page, confirm your order information. Read and accept the terms of service, and then click Buy Now.

Step 2: Configure the cluster whitelist

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
  3. Find the cluster and click the cluster ID.
  4. In the left-side navigation pane, choose Settings and Management > Whitelists.
  5. On the Whitelists page, you can click Add IP Whitelist to add an IP whitelist or click Modify to modify an existing IP whitelist.
    Modify an IP whitelist
    • Add an IP whitelist
      1. Click Add IP Whitelist.
      2. In the Add IP Whitelist panel, specify the name of the IP whitelist and enter the IP addresses that are allowed to access the cluster. Add an IP whitelist
        Note The name of the IP whitelist must meet the following requirements:
        • The name can contain lowercase letters, digits, and underscores (_).
        • The name must start with a letter and end with a letter or digit.
        • The name must be 2 to 120 characters in length.
    • Modify an IP whitelist
      1. On the right side of an IP whitelist name, click Modify.
      2. In the Modify Whitelist panel, enter the IP addresses that are allowed to access the cluster. Modify
        Note
        • A default IP whitelist that contains only the IP address 127.0.0.1 is automatically created for each cluster. This IP whitelist blocks all IP addresses.
        • If you set an IP whitelist to a percent sign (%) or 0.0.0.0/0, all IP addresses are allowed to access the cluster. We recommend that you do not use this configuration unless necessary because it compromises database security.
  6. Click OK.
    Note You can create at most 50 IP whitelists and add at most 1,000 IP addresses or CIDR blocks to the 50 IP whitelists.

Step 3: Create a database account

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
  3. Find the cluster and click the cluster ID.
  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.
    ParameterDescription
    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 PasswordEnter 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.

Step 4: Check the default values of and configure cluster parameters

For more information, see Configure parameters.

Step 5: Connect to the cluster

In this step, Data Management (DMS) is used to show how to connect to a PolarDB for PostgreSQL(Compatible with Oracle) cluster. To connect to a PolarDB for PostgreSQL(Compatible with Oracle) cluster by using a client, perform the following steps:

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
  3. Find the cluster and click the cluster ID.
  4. In the upper-right corner of the Overview page, click Log On to Database.
  5. In the dialog box that appears, enter the database account and password that you create in the PolarDB cluster.
    Use DMS to connect to a PolarDB cluster
  6. Click Login.
    Note If you are using DMS to connect to the PolarDB cluster for the first time, you are prompted to set the whitelist. Click Configure Whitelist to complete the authorization.
  7. After you log on to DMS, refresh the page. In the left-side navigation pane, click Logged in instance.
  8. Find and double-click the name of the database that you want to manage. Then, you can manage the database.

For more information about how to connect to a PolarDB for PostgreSQL(Compatible with Oracle) cluster, see Connect to a PolarDB cluster.

Step 6: Create a database

  • The following examples show how to create the dept, emp, and jobhist tables: 
    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 examples show how to insert data into the tables: 
    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 7: Query data

Execute SELECT statements to query data from the 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 exceeds 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 SQL statements to return data in a specific order by using ORDER BY. The following example shows how to return data in alphabetical 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 SQL statements to query distinct values in a column. The following example shows how to query distinct job titles in the job column:

SELECT DISTINCT job FROM emp;

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