PostgreSQL to BigQuery

​

Scenario

​

Prerequisites

1. Enable logical decoding in Postgres. Ensure that the following settings/GUCs are properly configured:
   1. wal_level: logical
   2. max_wal_senders: >1
   3. max_replication_slots: 4
2. Enable replication access for a PostgreSQL user - ALTER USER pg_user REPLICATION;
3. Ensure that both tables have primary keys. Composite primaries are also fine. If not, make sure your tables have REPLICA IDENTITY FULL.
4. If you are using PostgreSQL on the cloud, below links capture how to enable logical replication for each cloud:
   1. AWS RDS and Aurora PostgreSQL
   2. Azure Database for PostgreSQL - Flexible Server
   3. GCP Cloud SQL PostgreSQL

​

Step 1: Add Postgres and BigQuery Peers

-- Connect to PeerDB
psql "port=9900 host=localhost password=peerdb"

-- Add Postgres and BigQuery peers
CREATE PEER postgres_peer FROM postgres (...);
CREATE PEER bigquery_peer FROM bigquery (...);

​

Step 2: Real-Time CDC from PostgreSQL to BigQuery

​

Create MIRROR using SQL

-- Real-time CDC from PostgreSQL to BigQuery
CREATE MIRROR real_time_cdc
FROM postgres_peer TO bigquery_peer
WITH TABLE MAPPING (public.transactions:transactions, public.users:users)
WITH (
  do_initial_copy = true,
  snapshot_sync_mode='avro',
  snapshot_num_rows_per_partition = 500000,
  snapshot_max_parallel_workers = 4,
  snapshot_num_tables_in_parallel = 4,
  snapshot_staging_path = '<your_gcs_bucket>' // Needed for AVRO snapshot mode
);

  cdc_sync_mode = 'avro'
  cdc_staging_path = '<your_gcs_bucket>'

1. Data type mapping between Postgres and BigQuery.
2. If you want additional types to be supported or want to alter the existing data type mapping, please reach out to us. We can aim to support that within a few days. Also, PeerDB is fully open source, so feel free to submit a PR.

PeerDB also supports replicating TOAST columns very efficiently. Unlike most CDC tools, you don’t need to set up REPLICA IDENTITY FULL for replicating TOAST columns. This PR captures the infrastructural optimizations that PeerDB takes to support TOAST columns.

​

Create MIRROR using UI

​

Step 3: Validate the Mirror

-- Validate the mirror
SELECT COUNT(*) FROM bigquery_peer.transactions;
SELECT COUNT(*) FROM postgres_peer.public.transactions;

​

Step 4: Monitor the MIRROR

​

Coming Soon

1. Support for tables without primary keys using UNIQUE index or REPLICA IDENTITY FULL will be added in a few weeks.
2. Handling Schema Changes will be added in a few weeks.

​

Support