Amazon RDS PostgreSQL

Supports:

✅ Models
✅ Model sync destination
✅ Bulk sync source
✅ Bulk sync destination

Connection

Configuration

Name	Type	Description	Required
`ca_cert`	`string`	CA certificate Optional PEM root certificate trusted in addition to the system and Amazon RDS roots. Used by verify-ca and verify-full.	`optional`
`change_detection`	`boolean`	Use logical replication for bulk syncs ↓	`optional`
`database`	`string`	Database	`required`
`hostname`	`string`	Hostname used to connect to the Amazon RDS database	`required`
`iam_role_arn`	`string`	IAM role ARN Role that grants rds-db:connect access to this database user	`required`
`port`	`integer`	Port	`required`
`rds_endpoint_hostname`	`string`	RDS endpoint hostname RDS hostname used to sign IAM tokens and for TLS server name. Defaults to Hostname when omitted.	`optional`
`rds_endpoint_port`	`integer`	RDS endpoint port RDS port used to sign IAM tokens. Defaults to Port when omitted.	`optional`
`region`	`string`	AWS region	`required`
`ssh`	`boolean`	Connect over SSH tunnel ↓	`optional`
`ssl_mode`	`string`	SSL mode Accepted values: `require`, `verify-ca`, `verify-full`	`optional`
`tags`	`object`	Tags Additional tags to apply during role assumption	`optional`
`username`	`string`	Username	`required`

Read-only property	Type	Description
`external_id`	`string`	External ID for the IAM role

`change_detection`

When change_detection is true:

Name	Type	Description	Required
`publication`	`string`	Publication	`required`

{
  "name": "Amazon RDS PostgreSQL connection",
  "type": "amazon_rds_postgresql",
  "configuration": {
    "ca_cert": "",
    "change_detection": true,
    "database": "sampledb",
    "hostname": "database.123456789012.us-east-1.rds.amazonaws.com",
    "iam_role_arn": "arn:aws:iam::123456789012:role/polytomic-rds",
    "port": 5432,
    "publication": "polytomic",
    "rds_endpoint_hostname": "database.123456789012.us-east-1.rds.amazonaws.com",
    "rds_endpoint_port": 5432,
    "region": "us-east-1",
    "ssh": false,
    "ssl_mode": "verify-full",
    "tags": {},
    "username": "polytomic"
  }
}

`ssh`

When ssh is true:

Name	Type	Description	Required
`ssh_host`	`string`	SSH host	`required`
`ssh_port`	`integer`	SSH port	`required`
`ssh_private_key`	`string`	Private key	`required`
`ssh_user`	`string`	SSH user	`required`

{
  "name": "Amazon RDS PostgreSQL connection",
  "type": "amazon_rds_postgresql",
  "configuration": {
    "ca_cert": "",
    "change_detection": false,
    "database": "sampledb",
    "hostname": "database.123456789012.us-east-1.rds.amazonaws.com",
    "iam_role_arn": "arn:aws:iam::123456789012:role/polytomic-rds",
    "port": 5432,
    "rds_endpoint_hostname": "database.123456789012.us-east-1.rds.amazonaws.com",
    "rds_endpoint_port": 5432,
    "region": "us-east-1",
    "ssh": true,
    "ssh_host": "bastion.example.com",
    "ssh_port": 22,
    "ssh_private_key": "",
    "ssh_user": "root",
    "ssl_mode": "verify-full",
    "tags": {},
    "username": "polytomic"
  }
}

Example

{
  "name": "Amazon RDS PostgreSQL connection",
  "type": "amazon_rds_postgresql",
  "configuration": {
    "ca_cert": "",
    "change_detection": false,
    "database": "sampledb",
    "hostname": "database.123456789012.us-east-1.rds.amazonaws.com",
    "iam_role_arn": "arn:aws:iam::123456789012:role/polytomic-rds",
    "port": 5432,
    "rds_endpoint_hostname": "database.123456789012.us-east-1.rds.amazonaws.com",
    "rds_endpoint_port": 5432,
    "region": "us-east-1",
    "ssh": false,
    "ssl_mode": "verify-full",
    "tags": {},
    "username": "polytomic"
  }
}

Model Sync

Source

Configuration

Name	Type	Description	Required
`query`	`string`		`optional`
`table`	`string`	Table	`optional`
`view`	`string`	View	`optional`

Example

{
  ...
  "configuration": {
    "query": "SELECT * from users",
    "table": "users",
    "view": "active_users"
  }
}

Target

Amazon RDS PostgreSQL connections may be used as the destination in a model sync.

All targets

Configuration

Name	Type	Description	Required
`created_column`	`string`	’Created at’ timestamp column	`optional`
`preserve_table_on_resync`	`boolean`	Preserve destination table when resyncing	`optional`
`updated_column`	`string`	’Updated at’ timestamp column	`optional`
`write_null_values`	`boolean`	Copy null values When enabled updates will set fields to NULL when the source value is null	`optional`
`write_record_timestamps`	`boolean`	Write row timestamp metadata	`optional`

Example

{
  ...
  "target": {
      "configuration": {
        "created_column": "",
        "preserve_table_on_resync": false,
        "updated_column": "",
        "write_null_values": false,
        "write_record_timestamps": false
    }
  }
}

Target creation

Amazon RDS PostgreSQL connections may be used to create a new target for a model sync. The following parameters are required to create a new target:

NAME	DESCRIPTION	ENUM
name	Table name	false

Bulk Sync

Source

Configuration

Name	Type	Description	Required
`automatically_add_new_fields`	`boolean`	Automatically add new fields on selected tables	`optional`
`automatically_add_new_objects`	`boolean`	Automatically add new tables	`optional`
`mirror_publication_selections`	`boolean`	Mirror table and column selection with database publication	`optional`
`mirror_tables_without_unique_id`	`boolean`	Mirror tables without a unique identifier If set to true, Polytomic will mirror the table even if it doesn’t have a primary key or unique index.	`optional`
`replication_slot`	`string`	Replication slot Leave blank to allow Polytomic to manage a replication slot for this sync.	`optional`

Example

{
  ...
  "source_configuration": {
    "automatically_add_new_fields": false,
    "automatically_add_new_objects": false,
    "mirror_publication_selections": false,
    "mirror_tables_without_unique_id": false,
    "replication_slot": "polytomic"
  }
}

Destination

Configuration

Name	Type	Description	Required
`advanced`	`object`		`optional`
`mirror_schemas`	`boolean`	Mirror schemas	`optional`
`schema`	`string`	Output schema	`optional`

Advanced configuration (`advanced`)

Name	Type	Description	Required
`empty_strings_null`	`boolean`	Write empty strings as null	`optional`
`hard_deletes`	`boolean`	Propagate hard deletes instead of soft deletes	`optional`
`initial_execution`	`string`	On first sync	`optional`
`table_prefix`	`string`	Prefix table names with	`optional`
`truncate_existing`	`boolean`	Truncate destination table when source table does not have a primary key	`optional`

Example

{
  ...
  "destination_configuration": {
    "advanced": {
        "empty_strings_null": true,
        "hard_deletes": false,
        "initial_execution": "rebuild",
        "table_prefix": "",
        "truncate_existing": false
    },
    "mirror_schemas": false,
    "schema": "schema"
  }
}

Type handling

Destination types

POLYTOMIC TYPE	AMAZON RDS POSTGRESQL TYPE
`array<>`	`JSON`
`bigint`	`INT8`
`boolean`	`BOOL`
`date`	`DATE`
`datetime`	`TIMESTAMP`
`decimal(precision, scale)`	`NUMERIC(precision,scale)`
`double`	`DOUBLE PRECISION`
`int`	`INT4`
`json`	`JSON`
`jsonarray`	`JSON`
`number`	`NUMERIC`
`object{}`	`JSON`
`single`	`REAL`
`smallint`	`INT2`
`string`	`TEXT`
`time`	`TIME`

Source types

AMAZON RDS POSTGRESQL TYPE	POLYTOMIC TYPE
`ANYARRAY`	`jsonarray`
`CIDR`	`string`
`CSTRING`	`string`
`DATE`	`date`
`DOUBLE PRECISION`	`double`
`FLOAT`	`single`
`FLOAT4`	`single`
`FLOAT8`	`double`
`INET`	`string`
`INT`	`int`
`INT2`	`smallint`
`INT4`	`int`
`INT8`	`bigint`
`INTERVAL`	`string`
`JSON`	`json`
`JSONB`	`json`
`MACADDR`	`string`
`MONEY`	`number`
`NAME`	`string`
`NUMERIC(precision, scale)`	`decimal(precision, scale)`
`REAL`	`single`
`TEXT`	`string`
`TIME`	`time`
`TIMESTAMP`	`datetime`
`TIMESTAMPTZ`	`datetime_tz`
`TIMETZ`	`time`
`UUID`	`string`
`_BOOL`	`jsonarray`
`_BPCHAR`	`jsonarray`
`_BYTEA`	`jsonarray`
`_CHAR`	`jsonarray`
`_CSTRING`	`jsonarray`
`_DATE`	`jsonarray`
`_FLOAT4`	`jsonarray`
`_FLOAT8`	`jsonarray`
`_INT2`	`jsonarray`
`_INT2VECTOR`	`jsonarray`
`_INT4`	`jsonarray`
`_INT8`	`jsonarray`
`_JSON`	`jsonarray`
`_JSONB`	`jsonarray`
`_LSEG`	`jsonarray`
`_MONEY`	`jsonarray`
`_NAME`	`jsonarray`
`_NUMERIC`	`jsonarray`
`_PATH`	`jsonarray`
`_TEXT`	`jsonarray`
`_TIME`	`jsonarray`
`_TIMESTAMP`	`jsonarray`
`_TIMESTAMPTZ`	`jsonarray`
`_TIMETZ`	`jsonarray`
`_UUID`	`jsonarray`
`_VARCHAR`	`jsonarray`
`_XML`	`jsonarray`

Connection

Configuration

change_detection

ssh

Example

Model Sync

Source

Configuration

Example

Target

All targets

Configuration

Example

Target creation

Bulk Sync

Source

Configuration

Example

Destination

Configuration

Advanced configuration (advanced)

Example

Type handling

Destination types

Source types

`change_detection`

`ssh`

Advanced configuration (`advanced`)