Databases topics

CloudSQL instance failed to update Postgres and now its stuck in maintenance mode.

Sat, 16 Nov 2024 20:05:59 GMT

We had a routine database version upgrade from Postgres 15 to Postgres 17. 21 instances upgraded without issues but one did not.

Instance information:

db-custom-1-3840
maintenanceVersion: POSTGRES_15_8.R20240910.01_02
10gb sdd
HA mode
database size approx 4 GB
located in europe-west3-c

Symptoms:

After initiating the upgrade ~15min passed and then the error was displayed in Operations and logs:

The instance itself shows that it is under maintenance.

Troubleshooting:

All actions such as restart/patch/failover from WEB-UI and gcloud not working which is expected for instance that is in maintenance mode.
Looking at logs did not show any new information. Logs ended on instance shutdown.
After waiting a few hours backup restore was initiated. In the same region and same specifications new instance was created. The process did not finish in two hours. This time no errors are visible. Now new instance is also stuck in maintenance mode.
Then, the third instance was created now in europe-north1-b. Same result as with the previous attempt. It's stuck for 20 at this time.

Edit: The second restore attempt failed with the same error in Operations and logs after 2 hours.

Is Babelfish for PostgreSQL Possible on Google Cloud SQL?

Thu, 14 Nov 2024 18:54:46 GMT

Hi everyone,

Has anyone tried using Babelfish for PostgreSQL on Cloud SQL? From what I understand, it probably wouldn’t work since Babelfish requires some changes to PostgreSQL’s source code, which managed services like Cloud SQL don’t usually allow. But I’m curious, has anyone found a way to make it work or come across a Cloud SQL version that supports it?

We’re thinking it could help us migrate SQL Server-based applications more gradually. The idea would be to use Babelfish to get things running on PostgreSQL, then slowly update SQL Server-specific queries and procedures to ANSI SQL or PostgreSQL-native ones, eventually phasing out Babelfish completely.

Would love to hear if anyone has explored this or has thoughts on whether it’s possible. 😍

Thanks!

Restore an instance of PostgreSQL 11

Tue, 12 Nov 2024 20:54:20 GMT

Hi, i need to restore a PostgreSQL 11 database in my gcloud in a new instance, but when i proceed to create the instance, in the selector of the database don't show the database.

Someone had the same problem?

Thanks

pgvectorscale support in Cloud SQL for Postgres

Tue, 12 Nov 2024 13:51:32 GMT

Hi all.

Curious if the Cloud SQL team has any plans to support pgvectorscale for Postgres?

Many thanks in advance!

dynamic schema evaulavation in biglake ICERG table

Mon, 11 Nov 2024 10:13:07 GMT

hi Team,

as per the iceberg documentation the product will supports dynamic schema evaluvation.

we are looking for the solution to load the streaming files from GCS to BQ biglake iceberg table using spark stored procedure .

as per the GCP biglake iceberg documentation we have Spark SQL only to load the data into iceberg table . is there any alternative way to load the data into biglake iceberg table with dynamic schema evaluation .

Is it possible to select databases? - Database migration service

Mon, 11 Nov 2024 13:11:42 GMT

Is it possible to move a subset of databases using Google's Database Migration Service (DMS)?

We are not interested in migrating the entire database at once. We won’t be moving databases to the cloud in large batches, but rather individually.

The source database is MySQL and destination is Cloud SQL.

As I read it in the documentation, it is not possible. Are there any workarounds for this?

Thanks in advance!

Google Cloud

Fri, 08 Nov 2024 07:25:37 GMT

We're having trouble with the data export functionality. Typically, our data export takes about 10 minutes to complete. However, since yesterday, export goods have been continuously uploaded without being completed. Can anyone help me how to solve this problem?

Ctrl + C exits the MySQL client

Wed, 06 Nov 2024 00:50:03 GMT

If I use the Cloud SQL for MySQL client, hitting Ctrl + C directly exits the shell instead of doing nothing. This is annoying behavior since most developers are used to hitting Ctrl + C for exiting out of an unfinished/improperly typed query.

Steps to reproduce:

Use gcloud CLI to login to MySQL:

gcloud sql connect <name> --user=<username> --project <gcloud-project>

Select your database

Hit Ctrl + C

Observe that the client exits.

References: https://dba.stackexchange.com/a/132936

ChatGpt Api Connection With Google Cloud Database Issues

Tue, 05 Nov 2024 13:40:11 GMT

We are encountering multiple issues while trying to establish a connection between our Cloud Run service and Cloud SQL database using API Gateway. Our goal is to set up a secure, functional API that will allow external access to the database. Here’s a detailed summary of what we have done and the problems we’re facing.

Project Overview

Objective: Establish a secure API connection to our Cloud SQL database so our external application (using API Gateway) can interact with it.
Setup: We’re using Cloud SQL (MySQL) for data storage, Cloud Run for service hosting, and API Gateway for secure access. The final goal is to enable API requests to interact with the Cloud SQL database through Cloud Run, with api_key security in place.

Steps Completed So Far

API Gateway Configuration:
- Configured an API Gateway (LEX API Gateway) with a swagger.yaml file.
- Set up the x-google-backend extension to route requests from the Gateway to our Cloud Run service, with the following details:
  - Protocol: http/1.1
  - Address: Cloud Run service URL.
- Configured security with an api_key requirement.
Cloud SQL Database Setup:
- Instance Name: sqltiger
- Databases: Two databases named LexComms and Tiger.
- Public IP: Configured public IP access for the Cloud SQL instance.
- Permissions: Granted relevant roles (Cloud SQL Admin, Cloud SQL Client, Cloud SQL Viewer) to the service account used for API Gateway and Cloud Run.
Cloud Run Configuration:
- Environment Variables: Configured DB connection details as environment variables (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME).
- Cloud SQL Connections: Set up the Cloud SQL instance connection directly within Cloud Run.
- Service Account: Changed the service account for Cloud Run to the one with API Gateway permissions (lex-sql-access-api).
- Port Configuration: Verified that Cloud Run is set to use port 8080.

Current Issues

Database Connection Refusal:
- Error Message: When sending a request via curl to the API Gateway, we receive:
  json
  Copy code
  {"error":"2003: Can't connect to MySQL server on 'None:3306' (Errno 111: Connection refused)"}
- We have verified that the database is accessible via its public IP and credentials.
API Key and Access Issues:
- Error Message (Previously): Initially, we encountered a PERMISSION_DENIED error, which was resolved by enabling the API service in the project.
- Current Status: We still face issues where API requests do not seem to access the database, even after setting the API key and verifying that it is active and unrestricted.
Connection Testing from Local Machine:
- We attempted to connect to the database directly using the mysql command from a local environment but encountered issues due to command recognition (Windows environment). However, the database should theoretically be reachable via its public IP.
Cloud SQL Connection Configuration Uncertainty:
- We are uncertain whether the Cloud Run environment correctly recognizes the Cloud SQL connection, despite setting up the connection details both as environment variables and in the Cloud SQL connections section within Cloud Run.

Request for Assistance

Could you please guide us on the following points?

Configuration Check: Could you verify if our configuration of API Gateway, Cloud Run, and Cloud SQL aligns with best practices, particularly in relation to securing database connections through API Gateway?
Cloud SQL Direct Connection (Cloud Run): Are there any additional configurations required to ensure that Cloud Run can securely connect to Cloud SQL without issues?
Debugging Tips: If the configuration appears correct, could you suggest any debugging steps we could take to identify where the connection might be failing?

Thank you for your assistance with this complex setup.

firestore: Storage charges do not go down after deleting data

Mon, 04 Nov 2024 18:38:37 GMT

Hello,

I deleted a large amount of data from Firestore in our test project 5 days ago.

But the Cloud Firestore Storage cost in the billing report does not decrease.

Even though I deleted about 2 million documents, the cost increased instead of decreasing.

I also saw an article that said it could be because Firestore's garbage collection is delayed, but it's strange that it hasn't changed even after 5 days.

Did I miss something?

Thanks for your time and attention.

Replica DB Max Connections Display Error - Max_connections Value Mismatch

Mon, 04 Nov 2024 06:43:31 GMT

Environment Details:

We are experiencing an issue in our Google Cloud environment related to max_connections settings displayed for replica databases. Specifically, the primary database and its replica have different max_connections values configured, but the monitoring trend graph is displaying both of them with the same maximum connections value. This discrepancy does not align with the actual database settings.

Issue Description:

Primary Database: Configured with max_connections set to 10,000.
Replica Database: Configured with max_connections set to 6,000.
Trend Graph Issue: In Cloud Monitoring, the trend graph displays both the primary and replica databases as having 10,000 maximum connections, even though the replica is configured with a lower value.

This issue seems to be impacting the accuracy of monitoring data for all master-replica database configurations in our environment.

Troubleshooting Steps Performed:

Verified that each database's max_connections settings are correct (i.e., 10,000 for the primary and 6,000 for the replica).
Checked the data source in Cloud Monitoring and confirmed that the values displayed do not match the actual configuration.

Additional Information:

This issue appears to be a UI display error within Cloud Monitoring.
It can be replicated by following similar steps in a comparable master-replica setup.

Assistance Request:

We kindly request any insights or recommendations on how to ensure that Cloud Monitoring accurately displays the max_connections values for replica databases. Is this a known issue with the display in monitoring, or is there a specific configuration step we might be missing?

Any help or guidance would be greatly appreciated.

Thank you!

Best Regards
David

Should I make two cloud sql proxy for read replica?

Mon, 04 Nov 2024 06:37:43 GMT

Hi.

I created Cloud SQL instance and successfully connected to my gke pod with cloud sql proxy sidecar container in reference to the below link

https://cloud.google.com/sql/docs/postgres/connect-instance-kubernetes?hl=ko

And I created read replica instance, Should I make another cloud sql proxy for connecting read replica?

And If I want to make load balancer for multiple read replicas, is it only way to make HAproxy in reference to the below link?

https://cloud.google.com/blog/products/databases/using-haproxy-to-scale-read-only-workloads-on-cloud-sql-for-postgresql?hl=en

Thanks for helping me.

Firestore Indexes Disappeared

Fri, 01 Nov 2024 14:26:54 GMT

We have two environments: staging and production.

I recently deployed to our staging server, and got some errors, 'The query requires an index'. This I fixed by following the link it provides. Because I knew we'd be deploying the same code to production later too, I adjusted the link which took me to add the same keys to production as well. This *appeared* to work...

...until I just deployed production yesterday, and now I'm getting the same errors. After reviewing the database, the indexes were deleted.

Why would they have been deleted?

EDIT:

Worse than this, the indexes on the staging Firestore were also deleted, and now I'm getting the same errors on staging too. Is there a way to make indexes permanent? Or does Firestore require users to add them every few days..?

Cloud SQL service account connection limit

Tue, 29 Oct 2024 17:31:05 GMT

Despite hikari cp steps limits are in place. Is there a way to limit the google service account IAM connection to google cloudsql(consumer) in postgres ? KCC env; we are trying to consume external database resources

How to create PSC endpoint against Memory Store (Redis cluster)

Tue, 29 Oct 2024 09:45:30 GMT

Hi,

We are trying to use Memory store (redis cluster). But we are not able to setup PSC endpoint against it.

Can you please guide me with the steps for the same? We are not able to see PSC attachment as well in the Memstore cluster. But if I go to Private service connect, it shows private service connect against the private ips. Being managed service, we prefer to use PSC only and not limit us in VPC peering etc.

Thanks & Regards

Amit

Connecting DAG to MSSQL Data base in another Project

Fri, 25 Oct 2024 10:00:23 GMT

We have Created a Public Cloud Composer Environment in Project1 and Written Some DAGs.
In One of the DAG we want to connect to a MSSQL - Database . The Database Needs the Client Ip whitelisted before connecting .
Both the database and composer environment are in default networks of their respective projects.
We are able to connect another databases which are in the same project using dags.
What have we Tried

whitelisting the Public IP of the GKE did not work.
whitelisting Ip range of the GKE pods did not work.
whitelisted the Ip of the default NAT connection also .

We are not able to understand how to Connect to the database.

Unable to run pg_repack due to superuser error

Thu, 24 Oct 2024 17:51:07 GMT

Hello, I am trying to run `pg_repack` on one of my databases, but I'm stuck by this error message I get when trying to run the extension.

```

pg_repack --dry-run -h xxx.xxx.xx.xx -p 5432 -U postgres -d db_name -t table_name
INFO: Dry run enabled, not executing repack
Password:
ERROR: pg_repack failed with error: You must be a superuser to use pg_repack

```

I followed the guide here https://cloud.google.com/sql/docs/postgres/extensions#pg_repack on how to setup and run the extensions. The user I'm using has the cloudsqlsuperuser role and as far as I can tell has the correct permissions for the database in question too. I'm at a loss on what triage or debugging steps to take next.

For additional context I recently upgraded this db to PGv16.4. I am using pg_repack v 1.5.0 on my client, and in the database.

DMS large database

Tue, 22 Oct 2024 13:14:55 GMT

Hi, I'm starting with a migration of a large database from onpremise postgresql to SQL Cloud

I've migrated successfully our internal tests database, but now we are in the challenge to migrate production database

the size is approx 800gb, and here my questions

Is supportable this size? any concern?

where is located the dump file in the source machine, because I need to consider it on the disks size.

Any help will we highly appreciated

Questions about migrating from PostgeSQL to Spanner and how geo-partitioning work.

Mon, 21 Oct 2024 03:32:58 GMT

I'm looking to migrate a PostgreSQL database to Spanner to make use of the geo-partitioning feature but there are a few things I'm unsure about. Can someone help me with these questions please:

1/ How should I handle "special" data types like Postgis `geography`? What will the migration tool (https://github.com/GoogleCloudPlatform/spanner-migration-tool) turn them into?

2/ Can I move a row between geo-partitions?

3/ Does full text search work with geo-partitions?

4/ If I have a query like `SELECT * FROM table WHERE some_id = 1` and the `some_id` column is indexed. Does Spanner have to go through all geo-partitions to find it? Or it has a way to know which partition to look for?

Can't connect via cloudsqlproxy with new SQL instance without whitelisting IP

Fri, 18 Oct 2024 07:46:06 GMT

Hey there,

After years of using CloudSQL for MySQL with the cloudsqlproxy with several SQL instances I created a new SQL instance and suddenly faced the problem that I can't connect to the instance with a running cloudsqlproxy.

After a while I found out that I can connect when I whitelist my IP, which I never needed to do before in other instances. The GCP console in SQL->Connections clearly states "an authorised network OR the Cloud SQL Proxy" (see image attached below).

How do I connect?

- Install cloudsqlproxy on OS X via brew (latest version)

- cloudsqlproxy --projects=... -dir=<localdir>

- connect with my MySQL tool of choice using the unix socket path

How does the issue show up?

- I connect to the cloudsqlproxy successfully and the relevant unix socket path is printed out by cloudsqlproxy

- when connecting to the instance via my MySQL tool of choice it states that I have a username/password issue

- the cloudsql proxy shows a connection attempt

- I authorize my IP either by adding it to authorized networks manually or by doing `gcloud sql connect`

- I repeat connecting via my MySQL tool without disconnecting the cloudsqlproxy -> connection works

My CloudSQL MySQL 8.4 instance network setup:

- private IP & public IP (with default network)

- public IP without authorised networks (when I add my IP here it works, but it shouldn't be necessary)

- SSL encryption disabled because I am only using CloudSQL Proxy to connect

- private path disabled

Did anyone also have that issue or does anyone know how I can fix it? The big problem I face is that there are users who need to use the instance via the cloudsqlproxy which don't have a static IP.