Upgrading Your Database to an Iceberg Data Lake (Part 2)

The Series

This is a continuation of replicating your database to an Iceberg-based data lake. In Part 1, we covered the theory, the how, and the why of this. In this second part, I will cover the actual implementation, complete with code, commands, and lessons learned on putting this into production.

The Implementation Prerequisites

The only real prerequisites for this are to have a cloud instance (or have an on-prem solution) that allows you to spin up a Kubernetes cluster, having a source compatible with Debezium Server, and a BLOB store such as GCS or S3 to store your Iceberg table data in. I will give instructions on setting up all but the source in this article on GCP.

You won't even really need much Kubernetes knowledge to implement this basic example, as I will give you all the commands to run. Believe me, it will be needed beyond this basic example for doing this at full production scale, but for this example, you won't need to be a Kubestronaut.

Note that this can be deployed in different ways than Kubernetes, and if running it solo on a Kubernetes cluster, then I would recommend this for cost savings. If you are only running this application and not needing a full Kubernetes cluster, then consider running it as a standalone container on a compute platform such as GCE or EC2.

The Implementation Plan

The plan for this implementation is to follow these steps:

1. Prepare your source (Postgres in this case)
2. Set up permissions on your bucket/destination
3. Modify the Kubernetes deployment with your settings
4. Deploy the Kubernetes deployment
5. Test it out

It's a very simple plan by design, and I will be putting in points, calling out where improvements can be made for production use beyond this simple use case that I am intending as a proof-of-concept.

Since I am often referred to as "The BigQuery Dude," I am going to be implementing this on GCP since that's my native land. Note that this example is trivial to deploy on AWS or Azure as well. I will be deploying Debezium Server on GKE, using a source as Postgres on Cloud SQL, and using Iceberg tables on GCS, all of which have equivalents on AWS and Azure.

I am using as much platform-agnostic coding as I can, but some will be specific to GCP. Everything besides the "gcloud" commands and the "gs://" prefixes below will transfer directly over to any variety of Kubernetes, storage, and Postgres out there that may exist on the other providers.

For sample data throughout this entry, I am using the famous Northwind database that many of my generation grew up learning SQL on. You can download the code from GitHub here or to get straight to the SQL file here. This exists in the public schema and I am running everything with a user called "my_user" to make search and replacing easy as well as being able to run these directly as a test.

The Implementation (Source Database Replication)

The first step of implementation is to get your database ready to go. I won't go into details of setting this up, as it's been written many, many times before. I am going to assume you are using PostgreSQL on GCP's Cloud SQL for this test case. If you aren't using PostgreSQL, then look here for how to do these steps for your database of choice.

First, you need to make sure your wal_level flag is set to logical, mostly for self-hosted and non-Cloud SQL instances. In Cloud SQL, this is handled by the managed layer and called the "logical_decoding" flag. To add this, edit your instance and under flags ensure that cloudsql.logical_decoding is set to On, or if it hasn't been added, just add it and ensure that the value is set to On.

Next up is getting the replication user set up properly. I would HIGHLY recommend creating a new user for this with a descriptive name so you can monitor it better. I usually see this user named debezium or cdc, but you can choose whatever you would like.

To create a new user for this, run these commands (and look below if you hit an error):

CREATE USER my_user WITH REPLICATION LOGIN PASSWORD '<password>';

If you get errors running the above command about not being able to add the REPLICATION role to a user, then run this command on your user (assuming you have the privileges; if not, you may need an admin to do this for you or use the postgres user):

ALTER USER my_user WITH REPLICATION;

Now is the moment of truth to choose which table you want to replicate. I am doing a single table for simplicity's sake here. Run this command next, specifying the tables and schemas, and granting the selected user access. Note that you can specify as many as needed, separated by a comma:

-- Change "my_publication" to your preferred name
CREATE PUBLICATION my_publication
FOR TABLE public.orders;

-- Run these for each schema selected above
GRANT SELECT ON ALL TABLES IN SCHEMA public TO my_user;
GRANT USAGE ON SCHEMA public TO my_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO my_user;

For a more practical application beyond what I am covering in this article, this is the syntax of the SQL for doing multiple or all tables in a schema:

-- For multiple tables
CREATE PUBLICATION my_publication
FOR TABLE public.orders, public.order_details;

-- For all tables in a schema
CREATE PUBLICATION my_publication
FOR TABLES IN SCHEMA public;

-- Run these for each schema selected above
GRANT SELECT ON ALL TABLES IN SCHEMA public TO my_user;
GRANT USAGE ON SCHEMA public TO my_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO my_user;

Lastly, we need to create a replication slot. A replication slot is just a process that runs constantly, publishing the changed data to any receivers. Note I am calling this my_replication_slot for easier running and searching/replacing later. To do this, run the following command:

SELECT PG_CREATE_LOGICAL_REPLICATION_SLOT('my_replication_slot', 'pgoutput');

At this point, you are done with setting up replication in the source database (assuming you are following and using Postgres).

Important note: If you are not actively using this replication slot, it will run continuously and store changed data on disk. So, if you are not using it, then delete it because it will start consuming your disk space over time, thus causing you to have a more expensive database instance.

Service Account Security Notes

In this, I am running the GKE cluster and thus the Debezium Server service as the default Compute Engine service account. This is just for demonstration purposes, so as not to have to get into the Principle of Least Privilege. Do NOT do this in production with the default service account. Create a new service account that has only the permissions needed; this is beyond the scope of this article to deep dive into. I recommend starting the path down this rabbit hole with this article in the Google documentation.

Also, you will note I am calling out places below where I am doing things for the sake of simplicity, not security, since this is a demonstration. Take note of these, as I am sparing you security headaches down the road, and utilize a more secure method. PLEASE!

The Implementation (Build your Environment Variables)

I am adding this step to make your life easier, as I didn't do this my first time while writing this, and upon re-validating everything before publishing, I added this step to make life MUCH easier. This step is to make the commands below easier by not requiring you to edit your values for each and every step. Do not modify after the dashed line, as those values are generated.

Note that there are credentials in here, so if you are doing this in production, please use a more secure method for this, such as a secret manager instead.

The last set of commands will echo out each value for validation, except for the password value. If you see any errors, check your values and make sure that you have set them correctly.

Here is the command with comments above each to tell you what they are:

# The project's name
PROJECT_NAME=<project_name>
# The bucket where the Iceberg tables will live's name
BUCKET_NAME=<bucket-name>
# Base path in the above bucket where the Iceberg tables will live,
# this is normally a table or schema name.
# If unsure just default it to empty and it will write to the root of
# the bucket.
BUCKET_PATH=<bucket_path>
# Name of your cluster, I am using cdc_cluster in the commands above
CLUSTER_NAME=<cluster_name>
# Cluster region, I am using us-central1 in the commands above.
# This should be the same region as your Cloud SQL instance
CLUSTER_REGION=<cluster_region>
# Cloud SQL instance name
SQL_INSTANCE_NAME=<cloud_sql_instance_name>
# Database username that you created above
DATABASE_USER=<database_user>
# Database password for this user you created above
DATABASE_PASSWORD=<database_password>
# Database name from where above commands were applied
DATABASE_NAME=<database_name>

#----------------------------------------------------------------------
# Do NOT modify below this line as these values are pulling additional
# values and populating generated environment variables for usage later.
gcloud config set project $PROJECT_NAME
PROJECT_NAME=$(gcloud config get-value project)
PROJECT_ID=$(gcloud config get-value project 2>/dev/null)
SQL_NAME=gcloud sql instances describe $SQL_INSTANCE_NAME --format='value(connectionName)'

# Below will echo out everything from above to make sure it looks correct
echo "Project Name: $PROJECT_NAME"
echo "Project ID (Generated from gcloud): $PROJECT_ID"
echo "Bucket Name: $BUCKET_NAME"
echo "Bucket Path: $BUCKET_PATH"
echo "Full Bucket Path: gs://$BUCKET_NAME/$BUCKET_PATH"
echo "Cluster Name: $CLUSTER_NAME"
echo "Cluster Region: $CLUSTER_REGION"
echo "SQL Instance Name (Given): $SQL_INSTANCE_NAME"
echo "SQL Instance Name (Received from gcloud): $SQL_NAME"
echo "Database Username: $DATABASE_USER"
echo "Database Password (hidden): ******"
echo "Database Name: $DATABASE_NAME"

The Implementation (Bucket Permissions)

In order to read and write to a GCS bucket, we need to create the bucket. Then there is a set of permissions needed to be granted to the service account running the GKE cluster we will implement in the next section.

I have tested and tried to break down the IAM requirements to be simpler than what Google recommends, but in my testing, the following two predefined roles are the best options to grant to this service account:

• Storage Object Admin (i.e. roles/storage.objectAdmin)
• Storage Legacy Bucket Reader (i.e. roles/storage.legacyBucketReader)

Google mentions this here for their BigLake implementation, and I advise reading that page if you are going to be using BigLake. The one detail they leave out there is that you will need the permissions from the Storage Object Admin role to write Iceberg tables. Just apply this role and don't go down the rabbit hole of errors and trying combos of permissions, trust me.

Run the following command to create the bucket and apply the service account IAM policy bindings, using the default Compute Engine one, so change this as necessary:

gcloud storage buckets create gs://$BUCKET_NAME
gcloud storage buckets add-iam-policy-binding gs://$BUCKET_NAME \
    --member="serviceAccount:$PROJECT_NAME[email protected]" \
    --role="roles/storage.objectAdmin"
gcloud storage buckets add-iam-policy-binding gs://$BUCKET_NAME \
    --member="serviceAccount:$PROJECT_NAME[email protected]" \
    --role="roles/storage.legacyBucketReader"

The commands are simple to understand. The first one just sets your gcloud command to the correct project. The second one creates the bucket. Then the final two grant the necessary permissions to the default compute service account in the project.

The Implementation (Creating a Kubernetes Cluster)

This implementation is going to be for creating a Kubernetes cluster using Google Kubernetes Engine (GKE) Autopilot and loading up the Debezium Server onto it using a Helm chart. If you are familiar with GKE or have another cluster already running, then you probably know what you are doing, so you can skip ahead to the commands to load this below.

First up, we need to add one role to our default Compute Engine service account that's needed. Here is the command to do this:

gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:$PROJECT_NAME[email protected]" \
    --role="roles/container.defaultNodeServiceAccount"

Next up is creating the GKE Autopilot cluster itself. Just run the command below.

gcloud container clusters create-auto $CLUSTER_NAME \
    --location=$CLUSTER_REGION \
    --project=$PROJECT_ID

This will take a bit to fire up, but after a few minutes, you should get confirmation that it has completed, and you will be able to see your cluster in the GKE section of the GCP Console.

Note on Networking for Real-World Applications

When running this in the real world, there is a good chance you will be running Cloud SQL in another region or dealing with cross-project communication. My biggest recommendation is to do your best to utilize the private IP and the Cloud SQL proxy to get around some of the "particulars" when it comes to networking on GCP.

The Implementation (Deploying to Kubernetes Cluster)

Next up is the fun part of deploying the Debezium Operator on GKE. Just run this command and give it a few minutes to download and load up:

# Authenticate gcloud with the GKE cluster, this allows running kubectl later
gcloud container clusters get-credentials $CLUSTER_NAME --region $CLUSTER_REGION

# Add the Helm repository and update the local Helm source list
helm repo add debezium https://charts.debezium.io
helm repo update

# Install the operator into a dedicated namespace (e.g., debezium-system)
# It will create this namespace if it does not already exist
helm install debezium-operator debezium/debezium-operator \
  --namespace debezium-system \
  --create-namespace

Note: I left the comments in the command to express what each command is doing, so that will help you understand what's going on and diagnose any potential issues.

To validate that this is deployed and/or diagnose why the deploy failed in the worst case, run the command below that lists out all of the pods in the created namespace and also displays the associated logs:

# Get the pods in the debezium-system namespace
kubectl get debeziumserver -n debezium-system
# Display the logs for the pods
kubectl logs -l app.kubernetes.io/name=debezium-server -n debezium-system

Now you are ready to do the actual deploy!

The command below is a bit long, but it's doing a relatively simple operation of just deploying Debezium Server and its configuration with an attached Cloud SQL proxy. For those more fluent in Kubernetes, it is deploying the Cloud SQL Proxy as a sidecar on a pod that is running Debezium Server.

When you are ready to go, just run this command:

kubectl apply -f - <<EOF
apiVersion: debezium.io/v1alpha1
kind: DebeziumServer
metadata:
  name: debezium-sql-proxy
  namespace: debezium-system
spec:
  image: debezium/server:latest
  # --- Sidecar Configuration ---
  runtime:
    containers:
      - name: cloud-sql-proxy
        image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:latest
        args:
          - "--port=5432"
          - "$PROJECT_NAME:$CLUSTER_REGION:$SQL_NAME"
        securityContext:
          runAsNonRoot: true
  # --- Connector Configuration ---
  source:
    connector.class: io.debezium.connector.postgresql.PostgresConnector
    config:
      # Connect to localhost because the proxy is in the same Pod
      database.hostname: "127.0.0.1"
      database.port: "5432"
      database.user: "$DATABASE_USER"
      database.password: "$DATABASE_PASSWORD"
      database.dbname: "$DATABASE_NAME"
      topic.prefix: "cdc"
      # Required for Cloud SQL Postgres
      plugin.name: "pgoutput"
  # --- Iceberg Sink Configuration ---
  sink:
    type: iceberg
    config:
      # The Iceberg Catalog type (Hadoop is best for GCS-only setups)
      debezium.sink.iceberg.catalog.type: hadoop
      debezium.sink.iceberg.catalog.warehouse: gs://$BUCKET_NAME/$BUCKET_PATH

      # GCS FileSystem configuration
      debezium.sink.iceberg.fs.defaultFS: gs://$BUCKET_NAME
      debezium.sink.iceberg.io-impl: org.apache.iceberg.gcp.gcs.GCSFileIO

      # Table behavior: upsert (merges changes) or append
      debezium.sink.iceberg.upsert: "true"
      debezium.sink.iceberg.upsert.keep-deletes: "false"

      # Commit interval (how often to create a new Iceberg snapshot)
      debezium.sink.iceberg.table.commit.interval-ms: "60000"
EOF

Testing the Output (Finally!)

Now is the moment of testing! At this point, go into your Postgres and just insert rows into the table covered by that command we ran on it previously. That is all you need to do on the Postgres side for this and to trigger the replication going forward. It should start writing data straight to an Iceberg table on GCS.

To test this, we are going to use BigQuery to query the Iceberg table. In BigQuery, we will create a BigLake External Table. In non-BigQuery user terms, this means you are telling BigQuery I have tables in a specific format that live on a GCS bucket, and I would like to query them using BigQuery via an abstraction layer called BigLake for the GCS reads.

To do this, open up your GCP Console and navigate to the BigQuery section, which should open up a text editor window in the part they call BigQuery Studio.

Paste this command in there and modify it with the region where your bucket lives, the bucket name, and the bucket path to what you set above:

CREATE SCHEMA IF NOT EXISTS `testing_biglake_dataset`
OPTIONS(
  location = '<bucket_region>'
);

CREATE OR REPLACE EXTERNAL TABLE `testing_biglake_dataset.my_testing_iceberg_table`
WITH CONNECTION `testing_biglake_dataset.my_connection`
OPTIONS (
  format = 'ICEBERG',
  uris = ['gs://<bucket_name>/<bucket_path>/metadata.json']
);

Go ahead and run this; it will create a new specific dataset for this. This makes it so we can remove it all later without any hassle.

After that query runs, click the create a new query tab at the top of the BigQuery Studio editor pane. Then paste in this SQL and run it:

SELECT * FROM testing_biglake_dataset.my_testing_iceberg_table TABLESAMPLE SYSTEM (15 PERCENT)

This should return a few rows of data you inserted. Note that it does a table sample of 15%, so it won't return all the data. Since we are talking about BigQuery, and its amazing ability to run up big bills, I am taking this precaution to make it run very small queries.

At this point, you have successfully implemented replicating data from Postgres to Iceberg! Next up is doing this for multiple tables (or schemas) to get all of your data in a "database agnostic" datastore.

In Conclusion

This is just a basic writing of a single table to a single Iceberg table, but the power of this is immense. Imagine being able to write entire sales databases full of transactions in real-time to a data warehouse, which then can be queried to reflect up-to-date sales data on dashboards and reports.

The best part is that the data warehouse doing the querying doesn't matter; if you prefer BigQuery, ClickHouse, Snowflake, Databricks, etc., or a blend, then you are good to go. Staying platform agnostic is one of the best practices you can do right now to stay competitive, both technically and for keeping ahead on costs.

This is just one of the many topics we cover and advise on at DoiT International, so if you are interested in knowing more reach out to us to see how we can help you out with your cloud costs or your cloud operations.

Clean Up (If/As you Wish)

Lastly, if after testing this you want to remove it to reduce costs and plan how to run this in production, here is the command to "burn it all down" on the GKE cluster:

gcloud container clusters delete $CLUSTER_NAME \
    --location=$CLUSTER_REGION \
    --project=$PROJECT_ID

To get rid of the BigQuery dataset we created, run this in BigQuery Studio:

DROP SCHEMA IF EXISTS `testing_biglake_dataset`;

Do NOT forget this step if you are removing it all, as this removes the replication slot from your Postgres instance. If you do not do this, then you will have increased Postgres storage costs over time. Note, you will need to run this command inside of Postgres:

SELECT pg_drop_replication_slot('my_replication_slot');

How We DoiT

Here at DoiT International, we tackle problems like this all of the time, and I am always asked about ways to help save money while implementing data projects.

Helping out on implementing projects in the most effective and in the most cost effective way is part of our mission for our customers. Handling everything from cases like these to providing the best FinOps solutions for our customers is what we do, and I may be a little biased in saying this, but we do it VERY well.

In addition we provide best-in-class tools helping you with your cost optimization needs such as PerfectScale for Kubernetes and Select for BigQuery, Databricks, and Snowflake,