PostgreSQL | Polytomic Docs

Supports:

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

Connection

Configuration

Name	Type	Description	Required
`change_detection`	`boolean`	Use logical replication for bulk syncs ↓	`optional`
`client_certs`	`boolean`	Use client certificates ↓	`optional`
`database`	`string`	Database	`required`
`hostname`	`string`	Hostname	`required`
`password`	`string`	Password	`required`
`port`	`integer`	Port	`required`
`ssh`	`boolean`	Connect over SSH tunnel ↓	`optional`
`ssl`	`boolean`	Use SSL	`optional`
`username`	`string`	Username	`required`

`change_detection`

When change_detection is true:

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

{
  "name": "PostgreSQL connection",
  "type": "postgresql",
  "configuration": {
    "change_detection": true,
    "client_certs": false,
    "database": "sampledb",
    "hostname": "database.example.com",
    "password": "password",
    "port": 5432,
    "publication": "polytomic",
    "ssh": false,
    "ssl": true,
    "username": "postgres"
  }
}

`client_certs`

When client_certs is true:

Name	Type	Description	Required
`ca_cert`	`string`	CA certificate	`required`
`client_certificate`	`string`	Client certificate	`required`
`client_key`	`string`	Client key	`required`

{
  "name": "PostgreSQL connection",
  "type": "postgresql",
  "configuration": {
    "ca_cert": "",
    "change_detection": false,
    "client_certificate": "",
    "client_certs": true,
    "client_key": "",
    "database": "sampledb",
    "hostname": "database.example.com",
    "password": "password",
    "port": 5432,
    "ssh": false,
    "ssl": true,
    "username": "postgres"
  }
}

`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": "PostgreSQL connection",
  "type": "postgresql",
  "configuration": {
    "change_detection": false,
    "client_certs": false,
    "database": "sampledb",
    "hostname": "database.example.com",
    "password": "password",
    "port": 5432,
    "ssh": true,
    "ssh_host": "bastion.example.com",
    "ssh_port": 22,
    "ssh_private_key": "",
    "ssh_user": "root",
    "ssl": true,
    "username": "postgres"
  }
}

Example

{
  "name": "PostgreSQL connection",
  "type": "postgresql",
  "configuration": {
    "change_detection": false,
    "client_certs": false,
    "database": "sampledb",
    "hostname": "database.example.com",
    "password": "password",
    "port": 5432,
    "ssh": false,
    "ssl": true,
    "username": "postgres"
  }
}

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

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

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 table without a unique identifier If set to true	`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`

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	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

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`