How to Load Data from PostgreSQL to BigQuery?

Although PostgreSQL and BigQuery are both powerful tools in their own right, integrating the two can be challenging. Ensuring performance and reliability while minimizing data delay can also be complex for teams working with massive datasets. Additionally, implementing secure connections or designing pipelines between on-premises and cloud systems can often make things more complicated.

In this blog, we will give you a brief overview of PostgreSQL and Google BigQuery. We will also provide you with 2 methods on how you can set up your Postgres to BigQuery integration.

Methods To Load Data from PostgreSQL to BigQuery

Method 1: Using Hevo Data for Salesforce to PostgreSQL Integration

What You Need:

• PostgreSQL server (version 9.5 or higher, up to 17.x) with replication enabled and a user with read privileges
• Google Cloud Platform project with BigQuery enabled and billing set up
• Hevo account with a Team Administrator, Collaborator, or Pipeline Administrator role
• Hevo’s IP addresses are whitelisted on your PostgreSQL server
• Database user created with SELECT, USAGE, and CONNECT privileges on PostgreSQL

Step 1: Prepare PostgreSQL Source for Logical Replication

• Update PostgreSQL configuration (postgresql.conf) to enable WAL (Write Ahead Log) with parameters such as wal_level = logical, max_replication_slots, max_wal_senders, and wal_sender_timeout = 0
• Update client authentication in pg_hba.conf to allow replication connections for the Hevo user
• Restart the PostgreSQL server and grant the replication privilege to the database user
• Ensure Hevo’s IP addresses are whitelisted in your PostgreSQL settings for external connectivity

Step 2: Create Database User and Grant Privileges

• Create a dedicated read-only database user (if not done already) for Hevo to connect to
• Grant required privileges for that user:
  • CONNECT to the database
  • USAGE on the schema
  • SELECT on all tables in the schema
  • Set default privileges for future tables to be accessible by that user

Step 3: Configure PostgreSQL Connection in Hevo

• In the Hevo dashboard, click PIPELINES → + CREATE PIPELINE → Select PostgreSQL as the source type
• Fill in connection details: pipeline name, database host, port (default 5432), database name, user, password
• Select ingestion mode, preferably “Logical Replication”
• Configure SSH tunnel or SSL encryption if required
• Test the connection and proceed to destination setup

Step 4: Configure BigQuery as a Destination in Hevo

• Click DESTINATIONS → + CREATE DESTINATION → Select Google BigQuery
• Enter a unique destination name
• Connect using either a Google service account (upload Key JSON) or a user account (OAuth login)
• Select your BigQuery project ID and dataset or allow Hevo to create them automatically
• Optionally, allow Hevo to create a Google Cloud Storage bucket for staging data or select an existing bucket
• Enable advanced options like timestamp column and table/column name sanitization as needed
• Test the connection and save the destination settings

Step 5: Activate the Pipeline and Monitor

• Review all pipeline and destination settings and activate the pipeline
• Hevo will now continuously replicate data from PostgreSQL to BigQuery with real-time updates
• Use Hevo’s monitoring dashboard for pipeline health, error alerts, and data sync status
• Make adjustments or add transformations via Hevo’s no-code interface as needed

Method 2: Using Custom ETL Scripts for PostgreSQL to BigQuery 

Step 1: Extract Data from PostgreSQL

• Write SQL queries or use PostgreSQL commands (like the COPY command) to export the required data to CSV files or other intermediate formats. For example:

text

COPY your_table_name TO '/path/to/file.csv' CSV HEADER;

• This extracts data from PostgreSQL into a file ready for transfer.

Step 2: Clean and Transform the Data

• Make sure your data matches BigQuery’s format and requirements. This may include:
  • Converting dates to YYYY-MM-DD format
  • Encoding CSV files as UTF-8
  • Mapping PostgreSQL types to compatible BigQuery types
  • Quoting text fields properly to avoid delimiter issues
• Use SQL functions like TO_DATE or TO_TIMESTAMP in PostgreSQL if needed during extraction.

Step 3: Upload Data to Google Cloud Storage (GCS)

• Transfer the cleaned CSV files to a Google Cloud Storage bucket using tools like gsutil:

text

gsutil cp /path/to/file.csv gs://your-bucket-name/

• GCS acts as a staging area before loading into BigQuery.

Step 4: Load Data from GCS into BigQuery

• Use BigQuery’s UI, CLI, or API to create a table and load the data stored in GCS. You can configure schema detection, write preferences (append or overwrite), partitioning, and clustering during this step. For example, running a bq command:

text

bq load --source_format=CSV dataset.table gs://your-bucket-name/file.csv

Step 5: Automate with Scheduled SQL Scripts or Cron Jobs

Automate extraction and loading by writing and scheduling SQL scripts or shell scripts that run regularly (e.g., daily). This ensures data stays fresh and synchronized without manual intervention. 

You can schedule scripts via cron jobs or use BigQuery scheduled queries for periodic refreshes. 

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.

Limitations of the Manual Method:

1. 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.
2. Manual processes are susceptible to human errors, such as incorrect data export settings, file handling mistakes, or misconfigurations during import.
3. When migration is frequent or involves multiple tables and datasets, manual processes can become repetitive, inefficient, and add to the overall workload.
4. Manual migrations can be resource-heavy, taking up valuable computing power and human effort that could be better spent on more critical tasks.

Additional Read –

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

Conclusion

In this guide, we have walked you through two approaches for migrating data from PostgreSQL to BigQuery: the manual method, which requires significant configuration and effort, and the automated method using tools like Hevo Data. While manual migration can be complex, automated data pipeline tools greatly simplify the process. No matter which method you choose, following these steps will help ensure a smooth and successful migration.

With Hevo Data, you can seamlessly automate the entire migration process, eliminating the need for complex setups. Sign up for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

FAQ on PostgreSQL to BigQuery