Source PostgreSQL database

Stay organized with collections Save and categorize content based on your preferences.

This section contains information about:

  • The behavior of how Datastream handles data that's being pulled from a source PostgreSQL database
  • The versions of PostgreSQL database that Datastream supports
  • An overview of how to setup a source PostgreSQL database so that data can be streamed from it to a destination
  • Known limitations for using PostgreSQL database as a source

Behavior

The source PostgreSQL database relies upon its logical decoding feature. Logical decoding exposes all changes committed to the database and allows consuming and processing these changes in a user-friendly format using an output plugin. Datastream uses the pgoutput plugin, which is the standard PostgreSQL logical decoding plugin for PostgreSQL 10 and above.

  • All schemas or specific schemas from a given PostgreSQL source, as well as all tables from the schema or specific tables, can be selected.
  • All historical data is replicated.
  • All data manipulation lanuguage (DML) changes, such as inserts, updates, and deletes from the specified databases and tables, are replicated.
  • Only committed changes are replicated.

Versions

Datastream supports PostgreSQL version 10 and above.

Datastream supports the following types of PostgreSQL database:

  • Self-hosted PostgreSQL
  • Cloud SQL for PostgreSQL
  • Amazon RDS for PostgreSQL
  • Amazon Aurora PostgreSQL

Known limitations

Known limitations for using PostgreSQL database as a source include:

  • Streams are limited to 10,000 tables.
  • Events have a size limitation of 3 MB.
  • Tables that have more than 100 million rows can't be backfilled currently.
  • Tables without primary keys must have a REPLICA IDENTITY.
  • Not all changes to the source schema can be detected automatically, in which case data corruption may occur. The following schema changes may cause data corruption or failure to process the events downstream:
    • Dropping columns
    • Adding columns to the middle of a table
    • Changing the data type of a column
    • Reordering columns
    • Dropping tables (relevant if the same table is then recreated with new data added)
  • Datastream doesn't support replication of TRUNCATE events - truncating a table will not result in DELETE events and affected rows will not be deleted from the destination.
  • Datastream doesn't support columns of the Geometric data types or Range data types.
  • Datastream doesn't support replicating views.