PostgreSQL to BigQuery Migration: Step-by-Step Guide

PostgreSQL and BigQuery are both widely used across modern data stacks, but moving data between them isn’t always simple. Teams often face challenges around data freshness, performance at scale, and maintaining reliable pipelines.

These problems become more complex when working with large datasets or syncing data from production PostgreSQL systems to the cloud. When on-premises infrastructure is involved, security requirements and operational overhead add another layer of complexity.

In this guide, you’ll learn two practical methods to migrate data from PostgreSQL to BigQuery, including the trade-offs of each approach.

Method	Automation Level	Best For	Scalability	Maintenance	Key Limitations
Managed ELT Pipeline (Hevo)	Fully automated	Ongoing, real-time analytics	High	Minimal	Tool dependency
Manual Export + BigQuery Load	Low	One-time or small migrations	Limited	High	Manual updates, error-prone

Methods To Load Data from PostgreSQL to BigQuery

There are two commonly used methods to load data from PostgreSQL into Google BigQuery. The right approach depends on whether you want a fully automated pipeline or prefer manual control over data exports and loading.

Method 1: Managed ELT Pipeline (Hevo): A no-code, fully managed ELT solution that syncs PostgreSQL data to BigQuery in near real time without manual scripting. Ideal for teams that want automated data pipelines, reliable replication, and minimal operational overhead.

Method 2: Manual Export + BigQuery Load: A manual approach that exports PostgreSQL data as CSV files and loads them into BigQuery via Google Cloud Storage. Best suited for one-time migrations or small datasets where full control is preferred over automation.

Let’s understand the step-by-step process of each method:

Method 1: Managed ELT Pipeline (Hevo)

Hevo allows you to load data from PostgreSQL to BigQuery using a fully managed ELT pipeline. While Hevo supports multiple PostgreSQL variants, the steps below use Generic PostgreSQL as an example to explain the overall workflow.

Prerequisites for Using a Managed ELT Pipeline (Hevo):

• An active Hevo account with permission to create pipelines.
• Access to a PostgreSQL database, hosted on any supported environment (self-hosted, AWS RDS, Aurora, GCP, Azure, or Heroku).
• A PostgreSQL user with read access to the required schemas and tables.
• Logical replication is enabled on the PostgreSQL database for incremental data ingestion.
• Network connectivity between PostgreSQL and Hevo (via IP allowlisting or SSH tunnel).
• A Google BigQuery project and dataset with permission to create and update tables.
• A BigQuery service account with required roles for data loading.

PostgreSQL variants supported by Hevo:

• Generic PostgreSQL (self-hosted)
• Amazon RDS PostgreSQL
• Amazon Aurora PostgreSQL
• Google Cloud PostgreSQL
• Azure PostgreSQL
• Heroku PostgreSQL

Step 1: Configure PostgreSQL Source and Pipeline Settings

Define how Hevo should capture and process the data.

• Select a Pipeline Mode:
  
  • Logical Replication (Recommended) to capture inserts, updates, and deletes in near real time.
  • Table for timestamp-based incremental loads.
  • Custom SQL to ingest data using a user-defined query.
    
• Choose the Pipeline Type:
  
  • Edge (Recommended) for higher performance and better monitoring.
  • Standard for legacy use cases.

Step 2: Configure PostgreSQL as the Source

Here, you connect Hevo to your PostgreSQL database.

• Enter basic connection details:

• Pipeline name: Unique identifier for your PostgreSQL pipeline.
• Database host: Address where the PostgreSQL server is hosted.
• Database port: Port number used to connect to PostgreSQL.
• Database user: User with read access to required tables.
• Database password: Password for authenticating the database user.
• Database name: Name of the PostgreSQL database to ingest.
  

• Ensure the database user has read access.
• Set up network access using IP allowlisting or SSH tunneling.
• Finally, select “Test & Continue.”

After entering the connection details, Hevo provides additional configuration options through toggles:

• Load historical data: Ingest existing records before capturing new changes.
• Merge tables: Combine related tables into a unified dataset.
• Include tables in the pipeline: Select specific tables for ingestion.

Step 3: Select Tables to Replicate

In this step, you choose the PostgreSQL data you want to load into BigQuery.

• Select the schemas and tables to include.
• Enable incremental loading to avoid repeated full loads.
• Hevo automatically prepares the schema for BigQuery.

Step 5: Configure BigQuery as the Destination

This step defines where Hevo loads your PostgreSQL data in BigQuery.

• Navigate to Destinations and click Create Destination.
• Select Google BigQuery as the destination.
• Provide the required configuration details:

• Destination Name: Unique name to identify your BigQuery destination.
• Authorized User Account: GCP account with access to BigQuery and GCS.
• Project ID: The Google Cloud project storing your dataset.

Note: Select an existing dataset, or allow Hevo to create one automatically.

Hevo provides additional settings to enhance data handling and compatibility:

• Populate Loaded Timestamp: Adds a timestamp to track when data loads.
• Sanitize Table/Column Names: Converts names to BigQuery-compatible format.
• Enable Streaming Inserts: Allows near real-time data streaming into tables.

Step 6: Activate and Monitor the Pipeline

Once everything is configured, you can start data ingestion.

• Review the pipeline settings.
• Activate the pipeline to begin the initial data load.
• Monitor sync status, logs, and errors from the Hevo dashboard.
• After the initial load, Hevo continues syncing new data automatically

CTA banner:

Simplify PostgreSQL to BigQuery Migrations with Hevo

Move data from PostgreSQL to BigQuery without managing custom scripts, brittle pipelines, or ongoing maintenance. Build reliable, scalable ELT pipelines for analytics and reporting.

• Integrate PostgreSQL and 150+ data sources (60+ free sources) into BigQuery.
• Simplify data mapping and transformations using drag-and-drop workflows.
• Migrate structured and semi-structured data (CSV, JSON, and more) using automatic schema and data type mapping.
  

Trusted by 2,000+ teams to keep analytics data fresh, accurate, and production-ready.

Method 2: Manual Export + BigQuery Load (CSV/Cloud Storage)

Data is exported from PostgreSQL into CSV files and then loaded into BigQuery, either directly or via Google Cloud Storage (GCS).

Prerequisites:

• Access to the PostgreSQL database with read permissions.
• BigQuery project and dataset are ready for data loading.
• Google Cloud Storage bucket if using staged upload.
• Tools to export CSV files from PostgreSQL (e.g., pg_dump, COPY command, or pgAdmin).
• Optional: Local machine or server with enough storage to hold exported CSVs.

Step 1: Export Data from PostgreSQL

Extract tables from PostgreSQL as CSV or dumps, ready for BigQuery.

Export individual tables using the COPY command:

<em>COPY table_name TO '/path/to/file/table_name.csv' DELIMITER ',' CSV HEADER;</em>

Workflow:

• Exports the specified table to a CSV file.
• Uses commas as delimiters between columns.
• Includes column headers in the first row.
• File is ready to load into BigQuery.

Or create a backup of one or more tables using pg_dump:

<em>pg_dump -h host -U username -t table_name -F c database_name > table_name.dump</em>

Workflow:

• Connects to the PostgreSQL host (-h) and database.
• Uses specified user (-U) with access to the table.
• Dumps the table into a local file (table_name.dump).

Step 2: Upload CSV Files to Google Cloud Storage (Optional)

Stage files in GCS for faster, repeatable ingestion into BigQuery.

Upload using gsutil:

<em>gsutil cp /local/path/table_name.csv gs://your-bucket-name/</em>

Workflow:

• Copies the local CSV file to the specified GCS bucket.
• Maintains original file structure unless renamed.
• Makes the file accessible for BigQuery loading.
• Supports organizing files in folders per table/schema.

Note: Recommended for large datasets or repeated uploads.

Step 3: Load CSV Files into BigQuery

Import the staged CSV files into BigQuery, auto-detecting schema.

Load via bq CLI:

<em>bq load --autodetect --source_format=CSV dataset_name.table_name gs://your-bucket-name/table_name.csv</em>

Workflow:

• Loads CSV from GCS into BigQuery table.
• Automatically detects column names and data types.
• Creates the table if it doesn’t exist.
• Replaces existing table if –replace flag is used.

Step 4: Verify and Transform Data

Ensure data integrity and clean/transform it in BigQuery.

Example SQL for cleaning or transforming data:

CREATE OR REPLACE TABLE dataset_name.table_name_cleaned AS

SELECT 

    column1,

    SAFE_CAST(column2 AS INT64) AS column2

FROM dataset_name.table_name;

Workflow:

• Creates a new table (table_name_cleaned) in BigQuery.
• Selects required columns from the loaded table.
• Converts column2 safely to integer type.
• Useful for data cleaning, type correction, or transformations.

Limitations of the Manual Method

• The manual migration process can be time-consuming, requiring significant effort to export, transform, and load data, especially if the dataset is large or complex.
• Manual processes are susceptible to human errors, such as incorrect data export settings, file handling mistakes, or misconfigurations during import.
• When migration is frequent or involves multiple tables and datasets, manual processes can become repetitive, inefficient, and add to the overall workload.
• Manual migrations can be resource-heavy, taking up valuable computing power and human effort that could be better spent on more critical tasks.

Other Methods to Load Data from PostgreSQL to BigQuery

In addition to managed ELT tools and manual exports, Google Cloud provides native services for loading PostgreSQL data into BigQuery. These options work well for teams already operating within Google Cloud and looking for scheduled or  customizable pipelines.

Method 3: BigQuery Data Transfer Service (Scheduled Batch Loads)

BigQuery Data Transfer Service is designed for recurring, batch-based data ingestion with minimal infrastructure management. Here’s how to set up:

• Enable the BigQuery Data Transfer Service API
• This allows your project to create and manage scheduled data transfers into BigQuery.
• Create a destination dataset in BigQuery
• The dataset acts as the target location where PostgreSQL tables are loaded.
• Configure connectivity to the PostgreSQL source
• Ensure network access using IP allowlisting or private connectivity, depending on where PostgreSQL is hosted.
• Create a new transfer configuration
• From the BigQuery Data Transfers page, select PostgreSQL as the source and name the transfer.
• Provide connection details and select tables
• Specify database credentials and choose the tables to be included in the batch load.
• Define the transfer schedule
• Set how frequently the data should be refreshed, such as daily or hourly runs.

Method 4: Cloud Dataflow (Custom ETL Pipelines)

Cloud Dataflow enables highly customizable pipelines using Apache Beam, making it suitable for complex transformation requirements. The steps are:

• Enable the Dataflow API and prepare a Cloud Storage bucket
• Dataflow uses Cloud Storage for staging files and temporary processing.
• Ensure BigQuery tables are created in advance
• The destination tables must exist and match the expected schema before running the pipeline.
• Select the PostgreSQL-to-BigQuery Dataflow template
• This template provides a prebuilt pipeline that simplifies configuration.
• Configure the PostgreSQL source and query parameters
• Define connection details and specify which tables or queries should be ingested.
• Set optional pipeline settings
• Configure networking, encryption, and temporary storage based on security and performance needs.
• Run the Dataflow job
• Once started, Dataflow manages execution, scaling, and monitoring of the pipeline.

These methods differ mainly in how they handle batch vs. stream processing, schema evolution, and pipeline orchestration. Choosing the right approach depends on whether your workloads prioritize scheduled consistency, transformation flexibility, or operational simplicity.

What Is PostgreSQL?

PostgreSQL is a popular tool primarily used as an OLTP database and for analyzing data at scale. Its novel architecture, reliable at scale, contains a robust feature set, and extensibility gives it an advantage over other databases.

What is Google BigQuery?

Google BigQuery is a serverless, cost-effective, and highly scalable data warehouse with built-in machine learning capabilities. Its operations are carried out using the business intelligence engine. BigQuery integrates speedy SQL queries with Google’s infrastructure’s processing capacity to manage business transactions, data from several databases, and access control restrictions for users who see and query data.

Why Move Data from PostgreSQL to BigQuery?

Migrating PostgreSQL data to BigQuery helps organizations leverage cloud-scale analytics and make faster, smarter business decisions. Here’s why this migration is valuable:

Centralized analytics and reporting

By moving data from PostgreSQL to BigQuery, you can centralize all your business data in one location. This makes reporting, dashboard creation, and analytics seamless, reducing the time teams spend consolidating data manually from multiple sources.

High performance for complex queries

BigQuery’s distributed architecture allows you to run large, complex queries efficiently, something that can slow down traditional PostgreSQL databases. The architecture enables advanced analytics, aggregations, and multi-table joins at scale without performance bottlenecks.

Scalability and Cost Efficiency

BigQuery automatically scales storage and compute resources based on your workload, eliminating the need for manual infrastructure management. Its pay-per-use model ensures you only pay for the storage and queries you actually use, making it cost-effective as your data grows.

Combining PostgreSQL Data with Other Sources

Bringing PostgreSQL data into BigQuery allows you to integrate it with data from other systems like CRM, marketing platforms, or sales databases. It facilitates richer insights, cross-source analysis, and advanced BI that isn’t possible with isolated PostgreSQL instances.

Additional Read –

• Migrate Data from Postgres to MySQL
• PostgreSQL to Oracle Migration
• Connect PostgreSQL to MongoDB
• Connect PostgreSQL to Redshift
• Replicate Postgres to Snowflake

Why Choose Hevo for PostgreSQL to BigQuery?

There are a couple of ways to move data from PostgreSQL to BigQuery. You can do it manually by exporting CSVs and loading them into BigQuery, which works for small or one-time migrations but requires technical effort and repeated updates.

A managed ELT pipeline like Hevo takes care of the entire process automatically. It handles incremental updates, adapts to schema changes, and lets you combine PostgreSQL data with other sources, all without manual intervention.

The right approach depends on your data size, update frequency, and team bandwidth. For teams looking for a simple, reliable, and transparent solution, Hevo provides a streamlined way to sync PostgreSQL with BigQuery.

Book a 1:1 call today with Hevo experts to see how quickly you can set up your PostgreSQL to BigQuery pipeline.

FAQ on PostgreSQL to BigQuery