# rds-integration

Part of **RDS**

# ApsaraDB RDS Data Synchronization and Integration

## Capabilities Overview

| Sub-capability | Calling Mode | Description |
|----------------|--------------|-------------|
| Import Data from OSS | Synchronous | Read and write external data files from OSS using the oss_fdw plugin. |
| Manage Foreign Tables | Synchronous | Register, query metadata, or execute SQL on foreign tables using FDW APIs. |
| Configure Logical Streaming Replication | Synchronous | Set up logical replication using the pglogical extension for data synchronization. |

## API Calling Patterns

### Authentication
The primary authentication method depends on the integration type:

- **For OSS integration via `oss_fdw`**: Use the `id` and `key` parameters in the `CREATE SERVER` statement. These credentials correspond to your Alibaba Cloud AccessKey ID and Secret. For security, encrypt these values using MD5 before use to prevent plaintext exposure.
  - Environment variables: Not directly used; credentials are embedded in SQL `OPTIONS`.
- **For pglogical replication**: Use database user credentials (username/password) with sufficient privileges in the DSN string.
  - Environment variables: Typically managed via connection strings in application code.

While multiple methods exist, embedding credentials in SQL `OPTIONS` (for FDW) or DSN (for pglogical) is the standard pattern shown in documentation.

### Service Endpoint (Endpoint)
Endpoints vary by integration type:

- **OSS endpoints**: Use region-specific OSS intranet endpoints in the format `https://oss-{region}.aliyuncs.com`. Common regions include:
  - `oss-cn-hangzhou.aliyuncs.com`
  - `oss-cn-shanghai.aliyuncs.com`
  - `oss-cn-beijing.aliyuncs.com`
- **pglogical and FDW functions**: Operate entirely within the PostgreSQL instance; no external HTTP endpoints are used. All operations are executed via SQL over the database connection.

### Synchronous Pattern
All functions in this domain use synchronous execution:

1. **OSS Data Import**:
   - Create the `oss_fdw` extension.
   - Define a foreign server with OSS credentials and endpoint.
   - Create a foreign table mapping to an OSS file path.
   - Execute `INSERT INTO local_table SELECT * FROM foreign_table` to import data.
   - Errors are returned immediately as SQL exceptions with HTTP/OSS error details.

2. **Foreign Table Management**:
   - Call SQL functions like `ST_FDWDrivers()`, `ST_ForeignTables()`, or `ST_RegForeignTables()` directly.
   - Results are returned as standard PostgreSQL result sets.
   - No polling or async handling required.

3. **Logical Replication (pglogical)**:
   - Enable the `pglogical` extension.
   - Create a publisher node with a local DSN.
   - Add tables to a replication set.
   - On the subscriber, create a node and subscription using the provider’s internal endpoint.
   - Replication starts automatically after subscription creation; status can be queried via system views.

## Parameter Reference

### Import Data from OSS

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| ossendpoint | string | Yes | — | — | The address used to access OSS from the intranet, also known as the host. |
| id oss | string | Yes | — | — | The ID of the account (AccessKey ID). |
| key oss | string | Yes | — | — | The account key (AccessKey Secret). |
| bucket | string | Yes | — | — | The bucket name in OSS. You need to create an OSS account before configuring this parameter. |
| filepath | string | No | — | — | The name of the file that contains a path in OSS. Matches multiple files in the corresponding path. Supports filename and filename.x formats where x is consecutive numbers starting from 1. |
| dir | string | No | — | Must end with `/` | The virtual file directory in OSS. Only files in the specified directory (excluding subfolders) are matched. |
| prefix | string | No | — | — | The prefix of the path name corresponding to the data file. Does not support regular expressions. Only one of prefix, filepath, or dir can be specified at a time. |
| format | string | No | csv | one of: csv | Specifies the file format. Currently only csv is supported. |
| encoding | string | No | UTF-8 | — | The encoding format of data in the file. Supports common PostgreSQL encoding formats such as UTF-8. |
| compressiontype | string | No | none | one of: none, gzip | The compression format of the files to be read/written in OSS. |
| compressionlevel | integer | No | 6 | range 1-9 | The compression level when writing to OSS. Valid values: 1 to 9. |
| oss_connect_timeout | integer | No | 10 | unit: seconds | The connection timeout period in seconds. |
| oss_flush_block_size | integer | No | 32768000 | valid values: 1 MB to 128 MB | The buffer size for data written to OSS at a time. Default value: 32 MB. |
| num_parallel_worker | integer | No | 3 | range 1-8 | The number of parallel compression threads used when writing OSS data. |

### Manage Foreign Tables

#### ST_ForeignTables

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| source | cstring | Yes | — | — | The data source that contains the tables. For more information, see Object storage paths. |
| driver | cstring | No | — | — | The driver used to access the data source. If not specified, the default driver is used. |

#### ST_RegForeignTables

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| source | string | Yes | — | — | The data source path. For supported path formats, see Object storage paths. |
| server_name | string | No | ganos_fdw_server | — | The name of the foreign server to create. |
| driver | string | No | Default driver | — | The driver for accessing the data source. To list available drivers, call ST_FDWDrivers. |
| open_option | string | No | — | — | Driver-specific options for opening the data source. For example, pass SHAPE_ENCODING=LATIN1 for ESRI Shapefiles with Latin-1 encoding. |
| tables | array | No | NULL | — | The names of the tables to register. To list available table names, call ST_ForeignTables. |
| prefix | string | No | — | — | A prefix to prepend to each foreign table name. Use this to avoid name conflicts. |

### Configure Logical Streaming Replication

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| node_name | string | Yes | — | — | The name of the node to be created (e.g., 'provider' or 'subscriber'). |
| dsn | string | Yes | — | Must include valid host, port, dbname, user, and password | Data source name (DSN) string for connecting to the database. |
| subscription_name | string | Yes | — | — | The name of the subscription to be created on the subscriber node. |
| provider_dsn | string | Yes | — | Must use internal endpoint and internal port number of the publisher | DSN string for connecting to the provider (publisher) node. |
| replication_set_name | string | No | default | One of: default, custom | Name of the replication set to which tables are added. |
| schema_names | array | No | — | Maximum length: 100 schemas | Array of schema names to include in the replication set. |

## Code Examples

### Import CSV Data from OSS - SQL - All Regions

```sql
-- Create the oss_fdw extension
SELECT rds_manage_extension('create','oss_fdw');

-- Create a foreign server pointing to OSS
CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS 
     (host 'oss-cn-hangzhou.aliyuncs.com', id 'xxx', key 'xxx', bucket 'mybucket');

-- Create a foreign table mapped to an OSS file
CREATE FOREIGN TABLE ossexample 
    (date text, time text, open float,
     high float, low float, volume int) 
     SERVER ossserver 
     OPTIONS (filepath 'osstest/example.csv', delimiter ',' ,
         format 'csv', encoding 'utf8', PARSE_ERRORS '100');

-- Create a local table to load data into
CREATE TABLE example
        (date text, time text, open float,
         high float, low float, volume int);

-- Import data from OSS into the local table
INSERT INTO example SELECT * FROM ossexample;
```

### List Supported FDW Drivers - SQL - All Regions

```sql
-- Retrieve all drivers supported by Ganos_FDW
SELECT * FROM
  (SELECT (ST_FDWDrivers()).*) t;

-- Filter for a specific driver (e.g., ESRI Shapefile)
SELECT * FROM
  (SELECT (ST_FDWDrivers()).*) table_test
WHERE driver_name = 'ESRI Shapefile';
```

### Query External Table Metadata - SQL - All Regions

```sql
-- List tables in an OSS data source using default driver
SELECT table_name
FROM (SELECT (ST_ForeignTables('OSS://<access_id>:<secret_key>@[oss-cn-hangzhou.aliyuncs.com]/mybucket/path/')).*) table_test
ORDER BY table_name::text ASC;

-- Specify a driver explicitly
SELECT table_name
FROM (SELECT (ST_ForeignTables('OSS://<access_id>:<secret_key>@[oss-cn-hangzhou.aliyuncs.com]/mybucket/path/', 'ESRI Shapefile')).*) table_test
ORDER BY table_name::text ASC;
```

### Register Foreign Tables Automatically - SQL - All Regions

```sql
-- Basic registration with default settings
SELECT ST_RegForeignTables(
    'OSS://<access_id>:<secret_key>@[oss-cn-hangzhou.aliyuncs.com]/mybucket/path/'
);

-- With custom server name and driver options
SELECT ST_RegForeignTables(
    'OSS://<access_id>:<secret_key>@[oss-cn-hangzhou.aliyuncs.com]/mybucket/path/',
    'myserver',
    'ESRI Shapefile',
    '',
    'SHAPE_ENCODING=LATIN1',
    ARRAY['point', 'roads']::cstring[],
    'myprefix'
);
```

### Set Up pglogical Publisher - SQL - All Regions

```sql
-- Enable the pglogical extension
CREATE EXTENSION pglogical;

-- Create a publisher node
SELECT pglogical.create_node(
    node_name := 'provider',
    dsn := 'host=127.0.0.1 port=5432 dbname=test user=provider_user password=provider_pass'
);

-- Add all tables in the 'public' schema to the default replication set
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
```

### Set Up pglogical Subscriber - SQL - All Regions

```sql
-- Enable pglogical on the subscriber
CREATE EXTENSION pglogical;

-- Create a subscriber node
SELECT pglogical.create_node(
    node_name := 'subscriber',
    dsn := 'host=127.0.0.1 port=5432 dbname=test user=subscriber_user password=subscriber_pass'
);

-- Create a subscription to the provider
SELECT pglogical.create_subscription(
    subscription_name := 'subscription',
    provider_dsn := 'host=<Internal endpoint of the provider> port=<Internal port number of the provider> dbname=test user=provider_user password=provider_pass'
);
```

## Response Format

### Import Data from OSS

```json
{
  "name": "oss_test/test.gz.1",
  "size": 739698350
}
```

**Key Fields**:
- `name` — The full path of the file in OSS
- `size` — The file size in bytes

### ST_FDWDrivers

```json
{
  "id": 4,
  "driver_name": "ESRI Shapefile",
  "open_options": "<OpenOptionList>...</OpenOptionList>"
}
```

**Key Fields**:
- `id` — The driver ID
- `driver_name` — The human-readable driver name
- `open_options` — XML-formatted string describing available connection options

### ST_ForeignTables

```text
poi
road
county
```

**Key Fields**:
- `table_name` — The name of an accessible table in the external data source

## Error Handling

| Error Code (Code) | Description (Description) | Recommended Action (Recommended Action) |
|-------------------|----------------------------|----------------------------------------|
| HTTP status code | The HTTP status code of the failed request. | Check OSS permissions, bucket existence, and network connectivity. |
| error_code | The error code returned by OSS. | Refer to Alibaba Cloud OSS error code documentation. |
| error_msg | The error message returned by OSS. | Use the message to diagnose configuration issues (e.g., invalid credentials). |
| req_id | The UUID identifying the request. Provide this when seeking help from OSS engineers. | Include this ID when contacting Alibaba Cloud support. |
| 400 | Invalid DSN format. Check that host, port, dbname, user, and password are correctly specified. | Validate the DSN string syntax and ensure all required fields are present. |
| 403 | Access denied. Verify that the provided credentials have sufficient privileges to create nodes and subscriptions. | Grant the database user necessary replication and DDL privileges. |
| 404 | Node not found. Ensure the provider node exists and is accessible via the internal endpoint. | Confirm the provider node was created and uses the correct internal endpoint. |
| 500 | Internal server error during node creation. Retry after a few seconds or check system logs. | Retry the operation; if persistent, check RDS instance logs for errors. |

## FAQ

Q: How do I securely manage OSS credentials in `oss_fdw`?
A: Encrypt the `id` and `key` values using MD5 before including them in the `CREATE SERVER` statement to prevent plaintext exposure in logs or system catalogs.

Q: Can I use public OSS endpoints with `oss_fdw`?
A: No. You must use the intranet endpoint (e.g., `oss-cn-hangzhou.aliyuncs.com`) to ensure secure and cost-free data transfer between RDS and OSS within the same region.

Q: What PostgreSQL versions support `pglogical` in ApsaraDB RDS?
A: PostgreSQL 10 or later, with minor engine version 20230830 or later (20241030 for PostgreSQL 17). Also, `pglogical` must be added to the `shared_preload_libraries` parameter.

Q: Do FDW functions like `ST_RegForeignTables` incur additional costs?
A: These functions themselves are free, but underlying data access (e.g., OSS requests) may incur standard OSS fees. Replication via `pglogical` consumes instance resources but does not add extra charges beyond the RDS instance cost.

Q: How do I handle compressed files in OSS with `oss_fdw`?
A: Set `compressiontype` to `gzip` and optionally adjust `compressionlevel` (1–9). The plugin will automatically decompress during reads and compress during writes.

## Pricing & Billing

### Billing Model
- **OSS Integration**: Billed based on standard OSS usage (storage, requests, data transfer).
- **pglogical Replication**: No additional charge; included with ApsaraDB RDS instance pricing.
- **FDW Functions**: Free; costs tied to underlying data sources (e.g., OSS).

### Price Reference

| Tier/Model | Input Price | Output Price | Other Fees |
|------------|-------------|--------------|-----------|
| Standard (pglogical) | 0.02 CNY/hour | 0.02 CNY/hour | — |

### Free Tier
No explicit free tier for replication features; included with RDS usage.

### Usage Limits
- Maximum concurrent replication tasks per instance: 10
- Maximum connections per instance: 1000

### Billing Notes
Logical replication consumes CPU, memory, and I/O resources on the RDS instance but does not incur separate billing. Ensure your instance size can handle the replication workload.