Hologres:PSQL client

Go to the Postgres official website. Download the client installation package for PostgreSQL 11 or later that matches your operating system. Then, follow the prompts to install it.

• Windows system.

  1. On the System Properties > Advanced system settings page, click Environment Variables.

  2. Add the path to the PostgreSQL bin directory to the Path variable.

  3. Click OK.

• For macOS, you typically do not need to set environment variables. For more information, see Set Environment Variables.

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

• The statement for Linux is as follows.

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

  After you execute the statement, enter the AccessKey secret.

  

• The statement for macOS is as follows.

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

  

• The statement for Windows is as follows.

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

Usage examples

• Log on with an Alibaba Cloud account. The statement is as follows:

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

  

• Log on with a custom account

  • If the username of the custom account is abc, as shown in the following figure.

  • The logon statement is as follows:

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

    

After a Hologres instance is enabled, the system automatically creates the postgres database. This database has limited resources and is intended only for operations management. For business development, create a new database.

• Command syntax.

  CREATE Database <DatabaseName>;

  DatabaseName is the name of the database to be created.

• Example.

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

Use standard PostgreSQL statements in the PSQL client for data development.

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;

Develop jobs based on your business scenarios. The following are examples.

• Accelerate reading data from MaxCompute. For more information, see Accelerate MaxCompute data queries using foreign tables.

• Write data to Hologres in real time using Flink. For more information, see Hologres sink table.