6 ms·

Logical replication and decoding for Cloud SQL for PostgreSQL

73 points by wdb 5y ago

pyrophane 5y ago
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.
- tbrock 5y ago
  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.
- spookylettuce 5y ago
  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.
  - tlarkworthy 5y ago
    I second federated table connector (and scheduled queries).
    - deleted 5y ago
      [deleted]
  - patwolf 5y ago
    Same here. We previously used Stitch until they dropped the free tier.
  - sa46 5y ago
    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.
  - nceasy 5y ago
    I also use the external data connector, the problem is when you have two projects in different locations. Then I go for psycopg2 and uploat it to bigquery using bigquery magic lines
- mrbungie 5y ago
  An option, albeit a bit complex would be: Debezium => Kafka => Parquet in Google Cloud Storage => BigQuery external queries.
  - gunnarmorling 5y ago
    Why the Parquet step? You should be able to do straight Debezium -> Kafka -> BQ, using the BQ sink connector for Kafka Connect (https://github.com/confluentinc/kafka-connect-bigquery); https://github.com/confluentinc/kafka-connect-bigquery); we have users using this with the Debezium MySQL connector, I'd expect this to work equally for Postgres. Disclaimer: working on Debezium
- jeffbee 5y ago
  Bigquery can read directly from cloud SQL.
  - pyrophane 5y ago
    Are you referring to federated queries?
    - mikeklaas 5y ago
      You can do something like: CREATE TABLE xx AS SELECT * FROM EXTERNAL_QUERY('postgres.db', 'SELECT * FROM my_table'));
- joeswartz 5y ago
  One option is to use DBeam (http://github.com/spotify/dbeam http://github.com/spotify/dbeam) to export Avro records into GCS and then load to BigQuery with a load job.
- hermanradtke 5y ago
  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.
- idunno246 5y ago
  presumably alooma would be the one, since google aquired them, but two+ years they still arent integrated but stopped accepting new customers
- thejosh 5y ago
  We use fivetran for Heroku postgres, but also use it for other random sources to Snowflake. It's decent.
jacobkg 5y ago
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
- dilatedmind 5y ago
  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.
  - jacobkg 5y ago
    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.
- Diggsey 5y ago
  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...
atombender 5y ago
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.
- wdb 5y ago
  Blog article says its planned for later this year. :)
btown 5y ago
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-replication-to-replicate-managed-amazon-rds-for-postgresql-and-amazon-aurora-to-self-managed-postgresql/ 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-integration-of-postgresql-and-kafka/ 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/ 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.
- hamandcheese 5y ago
  Where I work we use debezium to enable “transactional publishing” to kinesis. Not only does it allow publishing events as part of an ordinary database transaction, it also provides a nice buffer if kinesis ever goes down. During the Nov. 2020 kinesis outage, our services using this mechanism kept chugging with no immediate issues.
- cpursley 5y ago
  Similarly - Supabase uses Elixir to listen to Postgres changes via logical replication. Pretty neat pattern and Elixir/Erlang is especially good at this sort of thing: https://github.com/supabase/realtime https://github.com/supabase/realtime

73 points by wdb 5y ago

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.

tbrock 5y ago
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.
spookylettuce 5y ago
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.
- tlarkworthy 5y ago
  I second federated table connector (and scheduled queries).
  - deleted 5y ago
    [deleted]
- patwolf 5y ago
  Same here. We previously used Stitch until they dropped the free tier.
- sa46 5y ago
  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.
- nceasy 5y ago
  I also use the external data connector, the problem is when you have two projects in different locations. Then I go for psycopg2 and uploat it to bigquery using bigquery magic lines
mrbungie 5y ago
An option, albeit a bit complex would be: Debezium => Kafka => Parquet in Google Cloud Storage => BigQuery external queries.
- gunnarmorling 5y ago
  Why the Parquet step? You should be able to do straight Debezium -> Kafka -> BQ, using the BQ sink connector for Kafka Connect (https://github.com/confluentinc/kafka-connect-bigquery); https://github.com/confluentinc/kafka-connect-bigquery); we have users using this with the Debezium MySQL connector, I'd expect this to work equally for Postgres. Disclaimer: working on Debezium
jeffbee 5y ago
Bigquery can read directly from cloud SQL.
- pyrophane 5y ago
  Are you referring to federated queries?
  - mikeklaas 5y ago
    You can do something like: CREATE TABLE xx AS SELECT * FROM EXTERNAL_QUERY('postgres.db', 'SELECT * FROM my_table'));
joeswartz 5y ago
One option is to use DBeam (http://github.com/spotify/dbeam http://github.com/spotify/dbeam) to export Avro records into GCS and then load to BigQuery with a load job.
hermanradtke 5y ago
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.
idunno246 5y ago
presumably alooma would be the one, since google aquired them, but two+ years they still arent integrated but stopped accepting new customers
thejosh 5y ago
We use fivetran for Heroku postgres, but also use it for other random sources to Snowflake. It's decent.

jacobkg 5y ago

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

dilatedmind 5y ago
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.
- jacobkg 5y ago
  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.
Diggsey 5y ago
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...

atombender 5y ago

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.

wdb 5y ago
Blog article says its planned for later this year. :)

btown 5y ago

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-replication-to-replicate-managed-amazon-rds-for-postgresql-and-amazon-aurora-to-self-managed-postgresql/ 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-integration-of-postgresql-and-kafka/ 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/ 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.

hamandcheese 5y ago
Where I work we use debezium to enable “transactional publishing” to kinesis. Not only does it allow publishing events as part of an ordinary database transaction, it also provides a nice buffer if kinesis ever goes down. During the Nov. 2020 kinesis outage, our services using this mechanism kept chugging with no immediate issues.
cpursley 5y ago
Similarly - Supabase uses Elixir to listen to Postgres changes via logical replication. Pretty neat pattern and Elixir/Erlang is especially good at this sort of thing: https://github.com/supabase/realtime https://github.com/supabase/realtime