Skip to main content
This guide shows how to build a Change Data Capture (CDC) pipeline from an Aptible PostgreSQL Database to Google BigQuery using Google Datastream. 
Aptible PostgreSQL -> Aptible Database Endpoint -> Google Datastream -> BigQuery
The Datastream connection uses a Database Endpoint with IP filtering and TLS certificate verification. PostgreSQL changes are read from the write-ahead log (WAL) using the pgoutput logical replication plugin.

​
Prerequisites

  • Administrator access to the Aptible Environment
  • The Aptible CLI
  • A Google Cloud project with billing enabled
  • The Datastream API and BigQuery API enabled in Google Cloud
  • A PostgreSQL Database on Aptible

​
Step 1: Configure Aptible Network Access

Create a Database Endpoint so Datastream can connect to your Database.
1

Create a Database Endpoint

In the Aptible Dashboard, navigate to the Database, open the Endpoints tab, and create a TCP Endpoint. Keep the Endpoint host and port available for the Google Datastream setup.
2

Get Google Datastream IP addresses

In Google Cloud, navigate to Datastream > Connection Profiles > Create Profile. Select PostgreSQL, choose the region where the stream will run, select IP allowlisting as the connectivity method, and copy the static IP addresses Google provides.
3

Allowlist Google IP addresses

Return to the Aptible Database Endpoint and add the Google Datastream static IP addresses to the Endpoint IP allowlist.
Enable IP filtering on the Database Endpoint. Without IP filtering, the Database Endpoint is reachable from the public internet.

​
Step 2: Configure PostgreSQL Replication

Use the default aptible database user to configure logical replication. Create a local tunnel to your Database: 
aptible db:tunnel YOUR_DATABASE_HANDLE
Copy the connection URL from the tunnel output and connect with psql or another PostgreSQL client. Then run: 
CREATE PUBLICATION datastream_publication FOR ALL TABLES;

SELECT pg_create_logical_replication_slot('datastream_slot', 'pgoutput');

ALTER SYSTEM SET wal_sender_timeout = 0;

SELECT * FROM pg_replication_slots;
wal_sender_timeout = 0 prevents idle replication connections from timing out when Datastream keeps the WAL stream open through the Aptible load balancer.

​
Step 3: Retrieve the Aptible CA Certificate

Download the Environment CA certificate so Google Datastream can verify the Database Endpoint certificate: 
aptible environment:ca_cert YOUR_ENVIRONMENT_HANDLE > aptible_ca.pem
You will upload aptible_ca.pem when creating the PostgreSQL source connection profile in Datastream.

​
Step 4: Create Datastream Connection Profiles

​
PostgreSQL Source

Create a PostgreSQL source connection profile in Google Datastream with these values:
  • Hostname: the Aptible Database Endpoint hostname, such as elb-xxx.aptible.in
  • Port: the Aptible Database Endpoint port
  • Username: aptible
  • Password: the Database credential password
  • Database name: db, unless your Database uses a different name
  • Encryption: Require SSL/TLS
  • Server verification: enabled
  • CA certificate: upload aptible_ca.pem
  • Connectivity: IP allowlisting
Run the connection test before creating the profile.

​
BigQuery Destination

Create a BigQuery destination connection profile in Datastream and select the Google Cloud project where replicated data should be written.

​
Step 5: Create and Start the Stream

In Google Datastream, create a stream with the PostgreSQL source profile and BigQuery destination profile. Use these source configuration values:
  • Replication slot name: datastream_slot
  • Publication name: datastream_publication
Choose the destination configuration and staleness limit that match your analytics requirements, then start the stream.

​
Step 6: Validate Replication

After Datastream initializes and performs the initial backfill, open BigQuery and confirm the dataset and tables are present. You can run a query like: 
SELECT * FROM `your-project.your_dataset.your_table` LIMIT 100;
If the stream fails to connect, verify the Database Endpoint host and port, IP allowlist, TLS settings, and uploaded Aptible CA certificate.