Hello everyone,
I have been experiencing intermittent occurrences of the pg8000.exceptions.InterfaceError when trying to establish connections from Cloud Run to my Postgres Server, which is managed by Cloud SQL. These errors seem to crop up once or twice every 2-5 days. Notably, my setup operates on a public network without the use of a private VPC. My tech stack includes PostgreSQL 11 and Python 3.7.
Here's a snippet of the code responsible for managing our database connection pool, utilizing the Google connector:
class DatabaseConnection:
_instance = None
pool: Engine = None
def __new__(cls):
if cls._instance is None:
cls._instance = super(DatabaseConnection, cls).__new__(cls)
return cls._instance
@classmethod
def initialize_pool(cls):
if cls.pool is None:
try:
settings = Settings()
# Initialize Cloud SQL Connector
connector = Connector()
def getconn() -> pg8000.dbapi.Connection:
# Use the connector for Cloud SQL
return connector.connect(
settings.DB_CONNECTION_NAME,
"pg8000",
user=settings.DB_USER,
password=settings.DB_PASSWORD,
db=settings.DB_NAME,
ip_type=IPTypes.PUBLIC,
)
# Use 'creator' to integrate Cloud SQL Connector with SQLAlchemy
cls.pool = create_engine(
"postgresql+pg8000://",
creator=getconn,
pool_size=10,
max_overflow=1,
pool_timeout=30,
pool_recycle=1080,
pool_pre_ping=True,
)
print("Database pool initialized")
except Exception as e:
print("Failed to initialize database pool")
traceback.print_exc()
@classmethod
def dispose_pool(cls):
if cls.pool is not None:
cls.pool.dispose()
cls.pool = None
print("Database pool disposed")
def get_connection(self):
if self.pool:
return self.pool.connect()
raise SQLAlchemyError("Database pool not initialized")
Despite my best efforts, the randomness of this error has made it challenging to reproduce consistently, which, in turn, has made debugging efforts equally elusive.
I am reaching out to the community for any insights, suggestions, or similar experiences that might shed light on this issue. I am eager to resolve this matter promptly as it's impacting the reliability and performance of our application.
If anyone has encountered similar issues or has expertise in this area, your guidance and suggestions would be immensely appreciated. Additionally, if there are any specific details or logs you require to assist me further, please let me know.
Thank you all for your time and assistance.
LinkedIn
Twitter
