database connections Archives

Tech Optimizer

December 8, 2024

Project X #9 ~ Blend PostgreSQL with Python for Better Scalability

The text discusses the integration of Python and PostgreSQL for developing scalable applications, transitioning from SQLite to PostgreSQL. It highlights the use of the psycopg2 library for connecting Python to PostgreSQL and Postgres.app for setup. The project will cover establishing secure database connections, performing bulk inserts, and safely updating records using parameterized queries to prevent SQL injection. By the end of the project, participants will be able to create tables, insert data, execute queries, update records, and delete information while following best practices for database management. The project also includes implementing logging and managing environment variables for database credentials. A limited-time lifetime discount on annual subscriptions is mentioned, offering immediate access to content.

Tech Optimizer

October 2, 2024

How a large financial AWS customer implemented high availability and fast disaster recovery for Amazon Aurora PostgreSQL using Global Database and Amazon RDS Proxy | Amazon Web Services

A financial institution collaborated with AWS to create a solution that enables sub-minute failover between Availability Zones and single-digit minute recovery times across AWS Regions for their wealth management customer portal. The solution utilizes automation for failure detection and failover, along with AWS-managed data replication, specifically employing the Amazon Aurora PostgreSQL-Compatible Edition and Amazon Aurora Global Database for cross-Region replication. Key components include canary outage detection via AWS Lambda, DNS redirection through Amazon Route 53, and control plane resilience using the Amazon Route 53 Application Recovery Controller. The architecture is based on a three-tier model, with in-Region failovers expected to occur in seconds and cross-Region recoveries within minutes. The architecture team aimed to reduce the Recovery Time Objective (RTO) from tens of minutes to seconds and established a Recovery Point Objective (RPO) of under one minute. The failover process prioritizes speed, opting for immediate failover rather than controlled switchover. Testing confirmed that in-Region failover resulted in minimal disruption, while cross-Region failover could be completed in single-digit minutes. Recent enhancements to Amazon RDS Proxy and Aurora PostgreSQL have improved the architecture's efficiency and user experience.

Tech Optimizer

October 2, 2024

How BCM One migrated data from an unencrypted Amazon RDS for PostgreSQL database instance to a new encrypted instance using AWS DMS | Amazon Web Services

BCM One is a global provider of NextGen Communications and Managed Services, with Flowroute as a brand specializing in SIP trunking and business messaging. Flowroute encrypted its Amazon RDS for PostgreSQL database instances using AWS DMS. Storage encryption is essential for protecting disk data and involves a key from AWS KMS. Existing RDS instances cannot be directly encrypted, so a new encrypted instance must be created using RDS snapshots and AWS DMS. The migration strategy includes creating an empty RDS instance, initiating a CDC-only AWS DMS task, creating a snapshot of the unencrypted instance, generating an encrypted snapshot, and restoring a new encrypted RDS instance. RDS Proxy is used to manage connections and minimize downtime during the transition. The process involves creating a DMS login role, configuring RDS parameters, creating DMS endpoints, and verifying replication success. After the migration, the proxy authentication must be updated to connect to the encrypted database. The migration process also highlights the importance of primary keys and unique constraints to prevent duplicate entries during replication.

Tech Optimizer

July 5, 2024

New – Rightsizing Recommendations for Amazon RDS MySQL and RDS PostgreSQL in AWS Compute Optimizer | Amazon Web Services

AWS Compute Optimizer now delivers recommendations for Amazon RDS MySQL and PostgreSQL databases to help detect idle instances, identify optimal instance types, and provisioned IOPS settings.

Tech Optimizer

July 5, 2024

Scaling PostgreSQL Connections with PgBouncer

- PostgreSQL's performance can degrade with an excessive number of concurrent connections. - Connection pooling solutions like PgBouncer are essential for high-traffic environments. - PgBouncer reduces the overhead of establishing and closing connections by reusing a pool of established connections. - It is recommended to set the maximum connections lower than 10 per CPU core, with a maximum of 20 per CPU core as the limit.