Postgres Pro AXE : Documentation: 1: Appendix D. S3 Storage Connection Scenario : Postgres Professional

Appendix D. S3 Storage Connection Scenario
Prev	Up	Part VI. Appendixes	Home	Next

Appendix D. S3 Storage Connection Scenario

For more information about storages, refer to Chapter 15.

Before performing this scenario:

To connect to an S3 storage:

Set duckdb.unsafe_allow_execution_inside_functions and duckdb.convert_unsupported_numeric_to_double to true:

ALTER SYSTEM SET duckdb.unsafe_allow_execution_inside_functions TO true;
SET duckdb.convert_unsupported_numeric_to_double = true;

Reload the Postgres Pro AXE server:
```
SELECT pg_reload_conf();
```

Create a simple secret using duckdb.create_simple_secret.

Example D.1.

SELECT duckdb.create_simple_secret(
      type          := 'S3',
      key_id        := 'access_key_ID_example',
      secret        := 'simple_secret_example',
      region        := '',
      url_style     := 'path',
      endpoint      := 'hostname:port',
      use_ssl       := 'false');

Create an S3 storage.

Example D.2.

  SELECT metastore.add_storage('s3_storage_example', 's3://demo/s3_storage_example/','s3://demo/s3_storage_example/tmp/');

Ensure that the S3 storage is configured correctly:

Create a heap table using CREATE TABLE.

Example D.3.

CREATE TABLE heap_table_example (id int4 NULL, "name" text NULL, price numeric(10,2) NULL, created_at timestamp NULL);

Insert rows into the heap table using INSERT.

Example D.4.

INSERT INTO heap_table_example VALUES (1, 'Item 1', 150.99, '2025-08-09 00:01:01.75');
INSERT INTO heap_table_example VALUES (2, 'Item 2', 300.99, '2025-08-10 00:01:01.75');

Create an analytical table from the heap table.

Example D.5.

SELECT metastore.add_table('analytical_table_example', 's3_storage_example', 'public.heap_table_example', '');

Copy the OLAP data to the analytical table from the heap table.

Example D.6.

SELECT metastore.copy_table('analytical_table_example', $$select * from heap_table_example$$);

Create a Postgres Pro view from the analytical table.
Example D.7.
```
SELECT metastore.create_view('analytical_table_example');
```
Ensure that the values that you inserted into the heap table can be retrieved from the analytical table.
Example D.8.
```
SELECT * from analytical_table_example;
```

Prev	Up	Next
C.3. ClickBench Benchmark Test	Home	Appendix E. Examples of Snapshot Usage

epub pdf