Hologres:PSQL client

Prerequisites

Before you begin, ensure that you have:

• A Hologres instance with a known endpoint and port (find these on the Instance Details page in the Hologres console)

• An AccessKey ID and AccessKey Secret for your Alibaba Cloud account, or the username and password for a RAM (Resource Access Management) custom account

• psql installed (PostgreSQL 11 or later)

Install psql

Download the PostgreSQL client from the Postgres official website. Select PostgreSQL 11 or later for your operating system, then follow the installer prompts.

After installation, add psql to your PATH if needed:

• macOS: No PATH change is typically needed.

• Windows: Go to System Properties > Advanced system settings and click Environment Variables

Connect to Hologres

Use platform-specific flag syntax to connect:

• Linux:

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

  Enter your AccessKey Secret when prompted for the password.

• macOS:

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

• Windows (interactive prompts):

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

Usage examples

Log in with an Alibaba Cloud account:

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

Log in with a custom account (username abc):

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

To connect using a graphical tool instead, see DataWorks Quick Start or Connect to HoloWeb and execute a query.

(Optional) Create a database

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

CREATE DATABASE <DatabaseName>;

Example:

-- Create a database named test
CREATE DATABASE test;

Skip this step if you have already created a business database.

Develop data

Use standard PostgreSQL statements in the psql client for data development. The following example creates a table, inserts rows, and queries the 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;

What's next

• Accelerate reading data from MaxCompute using foreign tables: Accelerate MaxCompute data queries using foreign tables

• Write data to Hologres in real time using Flink: Hologres sink table