Logical replication and decoding for Cloud SQL for PostgreSQL • Hacker News Board

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#11

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

dilatedmind wrote: post #10

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.

cloud sql proxy is secure and that is what all our developers use for local access to the database.

However, we have some third party data analysis tools (such as Tableau) that also connect to one of our databases. They are hosted in their own clouds and have to connect over the databases’s public IP address and can’t use cloud_sql_proxy. I of course manually confirmed that these connections use SSL but I would feel much more comfortable if I could enforce it from our end.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#12

Post by pyrophane » Sat, Jun 05, 2021, 7:13 PM UTC

jeffbee wrote: post #7

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.

Are you referring to federated queries?

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#13

Post by atombender » Sat, Jun 05, 2021, 7:44 PM UTC

Google just launched the Datastream product in beta, which looks very cool. I find it curious that it doesn't support Postgres at all, even as they're launching the logical replication feature for Cloud SQL.

In fact, the Datastream documentation has a diagram showing Postgres as a source, as well as custom sources — but, disappointingly, neither is supported. Only Oracle and MySQL are supported.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#14

Post by btown » Sat, Jun 05, 2021, 8:08 PM UTC

This is awesome! For comparison, this is also supported on Amazon RDS, so AFAICT this opens up the possibility of near-zero-downtime streaming migrations between the two cloud providers: https://aws.amazon.com/blogs/database/using-logical-replicat...

Also, it enables a really cool pattern of change data capture, which allows you to capture "normal" changes to your Postgres database as events that can be fed to e.g. Kafka and power an event-driven/CQRS system. https://www.confluent.io/blog/bottled-water-real-time-integr... is a 2015 post describing the pattern well; the modern tool that replaces Bottled Water is https://debezium.io/ . For instance, if you have a "last_updated_by" column in your tables that's respected by all your applications, this becomes a more-or-less-free audit log, or at the very least something that you can use to spot-check that your audit logging system is capturing everything it should be!

When you're building and debugging systems that combine trusted human inputs, untrusted human inputs, results from machine learning, and results from external databases, all related to the same entity in your business logic (and who isn't doing all of these things, these days!), having this kind of replayable event capture is invaluable. If you value observability of how your distributed system evolves within the context of a single request, tracking a datum as it evolves over time is the logical (heh) evolution of that need.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#15

Post by sa46 » Sat, Jun 05, 2021, 8:29 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.

Is the idea something like a scheduled:

   INSERT INTO bq_table SELECT * FROM EXTERNAL_QUERY('');

I'm guessing you're on the hook for keeping the schema up to date with the Postgres schema.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#16

Post by Diggsey » Sat, Jun 05, 2021, 8:48 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…

Also, if you use Cloud SQL in HA mode, it may still go down randomly for up to 90s with no warning or entry in the operation log, and this is considered expected behaviour.

Here is a direct quote from google support when we contacted them about our database going down outside of our scheduled maintenance window:

> As I mentioned previously remember that the maintenance window is preferred but there are time-sensitive maintenance events that are considered quite important such as this one which is a Live migration. Most maintenance events should be reflected on the operations logs but there are a few other maintenance events such as this one that are more on the infrastructure side that appear transparent to clients because of the nature of the changes made to the Google managed Compute Engine that host the instances, this is a necessary step for maintaining the managed infrastructure. For this reason this maintenance does not appear visible in your logs or on the platform.

Here "transparent to clients" means that the database is completely inaccessible for up to 90s. Furthermore, because there's no entry in the operation log, there's no way to detect if the database is down because of "expected maintenance", or because of some other issue without talking to a human at google support: so really great if you're woken up in the middle of the night because your database is down, and you're trying to figure out what happened...

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#17

Post by joeswartz » Sat, Jun 05, 2021, 9:06 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.

One option is to use DBeam (http://github.com/spotify/dbeam) to export Avro records into GCS and then load to BigQuery with a load job.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#18

Post by hermanradtke » Sat, Jun 05, 2021, 9:12 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 went with StitchData over FiveTran. We also had to build a custom way to delete records. Hopefully we can get rid of this soon.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#19

Post by [deleted] » Sat, Jun 05, 2021, 9:25 PM UTC

Diggsey wrote: post #16

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…

Also, if you use Cloud SQL in HA mode, it may still go down randomly for up to 90s with no warning or entry in the operation log, and this is considered expected behaviour. Here is a direct quote from google support when we contacted them about our database going down outside of our scheduled maintenance window: > As I mentioned previously remember that the maintenance window is preferred but there are time-sensitive…

[deleted]

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#20

Post by wdb » Sat, Jun 05, 2021, 9:53 PM UTC

atombender wrote: post #13

Google just launched the Datastream product in beta, which looks very cool. I find it curious that it doesn't support Postgres at all, even as they're launching the logical replication feature for Cloud SQL. In fact, the Datastream documentation has a diagram showing Postgres as a source, as well as custom sources — but, disappointingly, neither is supported. Only Oracle and MySQL are supported.

Blog article says its planned for later this year. :)