Alloy DB : PSC - SQL Error [08006]: An I/O error occurred while sending to the backend.

[Dhanush_152343]

Hi Team,

We are in the process of migrating from DB2 to AlloyDB. We do have BI tools which connects to DB2 to fetch and display data in report.

While in POC, we tried to execute the same SQL query from local desktop (DBeaver) connecting to AlloyDB which results in Error " SQL Error [08006]: An I/O error occurred while sending to the backend"

Logs from GCP - [pqcomm.c:974] could not receive data from client: Connection reset by peer; (original error: Connection reset by peer)

Connection enabled through PSA and VPC peering. Can you please help on this issue?

CloudOps team mentions that "TCP Transitory Connection Idle Timeout is 30 seconds and cannot be configured". They suggest query to be tuned, but we do not have much option as BI tool would generate the query.

Thanks!

[-Rhett]

Hi @Dhanush_152343,

Welcome to the Google Cloud Community!

Possible reasons for this error message are:

• Network connectivity issues: The connection reset error may be caused by idle TCP connection timeouts in the GCP network layer.
• Query configuration: The BI tool generates complex or long-running queries, leading to the connection exceeding the 30-second idle timeout.
• Database connection settings: VPC peering and PSA configuration may not be optimized for the query patterns.

Here are a few troubleshooting steps or workarounds you can try:

• Query Optimization: Consider modifying the queries to reduce execution time if possible, even though the BI tool auto-generates them. View the Query Insights dashboard to help analyze and improve your queries.
• Check the postgresql.conf file: If you’re using Python for your query, selster1 posted this workaround on the DBeaver github.

If you need more info about database migration, you can check these references:

• Import your data for migration: You can manually import your files from a Google Cloud Storage bucket. AlloyDB supports CSV, DMP, and SQL file formats.
• Use Google Cloud Database Migration Service (DMS): If your database source is supported, you may use DMS to streamline networking workflows, manage the initial snapshot and ongoing replication, and provide a status of the migration operations.

I hope this helped!