Hologres:PSQL client

Go to the official PostgreSQL website to download the installer for PostgreSQL version 11 or later that matches your operating system. Then, follow the on-screen instructions to complete the installation.

• For Windows:

  1. In the System Properties > Advanced window, click Environment Variables.

  2. Add the path to the PostgreSQL bin directory to the Path variable. In the System variables section, find and select the Path variable, and then click Edit....

  3. Click OK.

• For macOS, you typically do not need to set environment variables. If required, see Set Up Environment Variables.

Open the PostgreSQL client command-line interface and enter the connection information. The syntax is the same as connecting to a standard PostgreSQL database.

• On Linux, run the following command.

  psql -h <Endpoint> -p <Port> -U <AccessKey ID> -d <Database>

  When prompted, enter your AccessKey Secret.

  [postgres@iZj6cclqj7ttfq2f8stlgpZ ~]$
  [postgres@iZj6cclqj7ttfq2f8stlgpZ ~]$
  [postgres@iZj6cclqj7ttfq2f8stlgpZ ~]$ psql -h xxx-xxx-xxx-xxx.hologres.aliyuncs.com -p 80 -U LTAIxxx-xxx -d testdb
  Password for user xxx:
  psql (12.3, server 11.3)
  Type "help" for help.
  testdb=>
  testdb=>
  testdb=>
  testdb=>
  testdb=>

• On macOS, run the following command.

  PGUSER=<AccessKey ID> PGPASSWORD=<AccessKey Secret> psql -p <Port> -h <Endpoint> -d <Database>

  PGUSER=xxx PGPASSWORD=xxx psql -p 80 -h xxx.hologres.aliyuncs.com -d mydb
  psql (11.4, server 11.3)
  Type "help" for help.
  mydb=#

• On Windows, the client interactively prompts you for connection details.

  Server [localhost]: Endpoint
  Database [postgres]: Database
  Port [5432]: Port
  Username [postgres]: <AccessKey ID>
  Password for user <AccessKey ID>: <AccessKey Secret>

  Server [localhost]: Endpoint
  Database [postgres]: Database
  Port [5432]: 80
  Username [postgres]: <AccessKey ID>
  Password for user <AccessKey ID>:
  psql (11.8, server 11.3)
  Type "help" for help.
  postgres=#

Examples

• Log on with an Alibaba Cloud account:

  PGUSER="xxx" PGPASSWORD="xxx" psql -h hgpostcn-cn-xxx-cn-hangzhou.hologres.aliyuncs.com -p 80 -d demo

  PGUSER="LTAIxxx" PGPASSWORD="xT6qxxx" psql -h hgpostcn-cn-xxx-cn-hangzhou.hologres.aliyuncs.com -p 80 -d demo
  psql (14.2, server 11.3)
  Type "help" for help.
  demo=#

• Log on with a custom account:

  • If the custom username is abc, you can create it in HoloWeb. On the top navigation bar, choose security center. On the left-side navigation pane, click user management. After you select the target instance, you can view the user list. This list includes members, cloud accounts, account types, and role types. In the upper-right corner, click Create Custom User to add a BASIC account. The account name must be in the format BASIC$<user_name>, and the role type defaults to SuperUser after creation.

  • Run the following command to log on:

    PGUSER="BASIC\$abc" PGPASSWORD="xxx" psql -h hgpostcn-cn-xxx-cn-hangzhou.hologres.aliyuncs.com -p 80 -d demo

    PGUSER="BASIC\$abc" PGPASSWORD="1xxx" psql -h hgpostcn-cn-xxx-cn-hangzhou.hologres.aliyuncs.com -p 80 -d demo
    psql (14.2, server 11.3)
    Type "help" for help.
    demo=#

After a Hologres instance is created, a database named postgres is automatically created. This database has limited resources and is intended only for operational and management tasks. For development and production workloads, we recommend that you create a new database.

• Syntax:

  CREATE Database <DatabaseName>;

  DatabaseName is the name of the database that you want to create.

• Example:

  -- Create a database named test.
  CREATE Database test; 

Use standard PostgreSQL statements in the PostgreSQL client to develop data.

BEGIN;
CREATE TABLE nation (
  n_nationkey bigint NOT NULL,
  n_name text NOT NULL,
  n_regionkey bigint NOT NULL,
  n_comment text NOT NULL,
  PRIMARY KEY (n_nationkey)
);
CALL SET_TABLE_PROPERTY('nation', 'bitmap_columns', 'n_nationkey,n_name,n_regionkey');
CALL SET_TABLE_PROPERTY('nation', 'dictionary_encoding_columns', 'n_name,n_comment');
CALL SET_TABLE_PROPERTY('nation', 'time_to_live_in_seconds', '31536000');
COMMIT;
INSERT INTO nation VALUES
(11,'zRAQ', 4,'nic deposits boost atop the quickly final requests? quickly regula'),
(22,'RUSSIA', 3  ,'requests against the platelets use never according to the quickly regular pint'),
(2,'BRAZIL',  1 ,'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special '),
(5,'ETHIOPIA',  0 ,'ven packages wake quickly. regu'),
(9,'INDONESIA', 2  ,'slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull'),
(14,'KENYA',  0  ,'pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t'),
(3,'CANADA',  1 ,'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'),
(4,'EGYPT', 4 ,'y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d'),
(7,'GERMANY', 3 ,'l platelets. regular accounts x-ray: unusual, regular acco'),
(20 ,'SAUDI ARABIA',  4 ,'ts. silent requests haggle. closely express packages sleep across the blithely');
SELECT * FROM nation;

You can develop jobs based on your use case. Examples include:

• Accelerate queries on MaxCompute data. For more information, see Accelerate MaxCompute queries by using a foreign table.

• Write data to Hologres in real time using Flink. For details, see Hologres sink.