Export Hologres Data to OSS via COPY TO and hg_dump_to_oss - Hologres

Use the COPY TO command together with hg_dump_to_oss to export Hologres query results to an Object Storage Service (OSS) bucket. Both internal tables and foreign tables are supported, and cross-region export is available.

Prerequisites

Before you begin, ensure that you have:

A Hologres instance with Superuser privileges, or the pg_execute_server_program permission granted to your account
An OSS bucket in the target region
The AccessKey ID and AccessKey secret for your Alibaba Cloud account
The classic network endpoint of the target OSS bucket

If you are not a Superuser, ask a Superuser to grant the permission:

-- If the instance uses the simple permission model
CALL spm_grant('pg_execute_server_program', '<account-id-or-email-or-ram-user>');

-- If the instance uses the standard PostgreSQL authorization model
GRANT pg_execute_server_program TO <account-id-or-email-or-ram-user>;

Limitations

The data volume for a single export cannot exceed 5 GB.

How it works

hg_dump_to_oss runs as a server-side program that writes query results directly to OSS. The COPY TO command calls hg_dump_to_oss via the PROGRAM option, passing connection parameters and output options as arguments.

All examples in this topic use the following pattern:

COPY (<query>) TO PROGRAM 'hg_dump_to_oss <connection-args>' (<format-options>);

Export commands

Quick start

Export all rows from a table to a CSV file in OSS:

COPY (SELECT * FROM holo_test LIMIT 2)
TO PROGRAM 'hg_dump_to_oss
  --AccessKeyId     <your-access-key-id>
  --AccessKeySecret <your-access-key-secret>
  --Endpoint        oss-cn-hangzhou-internal.aliyuncs.com
  --BucketName      hologres-demo
  --DirName         holotest/
  --FileName        file_name
  --BatchSize       3000'
DELIMITER ',';

Replace the placeholders with your actual values:

Placeholder	Description	Where to find it
`<your-access-key-id>`	AccessKey ID	AccessKey Management page
`<your-access-key-secret>`	AccessKey secret	AccessKey Management page
`<endpoint>`	Classic network endpoint of the OSS bucket	Bucket details in the Bucket List page. Example: `oss-cn-beijing-internal.aliyuncs.com`

Do not add / or \ before the DirName value.

hg_dump_to_oss parameters

Parameter	Required	Description	Default	Example
`AccessKeyId`	Yes	AccessKey ID of your Alibaba Cloud account	—	—
`AccessKeySecret`	Yes	AccessKey secret of your account	—	—
`Endpoint`	Yes	Classic network endpoint of the OSS bucket	—	`oss-cn-beijing-internal.aliyuncs.com`
`BucketName`	Yes	Name of the OSS bucket	—	`hologres-demo`
`DirName`	Yes	Target folder path in OSS	—	`holotest/`
`FileName`	No	Name of the output file in OSS	—	`file_name`
`BatchSize`	No	Number of rows per write batch	`1000`	`5000`

The COPY TO command also accepts the following format options after the PROGRAM clause:

Option	Supported formats	Description	Default
`FORMAT`	`text`, `csv`, `binary`	Output format	`text`
`DELIMITER`	`text`, `csv`	Column separator. Not supported for `binary`.	Tab (`\t`) for text; comma (`,`) for CSV
`NULL`	`text`, `csv`	String to represent NULL values. Not supported for `binary`.	`\N` for text; empty unquoted string for CSV
`HEADER`	`csv`	Include a header row with column names	—
`QUOTE`	`csv`	Character to quote fields	Same as `QUOTE` value
`ENCODING`	All	File encoding	Current client encoding

More examples

Export from a foreign table with a header row

COPY (SELECT * FROM foreign_holo_test LIMIT 20)
TO PROGRAM 'hg_dump_to_oss
  --AccessKeyId     <your-access-key-id>
  --AccessKeySecret <your-access-key-secret>
  --Endpoint        oss-cn-hangzhou-internal.aliyuncs.com
  --BucketName      hologres-demo
  --DirName         holotest/
  --FileName        file_name
  --BatchSize       3000'
(DELIMITER ',', HEADER true);

Export to a bucket in a different region

Hologres supports cross-region export. For example, you can export data from an instance in China (Hangzhou) to an OSS bucket in China (Beijing) by specifying the Beijing endpoint:

COPY (SELECT * FROM holo_test_1 LIMIT 20)
TO PROGRAM 'hg_dump_to_oss
  --AccessKeyId     <your-access-key-id>
  --AccessKeySecret <your-access-key-secret>
  --Endpoint        oss-cn-beijing-internal.aliyuncs.com
  --BucketName      hologres-demo
  --DirName         holotest/
  --FileName        file_name
  --BatchSize       3000'
(DELIMITER ',', HEADER true, FORMAT CSV);

After the export completes, access the output files in the specified OSS folder using the OSS console or OSS tools.

Troubleshooting

Invalid query syntax

Error:

ERROR: syntax error at or near ")" LINE 1: COPY (select 1,2,3 from ) TO PROGRAM 'hg_dump_to_oss ...

The query inside COPY (...) is invalid. Check the SQL statement for syntax errors.

Connection failed (exit code 255)

Error:

DETAIL: child process exited with exit code 255

The Hologres server cannot reach the OSS endpoint. Use the classic network endpoint for your bucket—public network endpoints are not supported. Find the correct endpoint on the Bucket List page under bucket details, or refer to Regions and endpoints.

Program not found

Error:

DETAIL: command not found

The PROGRAM value must be exactly hg_dump_to_oss. Check for typos in the command name.

Invalid credentials or configuration

Exit code	Cause	Resolution
`101`	Invalid `AccessKeyId`	Verify your AccessKey ID on the AccessKey Management page.
`102`	Invalid `AccessKeySecret`	Verify your AccessKey secret on the AccessKey Management page.
`103`	Invalid `Endpoint`	Confirm the classic network endpoint for your region on the Bucket List page or in Regions and endpoints.
`104`	Invalid `BucketName`	Confirm the bucket name on the Bucket List page.
`105`	Missing required parameter	Check the parameter table above and make sure all required parameters are provided.