PostgreSQL to BigQuery: 2 Best Ways to Sync Data Fast

Businesses today require fast and efficient data movement to ensure operational systems and analytical models stay in sync. A common challenge for data engineers is syncing data from PostgreSQL to BigQuery in a scalable, real-time manner.

Many IT teams struggle with data pipeline failures, schema mismatches, and high compute costs when setting up these pipelines manually. Estuary Flow, a real-time data integration platform, simplifies the process by automating the pipeline setup. However, for those who prefer a manual method, we’ll also cover how to migrate data using Google Cloud services.

In this guide, you'll learn two effective ways to sync PostgreSQL to BigQuery:

1. The Automated, No-Code Method using Estuary Flow (for real-time syncing)
2. The Manual Migration & Loading Method (for one-time or periodic transfers)

Does PostgreSQL Work As a Data Warehouse?

Image Source

Yes, PostgreSQL can be used as a data warehouse, but it comes with significant challenges. One of the main difficulties is that data engineers must create the warehouse architecture from scratch, involving multiple interlinked databases for storage and modeling. This can lead to a complex and difficult-to-manage system, especially with large datasets.

Limitations of PostgreSQL as a Data Warehouse:

• Scalability Issues – PostgreSQL is not optimized for large-scale data aggregation and may struggle with performance compared to dedicated data warehousing solutions.
• Lack of Built-in Advanced Analytics – While it supports SQL-based aggregation, PostgreSQL lacks built-in tools for advanced analytics and reporting.
• Manual Infrastructure Management – Setting up PostgreSQL as a data warehouse requires significant engineering effort compared to cloud-based solutions like BigQuery.

Why BigQuery is a Better Alternative

BigQuery is a fully managed, cloud-based data warehouse designed to handle massive-scale data processing. It offers:

• High-speed, petabyte-scale queries using Google’s optimized infrastructure.
• Seamless scalability without the need for manual setup.
• Built-in integrations with BI tools like Looker, Tableau, and Google Data Studio for interactive reporting.
• Machine learning capabilities through Google Cloud AI integrations.

For businesses needing real-time analytics and cost-effective scaling, syncing PostgreSQL to BigQuery is the best solution.

Method 1: Sync PostgreSQL to BigQuery with Estuary Flow (Automated & No-Code)

Estuary Flow is a real-time data integration platform that can be used to transfer data between different data stores. It is designed to be lightweight, flexible, and easy to use, making it a good choice for building pipelines between your operational and analytical data stores.

Here’s your easy-to-follow steps to help sync data from PostgreSQL to BigQuery in minutes.

Prerequisites​

To complete this guide, you’ll need:

• An Estuary account. Go to the Flow web app at dashboard.estuary.dev to sign up for the free tier. 
• A PostgreSQL database from which to capture data via change data capture (CDC).
• A BigQuery dataset (this is the component equivalent to a database in the BigQuery architecture).

Step 1: Set Up Your PostgreSQL Database for CDC

Before capturing data from PostgreSQL, configure it for logical replication:

1. Enable logical replication (wal_level-logical) to allow real-time change data capture
2. Create a user role named flow_capture with the REPLICATION attribute. Flow will use this to capture the data.
3. Create a publication listing all the tables in your database.
4. Create a watermarks table to ensure accurate data backfill.

Step 2: Prepare BigQuery for Data Materialization

Estuary Flow needs access to your BigQuery dataset. To set this up:

1. Log into your Google Cloud console and create a new service account as described in the Google documentation. While you’re doing so:
   1. Grant the account access to the Google Cloud project that includes your BigQuery data warehouse.
   2. Grant these roles to the user: roles/bigquery.dataEditor, roles/bigquery.jobUser, and roles/storage.objectAdmin</code.
       
2. Click the name of the new service account from your list of service accounts. Click the Keys tab, then Add key, and download a JSON key. You’ll use this key later.
3. Finally, create a Google Cloud Storage bucket. Flow uses this as a staging area. Make sure the bucket is in the same Google Cloud region as your BigQuery warehouse.

Step 3: Configure Estuary Flow for PostgreSQL Capture

Now you’re ready to set up your CDC pipeline from PostgreSQL. Flow will detect all the available tables in your database and back them up in cloud storage as collections (next, you’ll push, or materialize, these data collections to BigQuery).

1. Go to the Captures page of the Flow web application, signing in if prompted.
2. Click New Capture → Choose PostgreSQL Connector.

3. In the Endpoint Config section, fill out the required properties. You should have these on hand from when you set up your database.
   1. Server address in the format host:port. 
   2. Database user (this should be flow_capture). 
   3. The password for the user.
   4. Database name (you’ll almost always leave the default, Postgres).
4. Click Next → Select the tables you want to sync → Click Save and Publish.
   

Step 3: Materialize the Data to BigQuery

You've captured tables from PostgreSQL into Flow collections. Flow has backfilled all the data in those tables, and is continuing to actively ingest new change events. 

The next step is to connect those collections to new tables in your BigQuery dataset.

1. After capturing data, click Materialize Collections → Choose BigQuery.
2. Fill in the required fields:
   1. Google Cloud Project ID
   2. BigQuery Dataset Name
   3. Google Cloud Storage Bucket (for staging)
3. Upload your Google Service Account JSON key.
4. Click Save and Publish.

Flow copies all data captured from your PostgreSQL tables into new tables in BigQuery. Whenever changes occur in the PostgreSQL source tables, they will be copied over to your BigQuery dataset without delay.

For more information, see:

• Guide to creating any Data Flow with Estuary.
  
• Details on the Estuary Flow PostgreSQL CDC connector.
  
• Details on the Estuary Flow BigQuery materialization connector.

Streamline data sync with Estuary Flow’s no-code, real-time pipeline from PostgreSQL to BigQuery. Start your free trial or contact us to learn more about our seamless integration solutions.

Benefits Of Using Estuary

• Quick implementation with a streamlined pipeline setup.
• Fully automated data sync without manual intervention.
• Schema registry management to automatically correct schema discrepancies.
• Real-time Change Data Capture (CDC) for seamless data replication.
• Event-driven architecture for highly efficient and responsive pipelines.
• No-code UI makes real-time streaming accessible to all teams, not just engineers.
• Lower operational costs by reducing the complexity of data integration.
• Reliable and scalable data movement with minimal effort.

Method 2: Migrate & Load PostgreSQL to BigQuery Manually

If real-time syncing is unnecessary, you can manually migrate and load data using Google Cloud services.

Step 1: Export PostgreSQL Data to CSV

• Use COPY command to export data from PostgreSQL:

plaintext
COPY my_table TO '/tmp/my_table.csv' WITH CSV HEADER;

• Transfer the CSV file to Google Cloud Storage (GCS):

plaintext
gsutil cp /tmp/my_table.csv gs://your-bucket-name/

Step 2: Load Data into BigQuery

• In Google Cloud Console, navigate to BigQuery.
• Click Create Table → Choose Google Cloud Storage as the source.
• Select the CSV file from your GCS bucket.
• Choose BigQuery Dataset & Table Name.
• Configure Schema Settings (auto-detect or manually define schema).
• Choose the Partitioning & Clustering Options if needed.
• Select Write Preferences:
  • "Append" to add new data
  • "Overwrite" to replace existing data
• Click Create Table.

Step 3: Automate with Scheduled Queries (Optional)

If you need periodic updates, you can set up a scheduled query:

• Go to BigQuery Console → Scheduled Queries.
• Create a new scheduled query using SQL:

plaintext
SELECT * FROM `your_project.your_dataset.your_table`

• Set the frequency (e.g., daily, hourly).
• Save and enable the schedule.

Your PostgreSQL data is now loaded into BigQuery! This method is best for one-time or scheduled batch migrations.

Conclusion

Syncing PostgreSQL to BigQuery is essential for real-time analytics and efficient data warehousing. While manual migration is an option, it is time-consuming and error-prone. Using Estuary Flow, businesses can achieve effortless, real-time data sync with minimal setup.

Get Started Today!

👉 Try Estuary Flow for free and set up your PostgreSQL to BigQuery pipeline in minutes: Sign Up Here

Related articles: 

• Postgres to Snowflake
• PostgreSQL to Databricks
• PostgreSQL to Iceberg
• PostgreSQL to SQL Server