Stream data from PostgreSQL databases | Datastream | Google Cloud Documentation
Skip to main content Console English Deutsch Español Español – América Latina Français Indonesia Italiano Português Português – Brasil עברית 中文 – 简体 中文 – 繁體 日本語 한국어 Sign in Datastream Start free Home Documentation Data analytics Datastream Guides Stream data from PostgreSQL databases Stay organized with collections Save and categorize content based on your preferences. You can use Datastream to replicate historical data and ongoing changes from a PostgreSQL database into your destination for real-time analytics and database synchronization.
To prepare for PostgreSQL replication in Datastream, review the replication behavior , supported versions and database types , best practices , requirements for read replicas , and known limitations . 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 later. 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 language (DML) changes, such as inserts, updates, and deletes from the specified databases and tables, are replicated.
Only committed changes are replicated. If you define a REPLICA IDENTITY on a table, Datastream treats the specified columns as primary keys. Datastream periodically sends heartbeat messages to the source database when it's connected to a primary instance. As a result, logical decoding message events ( op:"m" ) are inserted directly into the WAL file. These messages are required by Datastream to ensure source availability and to calculate freshness.
When you use a read replica as a source, you must configure heartbeat messages externally. See Replication from read replicas for more information. We recommend taking that into consideration if other replication setups read from the same source database. Versions Datastream supports PostgreSQL version 10 and later. Datastream supports the following types of PostgreSQL database: Self-hosted PostgreSQL Cloud SQL for PostgreSQL AlloyDB for PostgreSQL AlloyDB Omni Amazon RDS for PostgreSQL Amazon Aurora PostgreSQL Free Tier Datastream lets you stream from AlloyDB for PostgreSQL to BigQuery using the Free Tier, providing up to 100 GiB of change data capture data free every month.
For more information, see Datastream pricing . Best practices This section describes recommended best practices for configuring your PostgreSQL source for use with Datastream. Use multiple streams to prevent head-of-line blocking For PostgreSQL sources, Datastream uses a single logical replication slot for an entire stream. A large transaction or multiple updates on one high-volume table can delay data replication for all other tables in the same stream.
To prevent head-of-line blocking, create separate streams for different sets of tables. For example, you can create one stream for high-volume tables, and another stream for low-volume tables. This isolates high-churn tables and prevents them from delaying replication for other tables. Recommendation: identify tables with exceptionally high write ( INSERT / UPDATE / DELETE ) rates and place them in their own dedicated Datastream stream with a separate replication slot.
Avoid long-running transactions Long-running transactions can lead to WAL log buildup. Because WAL is sequential, PostgreSQL can't remove old WAL files needed by the replication slot until the long transaction completes. This increases the WAL disk usage. Furthermore, this can slow down logical decoding. The slowdown is caused by large transactions spilling changes to disk, which then requires slow, I/O-intensive reassembly upon commit, blocking the replication of all subsequent transactions.
Recommendation: on the source database, configure the statement_timeout and idle_in_transaction_session_timeout parameters to avoid long-running transactions. For more information, see the PostgreSQL documentation . Use table filtering when creating publications If you're replicating changes from only a few tables, ensure that you create a PUBLICATION that includes only those tables. When a publication is scoped to specific tables, PostgreSQL efficiently persists changes only for those tables in the replication slot.
This helps reduce the size of the replication slot and improves logical decoding performance. Proactively manage replication slots Datastream uses a logical replication slot on your PostgreSQL primary instance, which ensures that WAL files are retained until Datastream confirms that they've been processed. If a stream fails, is paused or deleted without dropping the replication slot, PostgreSQL continues to retain WAL files indefinitely.
This can fill up your database server disk and lead to a production outage. Recommendation: Set up efficient alerting and monitor WAL disk usage on your source PostgreSQL server. Configure replica identity correctly The REPLICA IDENTITY setting tells PostgreSQL what data to write to the WAL for UPDATE and DELETE events, allowing Datastream to identify which rows were changed. If you use BigQuery as a destination, avoid setting REPLICA IDENTITY to FULL .
Datastream uses the logged columns as a logical key for BigQuery MERGE operations. If REPLICA IDENTITY is set to FULL and a table has more than 16 columns, this exceeds the BigQuery 16-column limit for primary keys in MERGE operations and breaks the stream. Recommendations (in order of preference): Best: use a primary key. The default setting of REPLICA IDENTITY DEFAULT automatically and efficiently uses the existing primary key.
Good: if no primary key exists, create a UNIQUE NOT NULL index and set REPLICA IDENTITY USING INDEX INDEX_NAME . Least recommended: only use the REPLICA IDENTITY FULL setting on tables with no unique identifier. Be aware of the performance impact and the 16-column limit and the restriction on supported data types for primary keys if replicating to BigQuery. Replication from read replicas Datastream supports replication from PostgreSQL read replica instances for PostgreSQL version 16 and later.
To replicate from a read replica, you must perform the following setup steps on the primary instance: Create publications on the primary instance : While Datastream connects to the read replica, the publications that define the data to be replicated must be created on the primary instance. Configure WAL heartbeats : Datastream relies on periodic WAL heartbeat messages for its checkpointing mechanism. When connecting to a primary instance, Datastream handles the generation of these heartbeats.
However, for a read replica, these heartbeats must be generated externally. One way to set up periodic heartbeats is to create a cron task in PostgreSQL using the pg_cron extension: SELECT cron.schedule_in_database( 'datastream-heartbeat', -- Job name '* * * * *', -- Every minute $$SELECT pg_logical_emit_message(true, 'datastream', 'cdc heartbeat')$$, ' DATABASE_NAME ', -- Change this to your database name ' USERNAME ', -- Username to run as true -- Enabled ); Replace the following: DATABASE_NAME : The name of the database for which you want to generate heartbeats.
USERNAME : The name of the user to run the task as. Typically postgres . Known limitations Known limitations for using Datastream with a PostgreSQL database as a source include: Streams are limited to 10,000 tables. PostgreSQL strictly rejects NULL bytes ( 0x00 ) in UTF-8 string columns. Datastream doesn't strip NULL bytes. If a source string contains 0x00 null bytes, the insert batch fails and stops replication. We recommend that you clean or replace CHAR(0) characters on the source database before replicating.
You can't
The rest of this page is on the live site.