Postgres Pro AXE : Documentation: 1: 29.2. Configuring a Connection to an S3 Storage : Postgres Professional

29.2. Configuring a Connection to an S3 Storage
Prev	Up	Chapter 29. S3 Storages	Home	Next

29.2. Configuring a Connection to an S3 Storage #

To configure this scenario:

Set the duckdb.unsafe_allow_execution_inside_functions and duckdb.convert_unsupported_numeric_to_double configuration parameters to true.
These parameters allow Postgres Pro AXE to work with S3 storages.
Example 29.7.
```
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 to apply the updated configuration parameters:
```
SELECT pg_reload_conf();
```

Create a simple secret.

The secret stores the credentials for connecting Postgres Pro AXE to the S3 storage.

Example 29.8.

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

Create an S3 storage.

Example 29.9.

  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:
1. Create a heap table and insert the test data into it.
  Example 29.10.
  CREATE TABLE heap_table_example ( id int4 NULL, "name" text NULL, price numeric(10,2) NULL, created_at timestamp NULL );
2. Insert rows into the heap table using the INSERT command.
  Example 29.11.
  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');
3. Create an analytical table from the heap table.
  Example 29.12.
  SELECT metastore.add_table('analytical_table_example', 's3_storage_example', 'public.heap_table_example', '');
4. Copy the data from the heap table to the analytical table.
  Example 29.13.
  SELECT metastore.copy_table('analytical_table_example', 'SELECT * FROM heap_table_example');
  Postgres Pro AXE places Parquet files into a new subdirectory under the storage directory, named after the snapshot ID. Do not delete such directories.
5. Create a Postgres Pro view for the analytical table.
  Example 29.14.
  SELECT metastore.create_view('analytical_table_example');
6. Ensure that the values that you inserted into the heap table can be retrieved from the analytical table.
  Example 29.15.
  SELECT * FROM analytical_table_example;

Prev	Up	Next
29.1. Configuring Secrets for an S3 Storage	Home	29.3. Separating Read and Write Access to an S3 Storage

epub pdf