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 ↓	`false`
`client_certs`	`boolean`	Use client certificates ↓	`false`
`database`	`string`	Database	`true`
`hostname`	`string`	Hostname	`true`
`password`	`string`	Password	`true`
`port`	`integer`	Port	`true`
`ssh`	`boolean`	Connect over SSH tunnel ↓	`false`
`ssl`	`boolean`	Use SSL	`false`
`username`	`string`	Username	`true`

`client_certs`

When `client_certs` is `true`

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

`change_detection`

When `change_detection` is `true`

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

`ssh`

When `ssh` is `true`

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

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": false,
    "username": "postgres"
  }
}

Model Sync

Source

Configuration

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

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	`false`
`preserve_table_on_resync`	`boolean`	Preserve destination table when resyncing	`false`
`updated_column`	`string`	’Updated at’ timestamp column	`false`
`write_null_values`	`boolean`	Copy null values When enabled updates will set fields to NULL when the source value is null	`false`
`write_record_timestamps`	`boolean`	Write row timestamp metadata	`false`

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	`false`
`automatically_add_new_objects`	`boolean`	Automatically add new tables	`false`
`mirror_publication_selections`	`boolean`	Mirror table and column selection with database publication	`false`
`mirror_tables_without_unique_id`	`boolean`	Mirror table without a unique identifier If set to true	`false`
`replication_slot`	`string`	Replication slot Leave blank to allow Polytomic to manage a replication slot for this sync.	`false`

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`		`false`
`mirror_schemas`	`boolean`	Mirror schemas	`false`
`schema`	`string`	Output schema	`false`

Example

{
  ...
  "destination_configuration": {
    "advanced": {
        "empty_strings_null": false,
        "hard_deletes": false,
        "initial_execution": "",
        "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`

Connection

Configuration

client_certs

When client_certs is true

change_detection

When change_detection is true

ssh

When ssh is true

Example

Model Sync

Source

Configuration

Example

Target

All targets

Configuration

Example

Target creation

Bulk Sync

Source

Configuration

Example

Destination

Configuration

Example

Type handling

Destination types

Source types

`client_certs`

When `client_certs` is `true`

`change_detection`

When `change_detection` is `true`

`ssh`

When `ssh` is `true`