Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Logical replication and decoding for Cloud SQL for PostgreSQL

cloud.google.com

Post reply on HN
Print view View original

31–39 of 39 posts

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#31

Post by idunno246 »

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.

presumably alooma would be the one, since google aquired them, but two+ years they still arent integrated but stopped accepting new customers

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#32

Post by [deleted] »

tlarkworthy wrote: post #6
spookylettuce wrote: post #4

Earlier quoted context omitted.

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).

[deleted]

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#33

Post by nceasy »

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 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

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#34

Post by kevincox »

Diggsey wrote: post #25
jacobkg wrote: post #22

Earlier quoted context omitted.

That’s troubling. In fairness, when I last used RDS (2018) we had 9 databases running and we averaged about one database failover per month, with about 2-3 minutes of downtime per incident. I never got a satisfactory answer from support other than that this was a thing that sometimes happened.

To be clear: there is no failover happening, even though we do have a failover instance. If there was, we could at least detect that something had happened after the fact! The Cloud SQL failover only occurs in certain circumstances, and in all our time using Cloud SQL the failover has not once kicked in automatically (despite many outages). In fact, one of our earliest support issues was that the "manual failover" bu…

It sounds like the machine that the DB VM was running on was being taken offline or restarted for maintenance. The default behaviour for GCE is to live-migrate the VM to another machine. So I guess Cloud SQL uses the default here. (It may well be the best option as failover isn't instant either.) Live migration is usually much faster than 90s at well but if you are making heavy RAM updates that could definitely slow it down.

Either way I agree that some full-stack integration is needed on GCPs part to at least get that into the maitnaince log. It would also be nice to make most of these happen during the maitnaince window but IIUC they don't always have 24h notice of a machine reboot.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#35

Post by mikeklaas »

pyrophane wrote: post #12
jeffbee wrote: post #7

Earlier quoted context omitted.

Bigquery can read directly from cloud SQL.

Are you referring to federated queries?

You can do something like:

   CREATE TABLE xx AS SELECT * FROM EXTERNAL_QUERY('postgres.db', 'SELECT * FROM my_table'));

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#36

Post by eloff »

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…

Live migration is when they move you to another system.

They have to do that when hardware fails (if you're lucky), but that it's happening so often suggests it's part of regular software maintenance or something like that. Which is pretty unacceptable to me.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#37

Post by thejosh »

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 use fivetran for Heroku postgres, but also use it for other random sources to Snowflake.

It's decent.

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#38

Post by dilyevsky »

eloff wrote: post #36
Diggsey wrote: post #16

Earlier quoted context omitted.

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…

Live migration is when they move you to another system. They have to do that when hardware fails (if you're lucky), but that it's happening so often suggests it's part of regular software maintenance or something like that. Which is pretty unacceptable to me.

Yes this is regular occurrence on gcp

Re: Logical replication and decoding for Cloud SQL for PostgreSQL

#39

Post by vira28 »

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…

I am wondering why no in place upgrade? Are they concerned by doing “—-link” there may be data corruption and irrecoverable?
Post reply on HN