Logical replication and decoding for Cloud SQL for PostgreSQL

#1

Post by wdb » Sat, Jun 05, 2021, 4:49 PM UTC

Logical replication and decoding for Cloud SQL for PostgreSQL
cloud.google.com

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#2

Post by pyrophane » Sat, Jun 05, 2021, 5:18 PM UTC

Hijacking this thread a bit for a related question. Anyone have a solution for replicating data from Cloud SQL Postgres to BigQuery that they like?

Have been shopping around for a good way to do this, ideally with the ability to capture deletions and schema changes.

Have looked at Fivetran but it seems expensive for this use case, and won’t capture deletions until they can support logical replication.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#3

Post by tbrock » Sat, Jun 05, 2021, 5:28 PM UTC

pyrophane wrote: post #2

Hijacking this thread a bit for a related question. Anyone have a solution for replicating data from Cloud SQL Postgres to BigQuery that they like? Have been shopping around for a good way to do this, ideally with the ability to capture deletions and schema changes. Have looked at Fivetran but it seems expensive for this use case, and won’t capture deletions until they can support logical replication.

We get data into BigQuery from pg using a custom airflow plugin but recently started doing something similar on AWS with DMS to get data from postgres -> redshift.

DMS is awesome. No code is the best code. Big query is great but not having a “snap-your-fingers and the data is there” connector makes it a PITA for me to maintain.

As a result… we are using more redshift.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#4

Post by spookylettuce » Sat, Jun 05, 2021, 5:35 PM UTC

pyrophane wrote: post #2

Hijacking this thread a bit for a related question. Anyone have a solution for replicating data from Cloud SQL Postgres to BigQuery that they like? Have been shopping around for a good way to do this, ideally with the ability to capture deletions and schema changes. Have looked at Fivetran but it seems expensive for this use case, and won’t capture deletions until they can support logical replication.

I tend to utilize bigquery's external connections and scheduled queries to periodically clone my "hot" PG data to an BQ table for long term storage and analysis.

It seems so much easier to go PG=>BQ than the other way around.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#5

Post by mrbungie » Sat, Jun 05, 2021, 5:36 PM UTC

pyrophane wrote: post #2

Hijacking this thread a bit for a related question. Anyone have a solution for replicating data from Cloud SQL Postgres to BigQuery that they like? Have been shopping around for a good way to do this, ideally with the ability to capture deletions and schema changes. Have looked at Fivetran but it seems expensive for this use case, and won’t capture deletions until they can support logical replication.

An option, albeit a bit complex would be:

Debezium => Kafka => Parquet in Google Cloud Storage => BigQuery external queries.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#6

Post by tlarkworthy » Sat, Jun 05, 2021, 5:48 PM UTC

spookylettuce wrote: post #4

pyrophane wrote: post #2

Hijacking this thread a bit for a related question. Anyone have a solution for replicating data from Cloud SQL Postgres to BigQuery that they like? Have been shopping around for a good way to do this, ideally with the ability to capture deletions and schema changes. Have looked at Fivetran but it seems expensive for this use case, and won’t capture deletions until they can support logical replication.

I tend to utilize bigquery's external connections and scheduled queries to periodically clone my "hot" PG data to an BQ table for long term storage and analysis. It seems so much easier to go PG=>BQ than the other way around.

I second federated table connector (and scheduled queries).

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#7

Post by jeffbee » Sat, Jun 05, 2021, 5:48 PM UTC

pyrophane wrote: post #2

Hijacking this thread a bit for a related question. Anyone have a solution for replicating data from Cloud SQL Postgres to BigQuery that they like? Have been shopping around for a good way to do this, ideally with the ability to capture deletions and schema changes. Have looked at Fivetran but it seems expensive for this use case, and won’t capture deletions until they can support logical replication.

Bigquery can read directly from cloud SQL.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#8

Post by patwolf » Sat, Jun 05, 2021, 5:53 PM UTC

spookylettuce wrote: post #4

pyrophane wrote: post #2

Hijacking this thread a bit for a related question. Anyone have a solution for replicating data from Cloud SQL Postgres to BigQuery that they like? Have been shopping around for a good way to do this, ideally with the ability to capture deletions and schema changes. Have looked at Fivetran but it seems expensive for this use case, and won’t capture deletions until they can support logical replication.

I tend to utilize bigquery's external connections and scheduled queries to periodically clone my "hot" PG data to an BQ table for long term storage and analysis. It seems so much easier to go PG=>BQ than the other way around.

Same here. We previously used Stitch until they dropped the free tier.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#9

Post by jacobkg » Sat, Jun 05, 2021, 6:08 PM UTC

Glad to see that they are adding features but I wish the pace was faster. We have been using Cloud SQL for Postgres and overall it is good but there are a number of glaring and frustrating feature holes. The two top of mind for me are:

1) No way to force SSL connections without enforcing two-way SSL (which is a huge pain and not supported by all the clients we use). This is literally just a Postgres config option but they don’t expose it. RDS has had this feature since 2016.

2) No in place upgrade. This is again a feature built in to Postgres and RDS has had it for years. Instead the upgrade story for Cloud SQL is cumbersome and involves setting up a new instance, creating and loading backups, etc.

We switched to Cloud SQL from running our own Postgres and it is a huge improvement, but the feature set is disappointing compared to RDS

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#10

Post by dilatedmind » Sat, Jun 05, 2021, 6:21 PM UTC

jacobkg wrote: post #9

Glad to see that they are adding features but I wish the pace was faster. We have been using Cloud SQL for Postgres and overall it is good but there are a number of glaring and frustrating feature holes. The two top of mind for me are: 1) No way to force SSL connections without enforcing two-way SSL (which is a huge pain and not supported by all the clients we use). This is literally just a Postgres config option but…

how significant is 1 if you are using cloud sql proxy? My understanding is that the proxy tunnels traffic over an encrypted connection, so there is no benefit to adding an extra layer.