PostgreSQL programmer's reference

Before you can use this adapter, PostgreSQL must be configured as described in Configuring PostgreSQL to use PostgreSQL Reader.PostgreSQL setup

Striim provides wizards for creating applications that read from PostgreSQL and write to various targets. See Creating an application using a wizard for details.

The output data type for PostgreSQLReader is WAEvent. The elements are:

metadata: a map including:

To retrieve the values for these fields, use the META() function. See Parsing the fields of WAEvent for CDC readers.

data: an array of fields, numbered from 0, containing:

To retrieve the values for these fields, use SELECT ... (DATA[]). See Parsing the fields of WAEvent for CDC readers.

before: for UPDATE operations, contains the primary key value from before the update. When an update changes the primary key value, you may retrieve the previous value using the BEFORE() function.

dataPresenceBitMap, beforePresenceBitMap, and typeUUID are reserved and should be ignored.

The following application will write change data for all tables in all schemas in database mydb to SysOut. Replace striim and ****** with the user name and password for the PostgreSQL account you created for use by PostgreSQLReader (see Configuring PostgreSQL to use PostgreSQL Reader) and mydb and %.% with the names of the database and tables to be read. If the replication slot name is not striim_slot, specify it using the ReplicationSlotName property.

CREATE APPLICATION PostgreSQLTest;

CREATE SOURCE PostgreSQLCDCIn USING PostgreSQLReader (
  Username:'striim',
  Password:'******',
  ConnectionURL:'jdbc:postgresql://192.0.2.10:5432/mydb',
  ReplicationSlotName: 'striim_slot',
  Tables:'%.%'
) 
OUTPUT TO PostgreSQLCDCStream;

CREATE TARGET PostgreSQLCDCOut
USING SysOut(name:PostgreSQLCDC)
INPUT FROM PostgreSQLCDCStream;

END APPLICATION PostgreSQLTest;

PostgreSQLReader's output type is WAEvent. See WAEvent contents for change data and PostgreSQL Reader WAEvent fields for more information.

The following are examples of WAEvents emitted by PostgreSQLReader for various operation types. They all use the following table:

CREATE TABLE posauthorizations (
  business_name varchar(30),
  merchant_id character varying(35) PRIMARY KEY,
  primary_account bigint,
  pos bigint,
  code character varying(20),
  exp character(4),
  currency_code character(3),
  auth_amount numeric(10,3),
  terminal_id bigint,
  zip bigint,
  city character varying(20));

If you performed the following INSERT on the table:

INSERT INTO posauthorizations VALUES(
  'COMPANY 1',
  'D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu',
  6705362103919221351,
  0,
  '20130309113025',
  '0916',
  'USD',
  2.20,
  5150279519809946,
  41363,
  'Quicksand');

The WAEvent for that INSERT would be similar to:

data: ["COMPANY 1","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",6705362103919221351,0,"20130309113025",
"0916","USD",2.200,5150279519809946,41363,"Quicksand"]
metadata: {"TableName":"public.posauthorizations","TxnID":556,"OperationName":"INSERT",
"LSN":"0/152CD58","NEXT_LSN":"0/152D1C8","Sequence":1,"Timestamp":"2019-01-11 16:29:54.628403-08"}

If you performed the following UPDATE on the table:

UPDATE posauthorizations SET BUSINESS_NAME = 'COMPANY 5A' where pos=0;

The WAEvent for that UPDATE would be similar to:

data: ["COMPANY 5A","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",6705362103919221351,0,"20130309113025",
"0916","USD",2.200,5150279519809946,41363,"Quicksand"]
metadata: {"TableName":"public.posauthorizations","TxnID":557,"OperationName":"UPDATE",
"LSN":"0/152D2E0","NEXT_LSN":"0/152D6F8","Sequence":1,"Timestamp":"2019-01-11 16:31:54.271525-08"}
before: [null,"D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",null,null,null,null,null,null,null,null,null]

When an UPDATE changes the primary key, you may retrieve the old primary key value from the before array.

If you performed the following DELETE on the table:

DELETE from posauthorizations where pos=0;

The WAEvent for that DELETE would be similar to:

data: [null,"D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",null,null,null,null,null,null,null,null,null]
metadata: {"TableName":"public.posauthorizations","TxnID":558,"OperationName":"DELETE",
"LSN":"0/152D730","NEXT_LSN":"0/152D7C8","Sequence":1,"Timestamp":"2019-01-11 16:33:09.065951-08"}

Only the primary key value is included.

Data types created using CREATE TYPE are not supported.

The table below details how Striim maps the data types of a PostgreSQL source to ClickHouse data types when you create an application using a wizard with Auto Schema Creation, perform an initial load using Database Reader with Create Schema enabled, or run the schema conversion utility, or when Striim schema evolution creates or alters target tables.

See Data Types for a list of supported data type aliases (such as decimal and varchar).

For fixed-length data types, Striim interprets the length parameter as one character = one byte, which can result in errors if the data uses multi-byte characters. To avoid this issue, manually increase the size of the data type in the target, or change the target data type to blob or clob.

PostgreSQL data types created using CREATE TYPE are not supported.

*When using the schema conversion utility, these mappings appear in converted_tables_with_striim_intelligence.sql.